January 14, 2009 at 3:23 pm
I'm new to troubleshooting performance issues with SQL 2005 and I'm having a problem with a blocking process that appears for three minutes on a blocking process report, at random times of the day, and then goes away. Earlier today, while the blocking was happening, I issued a SELECT * on sys.dm_exec_sessions and sys.dm_exec_requests. Among other things, the requests query returned the session_id of the process being blocked as well as the value of the blocking_session_id and a wait_type of LCK_M_S, but did not show a row for the session id referenced in the blocking_session_id column.
Here's one of the entries from the Blocked Process Report:
<process id="processaaf978" taskpriority="0" logused="368" waitresource="KEY: 5:331853928005632 (9400ace56065)" waittime="5750" ownerId="692131782" transactionname="INSERT"
lasttranstarted="2009-01-14T14:09:25.633" XDES="0x6408e68" lockMode="S" schedulerid="3" kpid="5616" status="suspended" spid="119" sbid="0" ecid="0" priority="0" transcount="2"
lastbatchstarted="2009-01-14T14:09:25.633" lastbatchcompleted="2009-01-14T14:09:25.587" clientapp="Internet Information Services" hostname="STARRMA2" hostpid="5432" loginname="DMDUser"
isolationlevel="read committed (2)" xactid="692131782" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
(@P1 char(5),@P2 char(4),@P3 varchar(20),@P4 varchar(40),@P5 varchar(40),@P6 datetime)INSERT INTO translog (file_id,actor_id,field,oldval,newval,changdate) VALUES (@P1,@P2,@P3,@P4,@P5,@P6)
<process status="sleeping" spid="118" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2009-01-14T14:09:25.633" lastbatchcompleted="2009-01-14T14:09:25.633" clientapp="Internet
Information Services" hostname="STARRMA2" hostpid="5432" loginname="DMDUser" isolationlevel="read committed (2)" xactid="692131707" currentdb="5" lockTimeout="4294967295"
clientoption1="671088672" clientoption2="128058">
(@P1 char(3),@P2 decimal(3,0),@P3 decimal(3,0),@P4 bit,@P5 bit,@P6 varchar(30),@P7 decimal(11,2),@P8 char(3),@P9 datetime,@P10 datetime,@P11 char(2),@P12 char(3),@P13 varchar(5),@P14 char(1),@P15
char(5))UPDATE und SET
counties_id=@P1,borr_fico=@P2,cborr_fico=@P3,foreclosure=@P4,bankruptcy=@P5,processor=@P6,cashout_amt=@P7,processor_id=@P8,bankrupt_date=@P9,foreclose_date=@P10,statecode=@P11,countycode=@P12,msacode=@P13,pal_code=@P14
WHERE file_id=@P15
Can someone help to explain what might be going on with the two processes?
January 14, 2009 at 5:51 pm
It sounds like you have some slow performing queries. Can you post the execution plans?
Is there some sort of trigger or something becuase, if I'm reading the queries correctly, one is inserting into translog and the other is updating the und table. Does the und table have a trigger that would need to also insert into the translog table?
Just guesses without more information.
"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
January 16, 2009 at 11:55 am
if you go to my web site http://www.grumpyolddba.co.uk/monitoring/Creating%20Custom%20Reports%20for%20the%20DBA.htm
there's an rdl that will show blocking sessions, there's a couple of other rdls that may help by the sounds of things!! YOu just need sp2 on your client tools/ssms these rdls work as custom reports - you extract running queries here too.
With blocking it's fine knowing - what you need to know is what is blocking what and then work out why. The lock you quote is a shared lock and generally doesn't cause problems - you're more looking for exclusive locks.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply