SQL Server Performance

Run Maint on an entire server!!

Discussion in 'Contribute Your SQL Server Scripts' started by MichaelB, Oct 13, 2006.

  1. MichaelB Member

    I have created two procs that work together to replace the maint plans but run for an entire server[8D]! they will do the following:<br /><br />DBCC CHECKDB<br />Defrag<br />reindex<br />update stats<br />shrink<br /><br />yah.. it takes long and uses cursors [xx(] but it will do it all. Take out what you dont want to use or configure it differently. Thanks to all the nameless people I borrowed code from to patch it together![<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Have fun!<br /><br /><br />USE [EBS_Common]<br />GO<br />/****** Object: StoredProcedure [dbo].[mt_DefragDatabase] Script Date: 10/13/2006 12:55:22 ******/<br />SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br /><br /><br />CREATE proc [dbo].[mt_DefragDatabase] <br />as <br /><br />SET NOCOUNT ON<br />declare @maxfrag DECIMAL<br />set @maxfrag =95.00<br />--<br />--declare @tablename as varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />--set @tablename = 'eGo'<br />--<br />DECLARE @execstr VARCHAR (255) <br />DECLARE @objectid INT <br />DECLARE @indexid INT <br />DECLARE @frag DECIMAL <br />DECLARE @indexname CHAR(255) <br />DECLARE @dbname sysname<br />DECLARE @tablename VARCHAR (200) <br /><br />set @dbname= db_name() <br />IF @dbname IN ('master', 'msdb', 'model', 'tempdb') <br />BEGIN <br />PRINT 'This procedure should not be run in system databases.' <br />RETURN <br />END <br /><br /><br />-- Declare cursor <br />DECLARE tables CURSOR FOR <br />SELECT so.name<br />FROM sysobjects so <br />JOIN sysindexes si <br />ON so.id = si.id <br />WHERE so.type ='U' <br />AND si.indid &lt; 2 <br />AND si.rows &gt; 0 <br />--AND so.name = @tablename<br /><br />-- Create the table <br />CREATE TABLE #fraglist ( <br />ObjectName CHAR (255), <br />ObjectId INT, <br />IndexName CHAR (255), <br />IndexId INT, <br />Lvl INT, <br />CountPages INT, <br />CountRows INT, <br />MinRecSize INT, <br />MaxRecSize INT, <br />AvgRecSize INT, <br />ForRecCount INT, <br />Extents INT, <br />ExtentSwitches INT, <br />AvgFreeBytes INT, <br />AvgPageDensity INT, <br />ScanDensity DECIMAL, <br />BestCount INT, <br />ActualCount INT, <br />LogicalFrag DECIMAL, <br />ExtentFrag DECIMAL) <br /><br /><br />-- Open the cursor <br />OPEN tables <br /><br /><br />-- Loop through all the tables in the database <br />FETCH NEXT <br />FROM tables <br />INTO @tablename <br /><br /><br />WHILE @@FETCH_STATUS = 0 <br />BEGIN <br />-- Do the showcontig of all indexes of the table <br />INSERT INTO #fraglist <br />EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') <br />WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') <br />FETCH NEXT <br />FROM tables <br />INTO @tablename <br />END <br /><br />-- Close and deallocate the cursor <br />CLOSE tables <br />DEALLOCATE tables <br /><br /><br />-- Declare cursor for list of indexes to be defragged <br />DECLARE indexes CURSOR FOR <br />SELECT ObjectName , ObjectId, IndexName, ScanDensity <br />FROM #fraglist <br />WHERE ScanDensity &lt;= @maxfrag <br />AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') &gt; 0 <br /><br /><br /><br />-- Open the cursor <br />OPEN indexes <br /><br /><br />-- loop through the indexes <br />FETCH NEXT <br />FROM indexes <br />INTO @tablename, @objectid, @indexname, @frag <br /><br /><br />WHILE @@FETCH_STATUS = 0 <br />BEGIN <br />set @execstr = 'DBCC INDEXDEFRAG (' + RTRIM(@dbname) + ',[' + RTRIM(@tablename) + '],[' + RTRIM(@indexname) + ']) WITH NO_INFOMSGS' <br />EXEC (@execstr) <br /><br /><br />FETCH NEXT <br />FROM indexes <br />INTO @tablename, @objectid, @indexname, @frag <br />END <br /><br /><br />-- Close and deallocate the cursor <br />CLOSE indexes <br />DEALLOCATE indexes <br /><br /><br /><br /><br /><br />SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br /><br />--*****************************************************************<br />-- Proc Name:prc_ReindexServer<br />--<br />-- Purpose:To Reindex entire DB<br />--<br />-- Called By:Reindex Nightly Job<br />--<br />-- Created By:Michael Berry<br />--<br />-- Created on:10/11/2006<br />--<br />-- Modified By:<br />--<br />-- Modified Dte:<br />--<br />-- Modified Rsn: <br />--*****************************************************************<br /><br />ALTER Procedure [dbo].[mt_Database_Maintenance]<br /><br /><br />as<br />--******************************************************************<br />--<br />-- Step 1. Initalize<br />--<br />--******************************************************************<br /><br />SET NOCOUNT ON<br />SET ARITHABORT ON <br />SET QUOTED_IDENTIFIER ON <br /><br /><br />--******************************************************************<br />--<br />-- Step 2.Run reindex for each table in DB<br />--<br />--******************************************************************<br /><br />--select * from ebs_common.dbo.cleanuptext<br /><br /><br />declare @databasename as varchar(200)<br /><br /><br />declare curs3 cursor local fast_forward<br />for<br />select distinct<br /> name <br />from <br /> master.dbo.sysdatabases <br />where<br /> name not in ('master', 'msdb', 'model', 'tempdb','AdventureWorks','AdventureWorksDW') <br />open curs3<br /> <br />fetch next from curs3 into @databasename<br /><br />while @@fetch_status = 0<br />begin<br /><br /><br />declare @start datetime<br /><br />PRINT 'Started CHECKDB checks for '+ @databasename + ''<br />set @start = GETDATE()<br />DBCC CHECKDB (@databasename)<br />Print '******************* Completed CHECKDB for DB:'+ @databasename + ' in ' + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + ' Seconds *******************' <br /><br /> exec('<br /><br /> declare @start datetime<br /> <br /><br /> use ' + @databasename + '<br /><br /> <br /> PRINT ''Defrag Started For: ' + @databasename + '''<br /> set @start = GETDATE()<br /> exec mt_DefragDatabase<br /> Print ''******************* Completed Defrag For: ' + @Databasename + 'in '' + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + '' Seconds *******************''<br /><br /> declare @tablename varchar(300)<br /> declare curs4 cursor local fast_forward<br /> for<br /> Select table_name FROM information_schema.tables where table_type = ''BASE TABLE''<br /><br /><br /> open curs4<br /> <br /> fetch next from curs4 into @tablename<br /><br /> while @@fetch_status = 0<br /> begin<br /><br /> <br /> <br /> PRINT ''Reindexing Started For:'' + @TableName <br /> set @start = GETDATE()<br /> DBCC DBREINDEX(@TableName, '' '',0) WITH NO_INFOMSGS <br /> Print ''Completed Reindex Of: '' + @TableName +'' in '' + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + '' Seconds''<br /><br /><br /> PRINT ''Updating Stats Started For:'' + @TableName <br /> set @start = GETDATE()<br /> exec (''UPDATE STATISTICS ['' + @TableName + ''] with sample 30 percent'')<br /> Print ''******************* Completed Update Stats Of: '' + @TableName +'' in '' + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + '' Seconds *******************'' <br /><br /> fetch next from curs4 into @tablename<br /> end<br /> close curs4<br /> deallocate curs4')<br /><br /><br /> PRINT 'Shrinking ' + @databasename <br /> set @start = GETDATE()<br /> DBCC SHRINKDATABASE (@databasename, 10) <br /> Print '******************* Completed ShrinkDatabase for DB:'+ @databasename + ' in ' + cast((DATEDIFF(MILLISECOND, @start, GETDATE())/1000) as varchar(50)) + ' Seconds *******************' <br /> <br /><br /><br />fetch next from curs3 into @databasename<br />end<br />close curs3<br />deallocate curs3<br /><br /><br />--******************************************************************<br />--<br />-- Step 3. Clean up<br />--<br />--******************************************************************<br /><br /><br />SET NOCOUNT OFF<br />GO<br /><br />SET ANSI_NULLS OFF<br />GO<br />SET QUOTED_IDENTIFIER OFF<br />GO<br /><br /><br /><br /><br /><br /><br />******************************************<br />note: no warrenties expressed or implied!!!<br /><br /><br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7
  2. Luis Martin Moderator

    I've moved to relevant forum.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. MichaelB Member

    Ok.. I recommed dropping the defrag. Takes too long and doesnt help that much with speeding up the reindex as hoped. Duh! sorry about that!

    Michael B
    Sr. DBA

    "The fear of the Lord is the beginning of knowledge,
    but fools despise wisdom and instruction." Proverbs 1:7

Share This Page