Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
4149 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
4149 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  
 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.09 seconds. Powered By: Snitz Forums 2000