January 18, 2012 at 10:05 pm
I need a query for checking fragmentation level using
SELECT
avg_fragmentation_in_percent
,page_count
FROM sys.dm_db_index_physical_stats (DB_ID('Northwind'),Null,NULL,NULL,NULL)
where avg_fragmentation_in_percent < 80
My concern is I need to reindex only those tables from the Northwind database whose fragmentation percent is less than 80 %.
Sagar Sonawane
** Every DBA has his day!!:cool:
January 18, 2012 at 10:13 pm
You only want to reindex tables less than 80% fragmented? What about those that are higher?
Honestly, rather then roll your own I would recommend checking out Michelle Ufford's reindexing script which is fully customizable and will take all the hard work out of it for you. You can find it over at her blog www.sqlfool.com
January 19, 2012 at 11:06 am
Assuming that this is SQL Server 2005 or 2008, use the following query and run it against the database
Use yourDBname
SELECT ps.page_count, ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ps.avg_fragmentation_in_percent > 10 and ps.page_count > 200
ORDER BY ps.avg_fragmentation_in_percent desc
GO
This will not show fragmentation for indexes that are less than 200 pages in size. In my experience there is not much to gain by defragmenting indexes that are less than 200 pages. I have even heard of people ignoring indexes that are less than 1000 pages.
Reorg the indexes where fragmentation is between 10 and 30 %
Rebuild the indexes where fragmentation is greater than 30%
I have even see that rebuilding is a better option when pages > 10000 if the frag is greater than 10%
Blog
http://saveadba.blogspot.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply