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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply