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.

     Working with tempdb in SQL Server 2005 http://technet.microsoft.com/en-us/library/cc966545.aspx

     Recommendations to reduce allocation contention in SQL Server tempdb database http://support.microsoft.com/kb/2154845

     Understanding Row Versioning-Based Isolation Levels http://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx

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:%'