Any way to retrieve execution plan after PreparedStatement runs?

Posted on 2007-10-02
Last Modified: 2008-01-09
Dear Experts,

My Java app issues a Java PreparedStatement against an MS SQL 2000 db.  I suspect it's actually causing a table scan at a customer site.  Is there any way I can retrieve the execution plan after the PreparedStatement exectutes?  (The statement would be issued 10,000 times each time my app runs...)

I know it's not possible through Java.  Is it possible through SQL 2000?  'm a bit of a dummie about SQL 2000.  Is there a setting I can use such that the execution plan(s) would be stored in a SQL log?

Question by:BrianMc1958
    LVL 5

    Accepted Solution

    SQL Server Profiler has an option to track statements and also to show the execution plan employed. Start Profiler, select "New Trace..." (or the like), connect to your server machine and choose to monitor "Performance/Show Plan *" in the "Events" tab.
    LVL 27

    Assisted Solution

    Short answer: No.

    Long answer

    The execution plan is stored in the SQL Server memory space, but there is no way to retrieve it after the query has run.

    Maybe you could run a SQL Profiler trace against the customer's table to see why the query plan is not using the index.  
    There are several reasons why the Query Optimizer may not choose to use the index.
    The statistics may be old, and give an inaccurate view of the table. The table may not have a primary key. The table may have 100 rows or less.

    Has the customer updated the indexes on the table? Has the customer refreshed the statistics?


    Author Comment

    The trace is showing me what I need.  Thanks very much, folks.  This is extremely helpful.

    LVL 27

    Expert Comment


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now