Q1. Which of the following does DBCC DROPCLEANBUFFERS do?
- Removes all clean buffers from the buffer pool and clears the execution plan cache
- Flushes modified pages from the buffer pool to disk
- Removes all clean pages from the buffer pool
- 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
- In estimated query execution plans
- In actual query execution plans
- In cached query execution plans
- 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?
- SQL Server used the histogram of the index statistics on FirstNameId, but it only used a default sampling and the values weren’t correct
- That table only has the FirstNameId column, and SQL Server didn’t know Matthew’s FirstNameId
- SQL Server used the histogram of the index statistics on FirstNameId, but the statistics were out of date
- 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?
- We forgot to make the CTE recursive
- The CTE is not executed first: it’s all still just one query
- We didn’t use a RECOMPILE hint in the CTE
- 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
- Recompile can drive up CPU utilization
- Recompile makes monitoring performance using the query plan cache much harder
- Recompile is not supported by Microsoft
- 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
- It clears out the entire execution plan cache
- It requires a schema modification lock on the table
- It will recreate the table
- It doesn’t work for stored procedures
Q7. Which of the following should you consider when writing Dynamic SQL?
Choose all that apply
- How plan reuse will perform when run for different parameter values (if it is parameterized Dynamic SQL)
- How the Dynamic SQL can be debugged and edited later on
- How many execution plans will be generated
- Whether or not it’s best to use sp_executesql in your case