13 yrs of strong skill in Database and in the Cloud track. Sourav is Microsoft Certified Trainer Since 2017. He lives in Chicago.

Sourav Mukherjee

Understand what is parameter sniffing, how it can cause performance bottleneck in Sql Server and the potential solutions through this course

Programming

English

SQL Server Parameter Sniffing and Ways to Address this Killing Situation

Crash Course

Web Development

Personal and Skill Development

Develop your coding skills, learn a new language or prepare for campus placements and job interviews, you can do it all with these awesome courses.

Parameter sniffing and its potential solutions

Parameter Sniffing in Sql Server and the Potential Solutions

Lesson #1: Parameter Sniffing: How it hinders the SQL Server DB performance and ways to fix the problems.
When the SQL Server database engine compiles any stored procedure, it looks at the parameter values being passed and creates an execution plan based on such parameters. Such a process of checking at parameter values when compiling a stored procedure is commonly known as “parameter sniffing”.

Again, parameter sniffing (as the name suggests), relates to sniffing the parameters. when you execute any ad-hoc query /any stored procedure, the SQL server generates a unique query plan for every query or stored procedure that you execute. When you create any stored procedure with parameters, the SQL server sniffs the parameter range or type and creates a cost-effective execution plan accordingly and this can lead to the performance issues sometimes. We need to address this bottleneck.
Plan cache can store execution plans so that they can be reused later for us to execute the same query and an execution plan matches the current parameters of a query improves the performance. This can save both optimization time and the CPU cycle.

Lesson #1: Parameter Sniffing: How it Hinders the DB Performance in SQL Server and Fixes

When the query optimizer sniffed the parameters and determined the superlative approach for processing the stored procedure, it thrives to cache the execution plan in the procedure cache. All subsequent executions of the SP re-use the execution plan from the procedure cache regardless if different parameters are passed. The potential problem with this approach is that the parameters were used when the plan was cached may not be required to produce an optimal plan for all execution of the SP, especially those that have a significantly different set of records returned depending on the parameters passed. For instance, if you passed parameters that required many records to be read, the plan might decide a table or index scan would be the most efficient method to process the sp. Then if the same SP was called with a different set of parameters that would only return a specific record, it would use the cached execution plan and perform an table or index scan operation to resolve it’s query, even if an index seek operation would be more efficient in returning the results for the second execution of the sp.
When you try to create any stored procedure with parameters, the SQL server sniffs the parameter range, type and generates the cost-effective execution plan accordingly and this can be the difference in performance sometimes.

Lesson #2: Why Parameter Sniffing Degrades Performance?

Few suggestible workarounds to address the issue:
Create SQL server stored procedures using the with recompile option
Use the SQL server hint option (recompile)
Use the SQL server hint option (optimize for)
Use dummy variables on SQL server stored procedures
Disable SQL server parameter sniffing at the instance level
Disable parameter sniffing for a specific SQL server query
Disable specific trace flags (4136: disable parameter sniffing, 9481: disable new cardinality estimation)
Enable trace flag 4199 (for optimizer fixes. Note: by-default optimizer fixes choice is off.)

Lesson #3: Symptoms of Parameter Sniffing

To demonstrate this, I’ve created one database with the name as “parameter_sniffing”. Created one table pd_demo with 2 indexes – pk_ps_demo_id and ix_capturedate respectively and one procedure with the name as displaydemoinfo.

Lesson #4: Workaround of Parameter Sniffing using Practical Demo

In general, accurately functional parameterization can support in security for SQL Server and can have performance implications. Nonetheless, some queries cannot be fully parameterized for example when the column names, table names, or any other clauses need to be added or adapted dynamically. When the non-parameterized dynamic SQL is used, the performance and security implications are required to be kept in mind.

Lesson #5: Conclusion - Summary Overview

B.TECH Graduate in Computer Science and engineering | Systems Administrator | Passion for Technology | Enthusiast at Teaching

Sravan Kumar

PHP is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML. PHP: Hypertext Preprocessor, an open source, server-side, HTML embedded scripting language used to create dynamic Web pages. PHP is a widely-used, free, and efficient alternative to competitors such as Microsoft's ASP. A good benefit of using PHP is that it can interact with many different database languages including MySQL.

(Hindi) Introduction to PHP - Part 1


Done graduation from Thakur college of science and commerce,
Master in computer science ,
Educator in Unacademy.

Pratiksha Tripathi

In this course, Pratiksha has discussed the different aspects of R language. Here she had discussed the data interfaces, graph and charts, statistical charts and functions.

R Programming Language for Beginners

In this course Sravan Kumar Illustrates about various Form Handling mechanisms which maintains the website dynamically by using various predefined functions such as $_POST,$_GET and also about validating form.

Form Handling with PHP

Sanchit Gupta

This is the second course in the course series PHP Tutorials for Beginners in English. In this course, Sanchit will cover PHP language in more detail. You will see topics like variables, concatenation, arithmetic operation and much more. So stay tuned.

PHP Tutorial for Beginners: Part 2

Stopped Teaching at Unacademy.
Subscribe at youtube- https://www.youtube.com/channel/UCz-PmB0Sk2QGtLlt2rLH5mQ

Balwant Singh

In this course, the educator is discussing Php operators and flow control for 
Programming aspirants

(Hindi) PHP - Part2 for Programming Aspirants

In this Course Sravan Kumar illustrates about about advanced concepts in PHP and complete overview of File Management in PHP.

Lessons

6 lessons • 1h 27m

Invalid Date

NaN

Parameter Sniffing in Sql Server and the Potential Solutions

Invalid Date

NaN

Lesson #1: Parameter Sniffing: How it Hinders the DB Performance in SQL Server and Fixes

Invalid Date

NaN

Lesson #2: Why Parameter Sniffing Degrades Performance?

Invalid Date

NaN

Lesson #3: Symptoms of Parameter Sniffing

Invalid Date

NaN

Lesson #4: Workaround of Parameter Sniffing using Practical Demo

Invalid Date

NaN

Lesson #5: Conclusion - Summary Overview