Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

2008 Index Fragmentation Maintenance Expand / Collapse
Author
Message
Posted Tuesday, December 9, 2008 12:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Grumpy DBA (12/9/2008)
You have more control over what actually happens to the tables, indexes, etc. when you write your own maintenance jobs versus using SQL Server's Maintenance Plans. I have my own jobs for creating compressed full and t-log backups, monitoring database growth, index defrag/rebuild based on level of fragmentation, etc.

Maintenance Plans are a better alternative than no DB maintenance for a novice DBA.


This is interesting, because I use a combination of my own procedures/code and the maintenance plans. I do this because the maintenance plans are very good at managing the steps a process needs to do, but the plug-ins that are provided are not very good.

So, instead of using the Check Database Integrity Task - I will use the Execute SQL Task and put in that task: DBCC CHECKDB(mydb) WITH PHYSICAL_ONLY;

Which, of course is not an option that is available with the plug-in.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #616404
Posted Tuesday, December 9, 2008 12:23 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Jerry Hung (12/9/2008)

It will work for SQL 2005 as well if we change
Select db_name()
,object_name(s.object_id) As ObjectName
--,object_schema_name(s.object_id) As SchemaName -- 2008
,object_name(s.object_id) As SchemaName -- 2005

,i.Name As IndexName
,s.avg_fragmentation_in_percent



Jerry - object_schema_name is available in SQL Server 2005 and above. Not sure why you are repeating the object_name as the schema_name for the object in the above.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #616409
Posted Tuesday, December 9, 2008 12:28 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Alan Vogan (12/9/2008)
Good morning,

I'm running a similar script and yes, it is WAY better to write your own optimizations. How do you handle the exception when the index contains one of the following types?

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'TEST_PK' because the index contains column 'TESTCOL' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

The script still runs, but it's irritating to show up in the morning and see that the job has 'failed'.

Alan


You can check the column 'lob_data_space_id' in the table sys.tables. If there is a value there, then you have LOB data in the table.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #616419
Posted Tuesday, December 9, 2008 1:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 28, 2014 7:02 PM
Points: 1,082, Visits: 550
Jeffrey Williams (12/9/2008)
You can check the column 'lob_data_space_id' in the table sys.tables. If there is a value there, then you have LOB data in the table.


Woohoo! That's what I was lookin' for!

I also use a combination of SQL 2005 provided maintenance tasks and custom scripts.

Thanks for the tip!

Alan
Post #616456
Posted Wednesday, December 10, 2008 11:30 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 27, 2014 8:18 AM
Points: 774, Visits: 1,198
Jeffrey Williams (12/9/2008)
Jerry Hung (12/9/2008)

It will work for SQL 2005 as well if we change
Select db_name()
,object_name(s.object_id) As ObjectName
--,object_schema_name(s.object_id) As SchemaName -- 2008
,object_name(s.object_id) As SchemaName -- 2005

,i.Name As IndexName
,s.avg_fragmentation_in_percent



Jerry - object_schema_name is available in SQL Server 2005 and above. Not sure why you are repeating the object_name as the schema_name for the object in the above.


Interesting
Select db_name()
,object_name(s.object_id) As ObjectName
,object_schema_name(s.object_id) As SchemaName -- 2008
,object_name(s.object_id) As SchemaName -- 2005
,i.Name As IndexName
,s.avg_fragmentation_in_percent
From sys.dm_db_index_physical_stats(db_id(), Null, Null, Null, 'Sampled') s
Join sys.indexes i On i.object_id = s.object_id And i.index_id = s.index_id
Where i.index_id > 0
And i.index_id < 255
And s.avg_fragmentation_in_percent > 20

On 2005 9.0.2047 (SP1), 1399 (RTM)
'object_schema_name' is not a recognized built-in function name.

2005 9.0.3042 (SP2) supports object_schema_name

Note, above code doesn't run in compatibility 8.0 mode either clearly
'object_schema_name' is not a recognized built-in function name.


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #617276
Posted Wednesday, December 10, 2008 11:41 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
Jerry - that is definitely interesting. Did not know that it was introduced sometime after SP1.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #617280
Posted Friday, September 3, 2010 10:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 10:54 AM
Points: 33, Visits: 607
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
Post #980355
Posted Friday, September 3, 2010 1:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 13,925, Visits: 28,319
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #980489
Posted Friday, January 11, 2013 7:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:35 AM
Points: 80, Visits: 215
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
Post #1406030
Posted Friday, January 11, 2013 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 13,925, Visits: 28,319
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1406061
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse