using adventureworks database 2017 vs 2012

Using AdventureWorks Database 2017 vs 2012: Understanding Optimize for Unknown Feature

In this article, we will explore the concept of the 'Optimize for Unknown' feature in SQL Server and how it affects query execution. This feature was introduced in SQL Server 2008 and allows users to create a plan that is optimized for unknown parameters.

When using the 'Optimize for Unknown' feature, the plan created doesn't take into account the values passed in during execution. Instead, it pretends that the value is unknown and creates a plan based on this assumption. This approach can lead to mediocre plans, as demonstrated by two example queries. Query 1 estimated it would return 265 rows but actually returned only 4, while query 2 estimated it would return 265 rows but actually returned 2,636.

The 'Optimize for Unknown' feature follows the premise that re-using a stable execution plan is better than spending CPU to compile a unique plan every time a query runs. This approach can be beneficial if a query runs frequently and the parameters used are not critical to the outcome. However, it may not be suitable for queries with varying parameter values.

To use this feature, you need to add a query hint to your stored procedure. The hint can be added as follows:

```

id = 'Optimize for Unknown';

```

The 'Optimize for Unknown' feature is designed to solve the problem of unpredictable execution times. It achieves this by 'sniffing' the parameter value during the first run and compiling the plan based on that value. However, this approach can lead to inconsistent results if the query runs with different values.

In conclusion, the 'Optimize for Unknown' feature in SQL Server 2017 vs 2012 is a powerful tool that allows users to optimize query execution for unknown parameters. While it may not always produce the best plans, it can be useful in certain situations where query frequency and parameter stability are important factors.

Using adventureworks database 2017 vs 2012