SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 ClearTrace Support Forum
 Prepared SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aarondba
Starting Member

2 Posts

Posted - 03/19/2014 :  16:02:02  Show Profile  Reply with Quote
Hello,
I'm new to ClearTrace and have a question...

We use a lot of inline SQL code where I work, and I'm using ClearTrace to try to find poorly performing queries. The top consumer of CPU is sp_execute. No surprise there. I see a lot of SQL statements beginning with "{PREPARED}" which relates to this somehow I'm guessing, but doesn't seem to be an exact one-to-one match.

Is ClearTrace able to give me individual performance stats for all of the SQL statements that are run by sp_execute?

Will looking at the Statement level instead of the Batch level fill in the holes?

Thanks for your help.

Edited by - aarondba on 03/20/2014 06:50:56

graz
Chief SQLTeam Crack Dealer

USA
4138 Posts

Posted - 03/20/2014 :  01:02:27  Show Profile  Visit graz's Homepage  Reply with Quote
Looking at statements will help. However it should handle sp_execute properly too.

The {prepared} is because something used sp_prepare or sp_prepexec. That is usually parameterized SQL from ODBC.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

aarondba
Starting Member

2 Posts

Posted - 03/20/2014 :  06:58:29  Show Profile  Reply with Quote
quote:
Originally posted by graz

Looking at statements will help. However it should handle sp_execute properly too.

The {prepared} is because something used sp_prepare or sp_prepexec. That is usually parameterized SQL from ODBC.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.



If I understand correctly then in the ClearTrace analysis sp_execute has nothing to do with the SQL statements labeled {prepare}?

P.S. Sorry for calling it ClearCase in my first message. Too much IBM in my past I guess. :-)
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4138 Posts

Posted - 03/20/2014 :  18:06:09  Show Profile  Visit graz's Homepage  Reply with Quote
I figured out what you were referring to :)

The answer is ... sort of. If you "sp_execute 'select * from table'" that won't get tagged as a {prepared}. If you

sp_prepare "select * from table" as statement #1.
sp_execute #1
sp_unprepare #1

that will probably get tagged with {prepare}. I'd have to wade through the code to be sure but I think that's what happens.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000