Free SQL Help for Devs, DBAs, and the Curious

One of the performance aspects to consider when working with SQL Server, MySQL, DB2 and Azure SQL is the issue of parameter sniffing in stored procedures. Parameter sniffing is a process where the SQL Server's query optimizer uses the parameter values provided in the first run of a stored procedure to compile and optimize the execution plan for that stored procedure. This can bring about performance issues if the first set of parameter values is not representative of the entire range of possible values.

In SQL Server 2016 and 2017, one way to avoid parameter sniffing is to use local variables inside your stored procedures. The query optimizer cannot sniff the value of local variables, hence it resorts to using statistical data to estimate the values. Here's an example of how you can implement this:

CREATE PROCEDURE GetOrders @CustomerId INT AS
DECLARE @LocalCustomerId INT;
SET @LocalCustomerId = @CustomerId;
SELECT * FROM Orders WHERE CustomerId = @LocalCustomerId;

In MySQL 5.7, a similar approach can be used. The optimizer cannot sniff the value of local variables and hence it uses statistics to estimate the values. Here's how you can apply this in MySQL:

CREATE PROCEDURE `GetOrders`(IN CustomerId INT)
BEGIN
DECLARE LocalCustomerId INT;
SET LocalCustomerId = CustomerId;
SELECT * FROM Orders WHERE CustomerId = LocalCustomerId;
END;

In DB2 11.1, you have the option to use the REOPT ALWAYS clause in your CREATE PROCEDURE or CREATE FUNCTION statement. This forces the optimizer to generate a new access plan for each execution of the procedure or function, thereby avoiding parameter sniffing. Here's an example:

--#SET TERMINATOR @
CREATE PROCEDURE GetOrders (IN CustomerId INT)
LANGUAGE SQL
REOPT ALWAYS
BEGIN
DECLARE LocalCustomerId INT;
SET LocalCustomerId = CustomerId;
SELECT * FROM Orders WHERE CustomerId = LocalCustomerId;
END @

For Azure SQL, you can use the OPTIMIZE FOR UNKNOWN query hint. This tells the query optimizer to use statistical data rather than the actual parameter value when compiling the execution plan. Here's an example:

CREATE PROCEDURE GetOrders @CustomerId INT AS
SELECT * FROM Orders WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);

In conclusion, parameter sniffing can become an issue if you're not careful, especially when dealing with stored procedures. However, by employing some of the techniques discussed above, you can mitigate its effects and ensure optimal performance for your SQL Server, MySQL, DB2 and Azure SQL databases.