Parameter Sniffing and SQL Server

        Today I came to know about an interesting point "Parameter Sniffing". I search on net and found some good material and facts about it. First, what is parameter sniffing? Below is the definition which I got from here.

"Parameter sniffing refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation."

        When a stored procedure is created, a normalized query tree for the procedure is saved in the SYSPROCEDURES system table. The normalized query tree contains the referenced table/view and column information. The process of normalization parses the SQL statements into a more efficient format and resolves all referenced objects into their internal representations. During this process, table names, for example, are resolved into their object IDs and column names are resolved into column IDs. When the procedure is subsequently executed, the tree is retrieved from SYSPROCEDURES, and based on it, an optimized execution plan is created and stored in the procedure cache.

        For stored procedures that accept parameters that can be used in the WHERE clause, the execution plan in the procedure cache is the optimized path to the data (based on the parameters given during the first execution of the procedure). For such procedures, if the parameters can be significantly different for each execution of the procedure, it is probably worth exploring the use of the WITH RECOMPILE option during execution or even during the creation of the procedure. This does add the overhead of having to generate the execution plan for each execution of the procedure.
        The additional execution plans stay in cache until they are paged out, and could cause one execution to be very different from another. A user has no control or knowledge of which plan will be used. This can sometimes explain unexpectedly different execution times for the same procedure given the same data and parameters.

You can get more information on parameter sniffing at link shown below.

1. http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

2. http://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/

3. http://support.microsoft.com/kb/104445

Happy Programming !!

HTTP could not register URL http://+:8000/. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details)

        You may find this error when you try to register WCF service host using HTTP binding in windows vista. I had the same problem and I found the perfect solution here.

Value does not fall within the expected range exception while using ReportDataSource

           Here is my code which produce "Value does not fall within the expected range" exception. 

 ReportDataSource objReportDataSource 
              = new ReportDataSource("CategoryProduct");
 objReportDataSource.Name = "NewDataSet_CategoryProducts";
 // dsDataSet is DataSet object
 objReportDataSource.Value = dsData;

            After doing some try and error method I found that this exception is due to objReportDataSource.Value = dsData. You need to assign a DataTable to Value property instead of DataSet. Below is the code that is working fine.

 ReportDataSource objReportDataSource 
              = new ReportDataSource("CategoryProduct");
 objReportDataSource.Name = "NewDataSet_CategoryProducts";
 // dsDataSet is DataSet object
 objReportDataSource.Value = dsData.Tables[0];

Happy Programming !!!