March towards SQL Server : Day 24 – SQL DBA Interview Questions Answers – SQL Server performance Tuning – 2
In this blog, I am taking a different approach. It’s very common that interviewer promptly asks another question from your answers only. Performance Tuning is the area where this can be done easily during any interview. Interviewer may just try to see if you really know about practical implementation of performance tuning techniques or not.
Here, I am trying to establish an approach that can be taken while understanding and troubleshooting a performance issue as well as trying to answer some interview questions. So here is the deal – everything that is written in Blue Text is part of interview question series but Small with Italic Black fonts are real life situations. Let’s see how it goes.
Question 1 – How can SQL Server Management Studio help while troubleshooting Performance Issues?
- SQL Server Management Studio (SSMS), helps us to run the queries or DMV’s, We can generate execution query plans, Read/Write statistics, Time Statistics by running the query from SSMS.
Question 2 – How can Server Profiler trace help while troubleshooting Performance Issues?
- SQL Server Profiler trace can be used to identify all the queries running during the problem time and which queries are taking longer times to complete.
Question 3 – What is SQL Server Database Tuning Adviser (DTA)?
- SQL Server Database Tuning Adviser (DTA) is available to check for missing index and statistics recommendations.
Question 4 – What is SQL Server Performance Dashboard?
- SQL Server Performance Dashboard is used to generate performance related reports (This needs to be installed as additional plugin)
Question 5 – What are SQL Server Extended Events?
- Starting SQL Server 2008 R2, we can use SQL Server Extended Events can be used for more granular details with less performance overhead than profiler.
Once you know all the tools that you have – here is a real life scenario.
How do you approach or troubleshoot performance problems in SQL Server?
There are different ways of troubleshooting performance issues and there is no right or wrong way of troubleshooting performance issues, it is just my way of approaching in identifying the problem and then taking corrective actions.
First try to understand more details about the performance issues. Normally performance problems are reported by Developers or Client as per feedback from end users or some monitoring tools reporting specific performance issue on the server.
Ask Developers or Client questions to understand what they mean by performance problems and gather mode details to pinpoint issue to a specific application or a specific functionality of the application.
Troubleshooting approach will differ for general performance problems affecting whole application or more specific problem like slowness with specific Stored Procedure or Query.
Check the overall hardware resource usage like, CPU usage on the server, Memory usage on the server, I/O usage on the server and Network usage to see if there are any spikes in any of their usage. If yes, then drill further down in that direction, if everything looks normal, then will proceed with checking at SQL Server level.
Question 6 – How do you monitor resource usages?
- Resource usages can be monitored using Task Manager, Perfmon and using sys.dm_exec_query_stats and sys.dm_exec_sql_text
Check SQL Server errorlogs and eventlogs for any errors.
Question 7 – How do you check SQL Server error logs and Event Logs
- SQL Server logs can be viewed using xp_readerrorlogs. SQL Server management studio also provides Log file viewer tool.
Check for any blocking or heavy locking or high number of suspended sessions.
Question 8 – How do you check blocking, locking or suspended connections?
- By running SP_WHO2, SP_WHO, select * from sys.sysprocesses and Activity Monitor can be used to view blocking.
Check waits stats to see the top waits.
Question 9 – how do you check Wait stats?
- Select * from sys.dm_os_wait_stats gives information about wait stats.
Question 10 – What is wait time; signal wait time & resource wait time?
- SQL Server keeps track of the time that elapses between leaving the RUNNING state and becoming RUNNING again called the “wait time” The time spent on the RUNNABLE queue called the “signal wait time” – i.e. how long does the thread need to wait for the CPU after being signaled that its resource is available. We need to work out the time spent waiting on the SUSPENDED list called the “resource wait time” by subtracting the signal wait time from the overall wait time.
Checking if there are regular maintenance on the SQL Server like rebuilding indexes and update of statistics. If not, then will implement those which will significantly improve the performance.
Question 11 – How can you check stats information?
- DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid) can be used to view Stats information about individual index on a table.
Question 12 – How can you update stats on all tables in one go?
Use MyDatabase Go Exec sp_MSForEachtable 'UPDATE STATISTICS ? WITH FULLSCAN' GO
Question 13 – What is difference between Reorganizing and Rebuilding index?
- Index Rebuild: This process drops the existing Index and Recreates the index.
- Index Reorganize: This process physically reorganizes the leaf nodes of the index.
Question 14 – What is the key parameter for decision making about Rebuilding or Reorganizing index?
- Its Fragmentation level
Question 15 – How do you check the fragmentation level?
- DMF sys.dm_db_index_physical_stats can be used to view fragmentation level of an index.
Will run DMV’s to identify Top Duration, Top CPU, Top Read or Write intensive queries and try to tune them by creating appropriate indexes or report them to developer suggesting to re-write those queries.
Question 16 – What are missing indexes and how can they be identified?
- When you run a SQL query, SQL Server determines what indexes it would like to use, if these are not available, it makes a note of them. You can see details of these missing indexes by using DMVs.
Question 17 – What are unused indexes and how can they be identified?
- Unused indexes are those indexes that exist, but are not being used and therefore can be dropped to improve performance and decrease storage requirements.
What question do you ask Developers or Client to understand more about the performance issue?
What kind of performance issue are you seeing, can you be more specific? It is often said that the Application is slow or website is slow.
Is there any specific feature or a webpage of the application that is slow or is it that entire application is slow?
Question 18 – If you know that a feature of an application is performing slower than expected, how will you identify the corresponding SQL Statement?
- There are many ways of doing this exercise. A profiler trace can be used; a member of development team can be consulted. If SPID is known, then DBCC Input buffer and other options can be used to find the SQL Statement.
Since when you started seeing performance problems?
Have you started seeing these problems after recent upgrades to Application or SQL Server or OS and any other changes? Or were there anything changed recently on the system, like applying patches, Hardware or Application changes performed recently?
Question 19 – How can you check last patching activity status of SQL Server or Operating System?
- Open Windows Update by clicking the Start button. In the search box, type Update, and then, in the list of results, click Windows Update. In the left pane, click View update history. This shows the latest patch that is applied with other information like dates and KB Number.
Are you aware of any changes to the data or increase in number of users on the SQL Server recently?
Question 20 – If major bulk data deletion/insertion activity happened last night, how will this activity hit performance?
- All DML operations (INSERT, UPDATE, and DELETE) can cause index fragmentation.
So far have you observed anything that can point in a direction where could be the problem?
Have you checked application and web server to make sure problem does not lie there itself? How you came to conclusion that problem is with SQL Server?
Do we have any baseline on the application performance or query performance like, how much time it used to take before and how much time it taking now?
Have you performed any troubleshooting thus far and what are your findings, if any, so far?
How do you troubleshoot slowness with a specific Stored Procedure or a Query?
First, get more details like, how much time on an average this query was taking previously (baseline)?
Were there any changes to the stored procedure or query recently?
How often this query does runs?
Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers?
Check if this query is being blocked by other sessions.
Check if this query is waiting some any resource using wait stats DMV’s.
Check if statistics are up to date for the tables and indexes used in the Stored procedure or the query.
Check fragmentation of the objects in the stored procedure or the query.
Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
Check for any missing indexes based on the execution plan, based on table or clustered index scans.
Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.
References: Thanks to the all the SQL Server techies who wrote and shared the valuable information on some the blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.