Question about dm_db_index_operational_stats

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 2005 Forums

SQL Server Administration (2005)

Question about dm_db_index_operational_stats

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-12-08 : 18:27:53

When I run this query...


select object_name(os.object_id) as 'Table Name', i.name as 'Index Name',*
from sys.dm_db_index_operational_stats(db_id(), object_id('Activity'),NULL,NULL) os,
sys.indexes i
where os.index_id=i.index_id
and i.object_id=os.object_id
order by os.index_id

I am returned five records representing the five indexes on the "Activity" table. Two of the indexes have zero range_scan_count and zero singleton_lookup_count. Does that mean that they are not being used and should be deleted?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-08 : 18:35:44

Here's what I use to determine what can be deleted: http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

Be very careful dropping indexes. When I dropped a bunch in production a few months ago, I greatly impacted a process that runs infrequently. The DMV query showed reads were very slow, so I dropped some indexes. When the infrequent query ran, it impacted the entire system since it was doing a table scan on a large table. Now I only drop indexes where reads equals 0.

Make sure that you run the DMV queries after SQL Server has been up and running for a while as the data gets wiped out on restart. If you were to run it right after a restart, you would probably find that many indexes weren't in use. But if you waited a few days, the report would drastically change.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-12-08 : 18:49:48

That's an awesome query! Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-08 : 21:48:17

You're welcome. I can't take credit for writing it, but I bookmarked it!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources