Performance Issue

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.

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.

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.

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.

9: How do you check Wait stats?

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

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.

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.

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

Use MyDatabase

Go 

Exec sp_MSForEachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

GO

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.

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

Its Fragmentation level

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.

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.

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.

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.

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

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

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.

·         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?