What, When, Where, How, Who2

Question

Post reply

What, When, Where, How, Who2

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

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

John Cuthbertson SSC Enthusiast Points: 104 More actions · Answer 1

John Cuthbertson

SSC Enthusiast

Points: 104

June 17, 2010 at 7:27 pm

#1181891

Sorry Adam, not Andrew!

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

<?query --

FETCH API_CURSOR000000000002BC4F

--?>

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

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

David Lu SSC Eights! Points: 882 More actions · Answer 5

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

Dugi SSCoach Points: 17998 More actions · Answer 6

Dugi

SSCoach

Points: 17998

June 23, 2010 at 12:54 am

#1183742

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]

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

SQLRNNR

SSC Guru

Points: 281324

June 23, 2010 at 1:43 am

#1183766

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

Pinal Dave Old Hand Points: 306 More actions · Answer 8

Pinal Dave

Old Hand

Points: 306

June 23, 2010 at 7:25 am

#1183908

Very very nice article Love it.

Kindest Regards,

Pinal Dave
sqlauthority.com

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

SQLRNNR

SSC Guru

Points: 281324

June 23, 2010 at 8:48 am

#1183998

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

larry.meklemburg SSC Journeyman Points: 97 More actions · Answer 10

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 11

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

larry.meklemburg SSC Journeyman Points: 97 More actions · Answer 12

larry.meklemburg

SSC Journeyman

Points: 97

July 8, 2010 at 2:12 pm

#1190487

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.

Adam Machanic SSCoach Points: 15370 More actions · Answer 13

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 14

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

Irish Hall of Fame Points: 3797 More actions · Answer 15

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