December 10, 2008 at 11:41 am
Jerry - that is definitely interesting. Did not know that it was introduced sometime after SP1.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 3, 2010 at 10:47 am
Grant, just wanted to let you know that I have used this particular script (or a slight variation of it) at 3 different clients/employers now, and it has GREATLY improved the performance of their SQL servers.
Thanks for you wonderful contributions to the SQL Server community!
Dustin Mueller
@sqlcheesecake
September 3, 2010 at 1:32 pm
dustin.mueller (9/3/2010)
Grant, just wanted to let you know that I have used this particular script (or a slight variation of it) at 3 different clients/employers now, and it has GREATLY improved the performance of their SQL servers.Thanks for you wonderful contributions to the SQL Server community!
Thank you! Glad it's useful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2013 at 7:39 am
Hi,
Very nice script. I've been searching ages for this. I am not so comfortable with cursors, they tend to always crash for me.
However,
Do you really want to reorganize or rebuild indexes that has no pages?
I my self only look for defragmentation when page_count are above 1000 for small databases and 500 for bigger ones.
Your script is easily changed, as it is a very good script, to perform this.
I have done this
Added a variable ,@PctCount INT in DECLARE
Added av Column and a Value in #Frag table (pCount INT having pCount = s.page_count + Added AND s.page_count > 500 in Where clause)
Added ,@PctCount to the CURSOR AND FETCH
Inserted IF @PctCount > 499 WITH a BEGIN and END
-- CHOOSE DB TO CHECK FIRST (or master will be checked as default)
SET NOCOUNT ON
DECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMAL
,@PctCount INT
DECLARE @Defrag NVARCHAR(MAX)
CREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment DECIMAL
,pCount INT)
EXEC sp_msforeachdb 'INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment,
pCount
) SELECT ''?'' AS DBName
,t.Name AS TableName
,sc.Name AS SchemaName
,i.name AS IndexName
,s.avg_fragmentation_in_percent
,s.page_count
--,s.*
FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL,
NULL, ''Sampled'') AS s
JOIN ?.sys.indexes i
ON s.Object_Id = i.Object_id
AND s.Index_id = i.Index_id
JOIN ?.sys.tables t
ON i.Object_id = t.Object_Id
JOIN ?.sys.schemas sc
ON t.schema_id = sc.SCHEMA_ID
WHERE s.avg_fragmentation_in_percent > 10
AND s.page_count > 500
AND t.TYPE = ''U''
ORDER BY TableName,IndexName'
SELECT * FROM #Frag
DECLARE cList CURSOR
FOR SELECT * FROM #Frag
OPEN cList
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctCount > 499
BEGIN
IF @PctFrag BETWEEN 10.0 AND 30.0
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REORGANIZE'
EXEC sp_executesql @Defrag
PRINT @Defrag
PRINT ''
END
ELSE IF @PctFrag > 30.0
BEGIN
SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON [' + @DBName + '].[' + @SchemaName + '].[' + @TableName + '] REBUILD'
EXEC sp_executesql @Defrag
PRINT @Defrag
PRINT ''
END
END
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag,@PctCount
END
CLOSE cList
DEALLOCATE cList
DROP TABLE #Frag
January 11, 2013 at 8:16 am
Not just zero pages. It should have a page limit so that the defrag is across an index that's greater than 1 extent or the defrag just won't work. I haven't touched this since it was published 5 years ago. I may have to update it at some point.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply