Unexpected deadlocks in test environment but not in production environment

  • We are trying to understand why we are seeing the following occasional deadlock in our test environment (it has occurred 3 times this week). We do not see this occur in our production environment, where user activity is much higher. The hardware of these environments is roughly comparable, and both are running the following build of SQL Server:

    Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5029187) - 13.0.7029.3 (X64)

    Aug 16 2023 19:44:44

    Copyright (c) Microsoft Corporation

    Web Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

    The User_Events table in our test environment has around 889,000 records in total, whereas production has ten times that figure. The User_Event_Types table only contains 53 rows in both environments. The tables and indexes in both environments are identical, and the indexes are regularly maintained. No indexes appear to be missing.

    <deadlock>
    <victim-list>
    <victimProcess id="process1c00817d088"/>
    </victim-list>
    <process-list>
    <process id="process1c00817d088" taskpriority="0" logused="288" waitresource="PAGE: 5:1:9114 " waittime="1190" ownerId="8344748" transactionname="INSERT" lasttranstarted="2023-10-12T01:00:11.647" XDES="0x1c0124bf900" lockMode="IX" schedulerid="1" kpid="5892" status="suspended" spid="109" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-10-12T01:00:11.647" lastbatchcompleted="2023-10-12T01:00:11.647" lastattention="1900-01-01T00:00:00.647" clientapp=".Net SqlClient Data Provider" hostname="MyWebServer" hostpid="4128" loginname="WebServerServiceAccount" isolationlevel="read committed (2)" xactid="8344748" currentdb="5" currentdbname="MyDatabase" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
    <frame procname="MyDatabase.dbo.sproc_CreateUserEvent" line="18" stmtstart="1384" stmtend="1862" sqlhandle="0x03000500e69707694c234d01c5af000001000000000000000000000000000000000000000000000000000000"> INSERT dbo.User_Events (UserID, UserEventType, EventText, AuditUserID, AuditTimestamp) SELECT @UserID, MIN(UserEventType), @EventText, NULL, GETDATE() FROM dbo.User_Event_Types WHERE SystemKey = @SystemKe </frame>
    <frame procname="MyDatabase.dbo.sproc_ValidateUser" line="155" stmtstart="11636" stmtend="11816" sqlhandle="0x030005009bfa224bf2614d01c5af000001000000000000000000000000000000000000000000000000000000"> EXEC dbo.sproc_CreateUserEvent @UserID=@UserID, @SystemKey=@WebServiceNam </frame>
    </executionStack>
    <inputbuf> Proc [Database Id = 5 Object Id = 1260583579] </inputbuf>
    </process>
    <process id="process1c00c55d468" taskpriority="0" logused="3354984" waitresource="PAGE: 5:1:344287 " waittime="795" ownerId="8344702" transactionname="DELETE" lasttranstarted="2023-10-12T01:00:11.600" XDES="0x1c0051c1c10" lockMode="U" schedulerid="6" kpid="4852" status="suspended" spid="83" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-10-12T01:00:11.597" lastbatchcompleted="2023-10-12T01:00:11.597" lastattention="1900-01-01T00:00:00.597" clientapp="SQLAgent - TSQL JobStep (Job 0x3824D09C998B1D4EAF5C35A9E6F0D5A8 : Step 7)" hostname="MyDatabaseServer" hostpid="5280" loginname="SQLServerAgentServiceAccount" isolationlevel="read committed (2)" xactid="8344702" currentdb="5" currentdbname="MyDatabase" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
    <frame procname="MyDatabase.dbo.sproc_User_Events_Cleardown" line="20" stmtstart="1392" stmtend="1766" sqlhandle="0x030005005928111e57d35101c5af000001000000000000000000000000000000000000000000000000000000"> DELETE UE FROM dbo.User_Events UE INNER JOIN dbo.User_Event_Types UET ON UET.UserEventType = UE.UserEventType WHERE UE.AuditTimestamp &lt; DATEADD(wk, -UET.RetentionPeriodInWeeks, @Today </frame>
    <frame procname="adhoc" line="1" stmtend="64" sqlhandle="0x010005003d542b1040e0ee1cbf01000000000000000000000000000000000000000000000000000000000000"> EXEC dbo.sproc_User_Events_Cleardown </frame>
    </executionStack>
    <inputbuf> EXEC dbo.sproc_User_Events_Cleardown; </inputbuf>
    </process>
    </process-list>
    <resource-list>
    <pagelock fileid="1" pageid="9114" dbid="5" subresource="FULL" objectname="MyDatabase.dbo.User_Events" id="lock1c004c2a580" mode="U" associatedObjectId="72057597610164224">
    <owner-list>
    <owner id="process1c00c55d468" mode="U"/>
    </owner-list>
    <waiter-list>
    <waiter id="process1c00817d088" mode="IX" requestType="wait"/>
    </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="344287" dbid="5" subresource="FULL" objectname="MyDatabase.dbo.User_Events" id="lock1beefe27600" mode="IX" associatedObjectId="72057597610098688">
    <owner-list>
    <owner id="process1c00817d088" mode="IX"/>
    </owner-list>
    <waiter-list>
    <waiter id="process1c00c55d468" mode="U" requestType="wait"/>
    </waiter-list>
    </pagelock>
    </resource-list>
    </deadlock>

    The table structures are as follows. There are no triggers on these tables.

    CREATE TABLE dbo.User_Event_Types (
    UserEventType smallint IDENTITY(1,1) NOT NULL,
    Description nvarchar(60) NOT NULL,
    SystemKey nvarchar(50) NOT NULL,
    RetentionPeriodInWeeks smallint NULL,
    AuditUserID int NULL,
    AuditTimestamp datetime NULL,
    CONSTRAINT PK_User_Event_Types PRIMARY KEY CLUSTERED (UserEventType) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    );
    GO
    CREATE TABLE dbo.User_Events (
    UserID int NOT NULL,
    UserEventID int IDENTITY(1,1) NOT NULL,
    UserEventType smallint NOT NULL,
    EventText nvarchar(60) NULL,
    AuditUserID int NULL,
    AuditTimestamp datetime NOT NULL,
    CONSTRAINT PK_User_Events PRIMARY KEY CLUSTERED (UserID, UserEventID) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    CONSTRAINT FK_UE_UserEventType FOREIGN KEY(UserEventType) REFERENCES dbo.User_Event_Types (UserEventType)
    );
    GO
    CREATE NONCLUSTERED INDEX IX_User_Events_AuditTimestamp
    ON dbo.User_Events (UserEventType, AuditTimestamp)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
    GO

    The contentious stored procedures are as follows. The former of these is scheduled by a nightly job, and takes between 0-8 seconds to complete. We would expect the latter to be almost instantaneous.

    CREATE PROCEDURE dbo.sproc_User_Events_Cleardown
    AS
    DECLARE@Today datetime = CONVERT(datetime, CONVERT(date, GETUTCDATE()));
    DELETE UE
    FROM dbo.User_Events UE
    INNER JOIN dbo.User_Event_Types UET ON UET.UserEventType = UE.UserEventType
    WHERE UE.AuditTimestamp < DATEADD(wk, -UET.RetentionPeriodInWeeks, @Today);
    GO

    CREATE PROCEDURE dbo.sproc_CreateUserEvent (
    @UserID int,
    @SystemKey nvarchar(50),
    @EventText nvarchar(60) = NULL
    )
    AS
    INSERT dbo.User_Events (UserID, UserEventType, EventText, AuditUserID, AuditTimestamp)
    SELECT @UserID, MIN(UserEventType), @EventText, NULL, GETDATE() FROM dbo.User_Event_Types WHERE SystemKey = @SystemKey;
    GO
  • This XML snippet represents information about a database deadlock in SQL Server. Let me break down the key elements:

    1. <deadlock>: This is the root element that contains information about the deadlock.

    2. <victim-list>: This section lists the victim process of the deadlock. In this case, the victim process is identified by its id, which is "process1c00817d088."

    3. <process-list>: This section provides information about the processes involved in the deadlock. There are two processes listed:

    - <process id="process1c00817d088">: This process is involved in an INSERT transaction, and it holds an Intent Exclusive (IX) lock on a resource (PAGE: 5:1:9114). The process is currently suspended and is waiting for a resource in "MyDatabase.dbo.User_Events."

    - <process id="process1c00c55d468">: This process is involved in a DELETE transaction and holds an Update (U) lock on another resource (PAGE: 5:1:344287). This process is also suspended and is waiting for a resource in "MyDatabase.dbo.User_Events."

    4. <resource-list>: This section provides information about the resources involved in the deadlock. There are two resources listed:

    - <pagelock fileid="1" pageid="9114" dbid="5" subresource="FULL" objectname="MyDatabase.dbo.User_Events" id="lock1c004c2a580" mode="U" associatedObjectId="72057597610164224">: This resource is in Update (U) mode and is associated with process process1c00c55d468.

    - <pagelock fileid="1" pageid="344287" dbid="5" subresource="FULL" objectname="MyDatabase.dbo.User_Events" id="lock1beefe27600" mode="IX" associatedObjectId="72057597610098688">: This resource is in Intent Exclusive (IX) mode and is associated with process process1c00817d088.

    5. <owner-list> and <waiter-list>: These sections list the owners and waiters for each resource. In this case, process process1c00c55d468 is the owner of the first resource, while process process1c00817d088 is waiting for it. On the other hand, process process1c00817d088 is the owner of the second resource, while process process1c00c55d468 is waiting for it.

    This deadlock scenario occurs when two processes are each holding a lock on a resource and waiting for a lock held by the other process, resulting in a cyclic dependency. Deadlocks like this can be resolved using various techniques, such as adjusting transaction isolation levels, modifying query design, or using deadlock detection and retry mechanisms.

  • I know you said the environments are essentially the same, but then you list a number of differences. It's likely that the reason you're seeing it one environment and not the other is one of these differences. It could be settings within SQL Server that are different as well as hardware, databases, statistics. As Jonathan says, this does come down to the code, which may be the same in both environments, but all the associated stuff around it is why you're seeing different behaviors.

  • and on this case I would nearly bet that the 2 procs involved on the deadlock are called in a loop with an outside transaction

    process 1
    exec dbo.sproc_ValidateUser

    foreach user
    exec sproc_CreateUserEvent parm @userid
    next


    process 2
    exec sproc_User_Events_Cleardown



    you will need to look at the overall process within the ValidateUser proc and see how it can be improved - that one is the culprit for the deadlock.

    or change schedule so they never execute at the same time.

    or perhaps execute one after the other

     

  • Thanks all. I can confirm that the code is not being called in a loop, or in an explicit transaction. The parent procedure sproc_ValidateUser just performs some basic validation and then calls sproc_CreateUserEvent at the end.

    I have since learnt that the test environment has lower tier storage than the production environment. Obviously this is a big difference, but it still seems odd that the deadlocks are occurring at all, given how simple these queries are.

    Is there anything obvious that can be improved upon in the code I have provided? We have looked at recoding sproc_User_Events_Cleardown to delete records in batches, but the procedure then took a much longer amount of time to do what the current procedure does in 0-8 seconds.

  • I'd need to see the execution plan to see how the queries are being resolved in order to make suggestions for improvement.

  • and how is sproc_ValidateUser  getting executed?

    and were are transactions being set on the full chain - starting with the top level code that executes sproc_ValidateUser.

    a possible way of changing the sproc_User_Events_Cleardown to potentially diminish the issue is to split the delete from the select.

    drop table if exists #keys
    -- build table with list of keys in advance - this will get blocked by other writers, but should not deadlock

    select UE.UserID
    , UE.UserEventID
    into #keys
    FROM dbo.User_Events UE
    INNER JOIN dbo.User_Event_Types UET ON UET.UserEventType = UE.UserEventType
    WHERE UE.AuditTimestamp < DATEADD(wk, -UET.RetentionPeriodInWeeks, @Today);
    ;
    -- clustered index on above may or not help overall execution - test it.


    begin transaction -- explicitly separate the delete onto a transaction - deadlock unlikely, but still possible due to non clustered index update
    DELETE UE
    FROM dbo.User_Events UE
    inner join #keys k1
    on k1.UserID = UE.UserID
    and k1.UserEventID = UE.UserEventID
    ;
    commit
    ;
  • It's possible it would be far better to cluster the dbo.User_Events table by AuditTimestamp rather than UserID.  That could be a significant change, however, so if you would first run this script and post the results that would provide more details to make such a decision.  The longer SQL has been up and running the better the stats will be.

    /*capture system settings that will be changed so that they can be reset to their original values at the end of this script*/DECLARE @deadlock_priority smallint
    DECLARE @transaction_isolation_level smallint
    SELECT @deadlock_priority = deadlock_priority, @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_sessions
    WHERE session_id = @@SPID

    SET DEADLOCK_PRIORITY -8; /*"tell" SQL that if this task somehow gets into a deadlock, cancel THIS task, NOT any other one*/SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    --**********************************************************************************************************************

    SET NOCOUNT ON;

    DECLARE @filegroup_name nvarchar(128)
    DECLARE @include_system_tables bit
    DECLARE @list_filegroup_and_drive_summary bit
    DECLARE @list_missing_indexes bit
    DECLARE @list_missing_indexes_summary bit
    DECLARE @list_total_size bit
    DECLARE @max_compression int
    DECLARE @order_by smallint /* -2=size DESC; 1=table_name ASC; 2=size ASC; */DECLARE @table_name_pattern sysname
    DECLARE @table_name_exclude_pattern sysname = '#%'

    SET @list_missing_indexes = 1 --NOTE: can take some time, set to 0 if you don't want to wait.
    SET @list_missing_indexes_summary = 0 /*not available unless you uncomment the code for it which requires DelimitedSplit8K function*/SET @order_by = -2 /* -2=size DESC; 1=table_name ASC; 2=size ASC; */SET @list_total_size = 1
    SET @filegroup_name = '%'
    SET @table_name_pattern = 'User_Event%'

    IF @include_system_tables IS NULL
    SET @include_system_tables = CASE WHEN DB_NAME() IN ('master', 'msdb', 'tempdb') THEN 1 ELSE 0 END
    SET @list_filegroup_and_drive_summary = 0

    DECLARE @debug smallint
    DECLARE @format_counts smallint --1=',0'; 2/3=with K=1000s,M=1000000s, with 0/1 dec. places;.
    DECLARE @include_schema_in_table_names bit
    DECLARE @sql_startup_date datetime
    DECLARE @total decimal(19, 3)

    SELECT @sql_startup_date = create_date
    FROM sys.databases WITH (NOLOCK)
    WHERE name = 'tempdb'

    SET @include_schema_in_table_names = 1
    SET @format_counts = 3
    SET @debug = 0

    IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
    DROP TABLE dbo.#index_missing
    IF OBJECT_ID('tempdb.dbo.#index_operational_stats') IS NOT NULL
    DROP TABLE dbo.#index_operational_stats
    IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
    DROP TABLE dbo.#index_specs
    IF OBJECT_ID('tempdb.dbo.#index_usage') IS NOT NULL
    DROP TABLE dbo.#index_usage

    SELECT *
    INTO #index_operational_stats
    FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )

    CREATE TABLE dbo.#index_specs (
    object_id int NOT NULL,
    index_id int NOT NULL,
    min_compression int NULL,
    max_compression int NULL,
    drive char(1) NULL,
    alloc_mb decimal(9, 1) NOT NULL,
    alloc_gb AS CAST(alloc_mb / 1024.0 AS decimal(9, 3)),
    used_mb decimal(9, 1) NOT NULL,
    used_gb AS CAST(used_mb / 1024.0 AS decimal(9, 3)),
    rows bigint NULL,
    table_mb decimal(9, 1) NULL,
    table_gb AS CAST(table_mb / 1024.0 AS decimal(9, 3)),
    size_rank int NULL,
    approx_max_data_width bigint NULL,
    max_days_active int,
    UNIQUE CLUSTERED ( object_id, index_id )
    ) --SELECT * FROM #index_specs

    --**********************************************************************************************************************

    PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

    DECLARE @is_compression_available bit
    DECLARE @sql varchar(max)

    IF (CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Developer%' AND
    CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%')
    AND (CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) NOT LIKE '1[456789]%.%')
    SET @is_compression_available = 0
    ELSE
    SET @is_compression_available = 1
    SET @sql = '
    INSERT INTO #index_specs ( object_id, index_id,' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    min_compression, max_compression,' END + '
    alloc_mb, used_mb, rows )
    SELECT
    base_size.object_id,
    base_size.index_id, ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    base_size.min_compression,
    base_size.max_compression,' END + '
    (base_size.total_pages + ISNULL(internal_size.total_pages, 0)) / 128.0 AS alloc_mb,
    (base_size.used_pages + ISNULL(internal_size.used_pages, 0)) / 128.0 AS used_mb,
    base_size.row_count AS rows
    FROM (
    SELECT
    dps.object_id,
    dps.index_id, ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    MIN(p.data_compression) AS min_compression,
    MAX(p.data_compression) AS max_compression,' END + '
    SUM(dps.reserved_page_count) AS total_pages,
    SUM(dps.used_page_count) AS used_pages,
    SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.row_count ELSE 0 END) AS row_count
    FROM sys.dm_db_partition_stats dps ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    INNER JOIN sys.partitions p WITH (NOLOCK) ON
    p.partition_id = dps.partition_id ' END + '
    --WHERE dps.object_id > 100
    WHERE OBJECT_NAME(dps.object_id) LIKE ''' + @table_name_pattern + ''' AND
    OBJECT_NAME(dps.object_id) NOT LIKE ''' + @table_name_exclude_pattern + '''
    GROUP BY
    dps.object_id,
    dps.index_id
    ) AS base_size
    LEFT OUTER JOIN (
    SELECT
    it.parent_id,
    SUM(dps.reserved_page_count) AS total_pages,
    SUM(dps.used_page_count) AS used_pages
    FROM sys.internal_tables it WITH (NOLOCK)
    INNER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = it.parent_id
    WHERE it.internal_type IN ( ''202'', ''204'', ''211'', ''212'', ''213'', ''214'', ''215'', ''216'' )
    GROUP BY
    it.parent_id
    ) AS internal_size ON base_size.index_id IN (0, 1) AND internal_size.parent_id = base_size.object_id
    '
    IF @debug >= 1
    PRINT @sql
    EXEC(@sql)

    --**********************************************************************************************************************

    UPDATE [is]
    SET approx_max_data_width = index_cols.approx_max_data_width
    FROM #index_specs [is]
    INNER JOIN (
    SELECT index_col_ids.object_id, index_col_ids.index_id,
    SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width
    FROM (
    SELECT ic.object_id, ic.index_id, ic.column_id
    --,object_name(ic.object_id)
    FROM sys.index_columns ic
    WHERE
    ic.object_id > 100
    UNION
    SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id
    --,object_name(i_nonclus.object_id)
    FROM sys.indexes i_nonclus
    CROSS APPLY (
    SELECT ic_clus2.column_id
    --,object_name(ic_clus2.object_id),ic_clus2.key_ordinal
    FROM sys.index_columns ic_clus2
    WHERE
    ic_clus2.object_id = i_nonclus.object_id AND
    ic_clus2.index_id = 1 AND
    ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway
    ) AS ic_clus
    WHERE
    i_nonclus.object_id > 100 AND
    i_nonclus.index_id > 1
    ) AS index_col_ids
    INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id
    GROUP BY index_col_ids.object_id, index_col_ids.index_id
    ) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id

    UPDATE ispec
    SET table_mb = ispec_ranking.table_mb,
    size_rank = ispec_ranking.size_rank
    FROM #index_specs ispec
    INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(ORDER BY table_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
    FROM (
    SELECT object_id, SUM(alloc_mb) AS table_mb, MAX(rows) AS rows
    FROM #index_specs
    GROUP BY object_id
    ) AS ispec_allocs
    ) AS ispec_ranking ON
    ispec_ranking.object_id = ispec.object_id

    --**********************************************************************************************************************

    IF @list_missing_indexes = 1
    BEGIN
    SELECT
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME(mid.database_id) AS Db_Name,
    CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
    ispec.size_rank, ispec.table_mb,
    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
    WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
    WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
    ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id /*, mid.database_id*/) + '.'
    ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
    mid.equality_columns, mid.inequality_columns,
    LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, ',', '')) + 1 AS incl_col_count,
    mid.included_columns,
    user_seeks, user_scans, NULL AS max_days_active, /*cj1.max_days_active,*/ unique_compiles,
    last_user_seek, last_user_scan,
    CAST(avg_total_user_cost AS decimal(9, 3)) AS avg_total_user_cost,
    CAST(avg_user_impact AS decimal(9, 3)) AS [avg_user_impact%],
    system_seeks, system_scans, last_system_seek, last_system_scan,
    CAST(avg_total_system_cost AS decimal(9, 3)) AS avg_total_system_cost,
    CAST(avg_system_impact AS decimal(9, 3)) AS [avg_system_impact%],
    mid.statement, mid.object_id, mid.index_handle
    INTO #index_missing
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    INNER JOIN sys.indexes i ON i.object_id = mid.object_id AND i.index_id IN (0, 1) AND i.data_space_id <= 32767
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
    mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
    migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = mid.object_id AND
    dps.index_id IN (0, 1)
    OUTER APPLY (
    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    OUTER APPLY (
    SELECT ispec.table_mb, ispec.size_rank
    FROM dbo.#index_specs ispec
    WHERE
    ispec.object_id = mid.object_id AND
    ispec.index_id IN (0, 1)
    ) AS ispec
    --order by
    --DB_NAME, Table_Name, equality_columns
    WHERE
    1 = 1
    AND mid.database_id = DB_ID()
    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
    ORDER BY
    --avg_total_user_cost * (user_seeks + user_scans) DESC,
    Db_Name,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    Table_Name,
    equality_columns, inequality_columns,
    user_seeks DESC
    SELECT *
    FROM #index_missing
    ORDER BY ident
    /*
    IF @list_missing_indexes_summary = 1
    BEGIN
    SELECT
    derived.Size_Rank, derived.table_mb,
    derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks,
    ISNULL((SELECT SUM(user_seeks)
    FROM #index_missing im2
    OUTER APPLY dbo.DelimitedSplit8K (inequality_columns, ',') ds
    WHERE im2.Size_Rank = derived.Size_Rank AND
    LTRIM(RTRIM(ds.Item)) = derived.Equality_Column
    ), 0) AS Inequality_Seeks,
    derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,
    derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost
    FROM (
    SELECT
    Size_Rank, MAX(table_mb) AS table_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column,
    SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,
    MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,
    MIN(max_days_active) AS Max_Days_Active,
    MAX(avg_total_user_cost) AS Avg_Total_User_Cost,
    (SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,
    MAX(ds.ItemNumber) AS Equality#
    FROM #index_missing
    CROSS APPLY dbo.DelimitedSplit8K (equality_columns, ',') ds
    WHERE equality_columns IS NOT NULL
    GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))
    ) AS derived
    ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC
    END --IF
    */END --IF

    PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)

    --**********************************************************************************************************************

    -- list index usage stats (seeks, scans, etc.)
    SELECT
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME() AS db_name,
    ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb,
    CASE WHEN i.data_space_id <= 32767 THEN FILEGROUP_NAME(i.data_space_id) ELSE '{CS}' END AS main_fg_name,
    CAST(NULL AS int) AS filler,
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.'
    ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,
    key_cols AS key_cols, nonkey_cols AS nonkey_cols,
    LEN(nonkey_cols) - LEN(REPLACE(nonkey_cols, ',', '')) + 1 AS nonkey_count,
    CAST(NULL AS varchar(100)) AS filler2,
    ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
    ius.user_lookups, ius.user_updates,
    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
    WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
    WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
    ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
    ispec.alloc_gb AS index_gb, ispec.table_gb,
    SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +
    CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN ''
    ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [Uniq?],
    REPLACE(i.name, oa1.table_name, '~') AS index_name,
    i.index_id,
    ispec.approx_max_data_width AS [data_width],
    CAST(CAST(ispec.used_mb AS float) * 1024.0 * 1024.0 / NULLIF(dps.row_count, 0) AS int) AS cmptd_row_size,
    CASE
    WHEN ispec.max_compression IS NULL THEN '(Not applicable)'
    WHEN ispec.max_compression = 2 THEN 'Page'
    WHEN ispec.max_compression = 1 THEN 'Row'
    WHEN ispec.max_compression = 0 THEN ''
    ELSE '(Unknown)' END AS max_compression,
    i.fill_factor,
    dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,
    dios.range_scan_count, dios.singleton_lookup_count,
    DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,
    DATEDIFF(DAY, CASE
    WHEN o.create_date > @sql_startup_date AND @sql_startup_date > o.modify_date THEN o.create_date
    WHEN o.create_date > @sql_startup_date AND o.modify_date > @sql_startup_date THEN o.modify_date
    ELSE @sql_startup_date END, GETDATE()) AS max_days_active,
    dios.row_lock_count, dios.row_lock_wait_in_ms,
    dios.page_lock_count, dios.page_lock_wait_in_ms,
    ius.last_user_seek, ius.last_user_scan,
    ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    ius2.row_num,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update,
    GETDATE() AS capture_date
    INTO #index_usage
    FROM sys.indexes i WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
    INNER JOIN dbo.#index_specs ispec ON
    ispec.object_id = i.object_id AND
    ispec.index_id = i.index_id
    OUTER APPLY (
    SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)
    THEN 1 ELSE 0 END AS has_clustered_index
    ) AS cj2
    OUTER APPLY (
    SELECT STUFF((
    SELECT
    ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic WITH (NOLOCK)
    WHERE
    ic.key_ordinal > 0 AND
    ic.object_id = i.object_id AND
    ic.index_id = i.index_id
    ORDER BY
    ic.key_ordinal
    FOR XML PATH('')
    ), 1, 2, '')
    ) AS key_cols (key_cols)
    OUTER APPLY (
    SELECT STUFF((
    SELECT
    ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic WITH (NOLOCK)
    WHERE
    ic.key_ordinal = 0 AND
    ic.object_id = i.object_id AND
    ic.index_id = i.index_id
    ORDER BY
    COL_NAME(ic.object_id, ic.column_id)
    FOR XML PATH('')
    ), 1, 2, '')
    ) AS nonkey_cols (nonkey_cols)
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
    LEFT OUTER JOIN (
    SELECT
    database_id, object_id, MAX(user_scans) AS user_scans,
    ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
    database_id = DB_ID()
    --AND index_id > 0
    GROUP BY
    database_id, object_id
    ) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
    LEFT OUTER JOIN (
    SELECT
    referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys WITH (NOLOCK)
    WHERE
    is_disabled = 0
    GROUP BY
    referenced_object_id
    ) AS fk ON
    fk.referenced_object_id = i.object_id
    LEFT OUTER JOIN #index_operational_stats dios ON
    dios.object_id = i.object_id AND
    dios.index_id = i.index_id
    OUTER APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
    ) AS oa1
    OUTER APPLY (
    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    WHERE
    --i.object_id > 100 AND
    (i.is_disabled = 0 OR @order_by IN (-1, 1)) AND
    i.is_hypothetical = 0 AND
    i.data_space_id <= 32767 AND
    --i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
    o.name LIKE @table_name_pattern AND
    o.name NOT LIKE 'dtprop%' AND
    o.name NOT LIKE 'filestream[_]' AND
    o.name NOT LIKE 'MSpeer%' AND
    o.name NOT LIKE 'MSpub%' AND
    --o.name NOT LIKE 'tmp[_]%' AND
    --o.name NOT LIKE 'queue[_]%' AND
    --(DB_NAME() IN ('master', 'msdb') OR o.name NOT LIKE 'sys%')
    (@include_system_tables = 1 OR o.name NOT LIKE 'sys%')
    --AND o.name NOT LIKE 'tmp%'
    ) AND
    (@filegroup_name IS NULL OR CASE WHEN i.data_space_id <= 32767 THEN FILEGROUP_NAME(i.data_space_id) ELSE '{CS}' END LIKE @filegroup_name) AND
    (@max_compression IS NULL OR ispec.max_compression <= @max_compression)
    ORDER BY
    --cj2.has_clustered_index, ispec.size_rank, --heaps first, by size
    db_name,
    --i.index_id,
    --ius.user_seeks - ius.user_scans,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    table_name,
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
    key_cols
    OPTION (MAXDOP 3, RECOMPILE)

    SELECT *
    FROM #index_usage
    ORDER BY ident

    IF @list_total_size > 1
    SELECT SUM(index_gb) AS Total_Size_GB
    FROM #index_usage

    IF @list_filegroup_and_drive_summary = 1
    SELECT
    LEFT(df.physical_name, 1) AS drive,
    FILEGROUP_NAME(au_totals.data_space_id) AS filegroup_name,
    au_totals.total_mb AS total_fg_mb,
    au_totals.used_mb AS used_fg_mb,
    au_totals.total_mb - au_totals.used_mb AS free_fg_mb,
    CAST(df.size / 128.0 AS decimal(9, 3)) AS file_size_mb
    FROM (
    SELECT
    au.data_space_id,
    CAST(SUM(au.total_pages) / 128.0 AS decimal(9, 3)) AS total_mb,
    CAST(SUM(au.used_pages) / 128.0 AS decimal(9, 3)) AS used_mb
    FROM sys.allocation_units au
    INNER JOIN sys.filegroups fg ON
    fg.data_space_id = au.data_space_id
    GROUP BY au.data_space_id WITH ROLLUP
    ) AS au_totals
    INNER JOIN sys.database_files df ON
    df.data_space_id = au_totals.data_space_id
    ORDER BY filegroup_name, drive

    PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

    --**********************************************************************************************************************

    /*reset settings to their original values*/SET DEADLOCK_PRIORITY @deadlock_priority
    IF @transaction_isolation_level = 1
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    ELSE
    IF @transaction_isolation_level = 2
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    ELSE
    IF @transaction_isolation_level = 3
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    ELSE
    IF @transaction_isolation_level = 4
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    ELSE
    IF @transaction_isolation_level = 5
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    /*end of script*/

     

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • zoggling wrote:

    Thanks all. I can confirm that the code is not being called in a loop, or in an explicit transaction. The parent procedure sproc_ValidateUser just performs some basic validation and then calls sproc_CreateUserEvent at the end.

    I have since learnt that the test environment has lower tier storage than the production environment. Obviously this is a big difference, but it still seems odd that the deadlocks are occurring at all, given how simple these queries are.

    Is there anything obvious that can be improved upon in the code I have provided? We have looked at recoding sproc_User_Events_Cleardown to delete records in batches, but the procedure then took a much longer amount of time to do what the current procedure does in 0-8 seconds.

    The locks are page locks not row locks. So the fact that you have a slower disk and fewer rows in the test environment would explain why you are more likely to get locks in the test environment.

    You could try using lock hints to force row-level locks. The WITH (ROWLOCK) hint can be added to specific queries to ensure that only individual rows are locked.

    CREATE PROCEDURE dbo.sproc_User_Events_Cleardown
    AS
    DECLARE @Today DATETIME = CONVERT(DATETIME, CONVERT(DATE, GETUTCDATE()));

    DELETE UE
    FROM dbo.User_Events UE WITH (ROWLOCK) -- Add ROWLOCK hint here
    INNER JOIN dbo.User_Event_Types UET ON UET.UserEventType = UE.UserEventType
    WHERE UE.AuditTimestamp < DATEADD(WEEK, -UET.RetentionPeriodInWeeks, @Today);

    GO
  • @scottpletcher - apologies for the delay in responding to your post. Please find the requested results of running your script attached. Please let me know what you think, thank you.

    Attachments:
    You must be logged in to view attached files.
  • Removing duplicate comment. See below.

    • This reply was modified 3 months, 3 weeks ago by  zoggling.
    • This reply was modified 3 months, 3 weeks ago by  zoggling.
  • Jonathan AC Roberts wrote:

    zoggling wrote:

    Thanks all. I can confirm that the code is not being called in a loop, or in an explicit transaction. The parent procedure sproc_ValidateUser just performs some basic validation and then calls sproc_CreateUserEvent at the end.

    I have since learnt that the test environment has lower tier storage than the production environment. Obviously this is a big difference, but it still seems odd that the deadlocks are occurring at all, given how simple these queries are.

    Is there anything obvious that can be improved upon in the code I have provided? We have looked at recoding sproc_User_Events_Cleardown to delete records in batches, but the procedure then took a much longer amount of time to do what the current procedure does in 0-8 seconds.

    The locks are page locks not row locks. So the fact that you have a slower disk and fewer rows in the test environment would explain why you are more likely to get locks in the test environment.

    You could try using lock hints to force row-level locks. The WITH (ROWLOCK) hint can be added to specific queries to ensure that only individual rows are locked.

    CREATE PROCEDURE dbo.sproc_User_Events_Cleardown
    AS
    DECLARE @Today DATETIME = CONVERT(DATETIME, CONVERT(DATE, GETUTCDATE()));

    DELETE UE
    FROM dbo.User_Events UE WITH (ROWLOCK) -- Add ROWLOCK hint here
    INNER JOIN dbo.User_Event_Types UET ON UET.UserEventType = UE.UserEventType
    WHERE UE.AuditTimestamp < DATEADD(WEEK, -UET.RetentionPeriodInWeeks, @Today);

    GO

    Thank you for your response. Many thousand of rows are deleted each time sproc_User_Events_Cleardown is run daily. Will a ROWLOCK query hint not be escalated to a full table lock, potentially making the issue worse?

  • @zoggling:

    There should have been 2 result sets.  Did the first query -- for missing indexes -- not return any rows?

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • @scottpletcher - correct, no missing indexes were identified. Only the second query returned results.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply