March 22, 2011 at 4:47 pm
Is there any way to store a copy of a query plan and execute it later programatically, or does the database engine HAVE TO handle it itselff?
March 22, 2011 at 11:47 pm
A query plan is nothing more than a set of instructions for the query execution engine.
You can store one trivially, run the query with 'include actual execution plan', then you can save that query out as a .sqlplan file. Or you can query sys.dm_exec_cached_plans and sys.dm_exec_query_plan and get the plans out of the plan cache.
What's the reason behind this question?
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
March 23, 2011 at 5:24 am
I've never even heard of anyone executing a query plan before. Just a query.
Are you trying to execute a query with a specific query execution plan instead of letting the engine come up with a new one each time?
March 23, 2011 at 5:53 am
Well, you can try plan forcing. that allows you to take a valid plan and make the engine use it when you run the query. It's not executing the plan, but it is choosing that plan for executoin.
BUT
You need to be sure that you're making a good choice on that plan. The optimizer is, by and large, pretty smart. I wouldn't advocate taking control away from it unless you were sure that's the direction you want to go in.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2011 at 9:40 am
My situtation is I have literally 100k queries that need to execute every night. Currently the SQL is in a field in table. A CLR iterates the table executing each statment.
I want to know if there is a way to store the plan, instead of the SQL to save the optimization step
.sqlplan looks to be a managmenet studio feature.
sys.dm_exec_cached_plans needs a planid from the cache, but the cache is only storing about 5000 of the queries. I don't see a way to ensure, that a plan is not dropped from the cache because its only used once per day?
suggestions?
March 23, 2011 at 9:44 am
No, you can't really avoid the compile process. If you try plan forcing you're at least guaranteed which plan you get.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2011 at 9:46 am
KermitTheRock (3/23/2011)
My situtation is I have literally 100k queries that need to execute every night. Currently the SQL is in a field in table. A CLR iterates the table executing each statment.I want to know if there is a way to store the plan, instead of the SQL to save the optimization step
Is the optimisation causing a problem? Do you have measurable CPU increase caused by the compilation process and is if having a detrimental effect on the overall processing?
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
March 23, 2011 at 9:47 am
Grant Fritchey (3/23/2011)
No, you can't really avoid the compile process. If you try plan forcing you're at least guaranteed which plan you get.
However that can increase CPU usage as the optimiser has to keep going until it finds the plan specified. it can't just stop when it finds a good enough plan.
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
March 23, 2011 at 10:02 am
Yeah, I know it sounds crazy that I want to "avoid" optimization. But if the data and tables and query have not changed from one day to the next, running SQL through optimizer only costs processing power*100k rows.
If the optimizer take only a tenth of a second, that's almost 3 hours of CPU time a day!
March 23, 2011 at 10:05 am
Ok, then how about reducing from 100K to 1K queries?
Why the heck do you have so many transformations to go through?
March 23, 2011 at 10:08 am
KermitTheRock (3/23/2011)
Yeah, I know it sounds crazy that I want to "avoid" optimization. But if the data and tables and query have not changed from one day to the next, running SQL through optimizer only costs processing power*100k rows.If the optimizer take only a tenth of a second, that's almost 3 hours of CPU time a day!
I'm under the impression the optimizer might take 1/10 of a second selecting a query plan per query, regardless of the number of rows. it might use different hashing criteria based on the number of rows, but isn't it still 1query = a plan, based on the WHERE criteria and if a plan already exists or not?
Lowell
March 23, 2011 at 10:26 am
Depending on the query, the optimizer is blazingly fast. But, it's still not zero, I get it. Usually when you hit a limit based on how a tool behaves you have to ask two questions, am I using the tool correctly and am I using the right tool. In this situation, I'm not sure that the answer to the first one is yes, but about the second?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2011 at 10:54 am
KermitTheRock (3/23/2011)
Yeah, I know it sounds crazy that I want to "avoid" optimization. But if the data and tables and query have not changed from one day to the next, running SQL through optimizer only costs processing power*100k rows.
This confuses me. If the data has not changed (if there are no updates to the table or new inserts), then why do you have to run these queries every night?
And why can't you just cache the data for reuse instead of re-running the queries?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply