Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

What, When, Where, How, Who2 Expand / Collapse
Author
Message
Posted Thursday, June 17, 2010 7:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 24, 2010 9:40 PM
Points: 6, Visits: 20
Sorry Adam, not Andrew!
I isolated the problem thread and it displays Ok with Adam's code.
<?query --
FETCH API_CURSOR000000000002BC4F
--?>
Post #939319
Posted Thursday, June 17, 2010 11:45 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 18,083, Visits: 16,117
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
Post #939369
Posted Thursday, June 17, 2010 11:47 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 18,083, Visits: 16,117
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
Post #939370
Posted Thursday, June 17, 2010 11:49 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 18,083, Visits: 16,117
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
Post #939372
Posted Friday, June 18, 2010 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 7:10 PM
Points: 192, 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



Post #939725
Posted Wednesday, June 23, 2010 12:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:44 AM
Points: 1,260, Visits: 3,424
Jason,

Thanks for the article, good job!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #941532
Posted Wednesday, June 23, 2010 1:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 18,083, Visits: 16,117
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
Post #941563
Posted Wednesday, June 23, 2010 7:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 4:31 AM
Points: 60, Visits: 274
Very very nice article Love it.


Kindest Regards,

Pinal Dave
sqlauthority.com
Post #941728
Posted Wednesday, June 23, 2010 8:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 18,083, Visits: 16,117
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
Post #941829
Posted Thursday, July 8, 2010 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, January 5, 2014 1:05 PM
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
Post #949463
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse