Index usage stats

Question

Post reply

Index usage stats

rpatil22

Ten Centuries

Points: 1371
More actions
October 27, 2009 at 7:55 am

#227429

I am tuning a proc and ran it in tuning advisor.
It has recommanded to create 1 indexes and 3 statistics.
say index recommanded
IX5 - on col1,col2,col6
However I checked that table and found that there are already 4 indexes
IX1 - on col1,col2,col4
IX2 - on col4,col7,col8
IX3 - on col8,col3,col4
IX4 - on col10,col12,col14
How to analysze index usage report?"

Viewing 4 posts - 1 through 3 (of 3 total)

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

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

Here is a script I had for index usage. Not sure where it came from but it looks like it returns accurate info.

/*

important info are the user_ columns

low number of all columns means the index isnt being used

if @totalUses > 0 is set for @totalUses this will be used as a maximum

for total uses of the index. This allows you to see seldom used indexes.

*/

DECLARE @totalUses INT

SET @totalUses = 0-- Set to 0 to get all results

SELECT (us.user_seeks+us.user_scans+us.user_lookups+us.user_updates) AS totalUses,

OBJECT_NAME(I.OBJECT_ID) AS tableName, I.[name] AS indexName, I.type_desc AS indexType,

US.user_seeks, US.user_scans, US.User_lookups, US.user_updates, US.last_user_seek,

US.last_user_scan, US.last_user_lookup, US.last_user_update

FROM sys.indexes I

inner JOIN sys.objects O ON (I.OBJECT_ID = O.OBJECT_ID)

LEFT JOIN sys.dm_db_index_usage_stats US ON (US.OBJECT_ID = I.OBJECT_ID AND US.index_id = I.index_id AND database_id = DB_ID())

WHERE ((us.user_seeks+us.user_scans+us.user_lookups+us.user_updates) < @totalUses OR @totalUses = 0)

AND I.[name] IS NOT null

AND O.[type] in ('U','V')

--AND O.object_id = object_id('Person.Address') --uncomment to get 1 table

ORDER BY 1

There is also a good article from Gail Shaw on this site. Here[/url]

Or at sqlserverpedia.com

Here

rpatil22 Ten Centuries Points: 1371 More actions · Answer 2

I ran usage query and found that IX1 total uses is 26

while others are used for 1200+

However since tuning wizard recommanded to create

IX5 - on col1,col2,col6

and we have IX1 - on col1,col2,col4

can we modify IX1 TO on col1,col2,col4,col6 ?

( adding a another column col6 in existing index)

matt6288 SSCertifiable Points: 5058 More actions · Answer 3

Sorry about not getting back on this thread. I don't know if I overlooked the email or what. Anyway, check out the links in my previous post about indexes. It sounds like the current index isn't used to often but the only way to really tell what the effect will be is to test it. Don't modify/drop/create the index in production but do it in a development environment and see how performance goes.