Index Defrags 1

[snootalope]

I copied this script from an online source to tell me which index's I may need to defrag:

SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL,'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

However, when I run it I get:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.

I can't for the life of me figure it out...

Long story short, I issued the following to defrag all the indexs:

EXEC [sp_MSforeachtable] @command1="RAISERROR('DBCC DBREINDEX(''?'') ...',10,1) WITH NOWAIT DBCC DBREINDEX('?')"

Once it was done, I issued:

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);

And still, objects with more than 1000 pages still have fragmentation, some with percents as high as 99%!!

Our users overall SQL sessions are just starting to drag and I've got to figure out how to defrag these index's to get them back into shape..

help?

 

[ptheriault]

Here is a script that I use. You should use ALTER INDEX instead of DBCC DBREINDEX as that will not be supported in future releases.
Also, if the fragmentation level is higher than 30% it is a better idea to rebuild the index instead of defrag it. 30% is a random percentage. Some people may select a higher or lower number. But 30% works best for me. You should also check how fragmented the indexes become weekly. If they are becoming very fragmented in only a weeks time you most likely have a fill factor that is higher than it should be.

--SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
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,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID('db_name'), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
AND index_id > 0
ORDER BY object_name(object_id);

-- 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, @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;

-- 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';
        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

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

Thank you very much for the script! However, I tried running it on one of my test machines and get this:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '('.

 

[snootalope]

I still can't get this script to work... I keep getting:

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '('.

Can you please tell me why? This is driving me nuts!!

 

[ptheriault]

What is the compatability level of the database you are trying to run this on? Can you run it on other databases?

I don't see any errors and it runs for me.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

Compatibility Level is SQL 2000 - is that ok?

 

[ptheriault]

No, those ALTER TABLE ..REBUILD or REORGANIZE are 2005 commands.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[snootalope]

ah.. 10-4, thanks man!

 

[olahallengren]

I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Ola Hallengren

http://ola.hallengren.com

 

[snootalope]

Hey, olahallengren, is your IndexOptimize script written for SQL 2005 though? My sql db is compatibility level 2000.

 

[olahallengren]

It works like this.

The stored procedure has been designed for SQL Server 2005 SP2.

The database where the stored procedure is created has to be in compatibility level 90. You can create the stored procedure in the master database or in a custom admin database.

All other databases can be in any compatibility level.

You'll need CommandExecute.sql and DatabaseSelect.sql too to get it working. They should just be created in the same database as IndexOptimize.sql.

Ola Hallengren

http://ola.hallengren.com