October 27, 2009 at 7:55 am
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?"
October 27, 2009 at 8:14 am
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
October 27, 2009 at 9:14 am
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)
October 29, 2009 at 8:52 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply