SQL Server Performance Forum – Threads Archive

Get Query Execution Plan in code

I have an application with a search page where the user can enter one or more search keys. Based on the keys entered, the search may return anywhere between 1 and millions of rows. We limit the result returned to 1000 rows but when performing a particularly bad search, it still takes some time to sort and obtain the top 1000 records. The search keys are dynamic (different customers see different keys depending on who they are) and there are more than 20 possible keys so we don’t really have a way to tell what the "good" queries and what the "bad" queries are up front. I’d like to be able to warn the user that they are about to execute a query that may run for a while. Is there a way to obtain the estimated query execution plan in code and then interpret the results before actually running the query? What I’m think of is doing some analysis on the plan to determine approximately how many rows will be read and what indexes will be used to give the user some warning before they submit the query. Any response will be appreciated. Thanks, Chris

I think I may have answered my own question. If I execute "SET SHOWPLAN_ALL ON" and then run the query, the resultset returned contains all of the information from the graphical query execution plan that is produced by query analyzer. I figure that by looking at the estimated cost, IO, and rows numbers that I can make a good guess at the long running queries. Going farther, I can analyze the operations to focus on bookmark lookups and scans. When I’m done, I just set showplan off again and run the query. Has anyone ever done any analysis like this in code? Thanks, Chris

All the time, because in graphics mode you can’t print all.
So, the only way is what you did or SET SHOWPLAN_TEXT.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Sorry, but to me this sounds like you’re reinventing the wheel.
Your user fires a dynamic search query, you capture the output, analyze it and when it’s "good enough" you finally run the query? It might be worth having a look at the FAST n query hint in the explanations for SELECT in BOL.
Also, do you know this one:http://www.sommarskog.se/dyn-search.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

I guess I didn’t make my point clearly. I don’t need to make the queries faster and I already limit the query results to the top 1000 rows. Because of the nature of my system, I will always have some queries that will run long. What I am thinking of doing is analyzing the showplan data in my application and then warning the user with a popup message telling them that the query criteria that they have chosen may result in a long running query. I was thinking of doing this by analyzing the number of rows to be read, I/O costs, etc. and I was just wondering if anyone had done anything like that.

look at the output of SET SHOWPLAN_ALL ON get the EstimateRows EstimateIO & TotalSubtreeCost columns pick some queries that you think represent the most expsensive you want to allow, and set limits on these values see my articles on how to interpret them, the powerpoint are more recent than the articles

]]>