SQL 2000 Maintenance Scripts

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- UPDATE: AUGUST 25, 2004
-- RE: Fix for DESC clustered indexes. There are no errors for DESC in 
-- nonclustered indexes. Thanks!
-- 
-- For more scripts and sample code, check out 
--  http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- Execute this whole script to create the sp_RebuildIndexes stored procedure in Master.
-- Best Viewed with Courier New 12pt. and Tabs saved as 4 spaces not 8. (Tools, Options, Editor)

-- To use the sp_RebuildIndexes procedure once created use:
-- sp_RebuildIndexes 
--      To Rebuild All Indexes on All Tables for all that have a Scan Density < 100%
-- sp_RebuildIndexes @ScanDensity = 80
--      To Rebuild All Indexes on All Tables with a Scan Density of < 80%
-- sp_RebuildIndexes 'Authors'
--      To Rebuild All Indexes on the authors table - for a Scan Density of < 100%
-- sp_RebuildIndexes 'Authors', 80
--      To Rebuild All Indexes on the authors table - for a Scan Density of < 80%
-- Object Name and ScanDensity are both optional parameters. 
-- ScanDensity must be a whole number between 1 and 100.

USE master
go
IF OBJECTPROPERTY(object_id('sp_RebuildClusteredIndex'), 'IsProcedure') = 1
    DROP PROCEDURE sp_RebuildClusteredIndex
go

IF OBJECTPROPERTY(object_id('sp_RebuildIndexes'), 'IsProcedure') = 1
    DROP PROCEDURE sp_RebuildIndexes
go

CREATE PROCEDURE sp_RebuildClusteredIndex
(
    @TableName      sysname     = NULL,
    @IndexName      sysname     = NULL
)       
AS
-- Written by Kimberly L. Tripp of SYSolutions, Inc.
-- For more code samples go to http://www.sqlskills.com
-- NOTE: If your clustered index is NOT unique then rebuilding the clustered
-- index will cause the non-clustered indexes to be rebuilt. If the nonclustered
-- indexes were fragmented then this series of scripts will build them again.
-- This is something this script DOES NOT look for...because you should not create
-- clustered indexes on a non-unique column or composite set of columns.
IF @TableName IS NOT NULL
    BEGIN
        IF (OBJECTPROPERTY(object_id(@TableName), 'IsUserTable') = 0 
                AND OBJECTPROPERTY(object_id(@TableName), 'IsView') = 0) 
            BEGIN
                RAISERROR('Object: %s exists but is NOT a User-defined Table. This procedure only accepts valid table names to process for index rebuilds.', 16, 1, @TableName)
                RETURN
            END
        ELSE
            BEGIN
                IF OBJECTPROPERTY(object_id(@TableName), 'IsTable') IS NULL
                    BEGIN
                        RAISERROR('Object: %s does not exist within this database. Please check the table name and location (which database?). This procedure only accepts existing table names to process for index rebuilds.', 16, 1, @TableName)
                     

use master
go

if ((object_id('sp_waitstats') is not null) and (objectproperty(object_id('sp_waitstats'), 'IsProcedure') = 1))
    drop proc [dbo].[sp_waitstats]
go

create proc [dbo].[sp_waitstats] @orderby nvarchar(10) = N'total'
as

set nocount on

if (lower(@orderby) not in ('total', 'resource', 'signal'))
begin
    raiserror('Error: incorrect @orderby value, use ''total'', ''resource'', ''signal''', 10, 1) with nowait
    return
end

declare @requests   bigint,
        @totalwait  numeric(20, 5),
        @totalres   numeric(20, 5),
        @totalsig   numeric(20, 5),
        @endtime    datetime,
        @begintime  datetime

create table [#waitstats]
(
    [wait type]         varchar(80) not null, 
    [requests]          bigint not null,
    [wait time]         numeric(20, 5) not null,
    [signal wait time]  numeric(20, 5) not null,
    [now]               datetime not null default getdate()
)

insert into [#waitstats]([wait type], [requests], [wait time], [signal wait time])      
    exec ('dbcc sqlperf(waitstats) with tableresults, no_infomsgs')

select  @begintime = min([now]),
        @endtime   = max([now])
from    [#waitstats] 
where   [wait type] = 'Total'

--- subtract waitfor, sleep, and resource_queue from Total
select  @requests  = sum([requests]),
        @totalwait = sum([wait time]),
        @totalres  = sum(([wait time] - [signal wait time])),
        @totalsig  = sum([signal wait time]) 
from    [#waitstats] 
where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '***total***') 
and     [now] = @endtime

-- insert adjusted totals, rank by percentage descending
delete  [#waitstats] 
where   [wait type] = '***total***' 
and     [now] = @endtime

insert into #waitstats values('***total***', @requests, @totalwait, @totalsig, @endtime)

if (@orderby = N'total')
begin

    select  [requests],
            [wait type],
            [total wait time]       = [wait time],
            [resource wait time]    = [wait time] - [signal wait time],
            [signal wait time],
            [%total wait time]      = case @totalwait when 0 then 0 else cast(100 * [wait time] / @totalwait as numeric(20, 5)) end,
            [%resource wait time]   = case @totalres  when 0 then 0 else cast(100 * ([wait time] - [signal wait time]) / @totalres as numeric(20, 5)) end,
            [%signal wait time]     = case @totalsig  when 0 then 0 else cast(100 * [signal wait time] / @totalsig as numeric(20, 5)) end,
            [avg total wait time]   = case [requests] when 0 then 0 else cast(100 * @totalwait / [requests] as numeric(20, 5)) end,
            [avg resource wait time]= case [requests] when 0 then 0 else cast(100 * @totalres  / [requests] as numeric(20, 5)) end,
            [avg signal wait time]  = case [requests] when 0 then 0 else cast(100 * @totalsig  / [requests] as numeric(20, 5)) end
    from    [#waitstats]
    where   [wait type] not in ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total')
    and     [now] = @endtime
    or