January 3, 2012 at 1:53 pm
I'd be truly grateful for any assistance with the following Deadlock and Blocking scenario I am troubleshooting.
Problem: There is a Windows Silverlight Service running on the application server that triggers events at regular intervals. When the service is running we get deadlocks and often times blocking as a result on the database server.
There are no mentionable performance issues on either server and resources are not even close to being over utilized.
During a profiler trace I see the same t-sql executed at a consistent rate, auto incrementing a number in a table column, without causing a deadlock. However, about every 45 minutes to an hour a deadlock occurs on two threads executing the same t-sql statement to increment a number by 1.
The t-sql is tuned ok, it is an update with an explicit where clause and has an index on the key. There is only 45 records in the table it is updating.
I have a development environment set up where I can test changes to the environment so if you have suggestions on configs I can try that'd be great.
Prod Environments: (same in dev)
Application server:
Virtual Server, 2.33GHz 2 quad-core CPU, 8GB of RAM, Windows 2008 R2 64 bit OS, 1 Gbps NIC,
C: 20GB with 5.27GB free, E: 30GB with 14GB free, running IIS and Web services
Database server:
Physical Server, 24 CPUs 2.4GHz (12 cores, hyperthreaded), 32GB RAM, Windows 2008 R2 64 bit OS, 1 Gbps NIC,
Disk Arrays:
(C:(OS)) Logical Drive 1 (136.7 GB, RAID 1) \\.\PhysicalDrive0
(H:(App Install)) Logical Drive 2 (136.7 GB, RAID 1) \\.\PhysicalDrive1
(E:(Data)) Logical Drive 3 (273.4 GB, RAID 1+0) \\.\PhysicalDrive2
(F:(Logs)) Logical Drive 4 (273.4 GB, RAID 1+0) \\.\PhysicalDrive3
(G:(Backup)) Logical Drive 5 (273.4 GB, RAID 5) \\.\PhysicalDrive4
Top 10 high wait times:
LAZYWRITER_SLEEP
BROKER_TASK_STOP
XE_TIMER_EVENT
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
LOGMGR_QUEUE
REQUEST_FOR_DEADLOCK_SEARCH
CHECKPOINT_QUEUE
ONDEMAND_TASK_QUEUE
XE_DISPATCHER_WAIT
SLEEP_TASK
No Jobs or other tasks are running against the server when the deadlock happens.
I have tried to change the maxdop to 6 which made no impact, I limited the amount of max memory to a little less than the database size, made no difference.
Thoughts, ideas? Thanks in advance SQL Community Gurus!
Hawkeye DBA
January 3, 2012 at 1:57 pm
Any chance you've got the Deadlock Graph handy?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 3, 2012 at 2:05 pm
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2012 at 8:44 am
Hi, and thanks for the quick response!
FYI - I removed our server name, user name, and database name from the graph.
As you can see the web service issues the same statement at the same time agains the same resource ugh.
I am also looking at the app server, it appears it is loosing Pings, however this just recently started so I cannot say that is related since the deadlocks have been occurring for a month now. The deadlocks are being caused by the Windows SilverLight service, when that is stopped they stop.
Thanks again for your ideas!
Graph image is attached.
<?xml version="1.0" encoding="iso-8859-1" ?>
- <deadlock-list>
- <deadlock victim="process7796e08">
- <process-list>
- <process id="process7796e08" taskpriority="0" logused="0" waitresource="KEY: 6:72057594046513152 (b06e872dc16b)" waittime="1218" ownerId="5978893" transactionname="user_transaction" lasttranstarted="2012-01-04T08:49:41.177" XDES="0xaf1b4ff0" lockMode="X" schedulerid="20" kpid="4544" status="suspended" spid="53" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-01-04T08:49:41.177" lastbatchcompleted="2012-01-04T08:49:41.177" clientapp=".Net SqlClient Data Provider" hostname="removed" hostpid="3888" loginname="removed" isolationlevel="serializable (4)" xactid="5978893" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
<frame procname="adhoc" line="2" stmtstart="56" sqlhandle="0x02000000f0626d172d6e9de25d740243c9925562cdf63cb0">Update dbo.AutoNumberSettings Set NextValue = NextValue + 1 Where dbo.AutoNumberSettings.[ClassName] = @className</frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>
</executionStack>
<inputbuf>(@className nvarchar(47)) Update dbo.AutoNumberSettings Set NextValue = NextValue + 1 Where dbo.AutoNumberSettings.[ClassName] = @className</inputbuf>
</process>
- <process id="process96ce08" taskpriority="0" logused="0" waitresource="KEY: 6:72057594046513152 (b06e872dc16b)" waittime="1218" ownerId="5978895" transactionname="user_transaction" lasttranstarted="2012-01-04T08:49:41.177" XDES="0x80090940" lockMode="X" schedulerid="6" kpid="6168" status="suspended" spid="63" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-01-04T08:49:41.180" lastbatchcompleted="2012-01-04T08:49:41.177" clientapp=".Net SqlClient Data Provider" hostname="removed" hostpid="3888" loginname="removed" isolationlevel="serializable (4)" xactid="5978895" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
<frame procname="adhoc" line="2" stmtstart="56" sqlhandle="0x02000000f0626d172d6e9de25d740243c9925562cdf63cb0">Update dbo.AutoNumberSettings Set NextValue = NextValue + 1 Where dbo.AutoNumberSettings.[ClassName] = @className</frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>
</executionStack>
<inputbuf>(@className nvarchar(47)) Update dbo.AutoNumberSettings Set NextValue = NextValue + 1 Where dbo.AutoNumberSettings.[ClassName] = @className</inputbuf>
</process>
</process-list>
- <resource-list>
- <keylock hobtid="72057594046513152" dbid="6" objectname="DatabaseName.dbo.AUTONUMBERSETTINGS" indexname="PK_AutoNumberSettings" id="lock803a7f80" mode="S" associatedObjectId="72057594046513152">
- <owner-list>
<owner id="process96ce08" mode="S" />
</owner-list>
- <waiter-list>
<waiter id="process7796e08" mode="X" requestType="convert" />
</waiter-list>
</keylock>
- <keylock hobtid="72057594046513152" dbid="6" objectname="DatabaseName.dbo.AUTONUMBERSETTINGS" indexname="PK_AutoNumberSettings" id="lock803a7f80" mode="S" associatedObjectId="72057594046513152">
- <owner-list>
<owner id="process7796e08" mode="S" />
</owner-list>
- <waiter-list>
<waiter id="process96ce08" mode="X" requestType="convert" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
January 4, 2012 at 9:30 am
Oh no. An autonumber table. No wonder you have deadlocks.
Please post the definition of the AutoNumberSettings table, with all indexes and any triggers
Why are you using serialisable isolation level?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2012 at 9:32 am
Also, please run this and get me the execution plan.
BEGIN TRANSACTION
DECLARE @className nvarchar(47) = 'WillNotExist'
Update dbo.AutoNumberSettings Set NextValue = NextValue + 1 Where dbo.AutoNumberSettings.[ClassName] = @className
ROLLBACK TRANSACTION
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2012 at 9:39 am
LOL
Here's the table, it's name is not really what it looks like, unfortunately I have little say in the structure of this db as it is vendor-created.
[dbo].[AUTONUMBERSETTINGS](
[CLASSNAME] [nvarchar](250) NOT NULL,
[FORMATSTRING] [nvarchar](50) NOT NULL,
[PADWITHZEROSTOLENGTH] [int] NOT NULL,
[NEXTVALUE] [int] NOT NULL,
CONSTRAINT [PK_AutoNumberSettings] PRIMARY KEY CLUSTERED
(
[CLASSNAME] ASC
)
WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
No Triggers and only the one Primary Key
As for the serializable, that has to be coming from the Web Service executing the statement. The t-sql is not a stored procedure, merely a hard-coded t-sql query within the application it would appear.
January 4, 2012 at 9:41 am
She still needs this.
GilaMonster (1/4/2012)
Also, please run this and get me the execution plan.
BEGIN TRANSACTION
DECLARE @className nvarchar(47) = 'WillNotExist'
Update dbo.AutoNumberSettings Set NextValue = NextValue + 1 Where dbo.AutoNumberSettings.[ClassName] = @className
ROLLBACK TRANSACTION
Also what options do you have here? Can't change the code in any way aside from contacting the vendor about their issue? If not what limitations do you face?
January 4, 2012 at 9:42 am
Sorry, i've attached it 🙂
Thanks Gail and Ninja!
January 4, 2012 at 9:46 am
file version.
January 4, 2012 at 9:50 am
Hawkeye_DBA (1/4/2012)
As for the serializable, that has to be coming from the Web Service executing the statement.
Ok, but my question stands. Why are you using serialisable? Do you need that degree of isolation here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2012 at 9:54 am
Are there any other statements been sent from the webservice prior to this update? There's a user transaction here (not an auto-committed transaction), so there should be at least a BEGIN TRANSACTION and a COMMIT as well. I suspect there's also a select been sent as part of the transaction (because updates don't ever take shared locks, and there are shared locks held here)
Use Profiler if you don't want to dig into the code of the web service
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2012 at 9:55 am
My options are to tune the server, tune the hardware, and or tune the indexes.
I have a small concern that the hyperthreading on the db server processors may be causing a bit of an issue for the app server.
Basically once I rule out hardware or SQL configs I can ask the vendor to please correct whatever it is that is causing the deadlocks....
January 4, 2012 at 9:57 am
Hi Gail,
I am not aware that I can change that? If so, I'm all ears...
January 4, 2012 at 10:02 am
Hawkeye_DBA (1/4/2012)
My options are to tune the server, tune the hardware, and or tune the indexes.
Nope, nope and maybe
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply