September 8, 2009 at 1:20 am
Hi
I tried to rebuild/reogranize indexes based on the help available in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm.
First of all, it throws error for DB_ID().
Later after providing the DB_ID number I executed the script. But when I run the SELECT part alone, it shows the same list.
The code goes below:
The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Executing this query requires the VIEW DATABASE STATE permission
ensure a USE
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting 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.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = 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 = 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
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT 'Executed ' + @command;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO
Can anyone let me know what is going wrong?
Regards
Mohan Kumar VS
September 8, 2009 at 1:39 pm
Mohan Kumar (9/8/2009)
ensure a USE statement has been executed first.
I think that's your problem. Before running the rest of the script you need to change your database context because DB_ID() without a parameter returns the current database.
Whenever anyone has a question like this I always point them to Michelle Ufford's blog and excellent Index Defrag Script[/url].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2009 at 11:23 pm
Thanks for your response. But I tried after selecting the required database.
Anyways, I will try the script which you have mentioned also & inform you.
Rgds
Mohan Kumar VS
September 9, 2009 at 4:11 am
Let me know how you get on, as I tried your script and it didnt work for me either
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 6:05 am
I tried the script. But it is not completely eliminating the fragmentation. Upto 90% of fragmentation is removed.
Thanks again.
Regards
Mohan Kumar VS
September 9, 2009 at 6:11 am
You need to check the page_count for the indexes that are not being defragmented. If they only have a few pages the fragmentation will not be removed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2009 at 6:50 am
Just to expand slightly on Jack's (very patient) comments:
One reason that fragmentation might not be fully removed is because the first 8 data pages for an object come from 'mixed extents' (unless you set a trace flag, but I digress). Mixed extents are groups of 8 pages, where each page can belong to a different object. This is an optimization, partly so that very small objects don't require a full 64KB extent.
Until the object reaches 25 data pages in size, a clustered index rebuild will leave the mixed extents alone (the pages allocated from these mixed extents are overwhelmingly likely to be logically fragmented). This is by design.
When the object reaches 25 pages, a clustered index rebuild can consolidate the data pages into uniform extents (where all 8 pages belong to the same object). Higher levels of the index are subject to the same 25-page rule also, so mixed extent allocations may remain there.
Please don't obsess about fragmentation though - unless you know that you are range-scanning large tables/indexes. These will generally benefit from being defragmented. For tables which are accessed primarily with a seek to one or a small number of rows, fragmentation is generally not at all important.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 10:59 pm
Thanks Jack & Paul.
September 9, 2009 at 11:01 pm
I executed the script twice. There are some indexes still with 40-55% fragmentation and page_count ranging from 9-151.
Eg.
tbl1 Frag 38.88 PageCount18
tbl2 Frag 11.11PageCount9
tbl3 Frag 7.01 PageCount57
tbl4 Frag 5.30PageCount151
IndexID is 1 for all.
Can you please let me know what can be done for this?
Rgds
Mohan Kumar VS
September 11, 2009 at 7:43 am
Mohan Kumar (9/9/2009)
I executed the script twice. There are some indexes still with 40-55% fragmentation and page_count ranging from 9-151.Eg.
tbl1 Frag 38.88 PageCount18
tbl2 Frag 11.11PageCount9
tbl3 Frag 7.01 PageCount57
tbl4 Frag 5.30PageCount151
IndexID is 1 for all.
Can you please let me know what can be done for this?
Rgds
Mohan Kumar VS
If I recall, SQL Server may choose to bypass defragmenting if page count is less than about 1000, which is kind of a rule of thumb. Basically, at that page count or less, an index doesn't optimize selects much and treats the table the same way as if there was no index on it all. The costs of an index with that few pages are greater than a straightforward tablescan.
I wouldn't worry too much about anything fragmented lower than a 1000 pages. So when checking for fragmentation, filter out anything with less than that threshhold number of pages.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
September 11, 2009 at 8:07 am
Please see my previous post for details. Also:
Make sure you are doing a REBUILD rather than a REORGANIZE.
REORGANIZE just shuffles stuff around inside already-allocated pages - it does not allocate new pages.
To get the most comprehensive defragmentation specify REBUILD WITH (ONLINE = OFF, SORT_IN_TEMPDB = ON).
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2009 at 3:42 am
Thank you very much.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply