What, When, Where, How, Who2

  • Sorry Adam, not Andrew!

    I isolated the problem thread and it displays Ok with Adam's code.

    <?query --

    FETCH API_CURSOR000000000002BC4F

    --?>

  • John Cuthbertson (6/17/2010)


    Sorry Adam, not Andrew!

    I isolated the problem thread and it displays Ok with Adam's code.

    <?query --

    FETCH API_CURSOR000000000002BC4F

    --?>

    Thanks for the feed back. I will have to look at how he handles it. It could be useful.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • RenΓ© Scharfe (6/15/2010)


    Thanks, nice work. Just picking nits:

    The final bit about this procedure involves the input parameters. I chose to use TinyInts for these fields to permit a value of 0, 1, or NULL. I catch this in the where clause using case statements.

    That's a case for the data type bit (http://msdn.microsoft.com/en-us/library/ms177603(SQL.90).aspx). You can then use ISNULL or COALESCE when checking their values, that's shorter.

    Also, calling the first parameter @ShowSystemProcesses might be more intuitive, as in that case both NULL and 0 would have the same meaning.

    Thanks for the feedback. Good ideas.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • David Lu (6/17/2010)


    Hi Jason:

    It works very well for me. It is in prod now, after running on QA.

    It almost covers everything I need to know, just one more:

    Is it possible to get the called proc/func parameter value in the QueryText field?:-)

    Thanks a lot

    David

    I haven't tried that. I would typically use a trace to find that. Sounds like something to try.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • It sounds 'simple' to add the proc parameter, but it will be pretty hard to combine the trace with the text, since I could not find it from any dm views which might hold the calling proc parameter values.

    Probably just an item of wish list. You have covered enough info.

    Could you write something more about the TaskStatus, WaitType, CPUTime, etc; and their internal relationships and impact with performance?

    thanks

    -D

  • Jason,

    Thanks for the article, good job!

    πŸ˜‰

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Dugi (6/23/2010)


    Jason,

    Thanks for the article, good job!

    πŸ˜‰

    Thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Very very nice article Love it.


    Kindest Regards,

    Pinal Dave
    sqlauthority.com

  • Pinal Dave (6/23/2010)


    Very very nice article Love it.

    Thanks, I appreciate that.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I too get the error, but not every time that I run it. If I catch the culprit, I will let you know.

    I am using 9.00.4262.00 (X64).

    The error is;

    Msg 6841, Level 16, State 1, Line 24

    FOR XML could not serialize the data for node 'processing-instruction(definition)' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

    Great article and very useful.

    Thanks,

    Larry

  • larry.meklemburg (7/8/2010)


    I too get the error, but not every time that I run it. If I catch the culprit, I will let you know.

    I am using 9.00.4262.00 (X64).

    The error is;

    Msg 6841, Level 16, State 1, Line 24

    FOR XML could not serialize the data for node 'processing-instruction(definition)' because it contains a character (0x0000) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.

    Great article and very useful.

    Thanks,

    Larry

    Thanks for the note. Do you have anything like what John demonstrated? I would recommend trying Adam's script when this occurs. It should work and illustrate where the problem is. I am planning on comparing the two to determine how to avoid this error.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I believe it is exactly what occurs in John's case. Thanks for the idea. I will try running Adam's whoisactive script when I next get the error.

    As a note, I just ran your script and the error did not occur. I will keep you informed and thanks.

  • larry.meklemburg (7/8/2010)


    I believe it is exactly what occurs in John's case. Thanks for the idea. I will try running Adam's whoisactive script when I next get the error.

    As a note, I just ran your script and the error did not occur. I will keep you informed and thanks.

    Not trying to overshadow the work that Jason has done, but why not just stick with Who is Active? It doesn't have the error and returns all of the same data plus a lot more πŸ™‚

    --
    Adam Machanic
    whoisactive

  • Adam Machanic (7/8/2010)


    larry.meklemburg (7/8/2010)


    I believe it is exactly what occurs in John's case. Thanks for the idea. I will try running Adam's whoisactive script when I next get the error.

    As a note, I just ran your script and the error did not occur. I will keep you informed and thanks.

    Not trying to overshadow the work that Jason has done, but why not just stick with Who is Active? It doesn't have the error and returns all of the same data plus a lot more πŸ™‚

    Not a bad choice. Mine is by no means meant as a competitor to whoisactive.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Jason,

    I have used this to great effect! However, rather than use it as a stored proc, I just run the query to get the information.

    I'm sure you're asking why. Mainly because I support many databases that I am not the direct DBA for. If I am troubleshooting, this is one of the tools in my toolbox.

    Recently I have been getting errors and it took me a while to figure out just what the heck I did. Basically, if you run this script against any database other than Master, it fails with all manner of nifty errors :pinch:

    I just added "USE Master" at the top so that I don't make that mistake again.

    In anycase, thanks a ton for this handy tool!!

    Regards, Irish 

Viewing 15 posts - 31 through 45 (of 51 total)

You must be logged in to reply to this topic. Login to reply