Quiz on indexes and query performance 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. Which of the following does DBCC DROPCLEANBUFFERS do?

1. Removes all clean buffers from the buffer pool and clears the execution plan cache
2. Flushes modified pages from the buffer pool to disk
3. Removes all clean pages from the buffer pool
4. Clears the execution plan cache

Q2. Where can you see Query Time Stats in an execution plan with the most recent release of SQL Server Management Studio?

Note: these are only available in SQL Server 2014 SP2 and higher

1.  In estimated query execution plans
2.  In actual query execution plans
3.  In cached query execution plans
4.  In estimated and actual query execution plans

Q3. In our “slow” query, which of the following best describes the problem with statistics on dbo.FirstNameByBirthDate_1966_2015?

1.  SQL Server used the histogram of the index statistics on FirstNameId, but it only used a default sampling and the values weren’t correct
2.  That table only has the FirstNameId column, and SQL Server didn’t know Matthew’s FirstNameId
3.  SQL Server used the histogram of the index statistics on FirstNameId, but the statistics were out of date
4.  SQL Server looked up the wrong FirstNameId value in the histogram when it used the statistics

Q4. Why didn’t rewriting the query using a CTE make it faster?

1.  We forgot to make the CTE recursive
2.  The CTE is not executed first: it’s all still just one query
3.  We didn’t use a RECOMPILE hint in the CTE
4.  CTEs are bad for performance

Q5. Which of the following are downsides for using recompile hints to allow SQL Server to “sniff” the value of a local variable in a stored procedure?

Choose all that apply

1.  Recompile can drive up CPU utilization
2.  Recompile makes monitoring performance using the query plan cache much harder
3.  Recompile is not supported by Microsoft
4.  Recompile does not work in stored procedures

Q6. What is one thing to be aware of if you execute the built-in sp_recompile procedure against a table in SQL Server, to force any query that references the table to get a fresh plan the next time it runs?1

1.  It clears out the entire execution plan cache
2.  It requires a schema modification lock on the table
3.  It will recreate the table
4.  It doesn’t work for stored procedures

Q7. Which of the following should you consider when writing Dynamic SQL?

Choose all that apply

1.  How plan reuse will perform when run for different parameter values (if it is parameterized Dynamic SQL)
2.  How the Dynamic SQL can be debugged and edited later on
3.  How many execution plans will be generated
4.  Whether or not it’s best to use sp_executesql in your case

Scroll down for the answer key :point_down:

Answer Key

• A1. 3. Removes all clean pages from the buffer pool
• A2. 2. Actual Execution Plans
• A3. 2. That table only has the FirstNameId column, and SQL Server didn’t know Matthew’s FirstNameId. SQL Server didn’t know Matthew’s FirstNameId. Looking that up requires running a query, and SQL Server can’t currently run a query as part of the optimization process. (And optimization needs to be VERY FAST, so that wouldn’t make a whole lot of sense.)
• A4. 2. The CTE is not executed first: it’s all still just one query. CTEs can be useful in queries. In this case, the CTE rewrite is just a logically different way of phrasing the same query. The portion of the query inside the CTE isn’t executed in a separate step, it’s all still optimized as a single query, and has the same problem with “FirstNameId” being anonymous.
• A5. 2. Recompile makes monitoring performance using the query plan cache much harder, 1. Recompile can drive up CPU utilization
• A6. 2. It requires a schema modification lock on the table
• A1. 1, 2, 3, 4. All of these should be considered