Finding the blocking query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Finding the blocking query

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-05-21 : 09:20:18

Good morning,

I have an home grown application that keeps slowing and down and having lots of blocking. I have looked over this website and found lots of queries and SP's that show me the spid and name of user and database. Is there something out there that shows exactly the query thats causing the blocking?

This is what I am using so far:

------------------------------------------------------
---this finds the blocking chain.
IF EXISTS
(SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
SELECT spid, status, loginame=SUBSTRING(loginame,1,12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
dbname=SUBSTRING(DB_NAME(dbid), 1, 10), cmd, waittype
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0
ELSE
SELECT 'No blocking processes found!'
----------------------------------------------------------------------------------------------
-- The following script can be run to identify current, long-running transactions. This query provides results based on --the instant is runs, and will vary each time you run it.

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

---------------------------------------------------
use yardcheck
go
exec dbo.aba_lockinfo

------------------------
use master
go
select req_spid, count(*)
from master.dbo.syslockinfo
group by req_spid
having count(*)> 10

Thanks

Laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-21 : 12:25:55

If you know the spid, then run DBCC INPUTBUFFER(spidNo). This will only get part of the query though. To get the whole thing, run a trace in SQL Profiler to capture the activity.

Tara

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-05-21 : 13:18:00

Thanks Tara, that was the missing piece. Is there any other way in case a developer needs to find the long running query? Since you need to be a sysadmin to run this.

Thanks again

Laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-21 : 13:21:45

No. Although Derrick says that he has been successful providing permissions to use SQL Profiler. I tried granting the permissions on the stored procs, but it didn't work. I also found a Q article which says that it wouldn't work anyway. In a dev environment, I give out sysadmin temporarily as needed. When I say temporarily, I mean for only a couple of hours. And that's only if I'm too busy to work with the developer on the problem.

Tara

lauramayer
Posting Yak Master

152 Posts

Posted - 2004-05-21 : 14:03:05

That's to bad, I was hoping for another script I could run. Thanks for the help as always Tara

Laura

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-21 : 14:14:39

Forgive me for I have sinned. :) Do you have one of those sniped icons where he's sniping himself????

We had this insane permission chain on development (not prod) that was granting them the access they needed. It is the only way you can do it. We're doing that also. Set up an NT group with sysadmin access called DB-TRACE(or whatever). Then you can just move people in and out without having to add people in through the SQL Server Security interface.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources