patternsqlModerate
Find the value OPTIMIZE FOR UNKNOWN is using
Viewed 0 times
theunknownvalueforoptimizefindusing
Problem
If I have an
OPTIMIZE FOR UNKNOWN in my stored procedure, am I able to see what value the database decided was optimal?Solution
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.
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.
Context
StackExchange Database Administrators Q#152659, answer score: 17
Revisions (0)
No revisions yet.