DTC Error - Warning - The CID values for both machines are the same

In my experience this issue is generally caused by either physical servers that have been cloned from an Image, or virtual machines that have been deployed from a template.

DTCPing is a very useful tool in diagnosing issues with DTC Communication, and can be found here:

http://www.microsoft.com/en-us/download/details.aspx?id=2868

If for any reason the above link changes this tool can be found in the Microsoft Download Centre.

The error with the DTC Communication in this instance is caused by duplicate CIDs. This can be confimed by checking the registry for the following Key:

HKEY_CLASSES_ROOTCID

If these keys are identical on both machines then follow the following steps:

1. Use Add Windows Components, and remove Network DTC.
2. Go to the command line and run: MSDTC -uninstall
3. Go to the registry and delete the MSDTC keys in HKLM/Software/Microsoft/Software/MSDTC, HKLM/System/CurrentControlSet/Services/MSDTC, and HKEY_CLASSES_ROOTCID (if they’re still there).
4. Reboot
5. Go to the command line and run: MSDTC -install
6. Use Add Windows Components, and add Network DTC.
7. Go to the command line and run: net start msdtc 

The above information has worked for me, and the relevant information was taken from Wade Wegner's Blog:

http://www.wadewegner.com/2007/08/warning-the-cid-values-for-both-test-machines-are-the-same/ 

 You can sometimes get away with just doing the following, and I've found it works every time on Server 2008r2

     1.  Run a command prompt with administrative privileges.
     2.  At the command line run: MSDTC -uninstall (Unfortunately you don't get any messages)
     3.  Reboot the server.
     4.  Run a command prompt with administrative privileges.
     5.  At the command line run: MSDTC -install
     6.  At the command line run: sc config msdtc start= auto  (NOTE the space after the =)
     7.  At the command line run: sc start msdtc

TempDB Contention

Taken from a great post by Robert Davis on SQLServerCentral.com

http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/

Breaking Down TempDB Contention

What is tempDB contention?

From the outside looking in, tempDB contention may look like any other blocking. There are two types of contention that tends to plague tempDB's, especially when the tempDB is not configured to best practices (multiple, equally sized data files, located on a dedicated, high-speed drive, etc.). For the purpose of this blog, I want to focus on latch contention on the allocation pages.

What are allocation pages?

Allocation pages are special pages in the data files that track and mange extent allocations. There are 3 types of allocation pages that can experience contention and bring a server to a slow crawl.

Global Allocation Map (GAM): Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.

Shared Global Allocation Map (SGAM): Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.

Page Free Space (PFS): Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages.

Finding Latch Contention on Allocation Pages

You can use the dynamic management view (DMV) sys.dm_os_waiting_tasks to find tasks that are waiting on a resource. Tasks waiting on PageIOLatch or PageLatch wait types are experiencing contention. The resource description points to the page that is experiencing contention, and you can easily parse the resource description to get the page number. Then it's just a math problem to determine if it is an allocation page.

The Resource Description (sample):

The resource description will be in the form of <database ID>:<file ID>:<page number>. The tempDB is always database ID of 2. A sample resource description may look like 2:3:18070499. We want to focus on the page ID of 18070499.

The formula for determining the page type is as follows:

GAM: (Page ID - 2) % 511232

SGAM: (Page ID - 3) % 511232

PFS: (Page ID - 1) % 8088

If one of these formulas equates to 0, then the contention is on the allocation pages.

The query

Yes, I love posting queries as you may have noticed. Here is my query to check for allocation page contention in tempDB.

SELECT      session_id,

            wait_type,

            wait_duration_ms,

            blocking_session_id,

            resource_description,

            ResourceType = CASE

WHEN CAST(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'

            WHEN Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'

            WHEN Cast(Right(resource_description, Len(resource_description) - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'

            ELSE 'Is Not PFS, GAM, or SGAM page'

      END

FROM sys.dm_os_waiting_tasks

WHERE wait_type Like 'PAGE%LATCH_%'

AND resource_description Like '2:%'