4

The sys.dm_db_index_usage_stats DMV gives information about number of seeks and scans against a index in sql server

I have a large number of composite index that includes multiple columns. I suspect that these indexes cause a lot of maintenance overhead ann would like to narrow the number of columns.

Is there a way we can find out seeks and scans against a indivudual columns in a composite index.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Devanathan A
  • 53
  • 6

1 Answers1

3

SQL Server does not implement SKIP SCAN, so a seek over a composite index always includes leftmost (leading) columns of the index.

That is, if you have an index on (col1, col2), the index seek may be used for searching for col1 or col1 and col2, but not for col2 alone.

If you search for all of these columns, you will most probably benefit from using the index.

What is the "maintenance overhead" you mentioned and how does it differ between single-column and multiple-column indexes?

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Thanks for the input.. it helps.. I have a composite index with file columns like (col1,col2, col3,col4,col5) etc... Can we find how many times all five columns were used in the seek.. how many seeks were against 4 columsn etc.. Again thanks for the input – Devanathan A Dec 23 '09 at 21:05
  • `@Dave`: no, you can't tell it from the index usage stats. This is best done by looking at your query execution plans. – Quassnoi Dec 23 '09 at 21:08
  • Thanks Quassnoi!.. i need to pull some metrics to on the composite index usage in the server.. to justify thet they need to be narrowed down. I guess that are used my multiple stored procedure in the application. – Devanathan A Dec 23 '09 at 21:28
  • The Microsoft Connect item about the skip scan: https://connect.microsoft.com/SQLServer/feedback/details/695044/implement-index-skip-scan – usr Oct 15 '11 at 14:34