SQL Server Improvement Pillars

SQL Server 2008 improvement pillars

Creating Deadlocks

I have have been trying to re-create deadlocks in my database and after a few try I came up with a scripts which does it for me.

CREATE TABLE createDeadlock1 (deadLockId int)
go
Create Table createDeadlock2 (deadlockId int)
-- now populate the table with some data
insert into createDeadlock1
values (1)
insert into createDeadlock2
values(1)
-- now open two connections
steps 1 : In connection 1 run :
begin tran
update createDeadlock1
set deadlockId = 1

step2 : Then in connection 2 run
begin tran
update createDeadlock2
set deadlockId = 1
update createDeadlock1
set deadlockId = 1

step 3 - go back to connection 1 and run

update createDeadlock2
set deadlockId = 1


The above three steps will create a deadlock. Do it many times and you will see the counter value always increasing. To view the counter use sysperfinfo table as shown below.
select * from master.dbo.sysperfinfo
where instance_name = '_total' and counter_name like '%deadlock%'

sysperfinfo not time - adjusted

Recently, I come across a monitoring tool that uses sysperfinfo table to get out number of dead locks/second. It took me a good few minutes to figure out that using this value as a time – adjusted counter was wrong. I am not sure if it is by design or a bug in Microsoft that some of the counters are not time-adjusted. So, the values you are getting for this particular counter is an incremental value. While digging a bit into Microsoft knowledge base I came across an article written by Geoff Hiten (MVP) in February 25, 2004 and it has been reported as a bug. This shows that some of monitoring tools shows you wrong information. To view the article click sysperfinfo not time - adjusted. This article lists the counters that are affected.