Blocking, Deadlocking and general contention due to Writes in the Symantec_CMDB database.
Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlockvictim
This is normal behavior depending on many variables but can impact performance.
Implementing Row based versioning Isolation levels can greatly improve performance. It is important to note that it does have an impact on Tempdb performance and following Microsoft's best practices and recommendations around Tempdb configuration may be needed.
To implement the setting close all existing connections to the database and run the following SQL
ALTER DATABASE Symantec_CMDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER 10;
ALTER DATABASE Symantec_CMDB SET ALLOW_SNAPSHOT_ISOLATION ON
Tempdb factors are:
Location of the tempdb files - If possible do not put them on the system drive or the same drives as the Symantec_CMDB database
Size and file growth options - All files need to be defined identically, if the initial size is 1000 meg and to grow by 10% all new files need to be defined the same
Define multiple files for the tempdb database - As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
The following Microsoft articles where referenced.
This script may be helpful in troubleshooting and determining if tempdb contention could be a problem.
SELECT a.session_id, a.wait_type, a.wait_duration_ms, a.blocking_session_id, a.resource_description, CASE WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT) - 1 % 8088 = 0 THEN 'Is PFS Page' WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT) - 2 % 511232 = 0 THEN 'Is GAM Page' WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT) - 3 % 511232 = 0 THEN 'Is SGAM Page' ELSE 'Is Not PFS, GAM, or SGAM page' END resourcetype, c.text AS SQLText FROM sys.dm_os_waiting_tasks a INNER JOIN sys.sysprocesses b ON a.session_id = b.spid OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) c WHERE a.wait_type LIKE 'PAGE%LATCH_%' AND a.resource_description LIKE '2:%'
Imported Document ID: TECH207094
Subscribing will provide email updates when this Article is updated. Login is required.