Friday, 15 February 2013

Smart Index Rebuild


Use in your maintenance plan when you need to rebuild indexes in all databases but it takes too long to do every single index in every database, and be too hard to maintain a specific list every time another database was added or removed. With this script you can do the whole instance but only where the index fragmentation is above a specified threshold.




-- Specify your Database Name
USE [master] ;
GO

--create global temp table to hold the table name info
--IF EXISTS (SELECT 1 FROM tempdb.sys .tables WHERE [name] like '#tbllist%')
--        DROP TABLE #tbllist;

CREATE TABLE #tbllist(FullName varchar (255) NOT NULL, DatabaseName varchar(255), TableName varchar (255) NOT NULL)
GO


--get all three part table names from all databases
sp_msforeachdb
'INSERT INTO #tbllist SELECT ''['' + "?" + ''].['' + [TABLE_SCHEMA] + ''].['' + [TABLE_NAME] + '']'' as FullName, ''['' + "?" + '']'' as DatabaseName, [TABLE_NAME] as TableName
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG <> ''tempdb'' AND TABLE_TYPE = ''BASE TABLE'''

 -- Declare variables
 SET NOCOUNT ON ;
 DECLARE @fullname VARCHAR (255);
 DECLARE @DatabaseName varchar (255);
 DECLARE @tableName varchar (255);
 DECLARE @execstr VARCHAR (255);
 DECLARE @objectid INT ;
 DECLARE @indexid INT ;
 DECLARE @frag decimal ;
 DECLARE @maxfrag decimal ;

-- Decide on the maximum fragmentation to allow for.
SET @maxfrag = 30.0 ;


 -- Declare a cursor.
 DECLARE tables CURSOR FOR
 SELECT FullName, DatabaseName, TableName
 FROM #tbllist

 -- Create the table.
 CREATE TABLE #fraglist (
        ObjectName varchar (255),
        ObjectId INT ,
        IndexName varchar (255),
        IndexId INT ,
        Lvl INT ,
        CountPages INT ,
        CountRows INT ,
        MinRecSize INT ,
        MaxRecSize INT ,
        AvgRecSize INT ,
        ForRecCount INT ,
        Extents INT ,
        ExtentSwitches INT ,
        AvgFreeBytes INT ,
        AvgPageDensity INT ,
        ScanDensity decimal ,
        BestCount INT ,
        ActualCount INT ,
        LogicalFrag decimal ,
        ExtentFrag decimal );
 -- Open the cursor.
 OPEN tables;
 -- Loop through all the tables in the database.
 FETCH NEXT
 FROM tables
 INTO @fullname, @DatabaseName, @tableName;
 WHILE @@FETCH_STATUS = 0
 BEGIN;

 -- Do the showcontig of all indexes of the table
 INSERT INTO #fraglist
        EXEC ('DBCC SHOWCONTIG (''' + @fullname + ''')
       WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' );
--put the full table name into the object name, as we need it below
UPDATE #fraglist
SET ObjectName = @fullname
WHERE ObjectName = @tableName

 FETCH NEXT
 FROM tables
 INTO @fullname, @DatabaseName, @tableName;
 END;

 -- Close and deallocate the cursor.
CLOSE tables ;
DEALLOCATE tables ;

SELECT DISTINCT
        IDENTITY(int ,1, 1) as ord
        , 'Executing USE ' + T.DatabaseName + '; ' +
        'IF ((SELECT INDEXPROPERTY (' + CAST( F.ObjectId as varchar(255 )) + ', ' + CHAR(39 ) + F. IndexName + CHAR( 39) + ', ''IndexDepth'')) > 0) ' +
        'ALTER INDEX ' + RTRIM(F.IndexName) + ' ON ' + RTRIM (T. FullName) + ' REBUILD - ' + CAST(LogicalFrag as varchar(5)) + '% Fragmented' as [task_descr]
        , 'USE ' + T.DatabaseName + '; ' +
        'IF ((SELECT INDEXPROPERTY (' + CAST( F.ObjectId as varchar(255 )) + ', ' + CHAR(39 ) + F. IndexName + CHAR( 39) + ', ''IndexDepth'')) > 0) ' +
        'ALTER INDEX [' + RTRIM(F.IndexName) + '] ON ' + RTRIM (T. FullName) + ' REBUILD' as [exec_sql]
INTO #tmp_exec_rebuild_index
FROM #fraglist as F
        INNER JOIN #tbllist as T ON T.FullName = f.ObjectName
WHERE LogicalFrag >= @maxfrag
ORDER BY 1


DECLARE @max_loop int,
        @loopcount int ,
        @exec_sql varchar (4000),
        @exec_descr varchar (4000)
     


SET @max_loop = (SELECT MAX([ord] ) FROM #tmp_exec_rebuild_index)
SET @loopcount = 1

WHILE (@loopcount <=@max_loop )
BEGIN
        SET @exec_descr = (SELECT [task_descr] FROM #tmp_exec_rebuild_index WHERE [ord] = @loopcount)
        SET @exec_sql = (SELECT [exec_sql] FROM #tmp_exec_rebuild_index WHERE [ord] = @loopcount )
        PRINT @exec_descr
        EXEC(@exec_sql );
        SET @loopcount = @loopcount + 1
END


 -- Delete the temporary table.
 DROP TABLE #fraglist ;
 DROP TABLE #tbllist ;
 DROP TABLE #tmp_exec_rebuild_index
 GO

Overview data from all tables

count the number of indexes in all tables and is designed to build on that output to better understand how it contributes to storage requirements.   I arbitrarily chose to sort on the total table size but left a commented out line as an example to switch to sorting by total rows.  





set nocount on
if exists(select name from tempdb..sysobjects where name='##tmp') drop table ##tmp
if exists(select name from tempdb..sysobjects where name='##tmp2') drop table ##tmp2

--first temp table can hold the grouped data
--credit to Thava for gathering index counts
SELECT t.name AS TableName, t.[object_id], 
SUM ( CASE WHEN i.is_primary_key = 1 THEN 1 ELSE 0 END ) AS Primarykey, 
SUM ( CASE WHEN i.[type] = 1 THEN 1 ELSE 0 END ) AS ClusteredIndex, 
SUM ( CASE WHEN i.[type] = 2 THEN 1 ELSE 0 END ) AS NonClusteredIndex, 
SUM ( CASE WHEN i.[type] = 0 THEN 1 ELSE 0 END ) AS HeapIndex, 
COUNT ( * ) TotalNoofIndex into ##tmp2
FROM   sys.tables t
       LEFT OUTER JOIN sys.indexes i
            ON  i.[object_id] = t.[object_id]
GROUP BY
       t.name, t.[object_id]
order by TableName asc


--second temp table will hold the sizes
create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))
go

declare @tblname varchar(50)
declare tblname CURSOR for select name from sysobjects where x'U'

open tblname

Fetch next from tblname into @tblname

WHILE @@FETCH_STATUS = 0
  BEGIN
    insert into ##tmp
    exec sp_spaceused @tblname
    FETCH NEXT FROM tblname INTO @tblname
  END
CLOSE tblname

deallocate tblname
go
update ##tmp set 
  res = round((cast(REPLACE(res,' KB','') as real) / 1024),-1), --convert to MB
  data = round((cast(REPLACE(data,' KB','') as real) / 1024),-1),
  ind_sze  = round((cast(REPLACE(ind_sze,' KB','') as real) / 1024),-1),
  unsed  = round((cast(REPLACE(unsed,' KB','') as real) / 1024),-1)
  
select nam Table_Name,rows Total_Rows,res Total_Size_MB,data Data_size_MB,ind_sze Index_Size_MB,unsed Unused_Space,##tmp2.TotalNoofIndex as [Number of Indexes],##tmp2.ClusteredIndex as [NumClustered], ##tmp2.NonClusteredIndex as [NumNonClustered], ##tmp2.HeapIndex as [NumHeap], ##tmp2.Primarykey as [Has Primary Key]
from ##tmp
join ##tmp2 on ##tmp.nam = ##tmp2.TableName 
 order by cast(res as real) desc
--order by rows desc

drop table ##tmp
drop table ##tmp2

Get Scheduled Jobs from Multiple Servers

I needed to be able to loop thru several SQL servers and get the active maintenance jobs and the next run times. I hobled together several different scripts and came up with the below list.


-- Use the to find "Maintenance" Jobs. Just put a "%" in it for all jobs. % is a wild card in TSQL
DECLARE @JobName VARCHAR(50)
SET @JobName = 'maintenance%'

-- Server List, ALLWAYS have a comma at end
-- This list MUST be linked servers from whatever SQL server you are running it on
DECLARE @ServerName VARCHAR(30) 
       ,@position INT 
       ,@ServerList varchar(8000),
       @HoursForward varchar(300); 
SET @position=0; 
SET @ServerList='SAMY-PC\SQL2008R2'; 

-- Variable to hold dynamic SQL
DECLARE @sql VARCHAR(8000)     

-- Beginning of loop for servers
WHILE charindex(',',@ServerList)>0 
BEGIN 

-- Get the next server in the list
SET @ServerName = cast(substring(@ServerList,0, charindex(',',@ServerList)) as VARCHAR(30)) 

-- This gets all the jobs, part 2 below queries the results
SET @sql = 'WITH OurJobs AS (
    SELECT  job.[name]
     , CASE job.[description] WHEN ''No description available.'' THEN NULL ELSE job.description END AS Description
     , CASE sched.next_run_date
            WHEN 0 THEN ''Never''
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(sched.next_run_date as varchar), 120), 120)+'' ''+
              RIGHT(''0''+CAST((sched.next_run_time/10000) AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000))/100 AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((sched.next_run_time-((sched.next_run_time/10000)*10000)-((sched.next_run_time-((sched.next_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
      END AS NextRunDateTime

      , (
        SELECT CASE last_run_date
            WHEN 0 THEN ''Never''
            ELSE
              CONVERT(varchar(10), CONVERT(smalldatetime, CAST(last_run_date as varchar), 120), 120)+'' ''+
              RIGHT(''0''+CAST((last_run_time/10000) AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((last_run_time-((last_run_time/10000)*10000))/100 AS VARCHAR), 2)+'':''+
              RIGHT(''0''+CAST((last_run_time-((last_run_time/10000)*10000)-((last_run_time-((last_run_time/10000)*10000))/100*100)) AS VARCHAR), 2)
          END AS LastRunDateTime
        FROM ' + @ServerName + '.msdb.dbo.sysjobsteps
        WHERE job_id = job.job_id AND step_id = (
          SELECT MAX(step_id)
          FROM ' + @ServerName + '.msdb.dbo.sysjobsteps
          WHERE job_id = job.job_id
        )
      ) as LastSuccessfulExecution
      , job.date_modified
   FROM ' + @ServerName + '.msdb.dbo.sysjobs job JOIN ' + @ServerName + '.msdb.dbo.sysjobschedules sched
        ON sched.job_id = job.job_id
    WHERE job.enabled = 1 -- remove this if you wish to return all jobs
        AND sched.next_run_date > 0 --GETDATE()
)

-- Part 2, queries table of jobs
SELECT * FROM OurJobs 
WHERE DATEDIFF(hh, GETDATE(), NextRunDateTime) <= ' + CAST(@HoursForward AS CHAR(3)) + '
AND NextRunDateTime > GETDATE()
AND name LIKE ''' + @JobName + '''
ORDER BY NextRunDateTime ASC'

--PRINT @sql
EXEC (@sql)

SET @ServerList = substring(@ServerList, charindex(',',@ServerList)+1, LEN(@ServerList) - @position);

END

Top 10 Wait State Issues Reporter

The execution of this script will produce a report of the top 10 issues with wait states. You can modiy this script to produce more or less information as needed.



/* Top 10 Wait Stats Issiues Reporter*/


/* Create Temporary Tables for processing */

SET NOCOUNT ON;

create table #results
( wait_type nvarchar(60)
, waiting_tasks_count bigint
, wait_time_ms bigint
, max_wait_time_ms bigint
, signal_wait_time_ms bigint
, Comment varchar(max)
)


/* Insert Wait State information into temporary tables */

insert #results
( wait_type
, waiting_tasks_count
, wait_time_ms
, max_wait_time_ms
, signal_wait_time_ms
)

(select wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms from sys.dm_os_wait_stats 
where 

   wait_type = 'CXPACKET' 

/* CPU Issues */
OR wait_type = 'SOS_SCHEDULER_YIELD'
/* Network Issues */
OR wait_type = 'ASYNC_NETWORK_IO' 
/* Locking Issues */
OR wait_type = 'LCK_M_BU' 
OR wait_type = 'LCK_M_IS' 
OR wait_type = 'LCK_M_IU' 
OR wait_type = 'LCK_M_IX' 
OR wait_type = 'LCK_M_RIn_NL'
OR wait_type = 'LCK_M_RIn_S'
OR wait_type = 'LCK_M_RIn_U'
OR wait_type = 'LCK_M_RIn_X'
OR wait_type = 'LCK_M_RS_S' 
OR wait_type = 'LCK_M_RS_U' 
OR wait_type = 'LCK_M_RX_S' 
OR wait_type = 'LCK_M_RX_U' 
OR wait_type = 'LCK_M_RX_X' 
OR wait_type = 'LCK_M_S'    
OR wait_type = 'LCK_M_SCH_M' 
OR wait_type = 'LCK_M_SCH_S' 
OR wait_type = 'LCK_M_SIU'   
OR wait_type = 'LCK_M_SIX'   
OR wait_type = 'LCK_M_U'     
OR wait_type = 'LCK_M_UIX'   
OR wait_type = 'LCK_M_X'     
OR wait_type = 'LATCH_DT'    
OR wait_type = 'LATCH_EX'    
OR wait_type = 'LATCH_KP'    
OR wait_type = 'LATCH_SH'    
OR wait_type = 'LATCH_UP'    
--
/* Memory Issues */
OR wait_type = 'RESOURCE_SEMAPHORE' 
OR wait_type = 'RESOURCE_SEMAPHORE_MUTEX'
OR wait_type = 'RESOURCE_SEMAPHORE_QUERY_COMPILE'
OR wait_type = 'RESOURCE_SEMAPHORE_SMALL_QUERY' 
OR wait_type = 'WRITELOG'

/* Disk or Disk Subsystem Issues */
OR wait_type = 'PAGEIOLATCH_DT' 
OR wait_type = 'PAGEIOLATCH_EX' 
OR wait_type = 'PAGEIOLATCH_KP' 
OR wait_type = 'PAGEIOLATCH_SH' 
OR wait_type = 'PAGEIOLATCH_UP' 
OR wait_type = 'PAGELATCH_DT' 
OR wait_type = 'PAGELATCH_EX' 
OR wait_type = 'PAGELATCH_KP' 
OR wait_type = 'PAGELATCH_SH' 
OR wait_type = 'PAGELATCH_UP' 
OR wait_type = 'LOGBUFFER'
OR wait_type = 'ASYNC_IO_COMPLETION'
OR wait_type = 'IO_COMPLETION' 
)

DECLARE Waits_Cursor CURSOR FOR
SELECT [wait_type] FROM [dbo].[#results] 
OPEN Waits_Cursor
FETCH NEXT FROM Waits_Cursor
BEGIN
UPDATE [dbo].[#results] SET [Comment] = 'CPU - Execute this script: SELECT scheduler_id, current_tasks_count, runnable_tasks_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255 ;If runnable tasks count > zero, CPU issues if double digits for any length of time, extreme CPU concern' WHERE  dbo.[#results].[wait_type] = 'SOS_SCHEDULER_YIELD'
UPDATE [dbo].[#results] SET [Comment] = 'SETTINGS OR CODE - Wait stats shows more than 5% of your waits are on CXPackets, you may want to test lower (or non-zero) values of “max degree of parallelism”. Never set value great than # of CPUs' WHERE  dbo.[#results].[wait_type] = 'CXPACKET'
UPDATE [dbo].[#results] SET [Comment] = 'NETWORK - Occurs on network writes when the task is blocked behind the network' WHERE  dbo.[#results].[wait_type] = 'ASYNC_NETWORK_IO'
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Bulk Update (BU) lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_BU'
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Intent Shared (IS) lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_IS'
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Intent Update (IU) lock ' WHERE  dbo.[#results].[wait_type] = 'LCK_M_IU'
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Intent Exclusive (IX) lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_IX'
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a NULL lock on the current key value and an Insert Range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RIn_NL'
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a shared lock on the current key value and an Insert Range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RIn_S' 
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RIn_U' 
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RIn_X' 
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RS_S'  
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RS_U'  
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RX_S'  
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RX_U'  
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key' WHERE  dbo.[#results].[wait_type] = 'LCK_M_RX_X'  
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Shared lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_S'     
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Schema Modify lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_SCH_M' 
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Schema Modify lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_SCH_S' 
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Shared With Intent Update lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_SIU'   
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire a Shared With Intent Exclusive lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_SIX'   
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Update lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_U'     
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Update With Intent Exclusive lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_UIX'   
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting to acquire an Exclusive lock' WHERE  dbo.[#results].[wait_type] = 'LCK_M_X'     
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches' WHERE  dbo.[#results].[wait_type] = 'LATCH_DT'    
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches' WHERE  dbo.[#results].[wait_type] = 'LATCH_EX'    
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches' WHERE  dbo.[#results].[wait_type] = 'LATCH_KP'    
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches' WHERE  dbo.[#results].[wait_type] = 'LATCH_SH'    
UPDATE [dbo].[#results] SET [Comment] = 'LOCK - Waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches' WHERE  dbo.[#results].[wait_type] = 'LATCH_UP'
UPDATE [dbo].[#results] SET [Comment] = 'MEMORY - Query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts' WHERE  dbo.[#results].[wait_type] = 'RESOURCE_SEMAPHORE'
UPDATE [dbo].[#results] SET [Comment] = 'MEMORY - Query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests' WHERE  dbo.[#results].[wait_type] = 'RESOURCE_SEMAPHORE_MUTEX'
UPDATE [dbo].[#results] SET [Comment] = 'MEMORY - Number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans' WHERE  dbo.[#results].[wait_type] = 'RESOURCE_SEMAPHORE_QUERY_COMPILE'
UPDATE [dbo].[#results] SET [Comment] = 'MEMORY - Memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries' WHERE  dbo.[#results].[wait_type] = 'RESOURCE_SEMAPHORE_SMALL_QUERY'
UPDATE [dbo].[#results] SET [Comment] = 'MEMORY - Waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits' WHERE  dbo.[#results].[wait_type] = 'WRITELOG'
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem' WHERE  dbo.[#results].[wait_type] = 'PAGEIOLATCH_DT' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem' WHERE  dbo.[#results].[wait_type] = 'PAGEIOLATCH_EX' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem' WHERE  dbo.[#results].[wait_type] = 'PAGEIOLATCH_KP' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Share mode. Long waits may indicate problems with the disk subsystem' WHERE  dbo.[#results].[wait_type] = 'PAGEIOLATCH_SH' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem' WHERE  dbo.[#results].[wait_type] = 'PAGEIOLATCH_UP' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode' WHERE  dbo.[#results].[wait_type] = 'PAGELATCH_DT' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode' WHERE  dbo.[#results].[wait_type] = 'PAGELATCH_EX' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode' WHERE  dbo.[#results].[wait_type] = 'PAGELATCH_KP' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode' WHERE  dbo.[#results].[wait_type] = 'PAGELATCH_SH' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode' WHERE  dbo.[#results].[wait_type] = 'PAGELATCH_UP' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server' WHERE  dbo.[#results].[wait_type] = 'LOGBUFFER' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting for I/Os to finish' WHERE  dbo.[#results].[wait_type] = 'ASYNC_IO_COMPLETION' 
UPDATE [dbo].[#results] SET [Comment] = 'DISK - Waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits' WHERE  dbo.[#results].[wait_type] = 'IO_COMPLETION' 
END
  
FETCH NEXT FROM Waits_Cursor
CLOSE Waits_Cursor
DEALLOCATE Waits_Cursor

/* View Final Results */
SELECT TOP 10
   WAIT_TYPE AS 'OS WAIT STATS - WAIT TYPE'
 , WAITING_TASKS_COUNT AS 'TASKS WAITING COUNT'
 , WAIT_TIME_MS AS 'TIME WAITING (MS)'
 , MAX_WAIT_TIME_MS AS 'MAX TIME WAITING (MS)'
 , SIGNAL_WAIT_TIME_MS AS 'SIGNAL TIME WAITING (MS)'
 , COMMENT AS 'POSSIBLE ISSUES'
 FROM #RESULTS
 WHERE WAITING_TASKS_COUNT <> 0
 ORDER BY WAIT_TIME_MS DESC

 /* Clean Up */
DROP TABLE #RESULTS;
GO

Intelligent Index Reorganize and Rebuild Script

This script will provide a dynamic mechanism to decide between REBUILDING an index or simply REORGANIZING an index.  This will improve performance when this critical maintenance process executes.  This version of the script provided has been tested and approved for use on SQL Server 2005 & 2008 R2 Standard or Enterprise or R2.  This script is very thorough and provides several intelligence mechanisms for determining when and how to perform index maintenance on a given table.



Rules For Index Maintenance:

1.) Are there open cursors in the database, if so skip the database.

2.) Index Size is greater than 5 MB's.

3.) Reorganize = fragmentation level is between 5% and 30%

4.) Rebuild = fragmentation level is greater than 30%



In addition to the index maintenance script provided, there is a table named IndexMaintenanceHistory that will collect the historical runs of the index maintenance job.  This is useful for auditing purposes when you need to find out if certain indexes are having maintenance completed as required.






/****** Object:  StoredProcedure [dbo].[ReorgRebuildIndex]    Script Date: 10/18/2012 09:23:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReorgRebuildIndex]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ReorgRebuildIndex]
GO


/****** Object:  StoredProcedure [dbo].[ReorgRebuildIndex]     ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[ReorgRebuildIndex]
AS




DECLARE @indexCommandString VARCHAR(5000),
@dynSQL VARCHAR(5000),
@databaseName VARCHAR(255),
@executionStartTime DATETIME,
@executionCompleteTime DATETIME


CREATE TABLE #indexCommands
(
indexCommand VARCHAR(4000)
)

--Record which databases are being skipped for index maintenance due to open cursors.
SET @dynSQL = 'INSERT INTO dbo.IndexMaintenanceHistory
(
DatabaseName,
IndexCommandString,
DateTimeExecuted,
DateTimeCompleted

SELECT NAME,
  ''Index maintenance skipped for database ''+ NAME +'' for an active CURSOR statement.'',
  GETDATE(),
  GETDATE() 
FROM sys.sysdatabases
WHERE DBID > 4 AND dbid IN (
SELECT DISTINCT PRO.dbid 
FROM sys.dm_exec_cursors(0) CURS INNER JOIN sys.sysprocesses PRO
ON CURS.session_ID = PRO.spid
WHERE is_open =1 
)
AND dbid NOT IN (
SELECT DISTINCT SP.[dbid]
FROM sys.sysprocesses SP CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE [TEXT] LIKE ''FETCH%''
)
ORDER BY NAME '

EXEC (@dynSQL)


--Begin our maintenance tasks.
DECLARE curDatabase CURSOR FOR
--This statement filters out system databases and databases with open cursors. 
SELECT NAME 
FROM sys.sysdatabases
WHERE DBID > 4 AND dbid NOT IN (
SELECT DISTINCT PRO.dbid 
FROM sys.dm_exec_cursors(0) CURS INNER JOIN sys.sysprocesses PRO
ON CURS.session_ID = PRO.spid
WHERE is_open =1 
)
AND dbid NOT IN (
SELECT DISTINCT SP.[dbid]
FROM sys.sysprocesses SP CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE [TEXT] LIKE 'FETCH%'
)
ORDER BY NAME
OPEN curDatabase
FETCH NEXT FROM curDatabase INTO @databaseName
WHILE @@FETCH_STATUS = 0 
BEGIN
SET @executionStartTime = GETDATE()
/**************************************************************
* Begin Index Maintenance
**************************************************************/
TRUNCATE TABLE #indexCommands
--This looks for fragmented indexes that have atleast 5 MB's of data stored.
SET @dynSQL = '
USE [' +@databaseName + ']
--Lightweight method for checking index fragmentation in a given database.
SELECT CASE WHEN avg_fragmentation_in_percent BETWEEN 5 AND 30 THEN
''ALTER INDEX ['' + name + ''] ON '' + (SELECT TOP 1 TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = ''BASE TABLE'')
+ ''.['' + OBJECT_NAME(b.[OBJECT_ID]) + ''] REORGANIZE ;''
WHEN avg_fragmentation_in_percent > 30 THEN
''ALTER INDEX ['' + name + ''] ON '' + (SELECT TOP 1 TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = OBJECT_NAME(b.[OBJECT_ID]) AND TABLE_TYPE = ''BASE TABLE'')
+ ''.['' + OBJECT_NAME(b.[OBJECT_ID]) + ''] REBUILD WITH (FILLFACTOR = 90) ;''
END AS Index_Statement
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 5
AND index_type_desc <> ''HEAP''
AND page_count > 640
ORDER BY avg_fragmentation_in_percent DESC'
INSERT INTO #indexCommands(indexCommand)
EXEC (@dynSQL)
DECLARE curIndex CURSOR FOR
SELECT indexCommand
FROM #indexCommands
OPEN curIndex
FETCH NEXT FROM curIndex INTO @indexCommandString
WHILE @@FETCH_STATUS = 0

BEGIN
SET @dynSQL = 'USE [' +@databaseName+ ']
' + @indexCommandString

EXEC(@dynSQL)

SET @executionCompleteTime = GETDATE()

INSERT INTO dbo.IndexMaintenanceHistory
(
DatabaseName,
IndexCommandString,
DateTimeExecuted,
DateTimeCompleted
)
VALUES
(
@databaseName,
@indexCommandString,
@executionStartTime,
@executionCompleteTime
)

FETCH NEXT FROM curIndex INTO @indexCommandString
END
CLOSE curIndex
DEALLOCATE curIndex

/**************************************************************
* End Index Maintenance
**************************************************************/

FETCH NEXT FROM curDatabase INTO @databaseName

END

CLOSE curDatabase
DEALLOCATE curDatabase

DROP TABLE #indexCommands

GO




--------------------------------------------------------------------------

/********************************************************************************
* This table is used to collect index maintenance commands created and
*  and executed by the stored procedure dbo.ReorgRebuildIndex.  This will
*  provide a historical record of index maintenance routines performed in
*  the given SQL Server instance.
********************************************************************************/
/****** Object:  Table [dbo].[IndexMaintenanceHistory]    ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[IndexMaintenanceHistory](
[IndexCommandID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](255) NULL,
[IndexCommandString] [varchar](6000) NULL,
[DateTimeExecuted] [datetime] NULL,
[DateTimeCompleted] [datetime] NULL,
 CONSTRAINT [PK_IndexMaintenanceHistory] PRIMARY KEY CLUSTERED 
(
[IndexCommandID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO