question

Cyborg avatar image
Cyborg asked

SQL Server 2000 DMV

How can we monitor the index usage stats in SQL Server 2000? (Starting from SQL 9 we have DMVs are available for monitoring Server state health and diagnose the problem, but earlier to SQL Server 2005 it was not there)
dmv
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
No such thing as a DMV in SQL Server 2000. And no easy way to monitor index usage that I've ever seen. Best method I can think of, and not one I would recommend, would be to capture execution plans using a trace event. Then run xqueries against them to identify index usage. It would be extremely ugly and would cuase problems on your server. I would not do this, but it would get you the information you want.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 - but I wish it gave you 11 rep because then your rep number would be palindromic. God I'm sad!
0 Likes 0 ·
It was that way for a little while yesterday. I felt like locking it so no more points would come in. I'm sad too.
0 Likes 0 ·
I was thinking the same thing when I hit 2112 - OK, I'm a Rush fan too.
0 Likes 0 ·
DaniSQL avatar image
DaniSQL answered
You can use [DBCC SHOW_STATISTICS][1] to check the selectivity of an index and [Index Tuning Wizard SQL Server 2000][2] to achieve optimal indexing. But dont depend on it too much. EDIT: Also you can use [DBCC SHOWCONTIG][3] to identify fragmented indexes. [1]: http://technet.microsoft.com/en-us/library/aa258821(SQL.80).aspx [2]: http://technet.microsoft.com/en-us/library/cc966541.aspx [3]: http://technet.microsoft.com/en-us/library/cc966523.aspx#EEAA
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

But that just shows information about the index, not whether or not the index is being used and how it's being used. That information just isn't there in 2000.
3 Likes 3 ·
True..but i am throwing out there the tools you work with indexes in SQL Server 2000.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.