SQL Server Performance

Capture SQL statements before execution

Discussion in 'T-SQL Performance Tuning for Developers' started by kvreddy, Nov 25, 2008.

  1. kvreddy New Member

    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.
  2. madhuottapalam New Member

    You may want to try Server Side Trace or SQL Profiler Tool.
  3. kvreddy New Member

    Hi Madhu,
    Thanks for the reply,
    I tried with Trace, i am able to get queries, but those are after execution. not before execution. i would like to capture before execution.
  4. Adriaan New Member

    On the trace properties, under "Events", select from the TSQL category "Prepare SQL" and "SQL:StmtStarting". You might look at the other categories as well.
    Do you want to monitor the statements being executed?
    Or do you want to block the statements, so they don't execute?
  5. satya Moderator

    Can you explain what is the reason behind in capturing such an event that might contribute performance issues that are ongoing (if any). Also as suggested by Madhu running this on server side is better than running from the PROFILER on the othe rmachine.
  6. kvreddy New Member

    Hi Satya,
    i would like to capture statement before exuction and (optimize), and pass the new statement to execution.
    suggest me please if you have any idea.
  7. FrankKalis Moderator

    [quote user="kvreddy"]
    Hi Satya,
    i would like to capture statement before exuction and (optimize), and pass the new statement to execution.
    suggest me please if you have any idea.
    Can you please explain this a little bit more?
    Who is sending those statements you think are worth (or need) optimization?
    From how I understand you, this can not be inside SQL Serve, but rather must be some process hooking into the communication between the client and the server, capturing the commands, rewriting them, and then send them to the server. Not sure if this can be done or if I would do this at all...
  8. Adriaan New Member

    I'm not familiar with the inner workings of the ODBC libraries, but I've done a bit of troubleshooting over the years, where problems would get caused by a client application using the MS Jet engine, where the query statement issued by the client ended up in the trace with some changes to the statement. For instance,
    WHERE column <> ''
    ... would get changed to ...
    WHERE NOT (column = '')
    The query over ODBC was causing blocking on the tables involved, which disappeared when we changed the query to the "pass-through" type. The trace would now show the original query statement, and the blocking no longer occurred.
  9. satya Moderator

    Agree with Frank on what you are explaining is incomplete, why you want to control such type of execution when you have a chance to optimize your queries with supportive indexes or so, in terms of keeping up the performance.
  10. kvreddy New Member

    Hi Satya
    here is my task.
    i don't have source code of the application which accessing sql database and some reports giving very slow performance, so that before execution, just i want to capture and make some optimization and send to the execution. As you suggested i can create more indexes, but still i want to modify query also.
    i am not sure whether it is possible, just i am trying, as so far i did'nt find the way.
  11. satya Moderator

    Do you have the capability to stop the code executed by the application and pass-thru your own code to SQL Server?
    I hope not, if so either you can try to optimize these queries that are capture using PROFILER (server side trace) and check the execution plan to see where it is taking longer time. It is a painful job that you have to go through each and every aspect of these database queries, but you may also have the added advantage to get more recommendations from Database Tuning Advisor by submitting the same trace that has been collected earlier.
    Tutorial: Database Engine Tuning Advisor
    Database Engine Tuning Advisor Overview
    Database Engine Tuning Advisor (DTA) in SQL Server 2005

Share This Page