Indexes evaluation
-
I'm reviewing a long running process which involves the load of a table that has over 40 million rows. This table has 8 indexes, 1 clustered and 7 non-clustered. The non clustered indexes are dropped every day and recreated after the rows are loaded into the table. All of our processes run in a nightly batch so during the day the index usage should remain the same.
I ran this query and got the following results:
SELECT CAST( LEFT( I.Name, CHARINDEX( '_', I.Name ))
+ CAST( I.Index_Id AS char( 2 )) AS char(10)) Name,
CAST( I.Type_Desc AS CHAR(12)) Type,
COUNT(*) columns,
U.User_Seeks,
U.User_Scans,
U.User_Lookups,
U.User_Updates,
U.Last_User_Seek,
U.Last_User_Scan,
U.Last_User_Lookup,
U.Last_User_Update,
U.System_Seeks,
U.System_Scans,
U.System_Lookups,
U.System_Updates,
U.Last_System_Seek,
U.Last_System_Scan,
U.Last_System_Lookup,
U.Last_System_Update
FROM Sys.Dm_Db_Index_Usage_Stats U
JOIN Sys.Indexes I ON U.Index_Id = I.Index_Id AND U.Object_Id = I.Object_Id
JOIN sys.index_columns c ON i.index_id = c.index_id AND i.object_id = c.object_id
WHERE U.Object_Id = 1998018249
GROUP BY CAST( LEFT( I.Name, CHARINDEX( '_', I.Name ))
+ CAST( I.Index_Id AS char( 2 )) AS char(10)) ,
CAST( I.Type_Desc AS CHAR(12)) ,
U.User_Seeks,
U.User_Scans,
U.User_Lookups,
U.User_Updates,
U.Last_User_Seek,
U.Last_User_Scan,
U.Last_User_Lookup,
U.Last_User_Update,
U.System_Seeks,
U.System_Scans,
U.System_Lookups,
U.System_Updates,
U.Last_System_Seek,
U.Last_System_Scan,
U.Last_System_Lookup,
U.Last_System_Update;
Name Type columns User_Seeks User_Scans User_Lookups User_Updates Last_User_Seek Last_User_Scan Last_User_Lookup Last_User_Update System_Seeks System_Scans System_Lookups System_Updates Last_System_Seek Last_System_Scan Last_System_Lookup Last_System_Update
---------- ------------ ----------- -------------------- -------------------- -------------------- -------------------- ----------------------- ----------------------- ----------------------- ----------------------- -------------------- -------------------- -------------------- -------------------- ----------------------- ----------------------- ----------------------- -----------------------
INDEX_76 NONCLUSTERED 1 0 0 0 1 NULL NULL NULL 2014-12-11 02:41:39.640 0 0 0 0 NULL NULL NULL NULL
Index_77 NONCLUSTERED 1 445 0 0 1 2014-12-11 11:55:16.390 NULL NULL 2014-12-11 02:41:39.640 0 0 0 0 NULL NULL NULL NULL
Index_78 NONCLUSTERED 1 464 1 0 1 2014-12-11 11:55:16.390 2014-12-11 02:53:20.880 NULL 2014-12-11 02:41:39.640 0 0 0 0 NULL NULL NULL NULL
IX_75 NONCLUSTERED 4 0 0 0 1 NULL NULL NULL 2014-12-11 02:41:39.640 0 0 0 0 NULL NULL NULL NULL
IX_79 NONCLUSTERED 4 0 0 0 1 NULL NULL NULL 2014-12-11 02:41:39.640 0 0 0 0 NULL NULL NULL NULL
ix_80 NONCLUSTERED 1 1 1 0 1 2014-12-11 05:58:39.037 2014-12-11 02:41:39.640 NULL 2014-12-11 02:41:39.640 0 0 0 0 NULL NULL NULL NULL
IX_81 NONCLUSTERED 1 0 0 0 1 NULL NULL NULL 2014-12-11 02:41:39.640 0 0 0 0 NULL NULL NULL NULL
PK_1 CLUSTERED 3 6967 9650 134126 61803204 2014-12-11 09:10:02.913 2014-12-11 09:20:18.940 2014-12-11 11:55:16.390 2014-12-11 02:41:39.640 0 2338 0 0 NULL 2014-12-11 02:39:53.100 NULL NULL
Am I correct on assuming that 4 indexes haven't been used? Should I look somewhere else?
I'm still evaluating what's using the indexes and which ones, I just thought on asking if I was taking the correct road.
-
That's the way I'd interpret the data, except the fact that the clustered index has a bunch of lookups and there aren't a matching number of non-clustered index seeks, because I'm not aware how there'd be a lookup without a seek somewhere else.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
It looks like it's using the clustered index almost exclusively for all data access. I don't know if that's by design or if you thought it would use the other indexes and do lookups. Do the other indexes have included columns. Are your queries returning most of the columns from the table.
Tom
-
So looking at this I see you're doing more scans than seeks on your PK. That would make me either revisit my cluster or some of my queries.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Thanks to all of you.
I just re-read all of the posts and Jack's comment puzzles me. I'll try to find out and if I do, I'll come back.
This table is used as source for several fact tables and most of the time it's used entirely which should be the reason for having more scans than seeks on the clustered index (unless my reasoning is wrong). And yes, most columns are used in the queries, so indexes (even covering indexes) might be too large to be practical.
-
Luis Cazares (1/2/2015)
Thanks to all of you.I just re-read all of the posts and Jack's comment puzzles me. I'll try to find out and if I do, I'll come back.
This table is used as source for several fact tables and most of the time it's used entirely which should be the reason for having more scans than seeks on the clustered index (unless my reasoning is wrong). And yes, most columns are used in the queries, so indexes (even covering indexes) might be too large to be practical.
I'm not sure what you mean by puzzles you. Basically the data shows over 130k user lookups on the clustered index, and I usually see an equal or greater number of user seeks on the non-clustered indexes and I don't see that here. The user lookups on the clustered index are the key/bookmark lookups caused by non-covering non-clustered indexes. That could be explained by indexes being dropped and new ones being created.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply