Performance troubleshooting using Waits

WITH [Waits]
AS
(SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL')
)
SELECT TOP 10
        W1.[wait_type] AS [WaitType],
        CAST (W1.[WaitS] AS DECIMAL(14, 2)) AS [Wait (seconds)],
        CAST (W1.[ResourceS] AS DECIMAL(14, 2)) AS [Resource wait (seconds)],
        CAST (W1.[SignalS] AS DECIMAL(14, 2)) AS [Signal wait (seconds)],
        W1.[WaitCount],
        CAST (W1.[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
        CAST ((W1.[WaitS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait (seconds)],
        CAST ((W1.[ResourceS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgResource (seconds)],
        CAST ((W1.[SignalS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSignal (seconds)]
FROM
        [Waits] AS W1
        INNER JOIN [Waits] AS W2 ON W2.[RowNum] <= W1.[RowNum]
GROUP BY W1.[RowNum], W1.[wait_type], W1.[WaitS],
        W1.[ResourceS], W1.[SignalS], W1.[WaitCount], W1.[Percentage];
GO

Code template for transactions

Code template for correctly handling transactions in SQL Server:

SET XACT_ABORT ON;

BEGIN TRY
        BEGIN TRANSACTION
                -- Your script here

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        IF XACT_STATE() != 0  
                ROLLBACK TRANSACTION

        -- Re-throw error
        EXEC ThrowError
END CATCH
GO

Sample of ThrowError function:

CREATE PROCEDURE ThrowError
AS
BEGIN
   DECLARE
     @ErrorNumber INT
     , @ErrorMessage NVARCHAR(4000)
     , @ErrorSeverity INT
     , @ErrorState INT
     , @ErrorProcedure NVARCHAR(128)
     , @ErrorLine INT
     
   SELECT @ErrorNumber = ERROR_NUMBER()    
        , @ErrorMessage = ERROR_MESSAGE()
        , @ErrorSeverity = ERROR_SEVERITY()
        , @ErrorState = ERROR_STATE()
        , @ErrorProcedure = ERROR_PROCEDURE()
        , @ErrorLine = ERROR_LINE()

    SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine
   
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )
END
GO

Index usability - tune your index strategy

The view sys.dm_db_index_usage_stats returns statistical counts of the various index operations that have occurred to an index over time.

SELECT
     object_name(i.object_id) as TableName
     , i.name as [Index]
     , user_seeks + user_scans + user_lookups + user_updates as usability
     , user_seeks, user_scans, user_lookups, user_updates
     , i.index_id, i.type_desc, i.is_unique, i.is_primary_key, i.is_unique
FROM
     sys.indexes i    
     INNER JOIN sys.dm_db_index_usage_stats s ON s.index_id = i.index_id AND s.object_id = i.object_id
WHERE 1=1
     AND i.name is not NULL
     AND s.database_id = DB_ID() -- current DB
     --AND object_name(i.object_id) = '<TableName>'
ORDER BY
     usability, [Index], TableName;

List of long running queries

T-SQL script that returns the queries that take the longest time to run:

SELECT TOP 20
        R.session_id, R.status, R.start_time, R.command, Q.text
FROM
        sys.dm_exec_requests R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) Q
--WHERE R.status in ('runnable')
ORDER BY R.start_time

There’s also an interesting script, completely free on http://www.brentozar.com.

Copy into a separate database a list of tables having auto-increment columns

The following script copies many tables from a source DB into another destination DB, taking into account that some of these tables have auto-increment columns.

DECLARE
          @SourceDB nvarchar(128)= N'SourceDB'
        , @RowIndex AS TINYINT = 1
        , @TableName AS NVARCHAR(128)
        , @NoRows AS INT
        , @NoTables AS TINYINT
        , @Sql AS NVARCHAR(MAX)
        , @Message as NVARCHAR(MAX)
        , @ListOfColumns as NVARCHAR(MAX)
        , @HasIdentityColumn BIT       

USE DestinationDB

       
-- Fill list of tables to be copied
DECLARE @ListOfTables AS Table (RowIndex TINYINT IDENTITY(1,1), TableName NVARCHAR(128))
INSERT INTO @ListOfTables (TableName)
VALUES
          ('YourTable1')
        , ('YourTable2')
        , ('YourTable3')       
        , ('YourTable4')                               
        , ('YourTable5')         

-- Get number of tables to copy
SELECT @NoTables = COUNT(*)
FROM @ListOfTables
       
-- For each table      
WHILE @RowIndex <= @NoTables
BEGIN  
        SELECT @TableName = TableName FROM @ListOfTables WHERE RowIndex = @RowIndex
        SET @Message = CHAR(13) + CAST (@RowIndex as VARCHAR) + '. Process table ' + @TableName
        PRINT @Message
                       
        -- Get actual number of rows of table in destination DB
        SELECT @NoRows = SUM(pa.rows)
        FROM
                sys.tables ta
                INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
        WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)     AND ta.name= @TableName
       
        -- If it is already => proceed with next table                 
        IF @NoRows > 0
        BEGIN
                SET @RowIndex = @RowIndex + 1
                CONTINUE
        END    
       
        -- Get list of columns
        SET @ListOfColumns = NULL      
        SELECT @ListOfColumns = COALESCE(@ListOfColumns + ', ', '') + '[' + name + ']'
        FROM sys.columns
        WHERE object_id = object_id(@TableName)
               
        -- Check if table has identity column  
        SET @HasIdentityColumn = 0
        IF EXISTS ( SELECT COLUMN_NAME
                                FROM INFORMATION_SCHEMA.COLUMNS
                                WHERE
                                        TABLE_NAME = @TableName
                                        AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'ISIDENTITY') = 1)
        BEGIN
                SET @HasIdentityColumn = 1
        END
                                               
        IF @HasIdentityColumn = 1
        BEGIN
                -- Copy data by de-activating identity column
                SET @Sql = 'SET IDENTITY_INSERT {0} ON' + CHAR(13) +
                        'INSERT INTO {0} ({1}) SELECT {1} FROM {2}.dbo.{0}' + CHAR(13) +
                        'SET IDENTITY_INSERT {0} OFF'                                  
                SET @Sql = REPLACE(@Sql, '{0}', @TableName)
                SET @Sql = REPLACE(@Sql, '{1}', @ListOfColumns)
                SET @Sql = REPLACE(@Sql, '{2}', @SourceDB)
                EXEC sp_executesql @Sql
        END
        ELSE
        BEGIN
                -- Copy data
                SET @Sql = 'INSERT INTO {0} ({1}) SELECT {1} FROM {2}.dbo.{0}'
                SET @Sql = REPLACE(@Sql, '{0}', @TableName)
                SET @Sql = REPLACE(@Sql, '{1}', @ListOfColumns)
                SET @Sql = REPLACE(@Sql, '{2}', @SourceDB)
                EXEC sp_executesql @Sql
        END
               
        PRINT @Sql     
                       
        -- Process next table
        SET @RowIndex = @RowIndex + 1
END
GO

PRINT 'Script executed successfully!'
GO

T-SQL script for implementing transactional replication in SQL Server

T-SQL script that creates a transactional publication and its associated subscription using the following stored procedures:

  • sp_addlogreader_agent
  • sp_addpublication
  • sp_addarticle

DECLARE
        -- Name of the database where resides the table to be replicated
        @publicationDB AS SYSNAME = 'MyPublicationDB'

        -- Name of the publication
        , @publicationName AS SYSNAME = 'MyPublicationName'

        -- Table that will be replicated (published)
        , @publicationTable AS SYSNAME = 'MyTableToReplicate'

        -- Name of the database where the table will be replicated to
        , @subscriptionDB AS SYSNAME = 'MySubscriptionDB'

        -- Server where subscription database resides on
        , @subscriberServerName AS SYSNAME = @@SERVERNAME

        -- Login
        , @login AS SYSNAME = 'YourUser'               

        -- Password
        , @password AS SYSNAME = 'YourPassword'

        -- Snapshot folder
        , @snapshotFolder AS NVARCHAR(255) = N'D:\MSSQL\REPL'


USE MyPublicationDB

-- Enable transactional or snapshot replication on the publication database.
EXEC sp_replicationdboption
               @dbname=@publicationDB
               , @optname=N'publish'
               , @value = N'true';

-- Execute sp_addlogreader_agent to create the agent job.
EXEC sp_addlogreader_agent
               @publisher_login = @login
               , @publisher_password = @password
               , @publisher_security_mode = 0;

-- Create a new transactional publication with the required properties.
EXEC sp_addpublication
               @publication = @publicationName
               , @status = N'active'
               , @allow_push = N'true'
               , @allow_pull = N'true'
               , @independent_agent = N'true'
               , @snapshot_in_defaultfolder = 'false'
               , @alt_snapshot_folder = @snapshotFolder
               , @immediate_sync = N'true'

-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot
               @publication = @publicationName
               , @publisher_login = @login
               , @publisher_password = @password
               , @publisher_security_mode = 0

-- Add a article for the YoutTable table.
EXEC sp_addarticle
               @publication = @publicationName
               , @article = @publicationTable
               , @source_owner = 'dbo'
               , @source_object = @publicationTable
               , @schema_option = 0x000000000803509F
               , @type = N'logbased'
               , @destination_owner = N'dbo'
               , @destination_table = @publicationTable
               , @vertical_partition = N'false'

--Add a push subscription to a transactional publication.
EXEC sp_addsubscription
               @publication = @publicationName
               , @subscriber = @subscriberServerName
               , @destination_db = @subscriptionDB
               , @subscription_type = N'push';

--Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent
               @publication = @publicationName
               , @subscriber = @subscriberServerName
               , @subscriber_db = @subscriptionDB
               , @subscriber_login = @login
               , @subscriber_password = @password
               , @subscriber_security_mode = 0
GO

For checking the replication is correctly put in place:

SELECT * FROM syspublications WHERE name = @publicationName
SELECT name, * FROM sysarticles
SELECT * FROM syssubscriptions