Unused Indexes Size

Today I had to quickly find which indexes are not used on a database and the size of each one. Because of the hurry, I just googled a little and found two scripts with parts of the solution. I simply joined both scripts in a single one, and here you have it.

This script will give you the size of the unused indexes larger than 0 pages and not starting with he “PK” string (we can’t drop Primary Keys to gain space, even when not in use). Please don’t mind the bad spacing. Links to original scripts in the commentary.

/* Unused Indexes Size
https://thelonelydba.wordpress.com
based on scripts from:
 http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database
 http://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server
*/
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
   INDEXNAME = I.NAME, I.INDEX_ID,
   (s.used_page_count) * 8 AS IndexSizeKB
FROM SYS.INDEXES I JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
join sys.dm_db_partition_stats AS s ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.INDEX_ID NOT IN
   (SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S
    WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID
    AND DATABASE_ID = @dbid)
and left(i.name,2) <> 'PK'  and s.used_page_count <> 0
ORDER BY s.used_page_count desc, OBJECTNAME, I.INDEX_ID, INDEXNAME
GO
Advertisements

About mauriciorpp

Hi, I am Piccolo - but not the one from Dragon Ball. I'm from a highly competitive and fast-paced world too, the IT industry, and this space will be used to share some challenges I face daily on my career. As you will see, I don't do brawls but I need to be a fighter! Stay tuned.
This entry was posted in SQLServer and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s