January 30, 2016 at 5:17 am
Hi
I Enabled Server Side Profiler for a database.
in events I choose "Blocked process report" with threshold = 5 for totaly 2 hours in every days.
But the question is :
some times most of the cause of blocking is an SP that only contain a select with readuncommitted ???
is it possible ?
why ? and interesting point is on xml filed (bpreportxml) for secondary part (<blocking-process>) we dont have any sql line that have sqlhandle .
then we can not find the table and index related to this sp .
<blocked-process-report monitorLoop="1503033">
<blocked-process>
<process id="process3419f57c28" taskpriority="0" logused="0" waitresource="KEY: 9:720------7392 (8374a----c7b7)" waittime="8952" ownerId="22---9309" transactionname="SELECT" lasttranstarted="2016-01-30T11:26:58.860" XDES="0x27adca4660" lockMode="S" schedulerid="1" kpid="18768" status="suspended" spid="140" sbid="0" ecid="101" priority="0" trancount="0" lastbatchstarted="2016-01-30T11:26:58.860" lastbatchcompleted="2016-01-30T11:26:58.857" lastattention="1900-01-01T00:00:00.857" clientapp=".Net SqlClient Data Provider" hostname="D----" hostpid="9892" isolationlevel="read committed (2)" xactid="229---309" currentdb="9" lockTimeout="42949--7295" clientoption1="67--8672" clientoption2="128056">
<executionStack>
<frame line="1" stmtend="522" sqlhandle="0x020000000d-----a4daf40e0fa284c0bfd710000000000000000000000000000000000000000" />
<frame line="90" stmtstart="6418" stmtend="6444" sqlhandle="0x030009003e98a274d126260188a5000-------00000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
Proc [Database Id = 9 Object Id = 195---2862] </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="124" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-01-30T11:27:01.293" lastbatchcompleted="2016-01-30T11:27:01.293" lastattention="2016-01-30T11:23:07.240" clientapp=".Net SqlClient Data Provider" hostname="D------" hostpid="9408" loginname="D---" isolationlevel="read uncommitted (1)" xactid="2291---860" currentdb="9" lockTimeout="4294---7295" clientoption1="67108---2" clientoption2="12---056">
<executionStack />
<inputbuf>
Proc [Database Id = 9 Object Id = 1486---339] </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
Thank you
January 30, 2016 at 6:14 am
ReadUncommitted doesn't mean no locks. It means that selects don't take shared locks (and that your results can be quite inaccurate due to rows read twice or not read at all)
And the lines which you removed info from (for whatever reason) are the ones that tell you what the code is
Proc [Database Id = 9 Object Id = 195---2862] </inputbuf>
and
Proc [Database Id = 9 Object Id = 1486---339]
You have the DB ID and the object ID, so that will tell you the procedure.
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 30, 2016 at 6:45 am
yes, I searched this id on my db and found that sp which I told about it.
Blocking object was : a simple sp with readuncommitted
And
Blocked object was : another sp (not directly related to first sp)
And another point :
I profile all events for some minutes , (some minutes in the same time That I Profiled blocking) And I Found that when ever this sp became blocking root, It belonged to a transaction that has about 150 commands. and that tran. was opened for about 3 minutes.
plus
I monitored open transaction when we had many Blocked spids. Most of the Cause of blocking was an open transaction.
I can not relate between these open transaction and that sp , why sql server introduced this sp for the cause of blocking ?
January 30, 2016 at 6:56 am
If SQL said that procedure was the cause of the blocking, then it was the cause of the blocking.
150 commands and 3 minutes is insane for a transaction. Does it need to be that long?
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 30, 2016 at 7:15 am
GilaMonster (1/30/2016)
If SQL said that procedure was the cause of the blocking, then it was the cause of the blocking.150 commands and 3 minutes is insane for a transaction. Does it need to be that long?
MotivateMan, I have to agree with Gail about the sanity of such a large transaction. I have database backups that don't take 3 minutes. Do you really need to do that much work in a single transaction? If this fires as the result of a user event, the level of patience required for the page to come back is quite high.
MotivateMan1394 (1/30/2016)
I profile all events for some minutes , (some minutes in the same time That I Profiled blocking) And I Found that when ever this sp became blocking root, It belonged to a transaction that has about 150 commands. and that tran. was opened for about 3 minutes.
When you say this, do you mean:
A. The procedure was a part of the transaction
B. The procedure was the full transaction
C. The procedure was called multiple times and it added up to 150 commands and took 3 minutes.
I'm asking because if you have a huge process that includes the procedure (A) or a procedure with 150 commands (B), the probability of getting someone to go through the whole thing on a free help forum is a rather low. If you can do some investigation yourself and narrow down a few problem statements that consume 2:55 of the 3:00 run time, the odds go way up. If you're calling this procedure multiple times from inside a loop (C) then you may have set-based alternatives available to eliminate (or at least reduce) the problem. Then again, it may be a situation that's completely different and you need a redesign. There's no way of knowing anything for sure without additional details.
Edit: On the plus side, you've identified what the blocker is, which is a good thing. At least you know where to look.
January 30, 2016 at 7:18 am
I think there is a problem , when a transaction in between.
in detail :
when a connection created , you can get "Textcommand" of that spid. (very easy)
But if on that connection you run a transaction (for example in very bad tran. with 150 commands include some SPs) , And you want to get the Textcommand of that connection,
What is the result ?
for example with this Query : (I think sql server choose one of commands ,maybe randomly or base on some parameters !!!)
SELECT c.session_id ,'Kill '+Convert(varchar(5) ,c.session_id) k , t.text,s.host_name
,QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.dbid)) + '.'
+ QUOTENAME(OBJECT_NAME(t.objectid, t.dbid)) proc_name,
c.connect_time, s.last_request_start_time, s.last_request_end_time,
s.status,Program_name--,*
, Getdate() as infodate
FROM sys.dm_exec_connections c
JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE c.session_id in( select Blocked from sysprocesses where blocked <>0)
what do you think?
January 30, 2016 at 7:24 am
I think you get exactly what you asked for with that. The test of the most recent batch run on that session, as per c.most_recent_sql_handle. No, it's not random, or up to SQL.
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 30, 2016 at 7:43 am
Thank you GilaMonster And Ed Wagner
And about "Ed Wagner" Question :
This transaction are send by our application and This day I triggered to review it. (but it is time consuming)
and about contents of this transaction :
there are many select , update , ...
and that sp is one of that SPs and run some times . (I think It was called in ForEach structure) .
But in GilaMonster reply was a Point, maybe this SP is the most recent called sp in this Transaction.
I didnot notice to it :
c.most_recent_sql_handle.
Thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply