June 17, 2010 at 7:27 pm
Sorry Adam, not Andrew!
I isolated the problem thread and it displays Ok with Adam's code.
<?query --
FETCH API_CURSOR000000000002BC4F
--?>
June 17, 2010 at 11:45 pm
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
June 17, 2010 at 11:47 pm
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
June 17, 2010 at 11:50 pm
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
June 18, 2010 at 10:15 am
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
June 23, 2010 at 12:54 am
June 23, 2010 at 1:43 am
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
June 23, 2010 at 7:25 am
Very very nice article Love it.
Pinal Dave
sqlauthority.com
June 23, 2010 at 8:48 am
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
July 8, 2010 at 11:13 am
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
July 8, 2010 at 11:17 am
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
July 8, 2010 at 2:12 pm
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.
July 8, 2010 at 2:17 pm
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
July 8, 2010 at 3:09 pm
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
October 5, 2010 at 11:29 am
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