May 18, 2011 at 4:57 am
SQL Server 2005 introduces a new DMV (Dynamic Management View) to check index fragmentation levels: sys.dm_db_index_physical_stats. Although SQL Server 2005 still supports the SQL Server 2000 DBCC SHOWCONTING command, this feature will be removed on a future version of SQL Server. Here you can check the differences between both instructions when checking for fragmentation on the HumanResources.Employee table in the sample database AdventureWorks:
* DBCC SWOWCONTING example:
USE AdventureWorks;
GO
DBCC SHOWCONTIG ('HumanResources.Employee')
GO
DBCC SHOWCONTIG scanning 'Employee' table...
Table: 'Employee' (869578136); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 2.3
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 14.29%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 172.6
- Avg. Page Density (full).....................: 97.87%
* sys.dm_db_index_physical_stats DMV example:
USE AdventureWorks
GO
SELECT object_id, index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), OBJECT_ID('HumanResources.Employee'), NULL, NULL, NULL);
In this last example I have selected only relevant information to show from the DMV, you will see that DMV can provide much more details about the index structure. In case you wanted to show fragmentation details for all the objects in the AdventureWorks database, the command would be as follows:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'), NULL, NULL, NULL , NULL);
Please, refer to SQL Server 2005 Books Online for more information on sys.dm_db_index_physical_stats syntax.
How do we know if our database is fragmented? We have to pay attention to the avg_fragmentation_in_percent value. A value between 5-30% indicates moderate fragmentation, while any value over 30% indicates high fragmentation (book pages missing any order).
The avg_page_space_used_in_percent is another value that it is worth to look closely. This value represent the amount of spaced used in the indexes. A value below 75% is usually associated to internal fragmentation (more blank pages on our book than recommended).
In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the resultant values. For moderate fragmentation index reorganization will be enough, for heavily fragmented indexes a rebuild process is needed. The following table summarizes when to use each one (refer to this Books Online article for an in-depth coverage of this information):
avg_fragmentation_in_percent > 5 AND < 30
Reorganize Index ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30
Rebuild Index ALTER INDEX REBUILD
REORGANIZE statement is always executed online while REBUILD index is executed offline by default and can be optionally executed while database is accessed by users with the ONLINE statement, which can be a plus for 24x7 environments. This approach, which is only available in SQL Server 2005, has some limitations; refer to the ALTER INDEX statement in SQL Sever Books Online for more details.
If you are looking for an easy way to automate these processes the SQL Server Books Online reference for the sys.dm_db_index_physical_stats contains a sample script you can implements within minutes. This script will take care of reorganizing any index where avg_fragmentation_in_percent is below 30% and rebuilding any index where this values is over 30% (you can change this parameters for your specific needs). Add a new SQL Server Execute T-SQL statement task to your weekly or daily maintenance plan containing this script so you can keep you database fragmentation at optimum level
May 18, 2011 at 5:49 am
Am I misinformed in this? I was always told that reorg was between 10 and 30, not 5 and 30.
May 18, 2011 at 8:14 am
Ninja's_RGR'us (5/18/2011)
Am I misinformed in this? I was always told that reorg was between 10 and 30, not 5 and 30.
Reorg is between whatever values are right for your situation. In your particular question, I'd ask: is seeing a table that's 6% fragmented on one defrag run going to have fragmentation that bothers you enough to have wanted it defragged earlier, by just before the start of the next defrag run?
If you have a fixed maintenance window that you're not using all of, where you're allowed to lock tables, I'd say rebuild more than reorg, as in my experience rebuild does a more thorough job.
Ask yourself - for what business purpose are you choosing to reorg instead of rebuild? Examine your answer, perhaps do some benchmarks, and you'll know whether to reorg more, or less.
May 18, 2011 at 8:56 am
Ninja's_RGR'us (5/18/2011)
Thanks... not to be a smartass or anything but ever since 2005 I've always been told 10%, so where did he get that info?
Likewise, _why_ was 10% to 30% chosen for reorg? All reputable references I've seen say this is a rough guide that must be evaluated case by case.
See crainlee2's post on this, with a(n old) MS Technet reference for one set of information that in some cases indicates that rebuilds have advantages.
Also note that even in 2008 R2, Reorganize is a serial, not a parallel, task http://technet.microsoft.com/en-us/library/ms177571.aspx.
P.S. http://technet.microsoft.com/en-us/library/ms188388.aspx states "REORGANIZE
Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG."
May 19, 2011 at 3:10 am
I'm pretty sure the 30% was a ballpark figure that one of the guys who wrote SQL server came up with--it wasn't ever intended to be a "you must use this in all situations" number, but just a starting point for further optimisation against your own data.
May 20, 2011 at 10:04 am
paul.knibbs (5/19/2011)
I'm pretty sure the 30% was a ballpark figure that one of the guys who wrote SQL server came up with--it wasn't ever intended to be a "you must use this in all situations" number, but just a starting point for further optimisation against your own data.
I'm pretty sure some MS deveolpers were sitting in a bar, thinking about what number to use when they noticed Monty Python on the bar TV........
And Saint Attila raised the hand grenade up on high, saying, "O LORD, bless this Thy hand grenade that with it Thou mayest blow Thine enemies to tiny bits, in Thy mercy." And the LORD did grin and the people did feast upon the lambs and sloths and carp and anchovies and orangutans and breakfast cereals, and fruit bats and large chu... [At this point, the friar is urged by Brother Maynard to "skip a bit, brother"]... And the LORD spake, saying, "First shalt thou take out the Holy Pin, then shalt thou count to three, no more, no less. Three shall be the number thou shalt count, and the number of the counting shall be three. Four shalt thou not count, neither count thou two, excepting that thou then proceed to three. Five is right out. Once the number three, being the third number, be reached, then lobbest thou thy Holy Hand Grenade of Antioch towards thy foe, who being naughty in My sight, shall snuff it." Amen
Due to their intake of beers, they mistakenly added a 0 to 3 and there we have it.
Happy Friday everbody and my apologies for not adding anything technical to this conversation.
May 20, 2011 at 10:13 am
paul.knibbs (5/19/2011)
I'm pretty sure the 30% was a ballpark figure that one of the guys who wrote SQL server came up with
Yup, same with the 1000 page rebuild limit. General guidelines based on knowledge of the algorithms & the way the storage engine works.
Oh, and it was Paul Randal for both figures.
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
May 20, 2011 at 10:47 am
If you (like me) have a SQL 2005 instance with a DB set to level 8 compatability then the below which works for me may be of interest.
NOTE: This is a sample limited to a single table but it can be modified to return the info for all table indexes.
DECLARE @iDBID INT
DECLARE @iTableID INT
DECLARE @iIdxID INT
DECLARE @iMinFragPercent INT
DECLARE @sScanMode VARCHAR(12)
SELECT @iMinFragPercent = 0,
@iDBID = DB_ID('MY_DB'),
@iTableID = Object_ID('dbo.TABLEX'),
@iIdxID = NULL,
@sScanMode = 'LIMITED' /*CHOICES = DEFAULT or LIMITED or SAMPLED or DETAILED*/
SELECT D.Object_ID AS 'Table_ID',
Object_Name(D.Object_ID) AS 'TableName',
S.IndID AS 'Index_ID',
S.Name AS 'IndexName',
D.index_type_desc AS 'IndexTypeDesc',
D.index_level AS 'Index_Level(0=leafLevels,heaps)',
D.Avg_Fragmentation_In_Percent AS 'AvgFragInPercent',
D.Page_Count AS 'PageCount',
S.rows AS 'RowCount',
S.maxlen AS 'MaxLen',
S.OrigFillFactor AS 'OrigFillFactor',
S.dpages AS 'dPages',
S.groupid AS 'GrouoID',
S.id AS 'ID',
NULL
FROM sys.DM_DB_INDEX_PHYSICAL_STATS (@iDBID, @iTableID, @iIdxID, NULL, /*MODE=*/ @sScanMode )
D Join SYSINDEXES S ON D.Object_ID = S.ID AND D.Index_ID = S.IndID
WHERE 1 = 1
AND D.Avg_Fragmentation_In_Percent >= @iMinFragPercent -- Ignore Indexes without at least X fragmentaion
AND D.Index_ID > 0 -- Ignore heaps
AND D.Page_Count > 8 -- Ignore objects with less than 1 extent
Kindest Regards,
Just say No to Facebook!Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply