| Author |
Topic |
|
avmreddy17
Posting Yak Master
180 Posts |
 Posted - 2007-11-13 : 11:32:39
|
| Hi Gurus,I am running a simple SELECT statement on my TempDB SELECT * FROM TempDB..SysObjectsWhen I run this , it's getting blocked by some other Process which is not at all relevant to the above Qry. First it gets blocked by one Stored Proc say Proc1 ( The Proc1 is just a SELECT statement on a table which just has four rows ). So I just kill this PROC1 and again its gets blocked by some other stored Proc PROC2 which again is a simple stored Proc and it runs in not time.Any Suggestions as to where to start.Thx in AdvanceVenu |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-13 : 11:42:25
|
| Well, lot of things can get into the way of blocking your select. to name a few, front end app, other dev session, t-log back-up, temp variables...How do you run your select? From Query Analyzer? If you go to Enterprise Manger, you may find out the process ID of the blocking threads, and work form there. |
 |
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2007-11-13 : 11:46:14
|
| I am runnig the SELECT Statement SELECT Name FROM TempDB..SysObjectswith the SPID 171 and it getting blocked by SPID 699 ( running PROC).Even After killing the SPID 171 , stillI see in the master..SysProcesses it terrns rows for 171 with the waitresource.SPID---lastwaittype---------------------waitresource------ -------------------------------- --------------------------171----LCK_M_S--------------------------KEY: 2:1:1 (620069a1ce7c)SPID---lastwaittype---------------------waitresource------ -------------------------------- --------------------------699----PAGELATCH_UP---------------------2:1:763271ThxVenu |
|
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2007-11-13 : 11:54:31
|
| Even I cannot even open the enterprise Manager as it gets blocked by the same Stored Proc ( this SP is just a SELECT statement on a table with four rows in total ).I dropped this SP and still I see it as blocking other processes.Why I am not able to run a simple SELECT Starement from the Query Analyser ( SELECT * FROM TempDB..Sysobjects ) and the below SP is on a totally diff database called ConfigDB and the table just has four rows. If I run the sp seperately it runs in split second.1. There are no TLog back up running ( DB in simple recovery mode )Here is the Script of the SP ( I know I am using the * but it just has 4 rows ) whick blocks.DROP PROC usp_GetRestrictedStocksGOCREATE PROC usp_GetRestrictedStocksASBEGIN SELECT * FROM ConfigDB..RestrictedStocksENDAny thoughts |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-13 : 12:01:06
|
| I will suggest you run a profiler to track it done. There maybe some scripts out there that creates the sp on the fly, or something in that nature that is hard to catch and kill in QA and EM, but profile will give you best chance to see everything. |
|
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2007-11-13 : 12:46:55
|
| I ran the profiler with no luck.I dropped the blocking Stored Proc while I was running the SELECT * from TempDB..SysObjects from QA , but still it shows that Sp is blocking.Any Ideas. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2007-11-13 : 13:10:00
|
| sql server uses tempdb for a lot of stuff (besides your own #temp tables). It is constantly creating and dropping objects. So a SELECT from tempdb..sysobjects will likely be blocked while any of that is going on.have you tried changing the transaction isolation level for the SELECT? ie: select * from tempdb..sysobjects with (nolock)Be One with the OptimizerTG |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-13 : 13:10:57
|
| When you drop the sp, there is nothing to be called upon in the future, but the blocking sp is still alive in the session memory. Killing process ID one at a time will only wipe out one of those memory spaces. This is assuming there is no dynamic/embedded sql out there that will create the sp in question on the fly. |
|
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2007-11-13 : 13:18:46
|
| Hommer,Thanks for the reply.There are no Dynamic SQL / embedded SQL on this Server.I already tried running the SELECT Statement with ( NOLOCK ). it runs fine.But I am concerned why it's not running with out no Lock.Akso is there any way I can get all the SPID's runing a particular stored Proc.ThanksVenu |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-13 : 13:24:49
|
quote:
Originally posted by avmreddy17
Akso is there any way I can get all the SPID's runing a particular stored Proc.
This was already answered in your new thread. Either ask questions in this thread or start new ones. Do not do it in both places.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2007-11-13 : 13:29:02
|
| TG,Thanks for u're replyI know tempDB is used a lot apart from the temp tables I create, but why is it holding lock on the sysobjects table on the tempdb for so long.Kizer,Sorry for re-postingThx |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2007-11-13 : 13:36:41
|
| It totaly depends on what else is going on. Let's say someone issued a BEGIN TRAN then performed several long running things one of which involved sql server's use of tempdb. Until a COMMIT is issued, tempdb..sysobjects will be blocked because rows were written to it but not yet committed. What are you trying to accomplish? Maybe there is a better way.Be One with the OptimizerTG |
|
|
|
avmreddy17
Posting Yak Master
180 Posts |
Posted - 2007-11-13 : 13:51:50
|
| TG,All I want is to get to bottem of the SPID blocking this Sysobjects table on the TempDB Database.Thx |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-13 : 15:40:39
|
| Use 'sp_who2 active', it'll tell you. |
|
|
|
|
Blocking