June 25, 2012 at 8:21 am
Hi,
I have developed an application using ASP.NET 2008 and SQL server 2008. It sometimes gets locked so I ran TSQL Locks trace in profiler and got a lock report there but I don't know how to find out and resolve the problem using that trace.
the lock report is as follows:
<blocked-process-report>
<blocked-process>
<process id="process88235dc8" taskpriority="0" logused="0" waitresource="KEY: 9:72057594120634368 (af005beb78cd)" waittime="29593" ownerId="257117" transactionname="user_transaction" lasttranstarted="2012-06-22T15:18:45.367" XDES="0x88da7970" lockMode="S" schedulerid="1" kpid="3200" status="suspended" spid="78" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-06-22T15:18:45.367" lastbatchcompleted="2012-06-22T15:18:45.367" clientapp=".Net SqlClient Data Provider" hostname="WIN-SRV-2008-01" hostpid="988" loginname="NT AUTHORITY\NETWORK SERVICE" isolationlevel="read committed (2)" xactid="257117" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128024">
<executionStack>
<frame line="1" sqlhandle="0x020000009c12da30362ca6800685bf73a54853fda9eddf5c"/>
<frame line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000"/>
<frame line="63" stmtstart="3146" stmtend="3264" sqlhandle="0x030009006671ab62458a080177a000000100000000000000"/>
</executionStack>
<inputbuf>
Proc [Database Id = 9 Object Id = 1655402854] </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="74" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-06-22T15:16:38.297" lastbatchcompleted="2012-06-22T15:16:38.297" clientapp=".Net SqlClient Data Provider" hostname="WIN-SRV-2008-01" hostpid="988" loginname="NT AUTHORITY\NETWORK SERVICE" isolationlevel="read committed (2)" xactid="256040" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack/>
<inputbuf>
Proc [Database Id = 9 Object Id = 1895403709] </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
Any idea is appreciated
June 25, 2012 at 8:35 am
All that's telling you is that the object id's listed, one was waiting on the other. Look up the name of the object using object_name(objectid).
Instead of focusing directly on the locks, I'd focus on which queries are running slowly and work on tuning those.
"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
June 25, 2012 at 9:30 am
Thanks for the reply
Actually, I've been far from DBA stories for long. Could you tell me if there's any straight way to find slow queries?
June 25, 2012 at 9:59 am
The quickest method, but not necessarily the most accurate, is to use the dynamic management objects (DMOs). If you select from sys.dm_exec_query_stats, you can see aggregate calls for all queries currently in the cache on the server. You can sort by average run time or total run time and see what you need. The more sophisticated and accurate approach is to capture the calls using extended events and then aggregate the data.
"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
June 26, 2012 at 1:04 pm
Thanks for ur comment.
I don't know if here's a correct place to answer or not but when we published the application into a host server it was working slowly. and when we published it to another host server it times out so often and works again without making any changes to anything (even without restaring sql server or IIS ). when I look at the windows logs I see it is sql exception. Can the problem be due to something else other than slow queries?
June 26, 2012 at 1:15 pm
Hard to know. What's different about the different servers? Is one faster than another, more memory, more cpu, different kinds of disk storage, different settings on the sQL Server instance, different data, different statistics, different indexes... There's a very long, long list for why you may see different behavior, and I'm not sure about your details.
"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
June 26, 2012 at 1:23 pm
The only difference is that the former server is faster, it's got 3.46 GHz (3 processors) CPU but the latter server has got 1.87 GHz.
June 26, 2012 at 1:30 pm
OK, but I'm still in the dark here. You saw slower performance on a server with slower & fewer CPU? That's kind of normal. If your system was well tuned you could expect that. I'm sorry, but I just don't have enough info to tell you what the problem is and what you need to do to fix it. The original question was about blocking. You had a process that was blocked. But saying that is defining one symptom out of many, not the problem. What was blocked and why, for how long? What was blocking it? Why and for how long? You haven't identified any of these issues. We're jumping from topic to topic.
My suggestion, take some time, understand what's occurring on the server, gather metrics, get the longest running & most frequently called queries, the wait statistics on the OS, etc. With that understanding, you'll know what's happening on the server.
"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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply