Parameter Sniffing refers to a process whereby SQL Server’s execution environment “sniffs” the parameter values during first invocation, and passes it along to the query optimizer so that they can be used to generate optimal query execution plans.
While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is not optimal when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance when executed with typical values.
Click here to read full article.
Total Pageviews
Subscribe to:
Post Comments (Atom)
Blog Archive
Ideal SQL Query For Handling Error & Transcation in MS SQL
BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...
-
All we need to use the Custom paging in grid view without object data source. Here i am going to explain my code which is used in custom pa...
-
One of the common requests in ASP.NET is to submit a form when visitor hits an Enter key. That could be a case if, for example you want to m...
-
using System.Data; using System.Data.SqlClient; using System.Text; using System.IO; using iTextSharp.text; using iTextSharp.text.pdf; ...
No comments:
Post a Comment