October 10, 2008 at 7:58 am
Every time I run this query it is shown to be blocking itslef when I run sp_blocked in a SQL2000 Sp4 system. Where can I tighten the code to make it more effective...thanks in advance
(SELECT isnull(ClientList.strLast,'') + ' ' + isnull(ClientList.strFirst,'') AS AgentName, '###-##-' + substring(ClientList.TaxInfoId, 6, 4) as TaxInfoId, isnull(ClientList.strAgencyTrackingID,'') as strAgencyTrackingID, isnull(tblTransactionTypes.strTrxInfo,'') as strTrxInfo, isnull(tlkpCompanyC.strNAICNr,'') as strNAICNr, tlkpCompanyC.AgentName AS CompanyInfo, EffectiveTrans.strStateCd, EffectiveTrans.strRes, substring(EffectiveTrans.EntryDate, 5, 2) + '/' + substring(EffectiveTrans.EntryDate, 7, 2) + '/' + substring(EffectiveTrans.EntryDate, 1, 4) as EntryDate, isnull(PassInfo.memPassData,'') as CostInfo, Cast(EffectiveTrans.EffBillAmt as money)/100 as strFeeAmount1, EffectiveTrans.TransactionNr, EffectiveTrans.numBatchInfo FROM EffectiveTrans with (nolock) LEFT JOIN dbo.PassInfo with (nolock) ON dbo.EffectiveTrans.TransactionNr = dbo.PassInfo.TransactionNr and (strfldname='CostCenter' or
strfldname='Invoice') LEFT JOIN ClientList with (nolock) ON EffectiveTrans.SysIDInfo = ClientList.lngIndivCNT LEFT JOIN tlkpCompanyc with (nolock) ON EffectiveTrans.lngCmpCnt = tlkpCompanyc.lngCmpCnt AND EffectiveTrans.AgenttNr = tlkpcompanyc.AgenttNr LEFT JOIN tblTransactionTypes with (nolock) ON EffectiveTrans.strTrxType = tblTransactionTypes.strTrxType WHERE EffectiveTrans.AgenttNr = 1108 And EffectiveTrans.orgStrLetter='I' and EffectiveTrans.strTrxType in ('F','R','P','A') AND EffectiveTrans.EffTrxDate between '08/01/2008' and '08/15/2008') Union All (SELECT replace(isnull(AgencyInfo.AgentName,''),',','') AS AgentName, isnull(AgencyInfo.TaxInfoId,'') as TaxInfoId, isnull(AgencyInfo.strAgencyTrackingID,'') as strAgencyTrackingID, isnull(tblTransactionTypes.strTrxInfo,'') as strTrxInfo, isnull(tlkpCompanyC.strNAICNr,'') as strNAICNr, tlkpCompanyC.AgentName AS CompanyInfo, EffectiveTrans.strStateCd, EffectiveTrans.strRes,
substring(EffectiveTrans.EntryDate, 5, 2) + '/' + substring(EffectiveTrans.EntryDate, 7, 2) + '/' + substring(EffectiveTrans.EntryDate, 1, 4) as EntryDate, isnull(PassInfo.memPassData,'') as CostInfo, Cast(EffectiveTrans.EffBillAmt as money)/100 as strFeeAmount1, EffectiveTrans.TransactionNr, EffectiveTrans.numBatchInfo FROM EffectiveTrans with (nolock) LEFT JOIN dbo.PassInfo with (nolock) ON dbo.EffectiveTrans.TransactionNr = dbo.PassInfo.TransactionNr and (strfldname='CostCenter' or strfldname='Invoice') LEFT JOIN AgencyInfo with (nolock) ON EffectiveTrans.lngAgySysID = AgencyInfo.lngAgyCnt LEFT JOIN tlkpCompanyc with (nolock) ON EffectiveTrans.lngCmpCnt = tlkpCompanyc.lngCmpCnt AND EffectiveTrans.AgenttNr = tlkpcompanyc.AgenttNr LEFT JOIN tblTransactionTypes with (nolock) ON EffectiveTrans.strTrxType = tblTransactionTypes.strTrxType WHERE EffectiveTrans.AgenttNr = 1108 And EffectiveTrans.orgStrLetter='A' and
EffectiveTrans.strTrxType in ('F','R','P','A') AND EffectiveTrans.EffTrxDate between '08/16/2008' and '08/31/2008')
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 10, 2008 at 9:01 am
I've seen this happen on 2K5 with parallelism. If you look at the Activity Monitor you see one spid repeated a whole bunch of times and if I remember right, all but one shows that it is blocking itsself (50 blocking 50 repeated 5 times, then one 50 not blocked). I believe that is normal - it splits the query up into pieces and is waiting for one of the pieces to finish so it can sew it all back together. I think it would show up the same in 2K if you got a list of all the running spids. If I remember right, I saw this in 2K too, but it's been awhile.
Chad
October 10, 2008 at 9:04 am
yes it shows same SPID a bunch of times and then the message is SPID 50 blocking SPID 50 blocked by SPID 50..this is so strange..I dont know if there is a workaround for this...thanks again!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 10, 2008 at 10:07 am
I believe it is "normal", but confusing to see. Microsoft has a paper somewhere explaining it.
October 10, 2008 at 10:21 am
its not only confusing but it makes me at loss of words to explain this behaviour. I mean which process do I need to rollback to continue with the other...i need to search the knowledge base....thanks again!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 10, 2008 at 10:29 am
I don't think you have blocking in the "normal" sense. It's more of a "wait" than blocking. Do you have multiple CPUs? When you look at the query plan do you see parallelism operators? If so, there is nothing wrong. The blocking is indicating that the parallel threads are waiting for other threads to finish before putting all the work back together again.
October 10, 2008 at 10:32 am
I saw the below mentioned explanation somewhere on this site:
SP4 reports queries blocking on themselves?
What you are seeing is not related to locks, rather it is related to the
latch waits you see in sysprocesses.
In SP4 we are able to determine the blocking spid for latch waits when the blocking spid holds the latch in exclusive or update mode. In these cases the blocking spid information is populated into the "blocked" field in sysprocesses.
So why is the spid blocked by itself? Well, that has to do with how latches are used for IO operations. When an IO is issued for page, a latch is held on the page. Depending on the IO operation, the latch mode acquired is shared (SH) or exclusive (EX). The latch for the IO is acquired by the thread that issues the IO. Since all SQL Server IO operations are asynchronous, if the spid that issued the IO wants to wait for the IO to complete it will attempt to acquire another latch on the same page after issuing the IO. The first latch is released when the IO completes. This release allows the second latch request to be granted.
Here's an example of how this works:
1. Spid 55 wants to read page P1 which does not exist in the buffer pool.
2. Spid 55 acquires an EX latch on page P1 -- this marks spid 55 as owning the latch. The latch is in an in memory data structure, not the physical page itself. Since the page does not yet exist in memory the mode is EX in order to force other spids that may also want to access the page to wait for the IO to complete and also to prevent them from issuing a second IO operation for the same page.
3. Spid 55 issues the IO request to read P1 from disk.
4. Since Spid 55 wants to read the page, it must wait for the IO to
complete. It does this by attempting to acquire another latch (in this case a share (SH)) latch on the page. Since the latch is already held in EX, the SH request is blocked and the spid is suspended.
5. Upon completion of the IO the EX latch on the page is released.
6. The release of the EX latch grants the SH latch to spid 55.
7. Spid 55 can now read the page.
For the duration between steps 4 (the SH latch acquire) and step 5 (EX latch release) sysprocesses will indicate that spid 55 is blocked by itself with a wait type of PAGEIOLATCH_XX (where XX can be SH, UP, or EX) as an indication that it is waiting for the completion of an IO that it itself issued.
HTH.
MJ
October 10, 2008 at 10:41 am
Hi Manu - here's MS's page saying basically the same thing. http://support.microsoft.com/default.aspx/kb/906344
Nice summary.
Chad
October 10, 2008 at 7:44 pm
MANU the article was very helpful. I don't think there is any hotfix to take care of it. But then can I set up a job to notify me upon a blocking with considerably high waittime...thanks again!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 10, 2008 at 9:22 pm
I don't see a problem for a hotfix to address.
Blocking happens when one query locks a resource (e.g. a row on a table) and another separate query needs to access that row (for a select or separate update).
In this case, although it is showing as "blocking" what was described above is not happening. For one, you are not updating (or locking) a resource - you are selecting, so you could be blocking yourself, and even if you were doing an update, it is your SPID that has the lock, so I don't think you could block yourself then anyway. Manu's post describes a situation in SP4 where SQL Server reports what might better be described as a "wait" as a block. I guess technically you could say the query is "blocked by the disk read", but in that case it would be the "Disk" that was blocking, not your spid. It's confusing yes, but it doesn't sound to me like an issue.
I've also seen parallel queries show up as "blocking themselves", and that might be what you are seeing, particularly since you indicated there were multiple records of the spid blocking itself. I'll bet if you used sp_who instead, you would see that all but 1 was being "blocked" essentially those threads (although threads might not be the right term - I'd have to go look) are _waiting_ for that "unblocked" thread to finish it's work before combining all the work each CPU has done together into one result. Again though, it's not "blocked" in the traditional use since there are not any locks - it's waiting.
If you find that it is the parallelism and you just want it to go away, you can adjust the MDOP to make every query run on just one CPU. That will probably make the query a little slower, but the block/wait will go away. If it's the IO/latch issue - then you can tune your query a little and see if you can get it to run faster and that will minimize the chance that you see the blocking/waiting, but it will probably still be there and to me seems benign.
Good Luck!
Chad
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply