Quiz: Using Hints in Query Tuning

Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.

Questions

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

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

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

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

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

1. This impacts how SQL Server interprets statistics
2. This enables features and hotfixes in the storage engine
3. This enables features and hotfixes in the SQL Server optimizer
4. Using the new version allows SQL Server to create more statistics

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’))

Q5. What does the FAST hint do?

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

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

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

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

1. True
2. False

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

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

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

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

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

1. The table variable turned into a temporary table
2. The optimizer could see how many rows were in the table variable
3. The table variable turned into an in-memory table variable
4. The table variable used BATCH mode

Scroll down for the answer key :point_down:

Answer Key

• A1: 4. 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.
• A2: 1. 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.
• A3: 1. 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!
• A4: USE HINT
• A5: 2. Sets a row goal for the specified number of rows.
• A6: 4. 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.
• A7: 2. 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.
• A8: 1. 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.
• A9: 3. 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.
• A10: 2. The optimizer could see how many rows were in the table variable.