If I have an
OPTIMIZE FOR UNKNOWN in my stored procedure, am I able to see what value the database decided was optimal?
OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.
If you run DBCC SHOWSTATISTICS, it's the value listed in the "All density" column of the second result set:
In this example, I'm using the StackOverflow demo database. The density vector for the Reputation column is 5.962674E-05.
If you take that value, times the number of rows in the table, 5.962674E-05 * 5277831, you get 314.69985680094. That's the number of rows SQL Server will expect to come back for any given Reputation filter.
Benjamin Nevarez has a good article about
OPTIMIZE FOR UNKNOWN at his blog
Essentially, SQL Server is using statistics about the table along with math to determine which value to use.
From his post:
Density is defined as 1 / number of distinct values. The SalesOrderDetail table has 266 distinct values for ProductID, so the density is calculated as 1 / 266 or 0.003759399 as shown before on the statistics object. One assumption in the statistics mathematical model used by SQL Server is the uniformity assumption. Since in this case SQL Server can not use the histogram, the uniformity assumption tells that for any given value the data distribution is the same. To obtain the estimated number of records SQL Server will multiply the density by the current total number of records, 0.003759399 * 121,317 or 456.079, as shown on the plan. This is also the same as to divide the total number of records by the number of distinct values, 121,317 / 266, which also gives 456.079.