Dynamic Database Server Maintenance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Dynamic Database Server Maintenance

Author

Topic

shackclan
Starting Member

8 Posts

Posted - 2009-06-19 : 09:44:02

Every DBA understands the importance of keeping a streamlined database. In order to keep the database as fast as possible; it is necessary to perform maintenance on the database at some interval (usually weekly but sometimes monthly).

Now I am sure that there is a whole range of opinions concerning what is the best dbcc command to call in order to keep the database as responsive as possible; but, these are the commands I used and I am open to debate any other commands/functions which are better suited to keeping the database in tip-top shape.

Alright, now the commands I used are the INDEXDEFRAG (slated for removal in next release of SQL Server) and sp_updatestats.

Here is a breakdown of what the script does:
1. Retrieves list of user databases on SQL instance.
2. Retrieves list of all user table indexes on individual database.
3. Executes DBCC INDEXDEFRAG on each index whose name is not NULL (it happens!!!) and whose key count ([keycnt]) is greater than zero (ensures that index actually exists).
4. Executes sp_updatestats on the database.
5. Moves to next database in list.

/*********** BEGIN SCRIPT ***********/

DECLARE @SQL VARCHAR(8000)
, @INDEX_SQL VARCHAR(8000)

-- 1. Retrieve list of user databases on the server.
DECLARE DatabaseCsr CURSOR FOR
SELECT [name] AS DBName
FROM master.sys.databases
WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb')

DECLARE
@DBNAME NVARCHAR(255)

SET NOCOUNT ON
OPEN DatabaseCsr
FETCH NEXT FROM DatabaseCsr INTO @DBNAME
WHILE (@@fetch_status <> -1)
BEGIN
-- 2. Retrieve list of all user table indexes on current database.
-- 3. Perform index defrag on each table index.
IF (@@fetch_status <> -2)
BEGIN
/*PERFORM TABLE RELATED MAINTENANCE*/
SET @INDEX_SQL =
'USE [' + @DBNAME + '];
DECLARE @EMBED_SQL VARCHAR(8000)

DECLARE TableCsr cursor for
SELECT so.[name] AS TABLE_NAME
, si.[name] AS INDEX_NAME
FROM sys.objects so
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
WHERE so.type = ''U''
AND si.[name] IS NOT NULL
AND si.[keycnt] > 0

DECLARE
@TABLE_NAME NVARCHAR(255)
, @INDEX_NAME NVARCHAR(255)

SET NOCOUNT ON
OPEN TableCsr
FETCH NEXT FROM TableCsr INTO @TABLE_NAME, @INDEX_NAME
WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN
SET @EMBED_SQL = ''DBCC INDEXDEFRAG ([' + @DBNAME + '], ['' + @TABLE_NAME + ''], ['' + @INDEX_NAME + '']) WITH NO_INFOMSGS''
EXECUTE(@EMBED_SQL)
END
FETCH NEXT FROM TableCsr INTO @TABLE_NAME, @INDEX_NAME

END
CLOSE TableCsr
DEALLOCATE TableCsr'
EXECUTE(@INDEX_SQL)
/* END TABLE RELATED MAINTENANCE */
-- 4. Update all statistics on database.
/*PERFORM DATABASE RELATED MAINTENANCE*/
SET @SQL = 'USE [' + @DBNAME + ']; EXEC sp_updatestats'
EXECUTE(@SQL)
/* END DATABASE RELATED MAINTENANCE */
END
FETCH NEXT FROM DatabaseCsr INTO @DBNAME
-- 5. Move to next database.
END
CLOSE DatabaseCsr
DEALLOCATE DatabaseCsr
/*********** END SCRIPT ***********/

Any suggestions to make the script more robust or increase performance are greatly appreciated.

One last comment - it is highly advised that you run a full backup on each of your user databases after running this script.

Enjoy!
shackclan

shackclan
Starting Member

8 Posts

Posted - 2009-06-22 : 09:40:55

The [keycnt] column doesn't exist on SQL versions post SQL2K.

Thanks,
shackclan

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-22 : 10:05:19

Hi Shckclan,

HAve a look at Tara's fantastic stored proc ISP_ALTER_INDEX.

http://weblogs.sqlteam.com/tarad/archive/2007/04/17/60176.aspx

I was using something very similar to your posted sql (but for DBCC REBUILD). Tara's proc is much better though.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

shackclan
Starting Member

8 Posts

Posted - 2009-06-22 : 11:36:01

Thanks, Charlie. Appreciate the link. Will definitely incorporate Tara's code.

Thanks,
shackclan

shackclan
Starting Member

8 Posts

Posted - 2009-06-25 : 12:37:29

Here is a new script incorporating what Tara did in her maintenance proc. Will not work on versions prior to 2005.

/***** The Procedure *****/
CREATE PROCEDURE [dbo].[usp_DatabaseIndexMaintenance]
@DBNAME SYSNAME
, @STAT_MODE VARCHAR(8) = 'SAMPLED'
, @DEFRAG_TYPE VARCHAR(10) = 'REORGANIZE'
, @MIN_FRAG_PCNT INT = 25
, @MAX_FRAG_PCNT INT = 100
, @MIN_ROW_CNT INT = 0
AS

BEGIN

/*
** PRODUCTION DATE:
**
** CREATION DATE: February 27, 2007
** AUTHOR: Tara Kizer
** PURPOSE: Defragments indexes
**
** INPUTS: @DBNAME - name of the database
** @STAT_MODE - LIMITED, SAMPLED or DETAILED
** @DEFRAG_TYPE - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
** @MIN_FRAG_PCNT - minimum fragmentation level
** @MAX_FRAG_PCNT - maximum fragmentation level
** @MIN_ROW_CNT - minimum row count
**
** EXEC usp_DatabaseIndexMaintenance
** @DBNAME = 'DatabaseName',
** @STAT_MODE = 'SAMPLED',
** @DEFRAG_TYPE = 'REBUILD',
** @MIN_FRAG_PCNT = 10,
** @MAX_FRAG_PCNT = 100,
** @MIN_ROW_CNT = 1000
**
**
** MODIFICATION HISTORY
**
** 06/25/2009 - JCS
** Originally written by Tara Kizer. This version reflects the flow that she originally designed but
** with major changes on how it is executed. Original contained temp table for index storage; with a
** while loop to iterate through maintenance of each index. Index pull is now in a cursor loop with
** all necessary data pulled in one hit.
**
*/

SET NOCOUNT ON

DECLARE -- CURSOR VARIABLES
@SCHEMA_NAME SYSNAME
, @OBJECT_NAME SYSNAME
, @INDEX_NAME SYSNAME
, @INDEX_DISABLED BIT
, @PARTITION_NBR INT
, @INDEX_TYPE NVARCHAR(60)
, @IS_ONLINE BIT

DECLARE -- EXECUTION VARIABLES
@SQL NVARCHAR(4000)
, @PARTITION_CNT INT
, @PARM_DEF NVARCHAR(500)

IF (@STAT_MODE NOT IN ('LIMITED', 'SAMPLED', 'DETAILED'))
BEGIN
RAISERROR('@STAT_MODE must be LIMITED, SAMPLED or DETAILED', 16, 1)
RETURN
END

IF (@DEFRAG_TYPE NOT IN ('REORGANIZE', 'REBUILD'))
BEGIN
RAISERROR('@DEFRAG_TYPE must be REORGANIZE or REBUILD', 16, 1)
RETURN
END

-- Set the database to perform maintenance on.
SET @SQL = 'USE ' + @DBNAME
EXEC sp_executesql @SQL

DECLARE mntIndexCsr cursor for
SELECT SS.[name] AS SchemaName
, SO.[name] AS ObjectName
, SI.[name] AS IndexName
, SI.is_disabled AS IndexDisabled
, IPS.partition_number AS PartitionNumber
, IPS.index_type_desc AS IndexType
, CASE
WHEN ((@DEFRAG_TYPE = 'REBUILD') AND (CONVERT(INT, SERVERPROPERTY('EngineEdition')) = 3)) THEN
CASE
WHEN (IPS.index_type_desc = 'XML INDEX') THEN CAST(0 AS BIT)
WHEN ((IPS.index_type_desc = 'NONCLUSTERED INDEX') AND (IPS.alloc_unit_type_desc = 'LOB_DATA')) THEN CAST(0 AS BIT)
WHEN ((IPS.index_type_desc = 'CLUSTERED INDEX') AND (IPS.alloc_unit_type_desc = 'LOB_DATA')) THEN CAST(0 AS BIT)
ELSE CAST(1 AS BIT)
END
ELSE CAST(0 AS BIT)
END [Online]
FROM sys.dm_db_index_physical_stats (DB_ID(@DBNAME), NULL, NULL, NULL, @STAT_MODE) IPS
INNER JOIN sys.objects SO
ON IPS.object_id = SO.object_id
INNER JOIN sys.schemas SS
ON SO.schema_id = SS.schema_id
INNER JOIN sys.indexes SI
ON IPS.object_id = SI.object_id
AND IPS.index_id = SI.index_id
WHERE IPS.avg_fragmentation_in_percent BETWEEN @MIN_FRAG_PCNT AND @MAX_FRAG_PCNT
AND IPS.index_id > 0
AND COALESCE(IPS.record_count, 0) >= CASE WHEN (@STAT_MODE IN ('SAMPLED', 'DETAILED')) THEN @MIN_ROW_CNT ELSE 0 END
ORDER BY IPS.[object_id]

OPEN mntIndexCsr
FETCH NEXT FROM mntIndexCsr INTO @SCHEMA_NAME
, @OBJECT_NAME
, @INDEX_NAME
, @INDEX_DISABLED
, @PARTITION_NBR
, @INDEX_TYPE
, @IS_ONLINE
WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN
-- RETRIEVE PARTITION INFORMATION FOR THE INDEX
SET @SQL = '
SELECT @PARTITION_CNT = COUNT(*)
FROM ' + @DBNAME + '.sys.partitions
WHERE [object_id] = OBJECT_ID(@OBJECT_NAME) AND index_id = OBJECT_ID(@INDEX_NAME)'

SET @PARM_DEF = N'@OBJECT_NAME SYSNAME, @INDEX_NAME SYSNAME, @PARTITION_CNT INT OUTPUT'

EXEC sp_executesql
@SQL, @PARM_DEF, @OBJECT_NAME = @OBJECT_NAME, @INDEX_NAME = @INDEX_NAME,
@PARTITION_CNT = @PARTITION_CNT OUTPUT

-- BUILD MAINTENANCE SQL FOR INDEX
SET @SQL = 'ALTER INDEX [' + @INDEX_NAME + '] ON [' + @DBNAME + '].[' +
@SCHEMA_NAME + '].[' + @OBJECT_NAME + '] ' + @DEFRAG_TYPE

IF ((@IS_ONLINE = 1) AND (@INDEX_DISABLED = 0) AND (@PARTITION_NBR = 1))
SET @SQL = @SQL + ' WITH (ONLINE = ON)'

IF ((@PARTITION_NBR > 1) AND (@INDEX_DISABLED = 0) AND (@INDEX_TYPE <> 'XML INDEX'))
SET @SQL = @SQL + ' PARTITION = ' + CAST(@PARTITION_NBR AS VARCHAR(10))

EXEC (@SQL)
END
FETCH NEXT FROM mntIndexCsr INTO @SCHEMA_NAME
, @OBJECT_NAME
, @INDEX_NAME
, @INDEX_DISABLED
, @PARTITION_NBR
, @INDEX_TYPE
, @IS_ONLINE

END
CLOSE mntIndexCsr
DEALLOCATE mntIndexCsr

END
go

/***** Calling The Procedure *****/
DECLARE @SQL VARCHAR(8000)
, @INDEX_SQL VARCHAR(8000)

DECLARE DatabaseCsr CURSOR FOR
SELECT [name] AS DBName
FROM master.sys.databases
WHERE [name] = 'COLDFILES' -- NOT IN ('master', 'msdb', 'model', 'tempdb')

DECLARE
@DBNAME NVARCHAR(255)

SET NOCOUNT ON
OPEN DatabaseCsr
FETCH NEXT FROM DatabaseCsr INTO @DBNAME

WHILE (@@fetch_status <> -1)
BEGIN

IF (@@fetch_status <> -2)
BEGIN
/*PERFORM TABLE RELATED MAINTENANCE*/
SET @INDEX_SQL = 'EXEC [dbo].[usp_DatabaseIndexMaintenance] @DBNAME = ''' + @DBNAME + ''', @STAT_MODE = ''DETAILED'', @DEFRAG_TYPE = ''REORGANIZE'''

EXECUTE(@INDEX_SQL)
/* END TABLE RELATED MAINTENANCE */

/*PERFORM DATABASE RELATED MAINTENANCE*/
SET @SQL = 'USE [' + @DBNAME + ']; EXEC sp_updatestats'
EXECUTE(@SQL)
/* END DATABASE RELATED MAINTENANCE */
END
FETCH NEXT FROM DatabaseCsr INTO @DBNAME

END
CLOSE DatabaseCsr
DEALLOCATE DatabaseCsr

Thanks again Charlie for the link. Really liked what Tara did and think I made good use of her logic.

Enjoy!

Thanks,
shackclan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources