October 23, 2003 at 2:19 am
Goodaye,
I quote the question of the day......2003/10/23
"You are a database administrator with sysadmin permissions who is trying to troubleshoot a performance problem on your SQL Server 2000 SP 3 machine. You have tracked down a certain security ID (SPID) that is causing your performance problem to SPID 51. You suspect this user has executed a query a few moments ago that is causing your problem. What T-SQL command could be executed to determine what stored procedure sPID 51 executed last to cause your performance problem on your server."
Yes u can use DBCC INPUTBUFFER but.... with SQL2K SP3a there is a new function called fn_get_sql which gives u more information. How do I know this? Blood sweat and tears! And one less bad developer to deal with.
Here is the sample code.
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle)
Andy.
October 23, 2003 at 3:30 am
Cool! I love the way MS adds new features with barely any documentation. You should add it as an FAQ
Keith Henry
DBA/Developer/BI Manager
Keith Henry
October 23, 2003 at 3:33 am
quote:
Cool! I love the way MS adds new features with barely any documentation. You should add it as an FAQ
Didn't you make the update to BOL SP3 ???
It is documented there as 'New Information'
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 23, 2003 at 9:39 am
quote:
Didn't you make the update to BOL SP3 ???
Yeah, but I only read the bits I had to... , much lazier now I project plan and manage rather than DBA
Keith Henry
DBA/Developer/BI Manager
Keith Henry
October 24, 2003 at 8:13 am
Did you actually get this to work? I tried and I always get back a blank row from this function whereas DBCC INPUTBUFFER giver me valid info. Maybe the proble is I always get hex zeros regards of the spid I use. As in :
DECLARE @Handle binary(20)
SELECT @Handle= sql_handle FROM sysprocesses WHERE spid = 102
select @Handle
SELECT * FROM ::fn_get_sql(@Handle)
results in:
------------------------------------------
0x0000000000000000000000000000000000000000
(1 row(s) affected)
dbid objectid number encrypted text
----------------------------------------
(0 row(s) affected)
Francis
October 24, 2003 at 9:07 am
If you are getting a blank row, it is because the query is done. Execute it numerous times, on a long lasting process. You get to see the state of operation depending on locale. Inputbuffer simply shows you the current or last call regardless of whether it is awaiting command or not.
October 24, 2003 at 10:12 am
in the README file of SP3a this function IS DOCUMENTED (Item - 5.1.10).
🙂 You did read it, right?
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply