May 8, 2012 at 2:44 pm
We have a a very limited window as to when we can have any downtime on our databases. I want to monitor our index fragmentation so I can determine how often I need to reindex each database( I have over 5000 databases).
I have created the following query which then gets inserted into a table.
SELECT a.index_id, b.NAME AS IndexName, avg_fragmentation_in_percent, a.page_count, o.name AS TableName,'''+@DatabaseName+'''
FROM sys.dm_db_index_physical_stats (DB_ID(),NULL,
NULL, NULL, ''Limited'')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
INNER JOIN sys.objects AS o ON b.object_id=o.object_id
WHERE avg_fragmentation_in_percent>0 AND a.page_count>=100 And a.Index_id>0
ORDER BY a.avg_fragmentation_in_percent DESC
Can someone tell me if selecting agains sys.objects and sys.indexes locks the tables? Do I need to worry about blocking while this is running? I haven't seen any so my first thought is no. But I have only been running this in development .
May 8, 2012 at 4:29 pm
You're questions are always interesting pamozer!
Can someone tell me if selecting agains sys.objects and sys.indexes locks the tables?
Which tables? sys.objects and sys.indexes are views. Are you wondering about the system tables underlying these views, or the user tables they contain metadata for?
Do I need to worry about blocking while this is running?
Not directly, but indirectly. sys.dm_db_index_physical_stats is very resource-hungry and the stress it adds to your I/O subsystem can create blocking via a general slowdown on your system.
Intent Shared (IS) locks will be taken all around as your query scans each index. I am not aware of any blocking that IS locks might create unless something in your user database is trying to create new objects or indexes during this time (except as noted in BOL in SQL 2012 when using AlwaysOn availability groups, but that's probably not relevant here).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 8, 2012 at 4:33 pm
I'm glad you like my questions. I have never worked in an environment like this before so I am trying to gather as much information as I can so I can implement things properly without causing more stress on the system.
I guess my question is more in general with the sql statement above. And what kind of problems it can cause. I wanted to try to get an idea on each of our databases how quickly the indexes become fragmented by tracking the fragmentation.
May 8, 2012 at 4:46 pm
In general then, sys.dm_db_index_physical_stats should be used only during times of otherwise low system usage.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 8, 2012 at 4:48 pm
Ok Thanks. I do appreciate all your help. And that view is the best way to monitor indexes right?
May 8, 2012 at 4:54 pm
sys.dm_db_index_physical_stats, yes. If you dig into Ola's process you'll see that's what he uses, and it the primary data source available for index stats. To pick a nit, it is a function though, not a view 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply