April 3, 2014 at 10:45 pm
Hello,
What do you think about use WITH RECOMPILE on Web Server. Users still check different data from different tables. And from my point of view is redundant waste of memory. Because they will check next data and ex.plan on procedure will be same.
I think that little more execution time is ok and CPU will be also ok, now we have 16core and %CPUusage is about 35-50%.
Of course on Production procedures and adhoc queries I will not use this hint.
Thanks for your point of view.
April 3, 2014 at 11:37 pm
Still I will let SQL Server to decide instead of external WITH RECOMPILE.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 4, 2014 at 2:41 am
I think, as for almost all hints and options, it's a targeted solution to specific problems, not something to be put on every procedure blindly.
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
April 4, 2014 at 3:42 am
GilaMonster (4/4/2014)
I think, as for almost all hints and options, it's a targeted solution to specific problems, not something to be put on every procedure blindly.
+1
Only if you are sure that all performance problems are caused by using a suboptimal plan from cache, you could do this.
tony28 (4/3/2014)
I think that little more execution time is ok and CPU will be also ok, now we have 16core and %CPUusage is about 35-50%
In such cases when you apply a change affecting a lot of workload you should test, test and test. Maybe you are right, that your CPUs will survive compiling all SPs and queries by every execution, but usually you cannot be sure in advance. The fact that the whole recompiling could slows down your system should lead you to test intensively before you apply it to the production system.
___________________________
Do Not Optimize for Exceptions!
April 4, 2014 at 5:57 am
It's also not a question of web servers vs. something else. Query behavior is query behavior. Nothing all that special about queries coming from a web interface vs. queries coming from a desktop interface. It's all still queries.
I'm with Gail 100%. Query hints (which are really commandments to the optimizer) need to be used very sparingly and only to solve very specific problems. The instant you start thinking about putting a query hint everywhere, you're going down a bad path.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply