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
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
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
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’))
USE HINTQ5. 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
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
Q7. True or false: setting a row goal with FAST makes SQL Server completely ignore statistics and row counts
- True
- False
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
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
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