Post reply

Question of the day 2 cent

  • October 23, 2003 at 2:19 am

    #160891

    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

    #479239

    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


    According to everyone I know I "do something with computers?" for a living, so there you go.

  • October 23, 2003 at 3:33 am

    #479241

    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

    http://www.insidesql.de

    --
    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

    #479326

    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


    According to everyone I know I "do something with computers?" for a living, so there you go.

  • October 24, 2003 at 8:13 am

    #479480

    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

    #479499

    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

    #479515

    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