MJ-1115929 (8/1/2015)
@Grant FritcheyThanks Grant and hope you are doing well, we had chats several times some time back.
I have one query if we want to force the sql server to use the same plan will it give some performance ?
You can force a plan, but it won't reduce the compile time. In fact, it will add to it, so you're better off not using that approach especially in this situation.
what will be options in front of me. The procedure runs little faster after intial compilation , how can we avoid recompilation on this procedure or any method we can achieve the same.
You can add a query hint KEEPFIXEDPLAN. That will reduce the amount of recompiles you get, but, that can introduce other issues, possibly. If your statistics are changing much, you may need to generate different plans over time.
Anyway we are trying to break down the proc, but we need an immediate solution to satisfy some non technical management people over here, so atleast a temporary solution will suffice for now will get some time window to break down the proc and investigate on the same.
Thanks
MJ
Unfortunately, there's likely not an immediate solution or instant fix. Using the KEEPFIXEDPLAN hint will help reduce the number of recompiles, but it won't many recompiles that still occur any faster. Compile time goes up with complexity of the query and the amount of work that the optimizer has to do. The simpler the query, the faster the compile time. The more the query is tuned and has good indexes and statistics and isn't using nested views or nested user-defined functions, the faster the compile time.
"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