Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Any way to retrieve execution plan after PreparedStatement runs?

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?

  • 2
2 Solutions
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.
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?

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


Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now