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)
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.
+1 - but I wish it gave you 11 rep because then your rep number would be palindromic. God I'm sad!
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.
I was thinking the same thing when I hit 2112 - OK, I'm a Rush fan too.
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
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.
