Defrag Indexes

/* Drop Table Scripts:
Drop Table dbo.dba_indexDefragLog;
Drop Table dbo.dba_indexDefragExclusion;
*/
IF NOT EXISTS (
  SELECT [OBJECT_ID]
  FROM sys.tables
  WHERE [name] IN (
    N'dba_indexDefragLog'
    ,'dba_indexDefragExclusion'
    )
  )
BEGIN
 CREATE TABLE dbo.dba_indexDefragLog (
  indexDefrag_id INT IDENTITY(1, 1) NOT NULL
  ,databaseID INT NOT NULL
  ,databaseName NVARCHAR(128) NOT NULL
  ,objectID INT NOT NULL
  ,objectName NVARCHAR(128) NOT NULL
  ,indexID INT NOT NULL
  ,indexName NVARCHAR(128) NOT NULL
  ,partitionNumber SMALLINT NOT NULL
  ,fragmentation FLOAT NOT NULL
  ,page_count INT NOT NULL
  ,dateTimeStart DATETIME NOT NULL
  ,dateTimeEnd DATETIME NULL
  ,durationSeconds INT NULL CONSTRAINT PK_indexDefragLog PRIMARY KEY CLUSTERED (indexDefrag_id)
  );

 PRINT 'dba_indexDefragLog Table Created';

 CREATE TABLE dbo.dba_indexDefragExclusion (
  databaseID INT NOT NULL
  ,databaseName NVARCHAR(128) NOT NULL
  ,objectID INT NOT NULL
  ,objectName NVARCHAR(128) NOT NULL
  ,indexID INT NOT NULL
  ,indexName NVARCHAR(128) NOT NULL
  ,exclusionMask INT NOT NULL
  /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
  CONSTRAINT PK_indexDefragExclusion PRIMARY KEY CLUSTERED (
   databaseID
   ,objectID
   ,indexID
   )
  );

 PRINT 'dba_indexDefragExclusion Table Created';
END
ELSE
 RAISERROR (
   'One or more tables already exist.  Please drop or rename before proceeding.'
   ,16
   ,0
   );

IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
BEGIN
 DROP PROCEDURE dbo.dba_indexDefrag_sp;

 PRINT 'Procedure dba_indexDefrag_sp dropped';
END;
GO

CREATE PROCEDURE dbo.dba_indexDefrag_sp
 /* Declare Parameters */
 @minFragmentation FLOAT = 5.0
 /* in percent, will not defrag if fragmentation less than specified */
 ,@rebuildThreshold FLOAT = 30.0
 /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
 ,@executeSQL BIT = 1
 /* 1 = execute; 0 = print command only */
 ,@DATABASE VARCHAR(128) = NULL
 /* Option to specify a database name; null will return all */
 ,@tableName VARCHAR(4000) = NULL -- databaseName.schema.tableName
 /* Option to specify a table name; null will return all */
 ,@scanMode VARCHAR(10) = N'LIMITED'
 /* Options are LIMITED, SAMPLED, and DETAILED */
 ,@onlineRebuild BIT = 1
 /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
 ,@maxDopRestriction TINYINT = NULL
 /* Option to restrict the number of processors for the operation; only in Enterprise */
 ,@printCommands BIT = 0
 /* 1 = print commands; 0 = do not print commands */
 ,@printFragmentation BIT = 0
 /* 1 = print fragmentation prior to defrag; 
           0 = do not print */
 ,@defragDelay CHAR(8) = '00:00:05'
 /* time to wait between defrag commands */
 ,@debugMode BIT = 0
 /* display some useful comments to help determine if/where issues occur */
 ,@rebuildStats BIT = 1
 /* option to rebuild stats after completed index defrags */
AS
/*
    Exec dbo.dba_indexDefrag_sp
          @executeSQL           = 0
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1;
*********************************************************************************/
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;

BEGIN
 IF @debugMode = 1
  RAISERROR (
    'Undusting the cogs and starting up...'
    ,0
    ,42
    )
  WITH NOWAIT;

 /* Declare our variables */
 DECLARE @objectID INT
  ,@databaseID INT
  ,@databaseName NVARCHAR(128)
  ,@indexID INT
  ,@partitionCount BIGINT
  ,@schemaName NVARCHAR(128)
  ,@objectName NVARCHAR(128)
  ,@indexName NVARCHAR(128)
  ,@partitionNumber SMALLINT
  ,@fragmentation FLOAT
  ,@pageCount INT
  ,@sqlCommand NVARCHAR(4000)
  ,@rebuildCommand NVARCHAR(200)
  ,@dateTimeStart DATETIME
  ,@dateTimeEnd DATETIME
  ,@containsLOB BIT
  ,@editionCheck BIT
  ,@debugMessage VARCHAR(128)
  ,@updateSQL NVARCHAR(4000)
  ,@partitionSQL NVARCHAR(4000)
  ,@partitionSQL_Param NVARCHAR(1000)
  ,@LOB_SQL NVARCHAR(4000)
  ,@LOB_SQL_Param NVARCHAR(1000)
  ,@rebuildStatsID INT
  ,@rebuildStatsSQL NVARCHAR(1000)
  ,@indexDefrag_id INT;

 /* Create our temporary tables */
 CREATE TABLE #indexDefragList (
  databaseID INT
  ,databaseName NVARCHAR(128)
  ,objectID INT
  ,indexID INT
  ,partitionNumber SMALLINT
  ,fragmentation FLOAT
  ,page_count INT
  ,defragStatus BIT
  ,schemaName NVARCHAR(128) NULL
  ,objectName NVARCHAR(128) NULL
  ,indexName NVARCHAR(128) NULL
  );

 CREATE TABLE #databaseList (
  databaseID INT
  ,databaseName VARCHAR(128)
  ,scanStatus BIT
  ,statsStatus BIT
  );

 CREATE TABLE #processor (
  [INDEX] INT
  ,NAME VARCHAR(128)
  ,Internal_Value INT
  ,Character_Value INT
  );

 IF @debugMode = 1
  RAISERROR (
    'Beginning validation...'
    ,0
    ,42
    )
  WITH NOWAIT;

 /* Just a little validation... */
 IF @minFragmentation NOT BETWEEN 0.00
   AND 100.0
  SET @minFragmentation = 10.0;

 IF @rebuildThreshold NOT BETWEEN 0.00
   AND 100.0
  SET @rebuildThreshold = 30.0;

 IF @defragDelay NOT LIKE '00:[0-5][0-9]:[0-5][0-9]'
  SET @defragDelay = '00:00:05';

 IF @scanMode NOT IN (
   'LIMITED'
   ,'SAMPLED'
   ,'DETAILED'
   )
  SET @scanMode = 'LIMITED';

 /* Make sure we're not exceeding the number of processors we have available */
 INSERT INTO #processor
 EXECUTE XP_MSVER 'ProcessorCount';

 IF @maxDopRestriction IS NOT NULL
  AND @maxDopRestriction > (
   SELECT Internal_Value
   FROM #processor
   )
  SELECT @maxDopRestriction = Internal_Value
  FROM #processor;

 /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
 IF (
   SELECT SERVERPROPERTY('EditionID')
   ) IN (
   1804890536
   ,610778273
   ,- 2117995310
   )
  SET @editionCheck = 1 -- supports online rebuilds
 ELSE
  SET @editionCheck = 0;-- does not support online rebuilds

 IF @debugMode = 1
  RAISERROR (
    'Grabbing a list of our databases...'
    ,0
    ,42
    )
  WITH NOWAIT;

 /* Retrieve the list of databases to investigate */
 INSERT INTO #databaseList
 SELECT database_id
  ,NAME
  ,0 -- not scanned yet for fragmentation
  ,0 -- statistics not yet updated
 FROM sys.databases
 WHERE NAME = IsNull(@DATABASE, NAME)
  AND [name] NOT IN (
   'master'
   ,'tempdb'
   ) -- exclude system databases
  AND [STATE] = 0;-- state must be ONLINE

 IF @debugMode = 1
  RAISERROR (
    'Looping through our list of databases and checking for fragmentation...'
    ,0
    ,42
    )
  WITH NOWAIT;

 /* Loop through our list of databases */
 WHILE (
   SELECT COUNT(*)
   FROM #databaseList
   WHERE scanStatus = 0
   ) > 0
 BEGIN
  SELECT TOP 1 @databaseID = databaseID
  FROM #databaseList
  WHERE scanStatus = 0;

  SELECT @debugMessage = '  working on ' + DB_NAME(@databaseID) + '...';

  IF @debugMode = 1
   RAISERROR (
     @debugMessage
     ,0
     ,42
     )
   WITH NOWAIT;

  /* Determine which indexes to defrag using our user-defined parameters */
  INSERT INTO #indexDefragList
  SELECT database_id AS databaseID
   ,QUOTENAME(DB_NAME(database_id)) AS 'databaseName'
   ,[OBJECT_ID] AS objectID
   ,index_id AS indexID
   ,partition_number AS partitionNumber
   ,avg_fragmentation_in_percent AS fragmentation
   ,page_count
   ,0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */
   ,NULL AS 'schemaName'
   ,NULL AS 'objectName'
   ,NULL AS 'indexName'
  FROM sys.dm_db_index_physical_stats(@databaseID, OBJECT_ID(@tableName), NULL, NULL, @scanMode)
  WHERE avg_fragmentation_in_percent >= @minFragmentation
   AND index_id > 0 -- ignore heaps
   AND page_count > 8 -- ignore objects with less than 1 extent
   AND index_level = 0 -- leaf-level nodes only, supports @scanMode
  OPTION (MAXDOP 2);

  /* Keep track of which databases have already been scanned */
  UPDATE #databaseList
  SET scanStatus = 1
  WHERE databaseID = @databaseID;
 END

 CREATE CLUSTERED INDEX CIX_temp_indexDefragList ON #indexDefragList (
  databaseID
  ,objectID
  ,indexID
  ,partitionNumber
  );

 /* Delete any indexes from our to-do that are also in our exclusion list for today */
 DELETE idl
 FROM #indexDefragList AS idl
 INNER JOIN dbo.dba_indexDefragExclusion AS ide ON idl.databaseID = ide.databaseID
  AND idl.objectID = ide.objectID
  AND idl.indexID = ide.indexID
 WHERE exclusionMask & POWER(2, DATEPART(weekday, GETDATE()) - 1) > 0;

 SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
 FROM #indexDefragList;

 IF @debugMode = 1
  RAISERROR (
    @debugMessage
    ,0
    ,42
    )
  WITH NOWAIT;

 /* Begin our loop for defragging */
 WHILE (
   SELECT COUNT(*)
   FROM #indexDefragList
   WHERE defragStatus = 0
   ) > 0
 BEGIN
  IF @debugMode = 1
   RAISERROR (
     '  Picking an index to beat into shape...'
     ,0
     ,42
     )
   WITH NOWAIT;

  /* Grab the most fragmented index first to defrag */
  SELECT TOP 1 @objectID = objectID
   ,@indexID = indexID
   ,@databaseID = databaseID
   ,@databaseName = databaseName
   ,@fragmentation = fragmentation
   ,@partitionNumber = partitionNumber
   ,@pageCount = page_count
  FROM #indexDefragList
  WHERE defragStatus = 0
  ORDER BY fragmentation DESC;

  IF @debugMode = 1
   RAISERROR (
     '  Looking up the specifics for our index...'
     ,0
     ,42
     )
   WITH NOWAIT;

  /* Look up index information */
  SELECT @updateSQL = N'Update idl
            Set schemaName = QuoteName(s.name)
                , objectName = QuoteName(o.name)
                , indexName = QuoteName(i.name)
            From #indexDefragList As idl
            Inner Join ' + @databaseName + '.sys.objects As o
                On idl.objectID = o.object_id
            Inner Join ' + @databaseName + '.sys.indexes As i
                On o.object_id = i.object_id
            Inner Join ' + @databaseName + '.sys.schemas As s
                On o.schema_id = s.schema_id
            Where o.object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                And i.index_id = ' + CAST(@indexID AS VARCHAR(10)) + '
                And i.type > 0
                And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10));

  EXECUTE SP_EXECUTESQL @updateSQL;

  /* Grab our object names */
  SELECT @objectName = objectName
   ,@schemaName = schemaName
   ,@indexName = indexName
  FROM #indexDefragList
  WHERE objectID = @objectID
   AND indexID = @indexID
   AND databaseID = @databaseID;

  IF @debugMode = 1
   RAISERROR (
     '  Grabbing the partition count...'
     ,0
     ,42
     )
   WITH NOWAIT;

  /* Determine if the index is partitioned */
  SELECT @partitionSQL = 'Select @partitionCount_OUT = Count(*)
                                    From ' + @databaseName + '.sys.partitions
                                    Where object_id = ' + CAST(@objectID AS VARCHAR(10)) + '
                                        And index_id = ' + CAST(@indexID AS VARCHAR(10)) + ';'
   ,@partitionSQL_Param = '@partitionCount_OUT int OutPut';

  EXECUTE SP_EXECUTESQL @partitionSQL
   ,@partitionSQL_Param
   ,@partitionCount_OUT = @partitionCount OUTPUT;

  IF @debugMode = 1
   RAISERROR (
     '  Seeing if there''s any LOBs to be handled...'
     ,0
     ,42
     )
   WITH NOWAIT;

  /* Determine if the table contains LOBs */
  SELECT @LOB_SQL = ' Select @containsLOB_OUT = Count(*)
                            From ' + @databaseName + '.sys.columns With (NoLock) 
                            Where [object_id] = ' + CAST(@objectID AS VARCHAR(10)) + '
                                And (system_type_id In (34, 35, 99)
                                        Or max_length = -1);'
   /*  system_type_id --> 34 = image, 35 = text, 99 = ntext
                                max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
   ,@LOB_SQL_Param = '@containsLOB_OUT int OutPut';

  EXECUTE SP_EXECUTESQL @LOB_SQL
   ,@LOB_SQL_Param
   ,@containsLOB_OUT = @containsLOB OUTPUT;

  IF @debugMode = 1
   RAISERROR (
     '  Building our SQL statements...'
     ,0
     ,42
     )
   WITH NOWAIT;

  /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
  IF @fragmentation < @rebuildThreshold
   OR @containsLOB >= 1
   OR @partitionCount > 1
  BEGIN
   SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + N' ReOrganize';

   /* If our index is partitioned, we should always reorganize */
   IF @partitionCount > 1
    SET @sqlCommand = @sqlCommand + N' Partition = ' + CAST(@partitionNumber AS NVARCHAR(10));
  END;

  /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, rebuild it */
  IF @fragmentation >= @rebuildThreshold
   AND IsNull(@containsLOB, 0) != 1
   AND @partitionCount <= 1
  BEGIN
   /* Set online rebuild options; requires Enterprise Edition */
   IF @onlineRebuild = 1
    AND @editionCheck = 1
    SET @rebuildCommand = N' Rebuild With (Online = On';
   ELSE
    SET @rebuildCommand = N' Rebuild With (Online = Off';

   /* Set processor restriction options; requires Enterprise Edition */
   IF @maxDopRestriction IS NOT NULL
    AND @editionCheck = 1
    SET @rebuildCommand = @rebuildCommand + N', MaxDop = ' + CAST(@maxDopRestriction AS VARCHAR(2)) + N')';
   ELSE
    SET @rebuildCommand = @rebuildCommand + N')';

   SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' + @schemaName + N'.' + @objectName + @rebuildCommand;
  END;

  /* Are we executing the SQL?  If so, do it */
  IF @executeSQL = 1
  BEGIN
   IF @debugMode = 1
    RAISERROR (
      '  Executing SQL statements...'
      ,0
      ,42
      )
    WITH NOWAIT;

   /* Grab the time for logging purposes */
   SET @dateTimeStart = GETDATE();

   /* Log our actions */
   INSERT INTO dbo.dba_indexDefragLog (
    databaseID
    ,databaseName
    ,objectID
    ,objectName
    ,indexID
    ,indexName
    ,partitionNumber
    ,fragmentation
    ,page_count
    ,dateTimeStart
    )
   SELECT @databaseID
    ,@databaseName
    ,@objectID
    ,@objectName
    ,@indexID
    ,@indexName
    ,@partitionNumber
    ,@fragmentation
    ,@pageCount
    ,@dateTimeStart;

   SET @indexDefrag_id = SCOPE_IDENTITY();

   /* Execute our defrag! */
   EXECUTE SP_EXECUTESQL @sqlCommand;

   SET @dateTimeEnd = GETDATE();

   /* Update our log with our completion time */
   UPDATE dbo.dba_indexDefragLog
   SET dateTimeEnd = @dateTimeEnd
    ,durationSeconds = DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd)
   WHERE indexDefrag_id = @indexDefrag_id;

   /* Just a little breather for the server */
   WAITFOR DELAY @defragDelay;

   /* Print if specified to do so */
   IF @printCommands = 1
    PRINT N'Executed: ' + @sqlCommand;
  END
  ELSE
   /* Looks like we're not executing, just printing the commands */
  BEGIN
   IF @debugMode = 1
    RAISERROR (
      '  Printing SQL statements...'
      ,0
      ,42
      )
    WITH NOWAIT;

   IF @printCommands = 1
    PRINT IsNull(@sqlCommand, 'error!');
  END

  IF @debugMode = 1
   RAISERROR (
     '  Updating our index defrag status...'
     ,0
     ,42
     )
   WITH NOWAIT;

  /* Update our index defrag list so we know we've finished with that index */
  UPDATE #indexDefragList
  SET defragStatus = 1
  WHERE databaseID = @databaseID
   AND objectID = @objectID
   AND indexID = @indexID
   AND partitionNumber = @partitionNumber;
 END

 /* Do we want to output our fragmentation results? */
 IF @printFragmentation = 1
 BEGIN
  IF @debugMode = 1
   RAISERROR (
     '  Displaying fragmentation results...'
     ,0
     ,42
     )
   WITH NOWAIT;

  SELECT databaseID
   ,databaseName
   ,objectID
   ,objectName
   ,indexID
   ,indexName
   ,fragmentation
   ,page_count
  FROM #indexDefragList;
 END;

 /* Do we want to rebuild stats? */
 IF @rebuildStats = 1
 BEGIN
  WHILE EXISTS (
    SELECT TOP 1 *
    FROM #databaseList
    WHERE statsStatus = 0
    )
  BEGIN
   /* Build our SQL statement to update stats */
   SELECT TOP 1 @rebuildStatsSQL = 'Use [' + databaseName + ']; ' + 'Execute sp_updatestats;'
    ,@rebuildStatsID = databaseID
   FROM #databaseList
   WHERE statsStatus = 0;

   SET @debugMessage = 'Rebuilding Statistics: ' + @rebuildStatsSQL;

   IF @debugMode = 1
    RAISERROR (
      @debugMessage
      ,0
      ,42
      )
    WITH NOWAIT;

   /* Execute our stats update! */
   EXECUTE SP_EXECUTESQL @rebuildStatsSQL;

   /* Keep track of which databases have been updated */
   UPDATE #databaseList
   SET statsStatus = 1
   WHERE databaseID = @rebuildStatsID;
  END;
 END;

 /* When everything is said and done, make sure to get rid of our temp table */
 DROP TABLE #indexDefragList;

 DROP TABLE #databaseList;

 DROP TABLE #processor;

 IF @debugMode = 1
  RAISERROR (
    'DONE!  Thank you for taking care of your indexes!  :)'
    ,0
    ,42
    )
  WITH NOWAIT;

 SET NOCOUNT OFF;

 RETURN 0
END
GO

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO

Partitioned Tables and Columns

SELECT t.NAME AS TableName
 ,SCHEMA_NAME(T.schema_id) SchemaName
 ,ps.NAME AS PartitionScheme
 ,c.NAME
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN sys.index_columns ic ON (
  ic.partition_ordinal > 0
  AND ic.index_id = i.index_id
  AND ic.object_id = t.object_id
  )
INNER JOIN sys.columns c ON (
  c.object_id = ic.object_id
  AND c.column_id = ic.column_id
  )
WHERE i.index_id < 2

Table Sizes

Find the Biggest Tables:

DECLARE @id INT
DECLARE @type CHAR(2)
DECLARE @pages INT
DECLARE @dbname SYSNAME
DECLARE @dbsize DECIMAL(15, 0)
DECLARE @bytesperpage DECIMAL(15, 0)
DECLARE @pagesperMB DECIMAL(15, 0)

CREATE TABLE #spt_space (
 objid INT NULL
 ,rows INT NULL
 ,reserved DECIMAL(15) NULL
 ,data DECIMAL(15) NULL
 ,indexp DECIMAL(15) NULL
 ,unused DECIMAL(15) NULL
 )

SET NOCOUNT ON

-- Create a cursor to loop through the user tables
DECLARE c_tables CURSOR
FOR
SELECT id
FROM sysobjects
WHERE xtype = 'U'

OPEN c_tables

FETCH NEXT
FROM c_tables
INTO @id

WHILE @@fetch_status = 0
BEGIN
 /* Code from sp_spaceused */
 INSERT INTO #spt_space (
  objid
  ,reserved
  )
 SELECT objid = @id
  ,sum(reserved)
 FROM sysindexes
 WHERE indid IN (
   0
   ,1
   ,255
   )
  AND id = @id

 SELECT @pages = sum(dpages)
 FROM sysindexes
 WHERE indid < 2
  AND id = @id

 SELECT @pages = @pages + isnull(sum(used), 0)
 FROM sysindexes
 WHERE indid = 255
  AND id = @id

 UPDATE #spt_space
 SET data = @pages
 WHERE objid = @id

 /* index: sum(used) where indid in (0, 1, 255) - data */
 UPDATE #spt_space
 SET indexp = (
   SELECT sum(used)
   FROM sysindexes
   WHERE indid IN (
     0
     ,1
     ,255
     )
    AND id = @id
   ) - data
 WHERE objid = @id

 /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
 UPDATE #spt_space
 SET unused = reserved - (
   SELECT sum(used)
   FROM sysindexes
   WHERE indid IN (
     0
     ,1
     ,255
     )
    AND id = @id
   )
 WHERE objid = @id

 UPDATE #spt_space
 SET rows = i.rows
 FROM sysindexes i
 WHERE i.indid < 2
  AND i.id = @id
  AND objid = @id

 FETCH NEXT
 FROM c_tables
 INTO @id
END

SELECT TOP 25 Table_Name = (
  SELECT left(NAME, 25)
  FROM sysobjects
  WHERE id = objid
  )
 ,rows = convert(CHAR(11), rows)
 ,reserved_KB = ltrim(str(reserved * d.low / 1024., 15, 0) + ' ' + 'KB')
 ,data_KB = ltrim(str(data * d.low / 1024., 15, 0) + ' ' + 'KB')
 ,index_size_KB = ltrim(str(indexp * d.low / 1024., 15, 0) + ' ' + 'KB')
 ,unused_KB = ltrim(str(unused * d.low / 1024., 15, 0) + ' ' + 'KB')
FROM #spt_space
 ,master.dbo.spt_values d
WHERE d.number = 1
 AND d.type = 'E'
ORDER BY reserved DESC

DROP TABLE #spt_space

CLOSE c_tables

DEALLOCATE c_tables

Deadlock Graph

Find a Deadlock Graph:

SELECT CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph

FROM (
 SELECT CAST(target_data AS XML) AS TargetData
 FROM sys.dm_xe_session_targets st
 INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
 WHERE NAME = 'system_health'
 ) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData(XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Parse Deadlock graph:

DECLARE @deadlock TABLE (
 DeadlockID INT IDENTITY PRIMARY KEY CLUSTERED
 ,DeadlockGraph XML
 );

INSERT INTO @deadlock
VALUES ('');

-- insert the deadlock XML in the above line! Duplicate as necessary for additional graphs.
WITH CTE
AS (
 SELECT DeadlockID
  ,DeadlockGraph
 FROM @deadlock
 )
 ,Process
AS (
 -- get the data from the process node
 SELECT CTE.DeadlockID
  ,
  --[DeadlockTime]=
  CTE.[DeadlockGraph]
  ,[Victim] = CASE 
   WHEN Deadlock.Process.value('@id', 'varchar(50)') = Deadlock.Process.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)')
    THEN 1
   ELSE 0
   END
  ,[LockMode] = Deadlock.Process.value('@lockMode', 'varchar(3)')
  ,[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)')
  ,[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)')
  ,[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)')
  ,[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime')
  ,[BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime')
  ,[BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime')
  ,[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
  ,[ProcessID] = Deadlock.Process.value('@id', 'varchar(50)')
  ,[SPID] = Deadlock.Process.value('@spid', 'int')
  ,-- server process id
  [SBID] = Deadlock.Process.value('@sbid', 'int')
  ,-- server batch id
  [ECID] = Deadlock.Process.value('@ecid', 'int')
  ,-- thread id of given SPID (0 is always the parent)
  [IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)')
  ,[WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)')
  ,[LogUsed] = Deadlock.Process.value('@logused', 'int')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process') AS Deadlock(Process)
 )
 ,ExecutionStack
AS (
 -- get the data from the executionStack node
 SELECT CTE.DeadlockID
  ,ProcessID = Execution.Stack.value('../../@id', 'varchar(50)')
  ,Code = Execution.Stack.value('.', 'varchar(1000)')
  ,ProcName = Execution.Stack.value('@procname', 'sysname')
  ,Line = Execution.Stack.value('@line', 'int')
  ,RN = row_number() OVER (
   PARTITION BY CTE.DeadlockID
   ,Execution.Stack.value('../../@id', 'varchar(50)') ORDER BY (
     SELECT 1
     )
   )
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/process-list/process/executionStack/frame') AS Execution(Stack)
 )
 --SELECT * FROM ExecutionStack
 ,PageLock
AS (
 -- get the data from the pagelock node
 SELECT DeadlockID
  ,ObjectName = PageLock.Process.value('../../@objectname', 'varchar(200)')
  ,ProcessID = PageLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'Page'
  ,LockMode = PageLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/pagelock/owner-list/owner') AS PageLock(Process)
 )
 ,KeyLock
AS (
 -- get the data from the keylock node
 SELECT DeadlockID
  ,ObjectName = KeyLock.Process.value('../../@objectname', 'varchar(200)') + '.' + KeyLock.Process.value('../../@indexname', 'varchar(200)')
  ,-- get the index name also
  ProcessID = KeyLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'Key'
  ,LockMode = KeyLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/keylock/owner-list/owner') AS KeyLock(Process)
 )
 ,RidLock
AS (
 -- get the data from the ridlock node
 SELECT DeadlockID
  ,ObjectName = RIDLock.Process.value('../../@objectname', 'varchar(200)')
  ,ProcessID = RIDLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'RID'
  ,LockMode = RIDLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/ridlock/owner-list/owner') AS RIDLock(Process)
 )
 ,ObjectLock
AS (
 -- get the data from the objectlock node
 SELECT DeadlockID
  ,ObjectName = ObjectLock.Process.value('../../@objectname', 'varchar(200)')
  ,ProcessID = ObjectLock.Process.value('@id', 'varchar(200)')
  ,LockType = 'Object'
  ,LockMode = ObjectLock.Process.value('../../@mode', 'varchar(3)')
 FROM CTE
 CROSS APPLY CTE.DeadlockGraph.nodes('/deadlock-list/deadlock/resource-list/objectlock/owner-list/owner') AS ObjectLock(Process)
 )
-- combine all the data together, and display in
SELECT Process.[DeadlockID]
 ,Process.[SPID]
 ,Process.[SBID]
 ,Process.[ECID]
 ,Process.[DeadlockGraph]
 ,-- include the graph - can click and open in separate window
 Process.[ProcessID]
 ,Process.[Victim]
 ,[LockedType] = coalesce(PageLock.LockType, KeyLock.LockType, RIDLock.LockType, ObjectLock.LockType)
 ,[LockMode] = coalesce(PageLock.LockMode, KeyLock.LockMode, RIDLock.LockMode, ObjectLock.LockMode)
 ,[LockedObject] = coalesce(PageLock.ObjectName, KeyLock.ObjectName, RIDLock.ObjectName, ObjectLock.ObjectName)
 ,[Procedure #] = es.RN
 ,es.[ProcName]
 ,es.[Line]
 ,es.[Code]
 ,Process.[ClientApp]
 ,Process.[HostName]
 ,Process.[LoginName]
 ,Process.[TransactionTime]
 ,Process.BatchStarted
 ,Process.BatchCompleted
 ,Process.[InputBuffer]
 ,Process.[IsolationLevel]
 ,Process.WaitResource
 ,Process.LogUsed
FROM Process
INNER JOIN ExecutionStack es ON es.ProcessID = Process.ProcessID
 AND es.DeadlockID = Process.DeadlockID
LEFT JOIN PageLock ON PageLock.ProcessID = Process.ProcessID
 AND PageLock.DeadlockID = Process.DeadlockID
LEFT JOIN KeyLock ON KeyLock.ProcessID = Process.ProcessID
 AND KeyLock.DeadlockID = Process.DeadlockID
LEFT JOIN RIDLock ON RIDLock.ProcessID = Process.ProcessID
 AND RIDLock.DeadlockID = Process.DeadlockID
LEFT JOIN ObjectLock ON ObjectLock.ProcessID = Process.ProcessID
 AND ObjectLock.DeadlockID = Process.DeadlockID
ORDER BY Process.DeadlockID
 ,Process.victim DESC
 ,-- show the victim first
 Process.ProcessID
 ,Process.ECID
 ,-- show in thread order by SPID
 es.RN;-- execution stack order

Sync the Logins after Restore

One of the problems restoring a database to a new server is dealing with out of sync logins.
The link between a database user name and a server user name is the SID that is generated when the login is added to SQL Server.
There are stored procedures to sync the SID's but they have to be run for each user that is out of sync in each database.
the sp_msForEachDB system stored procedure. It is a handy procedure that will execute a batch of statements for every database on the server. The database name can be referenced in the script by using a ? as a placeholder

--First run this script to see if there are any logins that are out of sync.
DECLARE @Collation VARCHAR(100)
DECLARE @SQL VARCHAR(2000)

CREATE TABLE ##TempSync (
 DB_NME VARCHAR(50)
 ,DBUserName VARCHAR(50)
 ,SysLoginName VARCHAR(50)
 )

SELECT @Collation = CONVERT(SYSNAME, DatabasePropertyEx('master', 'Collation'))

SET @SQL = 'USE [?]
SELECT ''?'' DB_NME, A.name DBUserName,B.loginname SysLoginName 
FROM sysusers A      
JOIN master.dbo.syslogins B      
ON A.name Collate ' + @Collation + ' = B.Name       
JOIN master.dbo.sysdatabases C      ON C.Name = ''?'' WHERE issqluser = 1       AND (A.sid IS NOT NULL       AND A.sid <> 0x0)       AND suser_sname(A.sid) IS NULL       AND (C.status & 32) =0 --loading       AND (C.status & 64) =0 --pre recovery       AND (C.status & 128) =0 --recovering       AND (C.status & 256) =0 --not recovered       AND (C.status & 512) =0 --offline       AND (C.status & 1024) =0 --read only ORDER BY A.name'

INSERT INTO ##TempSync
EXEC sp_msforeachdb @SQL

SELECT *
FROM ##TempSync

DROP TABLE ##TempSync

--If the first script returns data, run this script to sync the logins.
DECLARE @Collation VARCHAR(100)
DECLARE @SQL VARCHAR(2000)

SELECT @Collation = CONVERT(SYSNAME, DatabasePropertyEx('master', 'Collation'))

SET @SQL = 'USE [?]
DECLARE @DBUserName varchar(50)
DECLARE @SysLoginName varchar(50)
DECLARE SyncDBLogins CURSOR FOR SELECT A.name DBUserName,        B.loginname SysLoginName FROM sysusers A      
JOIN master.dbo.syslogins B      ON A.name Collate ' + @Collation + 
 ' = B.Name       
JOIN master.dbo.sysdatabases C      ON C.Name = ''?'' 
WHERE issqluser = 1       AND (A.sid IS NOT NULL       AND A.sid <> 0x0)       AND suser_sname(A.sid) IS NULL       AND (C.status & 32) =0 --Loading       AND (C.status & 64) =0 --pre recovery       AND (C.status & 128) =0 --recovering       AND (C.status & 256) =0 --not recovered       AND (C.status & 512) =0 --offline       AND (C.status & 1024) =0 --read only ORDER BY A.name
OPEN SyncDBLogins
FETCH NEXT FROM SyncDBLogins INTO @DBUserName, @SysLoginName
WHILE @@FETCH_STATUS = 0 BEGIN    
EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName    
    FETCH NEXT FROM SyncDBLogins    INTO @DBUserName, @SysLoginName END
    CLOSE SyncDBLogins
    DEALLOCATE SyncDBLogins'

EXEC sp_msforeachdb @SQL

Update out-dated statistics

 Uses index and statistic update timestamps to select
 probable out-dated statistics and update them.

 In common a DBA schedules a maintenance plan to update all statistics within
 a database. Also it's possible to execute the system stored procedure sp_updatestats.
 Both could cause full table scans on all table and therefore a may high IO workload.

 This Transact-SQL statement estimate by index and statistic update timestamps the
 probable out-dated statistics and update them with resample option.

 Requires at least db_owner view permissions.
 Works with Microsoft SQL Server 2005 and higher versions.

SET NOCOUNT ON;

DECLARE @minDateDiff INT
 ,@sql NVARCHAR(1000)
 ,@schema NVARCHAR(130)
 ,@table NVARCHAR(130)
 ,@stat NVARCHAR(130)
 ,@lastupd DATETIME;

-- Min difference between statistics and index(es) update timestamp
-- in days.
SET @minDateDiff = 7;

DECLARE StatsCursor CURSOR LOCAL
FOR
SELECT SCH.NAME AS SchemaName
 ,OBJ.NAME AS ObjectName
 ,STA.NAME AS StatName
 ,STATS_DATE(STA.object_id, STA.stats_id) AS StatUpdateStamp
FROM sys.stats AS STA
INNER JOIN sys.objects AS OBJ ON STA.object_id = OBJ.object_id
INNER JOIN sys.schemas AS SCH ON OBJ.schema_id = SCH.schema_id
LEFT JOIN (
 SELECT IUS.object_id
  ,MIN(ISNULL(IUS.last_user_update, IUS.last_system_update)) AS LastUpdate
 FROM sys.dm_db_index_usage_stats AS IUS
 WHERE database_id = DB_ID()
  AND NOT ISNULL(IUS.last_user_update, IUS.last_system_update) IS NULL
 GROUP BY IUS.object_id
 ) AS IUS ON IUS.object_id = STA.object_id
WHERE OBJ.type IN (
  'U'
  ,'V'
  ) -- only user tables and views
 AND DATEDIFF(d, ISNULL(STATS_DATE(STA.object_id, STA.stats_id), {d N'1900-01-01' }), IUS.LastUpdate) > @minDateDiff
ORDER BY STATS_DATE(STA.object_id, stats_id) ASC;

-- Open the cursor
OPEN StatsCursor;

FETCH NEXT
FROM StatsCursor
INTO @schema
 ,@table
 ,@stat
 ,@lastupd

WHILE @@FETCH_STATUS = 0
BEGIN
 -- Infoprint.
 PRINT @schema + N'.' + @table + CHAR(9) + @stat + CHAR(9) + '-> ' + CONVERT(NVARCHAR(20), ISNULL(@lastupd, ''), 120);

 SET @sql = N'UPDATE STATISTICS ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@table) + N' ' + QUOTENAME(@stat) + N' ' + 'WITH RESAMPLE;';-- Or WITH FULLSCAN

 EXEC sp_executesql @sql;

 FETCH NEXT
 FROM StatsCursor
 INTO @schema
  ,@table
  ,@stat
  ,@lastupd;
END;

CLOSE StatsCursor;

DEALLOCATE StatsCursor;

Deleting large amounts of data

Situation(s):

Need to delete a very large amount of rows from a very large table in SQL Server.

Straight Forward Approach:

Just delete all of the rows in one shot. Things generally are not this simple ,now are they?

Problems:

• This would be the quickest way to delete a large number of rows but you will probably end up getting lock escalation to a table lock, which essentially makes the table unavailable during the delete. You would need a maintenance window
• Log can explode during the operation. You might run into issues of increasing the log space etc.
• If you have transactional replication on the table or you have DB mirroring then deleting a large number of rows from a table will generate a lot of log activity and it can bring the system under a lot of load.

Refined Approach:

To address the above problems we can do :

• Delete rows in an gradual and an iterative manner, by running the DELETE statement in a loop.
• Take log backups in regular intervals to contain the log growth.
• Space the operations to ease the pressure on the system if there is transactional replication or DB mirroring involved.

Partitioned Tables (Special case):

In the case of partitioned Tables we can use an interesting approach to address the log explosion.

• Create an identical Trash table ( name it anything) .
• Switch the partitions that are to be deleted.
• Truncate the Trash table.

TRUNCATE is minimally logged when compare to DELETE

Other special cases:

If the amount of data that has to be deleted is way more than the amount of data that has to be retained, you can exploit BULK Logging features:

• BULK COPY the data to be retained into another table.
• TRUNCATE the table
• BULK COPY the data back.