Category Archives: Administration

When sys.dm_db_index_usage_stats can be misleading

A while ago, I was looking at a particular index which was badly fragmented in development and I decided the best course of action was to rebuild the index. When I then ran my index fragmentation & usage query, the data looked odd. The fragmentation was gone, but the numbers didn’t seem right. It was then I noticed a really low number of index seeks being returned.
Continue reading

Trace file name ” is invalid

A colleague was trying to restore our production database’s to an internal server for this week and noted that the process failed due to a lack of storage. ┬áIn particular, one database seemed to have grown significantly since the last time we had done this (bi-weekly process). A quick look at the default trace, in particular, the Data File Auto Grow event would reveal all…or would it…?

Continue reading

Index Analysis

Do you often run a few variations of index queries against your SQL Server instance and look at multiple resultsets? Once upon a time, I did too. Now I rely on the query in this post. It’s not a finished product and I’ll no doubt revisit and add a few more columns and/or logic over time. However, it’s great for pulling back index keys, include columns, fragmentation, scans/seeks and so forth.
Continue reading