July 2, 2007 at 11:11 am
I am trying to debug some performance issue for the live site. One of the procedure has started timing out and end users have been getting errors.
So, I started with reindexing all the tables and made sure that the scan density is about 100% on all the tables used in the procedure.
Next thing I wanted to check was locks and blocks during the execution.
This is what I get when I execute the sp_lock on the spid : My question is: what does this data mean? What should I look for? I can review all the locking theory but I need to get it done quickly and no time to go through reading right now.
Any help/suggestion is appreciated.
Thanks
401 8 0 0 DB S GRANT
401 8 1737161334 1 PAG 1:6904050 IS GRANT
401 8 1125683158 1 PAG 1:1422599 IS GRANT
401 8 220736039 0 TAB IS GRANT
401 8 1737161334 0 TAB IS GRANT
401 8 1737161334 1 KEY (3c0122ee027b) S GRANT
401 8 1547308722 1 PAG 1:2197945 IS GRANT
401 8 599217435 0 TAB IS GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 2 876260948 0 TAB X GRANT
401 8 1929162018 0 TAB IS GRANT
401 8 503829007 0 TAB IS GRANT
401 8 1355308038 0 TAB IS GRANT
401 8 1125683158 0 TAB IS GRANT
401 8 1547308722 0 TAB IS GRANT
401 8 1547308722 1 KEY (0e02436a7f70) S GRANT
401 8 1723413459 0 TAB IS GRANT
July 2, 2007 at 11:48 am
All these blocking records belong t o spid # 401. To find out who is this run select * from sysprocesses.
Next column shows database id. #2 is tempdb, to find the other use select * from master..sysdatabases where dbid=8.
The next is object id. To find the name query sys.objects in a given database context.
The next is index id. 0 = heap table. 1 = clustered index.
Next - type of block. TAB = table, KEY = key.
Mode: X-exclusive, IS - intend to share.
Resorce: file id + page id.
GRANt - the fact that black has been granted.
July 2, 2007 at 12:10 pm
Forgot to mention, to display actual page data run this:
dbcc traceon (3604)
dbcc page(8,1,6904050,1) -- this example is for your 2nd line
dbcc traceoff (3604)
July 2, 2007 at 2:39 pm
IS GRANT can be safely disregarded.
Some times large queries generate those tempdb tables that you are expericencing. If you run dbcc inputbuffer(SPID) it may show the last command or sp excecuted by the spid and therefore give you an idea on what is excecuting.
You should also monitor "blocked by" column from sysprocess to see what is actually going on.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply