SQL Deadlock in Reporting Database

Question

SQL Deadlock in Reporting Database

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

rajeev.suryanarayan SSChasing Mays Points: 634 More actions · Answer 1

I didnt get the error today. It comes on a few days while it doesnt show its head on other. Also in an environment that has lesser number of processors, I haven't met with this issue at all. Any link?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

The processor issue could be that parallel execution plans are running slower. Slower transactions can lead to more deadlocks since locks are held longer and lock escalations are delayed. If your servers are running under the default parallelism threshold, you might consider increasing it.

But before you did all that, you need to identify the processes that are leading to the deadlock and figure out why they're causing it. It doesn't behoove you to begin adjusting the system when you're completely in the dark.You could just as easily mess something up as fix the problem.

If you're not monitoring your production systems by running a trace occasionally, especially during high usage hours, you should start. Load the trace output into a table and start identifying your longest running queries and the ones that are using the most cpu and the most frequently called. Get their execution plans and start addressing indexing or coding issues. Proactive maintenance is a great way to avoid problems. The best way it is to get involved in the database design early and test it, like voting in Chicago, early and often.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Have a look at this blog post, see if it applies.

http://blogs.msdn.com/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rajeev.suryanarayan SSChasing Mays Points: 634 More actions · Answer 4

Hey Folks.. I am back! 🙂

I didnt have the issue for few days and now it raises its ugly head again. I have the trace information below as suggested by you guys. Please do throw some light on what could possibly be happening!

---------------------------------------------

2008-10-23 07:19:03.61 spid56 DBCC TRACEON 1222, server process ID (SPID) 56. This is an informational message only; no user action is required.

2008-10-23 07:27:07.03 spid4s deadlock-list

2008-10-23 07:27:07.03 spid4s deadlock victim=process2e385c8

2008-10-23 07:27:07.03 spid4s process-list

2008-10-23 07:27:07.03 spid4s process id=process8da6b8 waittime=15 schedulerid=1 kpid=7736 status=suspended spid=60 sbid=0 ecid=93 priority=0 transcount=0 lastbatchstarted=2008-10-23T07:26:57.857 lastbatchcompleted=2008-10-23T07:26:52.577 clientapp=Microsoft SQL Server Management Studio - Query hostname=TXADBSNWH039 hostpid=9708 isolationlevel=read committed (2) xactid=1544987 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2008-10-23 07:27:07.03 spid4s executionStack

2008-10-23 07:27:07.03 spid4s frame procname=unknown line=1 sqlhandle=0x0200000067c7b802c8ff2ceca9cbb69da237e63d964c5873

2008-10-23 07:27:07.03 spid4s unknown

2008-10-23 07:27:07.03 spid4s inputbuf

2008-10-23 07:27:07.03 spid4s process id=process8da898 taskpriority=0 logused=20018 waittime=562 schedulerid=1 kpid=8268 status=suspended spid=60 sbid=0 ecid=33 priority=0 transcount=0 lastbatchstarted=2008-10-23T07:26:57.857 lastbatchcompleted=2008-10-23T07:26:52.577 clientapp=Microsoft SQL Server Management Studio - Query hostname=TXADBSNWH039 hostpid=9708 isolationlevel=read committed (2) xactid=1544987 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2008-10-23 07:27:07.03 spid4s executionStack

2008-10-23 07:27:07.03 spid4s frame procname=unknown line=1 sqlhandle=0x0200000067c7b802c8ff2ceca9cbb69da237e63d964c5873

2008-10-23 07:27:07.03 spid4s unknown

2008-10-23 07:27:07.03 spid4s inputbuf

2008-10-23 07:27:07.03 spid4s process id=process8daa78 taskpriority=0 logused=20014 waittime=906 schedulerid=1 kpid=10060 status=suspended spid=60 sbid=0 ecid=29 priority=0 transcount=0 lastbatchstarted=2008-10-23T07:26:57.857 lastbatchcompleted=2008-10-23T07:26:52.577 clientapp=Microsoft SQL Server Management Studio - Query hostname=TXADBSNWH039 hostpid=9708 isolationlevel=read committed (2) xactid=1544987 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2008-10-23 07:27:07.03 spid4s executionStack

2008-10-23 07:27:07.03 spid4s frame procname=unknown line=1 sqlhandle=0x0200000067c7b802c8ff2ceca9cbb69da237e63d964c5873

2008-10-23 07:27:07.03 spid4s unknown

2008-10-23 07:27:07.03 spid4s inputbuf

2008-10-23 07:27:07.03 spid4s process id=process8dad48 taskpriority=0 logused=20022 waittime=203 schedulerid=1 kpid=6484 status=suspended spid=60 sbid=0 ecid=60 priority=0 transcount=0 lastbatchstarted=2008-10-23T07:26:57.857 lastbatchcompleted=2008-10-23T07:26:52.577 clientapp=Microsoft SQL Server Management Studio - Query hostname=TXADBSNWH039 hostpid=9708 isolationlevel=read committed (2) xactid=1544987 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2008-10-23 07:27:07.03 spid4s executionStack

2008-10-23 07:27:07.03 spid4s frame procname=unknown line=1 sqlhandle=0x0200000067c7b802c8ff2ceca9cbb69da237e63d964c5873

2008-10-23 07:27:07.03 spid4s unknown

2008-10-23 07:27:07.03 spid4s inputbuf

2008-10-23 07:27:07.03 spid4s process id=process8db018 taskpriority=0 logused=20010 waittime=1312 schedulerid=1 kpid=7656 status=suspended spid=60 sbid=0 ecid=8 priority=0 transcount=0 lastbatchstarted=2008-10-23T07:26:57.857 lastbatchcompleted=2008-10-23T07:26:52.577 clientapp=Microsoft SQL Server Management Studio - Query hostname=TXADBSNWH039 hostpid=9708 isolationlevel=read committed (2) xactid=1544987 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2008-10-23 07:27:07.03 spid4s executionStack

2008-10-23 07:27:07.03 spid4s frame procname=unknown line=1 sqlhandle=0x0200000067c7b802c8ff2ceca9cbb69da237e63d964c5873

2008-10-23 07:27:07.03 spid4s unknown

2008-10-23 07:27:07.03 spid4s inputbuf

2008-10-23 07:27:07.03 spid4s process id=process8db978 taskpriority=0 logused=20024 waittime=187 schedulerid=1 kpid=4548 status=suspended spid=60 sbid=0 ecid=73 priority=0 transcount=0 lastbatchstarted=2008-10-23T07:26:57.857 lastbatchcompleted=2008-10-23T07:26:52.577 clientapp=Microsoft SQL Server Management Studio - Query hostname=TXADBSNWH039 hostpid=9708 isolationlevel=read committed (2) xactid=1544987 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200

2008-10-23 07:27:07.04 spid4s executionStack

2008-10-23 07:27:07.04 spid4s frame procname=unknown line=1 sqlhandle=0x0200000067c7b802c8ff2ceca9cbb69da237e63d964c5873

2008-10-23 07:27:07.04 spid4s unknown