Identify the cause of SQL Server blocking

SQL Server has a rich set of dynamic management views (DMVs) that helps you to quickly identify locking and blocking in SQL Server. That is why, writing such a query is quite simple in SQL Server. I used the following dynamic management views (DMVs) for my query.

• sys.dm_os_waiting_tasks - Returns information about blocked and blocking processes.
• sys.dm_exec_sessions - Returns information about authenticated sessions on SQL Server.
• sys.dm_exec_requests - Returns the detailed information about the requests currently executing on SQL Server.
• sys.dm_tran_locks - Returns the information about the current locks and the processes blocking them.
• sys.dm_exec_query_plan - Returns the showplan for the query in XML format.
• sys.dm_exec_sql_text - Returns the text of T-SQL batch.

The following is the query, which I have written using these dynamic management views (DMVs) that will help you to quickly identify the SPIDs and other information about the processes that are causing the blocking on SQL Server instance. This query returns the comprehensive information about the blocking and waiting processes, which is useful for troubleshooting SQL Server locking and blocking issues. This query is also a good way to analyze detailed information about locks, and help you to identify the cause of a large number of blocks.

WITH [Blocking]
AS (SELECT w.[session_id]
   ,s.[original_login_name]
   ,s.[login_name]
   ,w.[wait_duration_ms]
   ,w.[wait_type]
   ,r.[status]
   ,r.[wait_resource]
   ,w.[resource_description]
   ,s.[program_name]
   ,w.[blocking_session_id]
   ,s.[host_name]
   ,r.[command]
   ,r.[percent_complete]
   ,r.[cpu_time]
   ,r.[total_elapsed_time]
   ,r.[reads]
   ,r.[writes]
   ,r.[logical_reads]
   ,r.[row_count]
   ,q.[text]
   ,q.[dbid]
   ,p.[query_plan]
   ,r.[plan_handle]
 FROM [sys].[dm_os_waiting_tasks] w
 INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
 INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
 CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
 CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
 WHERE w.[session_id] > 50
  AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
      ,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
      ,b.[blocking_session_id] AS [BlockingSessionID]
      ,b.[login_name] AS [WaitingUserSessionLogin]
      ,s1.[login_name] AS [BlockingUserSessionLogin]
      ,b.[original_login_name] AS [WaitingUserConnectionLogin] 
      ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
      ,b.[wait_duration_ms] AS [WaitDuration]
      ,b.[wait_type] AS [WaitType]
      ,t.[request_mode] AS [WaitRequestMode]
      ,UPPER(b.[status]) AS [WaitingProcessStatus]
      ,UPPER(s1.[status]) AS [BlockingSessionStatus]
      ,b.[wait_resource] AS [WaitResource]
      ,t.[resource_type] AS [WaitResourceType]
      ,t.[resource_database_id] AS [WaitResourceDatabaseID]
      ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
      ,b.[resource_description] AS [WaitResourceDescription]
      ,b.[program_name] AS [WaitingSessionProgramName]
      ,s1.[program_name] AS [BlockingSessionProgramName]
      ,b.[host_name] AS [WaitingHost]
      ,s1.[host_name] AS [BlockingHost]
      ,b.[command] AS [WaitingCommandType]
      ,b.[text] AS [WaitingCommandText]
      ,b.[row_count] AS [WaitingCommandRowCount]
      ,b.[percent_complete] AS [WaitingCommandPercentComplete]
      ,b.[cpu_time] AS [WaitingCommandCPUTime]
      ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
      ,b.[reads] AS [WaitingCommandReads]
      ,b.[writes] AS [WaitingCommandWrites]
      ,b.[logical_reads] AS [WaitingCommandLogicalReads]
      ,b.[query_plan] AS [WaitingCommandQueryPlan]
      ,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

Sample Output

To examine the results of this query, run it on SQL Server where you are experiencing blocks. For example, when I executed this query on my test SQL Server where I'm deliberately running some code to cause blocking, it brings the following results (Note: To fit the resultset, I've split the resultset into seven images):

The following are the columns returned by this query:

• WaitingSessionID - The SPID of the waiting session.
• BlockingSessionID - The SPID of the blocking session.
• WaitingSessionUserLogin - The user session login name under which waiting session is currently executing.
• BlockingSessionUserLogin - The user session login name under which blocking session is currently executing.
• WaitingUserConnectionLogin - The login name that the user used to create waiting session.
• BlockingSessionConnectionLogin - The login name that the user used to create waiting session.
• WaitDuration - Waiting process wait time in milliseconds.
• WaitType - Type of wait.
• WaitRequestMode - Mode of the wait request.
• WaitingProcessStatus - The status of waiting process.
• BlockingSessionStatus - The status of blocking process.
• WaitResource - The name of the resource request is waiting for.
• WaitResourceType - The type of the resource request is waiting for.
• WaitResourceDatabaseID - The database id of the database in which the requested resource exists.
• WaitResourceDatabaseName - The name of the database in which the requested resource exists.
• WaitResourceDescription - The detailed description of the waiting resource.
• WaitingSessionProgramName - The name of the program that initiated the waiting session.
• BlockingSessionProgramName - The name of the program that initiated the blocking session.
• WaitingHost - The name of the workstation that is specific to waiting session.
• BlockingHost - The name of the workstation that is specific to blocking session.
• WaitingCommandType - The type of waiting session command.
• WaitingCommandText - The text of waiting session command.
• WaitingCommandRowCount - Expected number of rows return by the waiting session.
• WaitingCommandPercentComplete - Percentage of the waiting request client.
• WaitingCommandCPUTime - CPU time used by waiting session.
• WaitingCommandTotalElapsedTime - The total time elapsed in milliseconds since the waiting request arrived.
• WaitingCommandReads - The number of reads performed by the waiting session request.
• WaitingCommandWrites - The number of writes performed by the waiting session request.
• WaitingCommandLogicalReads - The number of logical reads performed by the waiting session request.
• WaitingCommandQueryPlan - Waiting command execution plan.
• WaitingCommandPlanHandle - Plan handle of the waiting session command.

As you can see from above resultset, that process 53 listed BlockingSessionID column of row 4 is not blocked by another process, hence identified as the SPID that is the cause of the blocking on my test SQL Server instance.

Next Steps

• Transaction locks are the most common cause of blocked processes. The stronger (least concurrent) the isolation level, the more likely it is to cause a blocked process.
• Try to use less granular lock for your queries, as the less granular the lock, the more likely a blocked process or deadlock will not occur. For example, if the entire table is locked, there is a higher likelihood of blocks than if only a single row is locked.
• Revisit your database design because bad database design could be potential reason for excessive locking and blocking.
• Check out these tips to learn more about locking and blocking:
  • Understanding SQL Server Locking
  • Understanding SQL Server Blocking
  • Locking and Blocking Tips category

Check SQL Server a specified database index fragmentation percentage (avg_fragmentation_in_percent)

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,  ind.name AS IndexName, indexstats.index_type_desc AS IndexType,  indexstats.avg_fragmentation_in_percent  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats  INNER JOIN sys.indexes ind   ON ind.object_id = indexstats.object_id  AND ind.index_id = indexstats.index_id  WHERE indexstats.avg_fragmentation_in_percent > 30  ORDER BY indexstats.avg_fragmentation_in_percent DESC

How To Enable Data Access For Server

exec sp_serveroption 'server name', 'data access', 'true'

Query To Drop All Constraints From Database

DECLARE @STR VARCHAR(MAX)
DECLARE CUR CURSOR FOR

SELECT 'ALTER TABLE ' + '[' + S.[NAME] + '].[' + T.NAME + '] DROP CONSTRAINT ['+ C.NAME + ']'
FROM SYS.OBJECTS C, SYS.OBJECTS T, SYS.SCHEMAS S
WHERE C.TYPE IN ('C', 'F', 'PK', 'UQ', 'D')
 AND C.PARENT_OBJECT_ID=T.OBJECT_ID AND T.TYPE='U' AND T.SCHEMA_ID = S.SCHEMA_ID
ORDER BY C.TYPE

OPEN CUR
FETCH NEXT FROM CUR INTO @STR
WHILE (@@FETCH_STATUS = 0) BEGIN
 PRINT @STR
 EXEC (@STR)
 FETCH NEXT FROM CUR INTO @STR
END

CLOSE CUR
DEALLOCATE CUR

Find All Other Tables Which Depends On Foreign Keys Involved That Reference Your Own Table

;WITH ReferencingFK AS
(
    SELECT
        fk.Name AS 'FKName',
        OBJECT_NAME(fk.parent_object_id) 'ParentTable',
        cpa.name 'ParentColumnName',
        OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable',
        cref.name 'ReferencedColumnName'
    FROM
        sys.foreign_keys fk
    INNER JOIN
        sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
    INNER JOIN
        sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
    INNER JOIN
        sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
)
SELECT
    FKName,
    ParentTable,
    ParentColumnName,
    ReferencedTable,
    ReferencedColumnName
FROM
    ReferencingFK
WHERE
    ReferencedTable = 'table_name'   --  <=== put your table name here!
ORDER BY
    ParentTable, ReferencedTable, FKName

How To Get SQL Server Version

Option 1

SELECT
        SERVERPROPERTY('productversion'),
        SERVERPROPERTY ('productlevel'),
        SERVERPROPERTY ('edition')

and get the details on the link: http://support.microsoft.com/kb/321185

Option 2

EXEC master..xp_msver

Option 3

SELECT @@version

Option 4

-- Option 4
DECLARE @ver NVARCHAR(128) = CAST(serverproperty('ProductVersion') AS NVARCHAR)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

SET @ver =
                CASE
                        WHEN @ver = '7' THEN 'SQL Server 7'
                        WHEN @ver = '8' THEN 'SQL Server 2000'
                        WHEN @ver = '9' THEN 'SQL Server 2005'
                        WHEN @ver = '10' THEN 'SQL Server 2008/2008 R2'
                        WHEN @ver = '11' THEN 'SQL Server 2012'
                        WHEN @ver = '12' THEN 'SQL Server 2014'
                        ELSE 'Unsupported SQL Server Version'
                END
SELECT @ver

Truncate All Tables in Database

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

--some of the tables have identity columns we may want to reseed them
EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Data Type Conversion

Implicit and Explicit Conversion

---

Data types can be converted either implicitly or explicitly.

Implicit conversions are not visible to the user. SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

GETDATE() implicitly converts to date style 0. SYSDATETIME() implicitly converts to date style 21.
Explicit conversions use the CAST or CONVERT functions.

The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. For example, the following CAST function converts the numeric value of $157.27 into a character string of '157.27':

CAST ( $157.27 AS VARCHAR(10) )

Use CAST instead of CONVERT if you want Transact-SQL program code to comply with ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.
The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Find Particular Database and All tables Sizes

SET NOCOUNT ON

DBCC UPDATEUSAGE(0)

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t