Any query for selecting unwanted indexes ?

Question

Post reply

Any query for selecting unwanted indexes ?

ravi@sql

SSCrazy

Points: 2121
More actions
December 15, 2011 at 2:12 am

#264864

Hi all,
Am working on my company product .
In our data base from long back my seniors created many indexes on many tables.
IS there any query find out un wanted index and duplicate index and un used index ?
And i wanted to know size of index , b'coz it should not cross 900 bytes.
so that i can clean my data base. and i can boost the performance !!!!
Please help me regarding this.
Regards,
Ravi.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TedT Right there with Babe Points: 796 More actions · Answer 1

Try looking at sys.dm_db_index_usage_stats

Although a word of warning - although an index may not be used 'frequently', it doesnt mean that it's not valid!!

Do you have the data model of your database? This might be a good start to identifying where indexing is required, and for what purpose.

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Cleaning up unused indexes is an OK use for your time, but, you need to be aware, sys.dm_db_index_usage_stats only records information since the last time the server was restarted (and several other events) so while it will show you indexes that have not been used, you need to be very aware of the time frame under which it's operating. You might see an index that hasn't been accessed for three weeks, but that's because it's an index for the monthly report that the boss runs or something along those lines. You can't simply run a script, identify & drop indexes. You need to test each drop to validate it doesn't adversely affect the system. You need to have monitoring in place on the system so you can see if dropping an index has adversely affected the system.

Also, if you're really concerned with excessive indexes, you should be monitoring your inserts & updates to see if they are slowing down because of indexes. Reads won't be affected by having too many indexes to choose from.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

http://www.youtube.com/watch?v=RZ4YTg4S7HI&feature=plpp_video

http://www.youtube.com/watch?v=Kcnj5bQghjY&feature=plpp_video

http://www.youtube.com/watch?v=XmE_Utp8CY0&feature=plpp_video

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 4

Reply to your first que to find out unused index.

found from Technet library

you can try this

SELECT SCH.name + '.' + OBJ.name AS ObjectName

,OBJ.type_desc AS ObjectType

,IDX.name AS IndexName

,IDX.type_desc AS IndexType

FROM sys.indexes AS IDX

LEFT JOIN sys.dm_db_index_usage_stats AS IUS

ON IUS.index_id = IDX.index_id

AND IUS.object_id = IDX.object_id

INNER JOIN sys.objects AS OBJ

ON IDX.object_id = OBJ.object_id

INNER JOIN sys.schemas AS SCH

ON OBJ.schema_id = SCH.schema_id

WHERE OBJ.is_ms_shipped = 0

AND OBJ.type IN ('U', 'V')

AND IDX.type > 0

AND IDX.is_disabled = 0

AND IDX.is_primary_key = 0

AND IDX.is_unique = 0

AND IUS.object_id IS NULL

ORDER BY ObjectName

,IndexName;

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 5

To know the size of Index you can try this for particular Database

SELECT

i.name AS IndexName,

SUM(page_count * 8) AS IndexSizeKB

FROM sys.dm_db_index_physical_stats(

db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s

JOIN sys.indexes AS i

ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

GROUP BY i.name

ORDER BY i.name

ravi@sql SSCrazy Points: 2121 More actions · Answer 6

Thank you very much all for your valuble suggestions and solutions 🙂 .

Regards,

Ravi.

RTaylor2208 SSChampion Points: 13298 More actions · Answer 7

Be carefull using the example above which usese the "detailed" option. As this looks at each leaf node perfromance can be incredibly slow. Changing this to simple whilst less accurate will return results much faster and use less resources.

MCITP SQL 2005, MCSA SQL 2012