July 21, 2016 at 6:29 pm
Hi,
How can we know if there is a parameter sniffing in the system, and if possible to clean up a specific execution plan?
Regards,
July 21, 2016 at 8:17 pm
1) Parameter sniffing happens by design for parameterized queries of various types as well as for stored procedures. It is often a very beneficial thing, sometimes a disastrously unfortuante thing.
2) There are many ways to remove a plan from cache. The most direct is
https://msdn.microsoft.com/en-us/library/ms174283.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2016 at 10:21 pm
If it's for a single stored procedure, wouldn't you just use WITH OPTION RECOMPILE?
July 22, 2016 at 1:58 am
pietlinden (7/21/2016)
If it's for a single stored procedure, wouldn't you just use WITH OPTION RECOMPILE?
Maybe. Maybe not. Depends on how much overhead the repeated compilations add.
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
July 22, 2016 at 8:20 am
A good indicator of parameter sniffing can be found from looking at the actual execution plan. If estimated numbers of rows differ wildly from the actual number of rows, then parameter sniffing may be the problem.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply