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.