November 7, 2009 at 3:05 am
Hi,
We have SQL Server 2005 EE 62 bit with SP3. I'm trying to find out how our production database indexes are getting fragmented. Until now I'm using the Maintenance Plan Index rebuild task to rebuild all indexes for all databases on weekly basis. But, reading from SSC forums & Articles I came to know that first we need to find out what database,what tables & what indexes are getting fragmented and based on that we can use better scripts to rebuild/reorganize indexes instead of blindly using Maintenance Plan Index rebuild task.
I have executed the below query to check the Index fragmentation:
SELECT avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent>30 AND page_count>1000
Results:
avg_fragmentation_in_percent page_count
92.4914675767918 2292
94.9044585987261 2480
98.8165680473373 1318
97.7777777777778 1028
44.8773448773449 16612
And then I used the below Script(found in BOL), to defragment the Index
-- Ensure a USE statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
After running the above script, I ran the below query to make sure the Indexes were defragmented. But I'm getting the same results as before running the Above Index defragment script
SELECT avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent>30 AND page_count>1000
Results:
avg_fragmentation_in_percent page_count
92.4914675767918 2292
94.9044585987261 2480
98.8165680473373 1318
97.7777777777778 1028
44.8773448773449 16612
Why after running the Index defragment script, the indexes were NOT defragmented?
Please advice me?
many thanks
November 7, 2009 at 4:06 am
Are the tables in question heaps? (no clustered index)
The script explicitly excludes heaps. The only way to defragment a heap is to create a clustered index on it, and then drop it again 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 7, 2009 at 5:26 am
I got the same thing you did after executing your query script in my AdventureWorks2008 db before and after executing your indexing script and I also got the same thing after running a DBCC DBREINDEX() on all the tables as well and I know that works. I also ran DBCC SHOWCONTIG() on all tables as well after your indexing and the scan density on all tables is at about 100%(which means the tables are no longer fragged), so I think that percentage column is 'possibly' reporting something different to you and that is confusing you thinking that there is still fragmentation after indexing when there really isn't. I have heard that complaint many times about the sys.dm_db_index_physical_stats function, but that could be a simple misunderstanding based on a lot of other things.. Updating the stats doesn't change it either. Heaps and mixed extents do play into it though as was mentioned before but I doubt all your tables fit into that category. anyway. However, that avg_fragmentation_in_percent column does NOT report scan density, which is the real important thing as far as I am concerned. It reports Logical Scan Fragmentation and that could still not change much even after indexing, if those tables do not have a lot of records in them to begin with. Making it not as meaningful, I never look at that column because it is irrelevant for small b-trees. So, you might want to check that first. ScanDensiity and the ratio between extent switches and total extents scanned are much more important to track IMHO in determining fragmentation.... 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 7, 2009 at 6:37 am
Online rebuilds generally result in greater residual fragmentation than offline rebuilds. For the 'best possible' results REBUILD offline, with MAXDOP = 1, and SORT_IN_TEMPDB = ON. Also make sure the database has sufficient free space for the process to use as workspace.
There is really no question of the DMVs reporting incorrect information - there are just a number of details to be accounted for. The DBCC commands are deprecated of course, but call the same internal routines, so it's not really possible for that to make a difference.
REORGANIZE never allocates new pages (aside from one use for temporary storage), so it is limited in how much it can reduce fragmentation. Change the script to always REBUILD if this concerns you.
REBUILDing all indexes in AdventureWorks with the options specified about resulted in only one table with fragmentation - DatabaseLog, which is a heap. There are also two nonclustered indexes with a small amount of residual 'fragmentation':
[IX_Address_StateProvinceID] ON [Person].[Address]
[IX_Customer_TerritoryID] ON [Sales].[Customer]
Looking at these with DBCC EXTENTINFO reveals that they are both still using a couple of pages from mixed extents.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 7, 2009 at 7:03 am
Then please let me rephrase that. The logical fragmentation column percentage in that dm function is not incorrect as much as it is irrelevant for small b-tress (small tables). Use scan density and extent switch ratios to get a better idea of realistic fragmentation for all tables. I think he is looking at something after indexing that is correct, but is misleading him to think he has still has fragmentation when he really no longer does. That was my overall point..:) I totally agree that the AdventureWorks2008 database is NOT fragmented to begin with, which was my whole point. But that dm function column still shows a high percentage for most of the tables both before and after indexing, and that is confusing for him in his db I believe...
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 7, 2009 at 7:29 am
I've just noticed that his original query specified a NULL for the scanning mode!
I would like to see results from:
select ips.avg_fragmentation_in_percent,
ips.page_count,
object_name(ips.object_id),
si.name,
ips.index_type_desc,
ips.fragment_count,
ips.page_count,
ips.record_count
from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
join sys.indexes si on si.object_id = ips.object_id and si.index_id = ips.index_id
where page_count>=1000
and index_level = 0
and avg_fragmentation_in_percent > 0;
There are a number of subtleties involved, including some reasons why the DMV will report more accurate results than SHOWCONTIG, so I would also encourage the OP to read the notes at: http://msdn.microsoft.com/en-us/library/ms188917.aspx (the sys.dm_db_index_physical_stats entry).
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 7, 2009 at 8:05 am
Very good point Paul! The three queries (yours, his singleton query, his query inside his index script which BTW he got out of BOL :)) all return different values in that column with yours NOW returning the most accurate percentage in that column (avg_fragmentation_in_percent) post indexing (less than 1%). No Logical Scan fragmentation. That DMV is as you say a little touchy depending on what you feed to it. Good to know...:) Travis.
--Pauls amended query
select ips.avg_fragmentation_in_percent,
ips.page_count,
object_name(ips.object_id),
si.name,
ips.index_type_desc,
ips.fragment_count,
ips.page_count,
ips.record_count
from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
join sys.indexes si on si.object_id = ips.object_id and si.index_id = ips.index_id
where page_count>=1000
and index_level = 0
and avg_fragmentation_in_percent > 0
GO
--Mani's singleton query
SELECT avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats (5, NULL, NULL, NULL, NULL)
where avg_fragmentation_in_percent>30
GO
--Mani's recordset query inside of his index script
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
GO
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
November 9, 2009 at 4:15 am
Also, keep in mind that shrinking your database can often defrag your indexes badly negating the benefits of rebuilding then, which is yet another reason you should never set AUTO_SHRINK option on for your databases. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply