SQL Server performance Tuning – 2

SQL Server performance Tuning – 2

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.

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.

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.

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)

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.

6: 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.

7: 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 error logs and event logs for any errors.

8: 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.

9: 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.

10: How do you check Wait stats?

• Select * from sys.dm_os_wait_stats gives information about wait stats.

11: 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.

12: 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.

13: How can you update stats on all tables in one go?

Use MyDatabase

Go 

Exec sp_MSForEachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

GO

14: 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.

15: What is the key parameter for decision making about Rebuilding or Reorganizing index?

• Its Fragmentation level

16: 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, and 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.

17: 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.

18: 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.

19: 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?

20: 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.

21: 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.

21: 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.     

22: 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.