January 29, 2012 at 11:03 am
On adventureworks 08, when I run a query to see the fragmentation information. I got following result.
DBNAME: AdventureWorks
Tablename:ProductModelProductDescriptionCulture
Index Name:PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
Index Type: CLUSTERED INDEX
AVG_FRAGMENTATION_IN_PERCENT: 75
Page counts: 4
If we notice, AVG_FRAGMENTATION_IN_PERCENT is at 75. So, I though the fragmentation level is high. According to theory I read if the fragmentation is >30% we need to rebuild the index. So, I actually ran the following query, where it automatically reorganize and rebuild the index.
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_do2
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_do2;
-- 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 table #work_to_do2
But, Unfortunately I didn't see any change in fragmentation level. Can I know the reason why the fragmentation level doesn't changed.
In alternate to this I have tired to run the following query, but still NO CHANGE in fragmentation level
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)".
Can some one please let me know the reason of unchanged fragmentation. This is just an example of one table, but there are many tables in Adventureworks which has more fragmentation on it. Please, let me know. Thank you
January 29, 2012 at 11:18 am
DBA_SQL (1/29/2012)
On adventureworks 08, when I run a query to see the fragmentation information. I got following result.DBNAME: AdventureWorks
Tablename:ProductModelProductDescriptionCulture
Index Name:PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID
Index Type: CLUSTERED INDEX
AVG_FRAGMENTATION_IN_PERCENT: 75
Page counts: 4
There's your reason right there. 4 pages means that those pages are in a mixed extent and rebuilds won't change mixed extents to dedicated with that few pages in the table. Also the table is way, way too small to worry about fragmentation on. The general guideline on when to start worrying about fragmentation on an index is 1000 pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2012 at 1:29 pm
Understood. So how about if we have the following situations:
Index Type: Heap
Avg_Fragmentation_IN_Percent: 99
Page counts : 885
or
Index type: Clustered Index
Avg_Fragmentation_IN_Percent: 66
Page counts : 3.
If we have less page counts then do we not need to worry about fragmentation?
January 29, 2012 at 2:24 pm
The first I might be a little concerned, the second not at all.
Logical or extent fragmentation only affects large range scans from disk. Emphasis large. You can't have a large range scan on a table with 3 pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2012 at 3:49 pm
DBA_SQL (1/29/2012)
Understood. So how about if we have the following situations:Index Type: Heap
Avg_Fragmentation_IN_Percent: 99
Page counts : 885
heaps by their very nature are fragmented, when pulling index frags ignore index ids of 0
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2012 at 4:12 pm
The fragmentation values for a heap are extent fragmentation, not logical fragmentation (heaps can never be logically fragmented). It can still be something to be aware of, though it's not easy to fix.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2012 at 5:22 pm
GilaMonster (1/29/2012)
The fragmentation values for a heap are extent fragmentation
funny, thats exactly what it says in BOL. Either way it's not relative to an index maintenance operation, so filter it out
GilaMonster (1/29/2012)
though it's not easy to fix.
i'd be interested to hear your fix!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2012 at 7:08 pm
The good thing what I noticed on my DB is it actually happening on most of temporary tables. But, for time being it can be ignored. But, if it happens for any used tables then do we have any steps to resolve. I am the only DBA working, so I do not have much support to work on issues. So, provided possible guidance would be appreciable. Thank you
January 29, 2012 at 11:24 pm
In my opinion there is no reason to worry about indexes that are less than 500 or even 1000 pages. On several occasions I have seen that rebuilding an index that is less than 500 pages does not eliminate fragmentation completely.
I have also seen that for smaller indexes, index scans are not a big issue since the number of pages to scan is not too large. I would worry about indexes only if they are more than 500-1000 pages. Because those are the ones that will take longer to scan and also longer to rebuild.
Blog
http://saveadba.blogspot.com/
January 30, 2012 at 4:57 am
Perry Whittle (1/29/2012)
GilaMonster (1/29/2012)
though it's not easy to fix.i'd be interested to hear your fix!
On 2005 - create a clustered index. (don't drop it again)
On 2008 - create a clustered index (don't drop it again) or ALTER TABLE ... REBUILD. May or may not have the desired effect, it's more for removing forwarding pointers and compressing the heap, but it can also remove extent fragmentation of the heap.
p.s. For a heap, the two things I'd look at more than extent fragmentation to decide if a rebuild is a good idea (assuming that for whatever reason it can't become a clustered index) are the avg page space used and the number of forwarding pointers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2012 at 8:15 am
Yes exactly, created clustered index will usually provide some change. I will update, once i add clustered index.
January 30, 2012 at 2:42 pm
Yes, by adding clustered index onto column reduced the Fragmentation level. Thank you all for your support.
--Every Day is a Learning Day...:-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply