Free SQL Help for Devs, DBAs, and the Curious

Parameter sniffing is a common behavior in SQL Server 2012 and 2014, MySQL 5.6, DB2 10.5, and Azure SQL, where the SQL Server query optimizer uses the first-executed parameter value to estimate the cost and generate an execution plan. This is usually beneficial, as it allows the optimizer to generate a plan based on actual parameter values rather than generic estimates. However, at times, this can lead to performance issues if the first-executed parameter value is not a good representative of subsequent values. In such scenarios, using the OPTIMIZE FOR UNKNOWN hint can be beneficial.

The OPTIMIZE FOR UNKNOWN hint tells the SQL Server query optimizer to use statistical data instead of the initial parameter values to compile the query. This generally results in a more optimal plan for a wider range of parameter values. It's crucial to note that OPTIMIZE FOR UNKNOWN is available in SQL Server but not in MySQL 5.6 or DB2 10.5. In these systems, similar functionality can be achieved by using prepared statements or query recompilation techniques.

Here's how you can use the OPTIMIZE FOR UNKNOWN hint in SQL Server 2012 and 2014:

DECLARE @CustomerID INT = 1

SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (OPTIMIZE FOR UNKNOWN)

In the above example, SQL Server will generate a plan that is optimal for a wide range of @CustomerID values, not just the initial value of 1.

For MySQL 5.6, consider using prepared statements to avoid parameter sniffing, like so:

SET @CustomerID = 1;

PREPARE stmt FROM 'SELECT * FROM Orders WHERE CustomerID = ?';

EXECUTE stmt USING @CustomerID;

DEALLOCATE PREPARE stmt;

In DB2 10.5, the REOPT ALWAYS option can be used to ensure that DB2 always compiles the query before execution, which can help to avoid parameter sniffing:

UPDATE COMMAND OPTIONS USING S OPT REOPT ALWAYS;

DECLARE @CustomerID INT = 1;

SELECT * FROM Orders WHERE CustomerID = @CustomerID;

In Azure SQL, you can use the OPTIMIZE FOR UNKNOWN hint in the same way as in SQL Server. However, Azure SQL also offers an automatic tuning feature that can help to mitigate performance issues related to parameter sniffing.

While the OPTIMIZE FOR UNKNOWN hint and similar techniques can be useful in avoiding parameter sniffing issues, they should be used judiciously, as they can also lead to sub-optimal plans in some cases. Therefore, always test the performance with and without these options to ensure that they are beneficial for your specific workload.