index defragmentation
-
Hi. I'm trying to implement index defragmentation.
To test the procedure, I've ran:
SELECT *FROM sys.dm_db_index_physical_stats(DB_ID('Test'), NULL, NULL, NULL , NULL)
ORDER BY 'avg_fragmentation_in_percent' DESC
command to find out the fragmentation in the 'Test' database.
After that, I've ran the defrag script provided in books online Example D - Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indices at http://msdn.microsoft.com/en-us/library/ms188917.aspx
It reported some indexes to be reorganized, some rebuilt, but running sys.dm_db_index_physical_stats against the DB afterwards still reports a lot of fragmented indices - see the attached picture. What's the problem? Or aren't that data indices?
-
I believe the issue is that these are heaps. You need a clustered index on the data to defragment.
-
Steve Jones - Editor (4/1/2010)
I believe the issue is that these are heaps. You need a clustered index on the data to defragment.Not any more!
SQL Server 2008 supports the following syntax to defragment a HEAP directly:
ALTER TABLE dbo.MyTable REBUILD;
In some SKUs the operation can even be ONLINE (but single-threaded in that case).
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
rdouglas66 (4/1/2010)
If you are looking for a defrag script then I would recommend Michelle Ufford's at http://sqlfool.com/2009/06/index-defrag-script-v30/ it does anything you could ever need.Except defragment heaps 😀
The offending routine in her code is:
INSERT INTO #indexDefragList
SELECT
database_id AS databaseID
, QUOTENAME(DB_NAME(database_id)) AS 'databaseName'
, [OBJECT_ID] AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */
, Null AS 'schemaName'
, Null AS 'objectName'
, Null AS 'indexName'
FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
WHERE avg_fragmentation_in_percent >= @minFragmentation
And index_id > 0 -- ignore heaps
And page_count > 8 -- ignore objects with less than 1 extent
And index_level = 0 -- leaf-level nodes only, supports @scanMode
OPTION (MaxDop 2);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Doh!, you're right Paul. I'd forgotten that.
Have you tried just a straight rebuild on one of these tables to see what is does?
-
I'm far from being anything close to SQL expert or a DBA, so bear with me please.
I've come across Michelle Utford's script before - if I understand the note in the script excerpt, it will ignore heaps, right?
I cannot make any modifications to the databases (as in creating new indices etc.) So to defragment heap tables, I should use ALTER TABLE REBUILD?
Also - is this necessary? I mean such fragmentation as seen in the sample I posted in the original post - does it pose a performance problem at all? (my layman analogy with filesystem fragmentation would make me think so)
If so, how would I automate this for every table in every database on the server?
Thanks in advance
oh, one more newbie question - what exactly does the ONLINE/OFFLINE defragmentation mean? I've read the description in books online, but I admit I'm not sure I understand it correctly.
-
Rambler (4/1/2010)
I've come across Michelle Utford's script before - if I understand the note in the script excerpt, it will ignore heaps, right?Right.
I cannot make any modifications to the databases (as in creating new indices etc.) So to defragment heap tables, I should use ALTER TABLE REBUILD?
Yes.
Also - is this necessary? I mean such fragmentation as seen in the sample I posted in the original post - does it pose a performance problem at all?
It depends. Fragmentation can reduce performance significantly - but only where a range of values is scanned.
Single-key fetches are not affected by fragmentation.
Heaps are interesting things. Things like 'forwarded records' can also hurt range-scan performance very badly.
See http://sqlblog.com/blogs/hugo_kornelis/archive/2006/11/03/The-table-scan-from-hell.aspx
If the heap is (partially) scanned regularly, or has many forwarded records, I would probably say yes, rebuild it.
what exactly does the ONLINE/OFFLINE defragmentation mean? I've read the description in books online, but I admit I'm not sure I understand it correctly.
When an index (or table) is built OFFLINE, no one can access the structure while it is being rebuilt.
ONLINE allows concurrent access while the rebuild takes place, but is only available in Enterprise Edition (and equivalents).
There are a number of subtleties, but that is essentially it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Thanks for the information Paul.
It's running on the Web edition actually, so I'm not sure where exactly does it stand with the ONLINE/OFFLINE operations, but it doesn't matter much, defragmentation/table rebuild would be done in off hours anyway.
Also, can you or someone else help me with automating heaps defrag - or at least give some hints to articles/resources? I can't really code SQL scripts, but I can utilize and/or modify existing for our needs.
-
Web Edition doesn't support ONLINE, no.
I always prefer to rebuild OFFLINE where I can anyway 🙂
Not aware of any scripts, but I have asked Michelle if she has any plans to incorporate heap rebuilding in her script.
As you say, maybe someone else knows of one.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply