capture SQL statements before execution

Question

capture SQL statements before execution

v.reddy

SSC Rookie

Points: 43
More actions
November 26, 2008 at 11:40 am

#383473

Hi Experts,
I would like to capture sql statements before execution, can we do that?
I was tried with SMO object, but it captured only for that instance, i wanted to capture all the sql statement which comming from other applications.
any help is appriciated.
thanks
kv

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

You can use profiler and catch the statement/sp starting events. Not sure if it's the right solution though.

What's the reason behind the question? What are you trying to do with the statements and why do you need to capture before execution?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

v.reddy SSC Rookie Points: 43 More actions · Answer 2

HI,

Thanks for the response,

i was tried with Trace, but its shows only after exection, not before execution of sql statements.

i would like to capture statement before exuction and change something (optimize), and pass the statement to execution.

Thanks

kv

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Trace does show before execution. That's what the StmtStarting and SPStarting events are. The StmtCompleted and SPCompleted are the events that fire after execution. It's not going to let you change things though.

What (specifically) are you trying to do to optimise and why can't you change the SQL statements wherever they are?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

v.reddy SSC Rookie Points: 43 More actions · Answer 4

HI GilaMonster

Thank you so much for the response.

Are you sure Trace will show satements before execution?

i belive trace will show only after execution.

let me work on this and will post here.

thanks

kv

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

v.reddy (11/26/2008)

Are you sure Trace will show satements before execution?

You're welcome to check books online. All of the events are there along with their descriptions.

i belive trace will show only after execution.

The StmtStarted and SPStarted are logged when the statements start (hence their name), ie as execution starts. The StmtComplete and SPCompleted events are logged when the statements complete (hence their name).

You're not going to be able to catch and modify a statement from a trace though. It's purely a capture of events as they occur.

What, exactly, are you trying to do with the captured queries?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

v.reddy SSC Rookie Points: 43 More actions · Answer 6

Hi Gail Shaw

Thanks for the confirmation,

Here is my task.

I have one application, that accessiong SQL database, some reports are giving slow performance, so i wanted to catch those queries and improve some optimization then send to the execution.

i cannot change in the application, becasue i dont' have source code.

is there any way to find source code from com component.

please advise.

thanks

kv

Sakthivel Chidambaram SSCertifiable Points: 5525 More actions · Answer 7

Hi,

From your post's I can understand that Reporting application was fine earlier and now you are getting into performance problems. So my suggesstion is, trace the T-SQL statements from the application through profiler and find the table's accessed... Re-Index or Create Indexes... Check for the resource levels whether you can add more RAM... You can try this instead of thinking fully out-of-box... 🙂

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

v.reddy (11/26/2008)
HI,
Thanks for the response,
i was tried with Trace, but its shows only after exection, not before execution of sql statements.
i would like to capture statement before exuction and change something (optimize), and pass the statement to execution.
Thanks
kv

The only way to do that would be with a whole bunch of INSTEAD OF triggers. You'd have to "catch" it before it happens, on the tables you are updating, and possibly change what is being done. This c an easily get messy and be worse than before, so in most of thest cases "less is more" (don't do a lot this way or you will drag your house down on top of yourself).

That being said - Gail is onto something: if it's so easy to catch and "optimize" a statement automatically, why not fix the source system to send in the right code to begin with. If it's not that straightforward of a statement to optimize, do you really trust yourself to write something automated to *automatically* change the incoming statement to something more optimal (with no risk of screwing up the semantics/original intent of the query?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Actually, Plan Guides might be able to do this. Unfortunately, I missed that session at PASS.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 10

rbarryyoung (11/27/2008)
Actually, Plan Guides might be able to do this. Unfortunately, I missed that session at PASS.

Only if a hint will help the query, and only if its one of the hints that's in the OPTION clause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 11

v.reddy (11/27/2008)
I have one application, that accessiong SQL database, some reports are giving slow performance, so i wanted to catch those queries and improve some optimization then send to the execution.

That's not a very practical option. There's no way to intercept and change SQL queries (except for modifications, where you can use the instead of trigger).

Is there no one that has the source for the app?

Com components can be decompiled, but it's very much a non-trivial operation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass