September 23, 2012 at 10:37 am
PiMané (9/21/2012)
So it depends mostly on the data in the tables.Being a "generic" software spread across the country we can have databases with customers mainly from one state (where a cluster index scan would be better) and also databases witch customer from many different states (where a index seek would be better)..
But since the query plan will be recompiled the 1st time on each database it will probably be optimized for its case.
We can have a database where the plan says to use a index scan and another database with the same structure but different data with an index seek...
There's not much that can be done in these situations since our SW is all over the country (20.000 customers) and each case is specific...
The question is how often a particular plan choice (the scan versus seek you mention) is seriously sub-optimal. If your monitoring or customer reports suggest that parameter-sniffing causes a plan to be cached that causes a real problem, you could look at one of the solutions Gail mentions.
OPTIMIZE FOR UNKNOWN is one option, applicable if a plan optimized for the average case is going to perform well enough for all cases that matter to you.
Another option Gail mentions is to add OPTION (RECOMPILE) to the statement(s) that is particularly sensitive to parameter values. This compiles a fresh plan for the statement each time it is executed based on the specific parameter values at that point in time. Depending on the complexity of the statement, and how often it is executed, you may find that OPTION (RECOMPILE) costs you more in overhead (compilation and optimization is not for free!) than you save.
There are other options, and I think Gail covers them all. Perhaps it is just a case of grabbing another coffee and re-reading her article carefully?
Joe Celko is correct that it is generally a good idea to avoid hints. This is particularly true if you are not absolutely clear on the risks and benefits. On the other hand, there are circumstances where even the best database design with the best statistical information in the world can still experience poor plan selection based on sniffed parameter values. You should also be aware that cached plans can be recompiled for all sorts of reasons, and the plan cached will be based on the parameter values seen at that particular unpredictable moment.
Ultimately, it is a judgement call, based on information only you can know. If you are still not clear on the trade-off you would be making, please ask further questions.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 24, 2012 at 5:27 am
PiMané (9/21/2012)
So it depends mostly on the data in the tables.Being a "generic" software spread across the country we can have databases with customers mainly from one state (where a cluster index scan would be better) and also databases witch customer from many different states (where a index seek would be better)..
But since the query plan will be recompiled the 1st time on each database it will probably be optimized for its case.
We can have a database where the plan says to use a index scan and another database with the same structure but different data with an index seek...
There's not much that can be done in these situations since our SW is all over the country (20.000 customers) and each case is specific...
Thanks,
Pedro
Not so much the data in the tables, but the statistics that describe that data. Those are what drives the optimizer, not the data itself.
"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
September 24, 2012 at 5:28 am
Roland Alexander STL (9/21/2012)
Which is the point of using local variables, since it will force a new plan each time the query is run. Of course, this is also the downside of using local variables.
No, local variables won't force new plans each time a query is run.
"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
September 24, 2012 at 5:31 am
CELKO (9/21/2012)
If you ask a VERY general question you will get a VERY general answer. The best general answer is not to use hints at all and trust the optimizer. The hint is inserted to take care of one problem that happens one time and never removed. Everyone is too scared to take a chance that it might be important.Like uncommented patches in assembly language programs 🙂 So, if you use it, comment it in great detail for the poor bastard who has to maintain the system after you leave.
+INFINITY
Although, dealing with bad parameter sniffing is one of the few places where I willingly apply hints. Despite that, I'm right there with you, avoid the hints, trust the optimizer.
"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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply