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

Lesson 2 of 6 • 0 upvotes • 14:10mins

Sourav Mukherjee

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.