SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What, When, Where, How, Who2


What, When, Where, How, Who2

Author
Message
John Cuthbertson
John Cuthbertson
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 20
Sorry Adam, not Andrew!
I isolated the problem thread and it displays Ok with Adam's code.
<?query --
FETCH API_CURSOR000000000002BC4F
--?>
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33110 Visits: 18560
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

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33110 Visits: 18560
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

SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33110 Visits: 18560
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

David Lu
David Lu
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 135
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
Dugi
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2238 Visits: 3511
Jason,

Thanks for the article, good job!
;-)

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33110 Visits: 18560
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

Pinal Dave
Pinal Dave
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 275
Very very nice article Love it.


Kindest Regards,

Pinal Dave
sqlauthority.com
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33110 Visits: 18560
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

larry.meklemburg
larry.meklemburg
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 15
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search