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

 All Forums
 Other Forums
 ClearTrace Support Forum
 Prepared SQL

Author  Topic 

aarondba
Starting Member

2 Posts

Posted - 2014-03-19 : 16:02:02
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.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2014-03-20 : 01:02:27
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 - 2014-03-20 : 06:58:29
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

4149 Posts

Posted - 2014-03-20 : 18:06:09
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
   

- Advertisement -