Questions

Q1. What is one thing that can go wrong if you use an index hint?

• SQL Server may ignore the index hint
• SQL Server will dynamically create the index if it doesn’t exist, which may be slow
• SQL Server won’t allow you to drop or rename the index afterward
• If the index is dropped or renamed, the query will fail

[Show Answer] If the index is dropped or renamed, the query will fail. SQL Server is really serious about index hints: so serious that if the index isn’t there with the exact name you specify, your query will fail.

Q2. What does the “excessive grant” warning mean on a query plan?

• The query was allocated far more memory than it used
• The query read too many wide columns (too large a data size)
• The query read too many rows
• The query got a special “Grant Fritchey” query optimization

[Show Answer] The query was allocated far more memory than it used. “Excessive grant” means that the amount of memory allocated was far more than what SQL Server saw the query actually use. You’ll see this only in an “actual” execution plan. If you aren’t familiar with Grant Fritchey, look him up sometime! He writes a great blog and books on SQL Server.

Q3. Why does the version of the cardinality estimator matter for performance of queries?

• This impacts how SQL Server interprets statistics
• This enables features and hotfixes in the storage engine
• This enables features and hotfixes in the SQL Server optimizer
• Using the new version allows SQL Server to create more statistics

[Show Answer] This impacts how SQL Server interprets statistics. The new cardinality estimator introduced in SQL Server 2014 includes lots of changes to the algorithms and assumptions that SQL Server uses to interpret how many rows will come out of joins and other operators. It still uses the same statistics, it just does the math differently!

Q4. Fill in the blank: what is the new syntax for a query hint that we got in SQL Server 2016 SP1?

OPTION ( _____ (‘FORCE_LEGACY_CARDINALITY_ESTIMATION’))

[Show Answer] USE HINT

Q5. What does the FAST hint do?

• Makes SQL Server recompile the query in the middle of execution if it isn’t fast
• Sets a row goal for the specified number of rows
• Prioritizes the query at execution time so that it can’t be blocked
• Tells SQL Server to test the query in advance to make sure that you have the fastest execution plan

[Show Answer] Sets a row goal for the specified number of rows.

Q6. What is one downside to hinting the legacy cardinality estimator?

• The legacy cardinality estimator is deprecated and will be removed from the product soon
• The legacy cardinality estimator is only available on SQL Server 2005 and prior
• Hinting the legacy cardinality estimator is not technically supported by Microsoft
• The new estimator is regularly being improved, so you need to test to see if removing the hint does better

[Show Answer] The new estimator is regularly being improved, so you need to test to see if removing the hint does better. The legacy cardinality estimator hasn’t been deprecated, and is widely used. It’s available in all versions of SQL Server, it’s just not the default in SQL Server 2014 and higher. Hinting the legacy cardinality estimator is documented by Microsoft and is supported, no problems there.

Q7. True or false: setting a row goal with FAST makes SQL Server completely ignore statistics and row counts

• True
• False

[Show Answer] False. Even with a FAST row goal, SQL Server still considers information about the table. Remember, in the example where I set the row goal to 2+ billion, SQL Server still showed estimated rows as the number of rows in the table in our plan.

Q8. What does specifying join hint(s) in a query hint do?

• Limits the query to only the physical joins you list in the hint
• Limits the first join listed in the query to the joins you list in the hint
• Encourages SQL Server to use those physical joins, but doesn’t require it
• Forces the query to use parallelism

[Show Answer] Limits the query to only the physical joins you list in the hint. Query hints are those hints at the end of a query. Specifying join hint(s) there limits the join types available for the entire query.

Q9. Why did SQL Server know the row count for the #NameRank temp table even though we didn’t use a RECOMPILE hint?

• We enabled a trace flag that let SQL Server see row counts on temp tables
• SQL Server always recompiles references to temp tables, no matter what
• SQL Server automatically created column statistics on the temp table
• It was a lucky guess, just that once

[Show Answer] SQL Server automatically created column statistics on the temp table. SQL Server didn’t just get lucky: temporary tables support automatically created column statistics, which helped it estimate the row count.

Q10. What did trace flag 2453 change about the table variable in our demo?

• The table variable turned into a temporary table
• The optimizer could see how many rows were in the table variable
• The table variable turned into an in-memory table variable
• The table variable used BATCH mode

[Show Answer] The optimizer could see how many rows were in the table variable.

Back to: Query Tuning with Hints & Optimizer Hotfixes (2 hours 15 minutes) > Query tuning with hints