Finding most commonly used indexes? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Finding most commonly used indexes?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-03-13 : 22:56:11

I'm working on reorganizing indexes so that the most commonly written-to indexes are on the fastest disks. I've got some of the obvious ones for my app, but beyond that, it seems like guesswork. I found this great article for SQL 2005, but our production app is still on SQL 2000.

Anyone know of a similar means to figure out what indexes are being used?

Thanks
-b

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-03-14 : 00:20:24

there is always the undocumented DBCC MEMUSAGE command.

You could run something like

Create table #memusage (dbid INT, objid INT, indexid INT, Buffers INT, Dirty INT)
insert into #memusage Exec ('dbcc memusage (IDS, 1000)')
select * from #memusage

My example uses a temp table, but you probably should use a permanent table, maybe with a datetime column. Another job could aggregate that information to a results table for you. You could create a table that contains all your indexes and then tally against them for each time an index showed up in the cache.

Keep in mind that this is an undocumented DBCC command. It has been unsupported since SQL 7. There is also a KB article saying that running DBCC MEMUSAGE can be very bad on a busy system. http://support.microsoft.com/kb/196629

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources