Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server DBA Performance Tuning Interview Questions


By:   |   Read Comments (11)   |   Related Tips: More > Professional Development Interview Questions DBA

Next Free Webcast - The more things change… DBAs versus Sysadmins in cloud availability


Problem

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. Good luck!

Solution

Question Difficulty = Easy

 

  • Question 2: Explain how the hardware running SQL Server can help or hinder performance.

 

  • 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 operation is a much more 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?

 

  • Question 5: True or False - It is possible to correlate the Performance Monitor metrics with Profiler data in a single SQL Server native product?

Question Difficulty = Moderate

  • Question 1: How can I/O statistics be gathered and reviewed for individual database files?

 

  • 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:

 

  • Question 3: True or False - It is always beneficial to configure TempDB with an equal number of fixed sized files as the number of CPU cores.
    • False - With always being the operative word in the question.
    • Depending on the version of SQL Server, the disk subsystem, load, queries, etc., a 1 to 1 ratio of files to cores may be necessary on high end SQL Servers with intense processing.
    • If you do not have that luxury, a starting point may to be have half the number of tempdb files as compared to CPU cores.
    • This is a configuration to load test and monitor closely depending on the type of processing, load, hardware, etc. that your SQL Server is expected to support.

 

  • 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 3: Explain why the NORECOMPUTE 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.
    • 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 and beyond hint.
    • Additional information: Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint
Next Steps
  • As you prepare for an interview, consider the interview tips on MSSQLTips.com.
  • Check out these related tips:
  • If you have a real "stumper of a question", feel free to post it in the comments section for this tip.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Send me SQL tips:

    



Sunday, September 08, 2013 - 4:26:35 PM - Jeremy Kadlec Back To Top

@SQL Server Training,

Check out these tips - http://www.mssqltips.com/sql-server-tip-category/61/locking-and-blocking/

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader


Friday, August 23, 2013 - 5:40:26 AM - sql server training Back To Top
can u please explain me about this question... What is the difference between lock, block and deadlock?

Monday, October 08, 2012 - 4:33:57 PM - Gene Wirchenko Back To Top
I do not think that clear instructions at the top can be bettered. If someone misses them, he could miss anything.

Monday, October 08, 2012 - 2:18:44 PM - Greg Robidoux Back To Top

@Gene - thanks for the feedback. 

The orignal version of this tip and other similar tips had the answers hidden, but this seemed to confuse a lot of people so we changed this to show the answers. 

Maybe we can come up with a better approach to hide the answers.


Monday, October 08, 2012 - 1:34:14 PM - Gene Wirchenko Back To Top
Regarding: Please reference the following: "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!" SAY WHAT? But this tip does not have that. How about editing it so that it does have it? I like the hidden answers approach. When answers are visible, it is too easy to read on. While you are at it, how about converting any quiz tips that do not have hidden answers and make this the standard for future quiz tips?

Friday, November 19, 2010 - 9:09:10 AM - Yusuf Back To Top
In its exact form is this true for SQL 2005 as well ? Question Difficulty = Moderate 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.

Wednesday, October 06, 2010 - 9:36:12 AM - CFRandall Back To Top
"It is beneficial to configure TempDB with an equal number of fixed sized files as the number of CPU cores" is mostly false for SQL Server versions after 2000. See Paul S. Randall's excellent blog series on SQL Server misconceptions for more information: 

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core


Wednesday, September 01, 2010 - 9:19:20 AM - Admin Back To Top
Hardik,

Please reference the following:

"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!"

Thank you,
The MSSQLTips Team


Wednesday, September 01, 2010 - 7:46:47 AM - Hardik Back To Top
Good Article.

If you provide answers (just bullet points), then it would be great.

If you dont want to give answers here, then is it possible to send on email id?


Monday, August 03, 2009 - 4:23:19 AM - --cranfield Back To Top

http://sqlserverpedia.com/blog/sql-server-bloggers/quick-tip-tempdb-on-a-multi-core-sql-server-box/


Thursday, February 07, 2008 - 11:12:11 AM - aprato Back To Top

 Great article

 I did have one question  about this statement

"It is beneficial to configure TempDB with an equal number of fixed sized files as the number of CPU cores"

 Can you elaborate more on the "why"?  I'm not totally clear.



Learn more about SQL Server tools