SQL Server Performance Tuning Interview Questions

In the latest installment of the SQL Server interview questions, we will outline questions suitable for a DBA or Developer interview to assess the candidates skills related to SQL Server performance tuning. In this tip, the questions are there to read, but the answers are intentionally hidden to really test your skills. Once you read the question and have determined your answer, then highlight the answer to see how you did. Good luck!

Solution

Question Difficulty = Easy

• Question 1: Name five different tools which can be used for performance tuning and their associated purpose.
  • Performance Monitor\System Monitor - Tool to capture macro level performance metrics.
  • Profiler - Tool to capture micro level performance metrics based on the statements issued by a login, against a database or from host name.
  • Server Side Trace - System objects to write the detailed statement metrics to a table or file, similar to Profiler.
    • Additional information: SQL Server Performance Statistics Using a Server Side Trace
  • Dynamic Management Views and Functions - SQL Server objects with low metrics to provide insight into a specific portion of SQL Server i.e. the database engine, query plans, Service Broker, etc.
    • Additional information: Dynamic Management Views\Functions
  • Management Studio's Built-In Performance Reports - Ability to capture point in time metrics as pre-defined by Microsoft.
    • Additional information: Built-In Performance Reports in SQL Server 2005
  • Custom scripts - Custom scripts can be developed to monitor performance, determine IO usage, monitor fragmentation, etc all in an effort to improve performance.
  • Third party applications - Performance monitoring and tuning applications from vendors in the SQL Server community.
    • Additional information: SQL Server Performance Monitoring Tools
• Question 2: Explain how the hardware running SQL Server can help or hinder performance.
  • Taxed CPUs will queue requests and hinder query performance.
  • Insufficient memory could cause paging resulting in slow query performance.
  • Incorrectly configured disk drives could exacerbate IO problems.
  • Additional information: Hard Drive Configurations for SQL Server and Hardware 101 for DBAs
• Question 3: Why is it important to avoid functions in the WHERE clause?
  • Because SQL Server will scan the index or the table as opposed to seeking the data. The scan is a much costly operation than a seek.
  • Often a slightly different approach can be used to prevent using the function in the WHERE clause yielding a favorable query plan and high performance.
  • Additional information: Performance Tip: Avoid functions in WHERE clause
• Question 4: How is it possible to capture the IO and time statistics for your queries?
  • Use the SET STATISTICS IO and SET STATISTICS TIME settings in your queries or enable the settings in your Management Studio session.
  • Additional information: Getting IO and time stats for your queries
• Question 5: True or False - It is possible to correlate the Performance Monitor metrics with Profiler data in a single SQL Server 2005 native product?
  • True - This functionality is possible with SQL Server Profiler.
  • Additional information: Correlating Performance Monitor and Trace Data

Question Difficulty = Moderate

• Question 1: How can I/O statistics be gathered and reviewed for individual database files?
  • By using the fn_virtualfilestats function to capture the metrics.
  • This process can be automated with a script to determine the file usage with numerous samples.
  • Additional Information: Gathering I/O statistics down to the database file level
• Question 2: What is a query plan and what is the value from a performance tuning perspective?
  • A query plan is the physical break down of the code being passed to the SQL Server optimizer.
  • The value from a performance tuning perspective is that each component of the query can be understood and the percentage of resource utilization can be determined at a micro level. As query tuning is being conducted, the detailed metrics can be reviewed to compare the individual coding techniques to determine the best alternative.
  • Additional Information: Maximizing your view into SQL Query Plans and Capturing Graphical Query Plans with Profiler
• Question 3: True or False - It is beneficial to configure TempDB with an equal number of fixed sized files as the number of CPU cores.
  • True.
• Question 4: Explain the NOLOCK optimizer hint and some pros\cons of using the hint.
  • The NOLOCK query hint allows SQL Server to ignore the normal locks that are placed and held for a transaction allowing the query to complete without having to wait for the first transaction to finish and therefore release the locks.
  • This is one short term fix to help prevent locking, blocking or deadlocks.
  • However, when the NOLOCK hint is used, dirty data is read which can compromise the results returned to the user.
  • Additional information: Getting rid of some blocking issues with NOLOCK
• Question 5: Explain three different approaches to capture a query plan.
  • SHOWPLAN_TEXT
  • SHOWPLAN_ALL
  • Graphical Query Plan
  • sys.dm_exec_query_optimizer_info
  • sys.dm_exec_query_plan
  • sys.dm_exec_query_stats

Question Difficulty = Advanced

• Question 1: True or False - A LEFT OUTER JOIN is always faster than a NOT EXISTS statement.
  • False - With always being the operative word. Depending on the situation the OUTER JOIN may or may not be faster than a NOT EXISTS statement. It is necessary to test the techniques, review the query plans and tune the queries accordingly.
• Question 2: Name three different options to capture the input (code) for a query in SQL Server 2005.
  • DBCC INPUTBUFFER
  • fn_get_sql
  • sys.dm_exec_sql_text
  • Additional information: SQL Server statements currently running with fn_get_sql and SQL Server 2000 to 2005 Crosswalk - Code Identification
• Question 3: Explain why the NOCOMPUTE option of UPDATE STATISTICS is used.
  • This command is used on a per table basis to prevent the table from having statistics automatically updated based on the 'Auto Update Statistics' database configuration.
  • Taking this step will prevent UPDATE STATISTICS from running during an unexpected time of the day and cause performance problems.
  • By setting this configuration it is necessary to manually UPDATE STATISTICS on a regular basis.
  • Additional information: The NORECOMPUTE option of UPDATE STATISTICS
• Question 4: Explain a SQL Server deadlock, how a deadlock can be identified, how it is a performance problem and some techniques to correct deadlocks.
  • A deadlock is a situation where 2 spids have data locked and cannot release their lock until the opposing spid releases their lock. Depending on the severity of the deadlock, meaning the amount of data that is locked and the number of spids that are trying to access the same data, an entire chain of spids can have locks and cause a number of deadlocks, resulting in a performance issue.
  • Deadlocks can be identified by Profiler in either textual, graphical or XML format.
    • Additional information: Capturing Deadlock Information in XML Format and How To: Graphical Deadlock Chain
  • Deadlocks are a performance problem because they can prevent 2 or more processes from being able to process data. A deadlock chain can occur and impact hundreds of spids based on the data access patterns, number of users, object dependencies, etc.
  • Deadlocks could require a database design change, T-SQL coding change to access the objects in the same order, separating reporting and OLTP applications, including NOLOCK statements in SELECT queries that can accept dirty data, etc.
• Question 5: Please explain why SQL Server does not select the same query plan every time for the same code (with different parameters) and how SQL Server can be forced to use a specific query plan.
  • The query plan is chosen based on the parameters and code being issued to the SQL Server optimizer. Unfortunately, a slightly different query plan can cause the query to execute much longer and use more resources than another query with exactly the same code and only parameter differences.
  • The OPTIMIZE FOR hint can be used to specify what parameter value we want SQL Server to use when creating the execution plan. This is a SQL Server 2005 hint.
  • Additional information: Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint