Tools used in starting troubleshooting of performance issues SQL Server Database Engine

  • Article

Basic  troubleshooting in performance issues on a database or on environment with SQL Server Database Engine

It’s not uncommon to experience the occasional slowdown of a database running the Microsoft SQL Server database software. The reasons can range from a poorly designed database to a system that is improperly configured for the workload. As an administrator, you want to proactively prevent or minimize problems; if they occur, you want to diagnose the cause and take corrective actions to fix the problem whenever possible. In the following blog you can find basic steps for diagnosing and troubleshooting common performance problems by using publicly available tools such as: SQL Server Profiler, System Monitor (in the Windows Server 2003 operating system) or Performance Monitor (in the Windows Vista operating system  and Windows Server 2008), also known as Perfmon, dynamic management views (sometimes referred to as DMVs); SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.

There can be many reasons for a slowdown in SQL Server. We use the following three key symptoms to start diagnosing problems:

-      Resource bottlenecks: CPU, memory, and I/O bottlenecks are taken as examples. We do not consider network issues. For each resource bottleneck, we describe how to identify the problem and then iterate through the possible causes. For example, a memory bottleneck can lead to excessive paging that ultimately impacts performance;

-      tempdb bottlenecks: Because there is only one tempdb for each SQL Server instance, it can be a performance and a disk space bottleneck. An application can overload tempdb through excessive DDL or DML operations and by taking too much space. This can cause unrelated applications running on the server to slow down or fail;

-      A slow-running user query: The performance of an existing query might regress, or a new query might appear to be taking longer than expected;

  • Changes in statistical information can lead to a poor query plan for an existing query.
  • Missing indexes can force table scans and slow down the query.
  • An application can slow down due to blocking even if resource utilization is normal.
  • Excessive blocking can be due to poor application or schema design or the choice of an improper isolation level for the transaction.

Tools to collect and analyze data:

 1.       Dynamic management views

 We can interrogate some views to see the resource bottleneck or if there are any blocking. In the following link you can find all the Dynamic Management Views and Functions:  https://msdn.microsoft.com/en-us/library/ms188754(v=sql.100).aspx.

To identify the bottleneck queries you can use the SQL Server Management Studio or Command Prompt and the following kb: https://support.microsoft.com/kb/271509 to create a script that can be ran at a specific time, giving us as output the result of all the queries executed. Also to better understand and read the wait types we can use the KB article: https://msdn.microsoft.com/en-us/library/ms188754(v=sql.100).aspx
 

2.       SQL Server Profiler

Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.  For more information about how to use the SQL Profiler and when to use it you can access the following link: https://msdn.microsoft.com/en-us/library/ms181091(v=sql.100).aspx

 

3.       Performance Monitor

Performance Monitor is a simple yet powerful visualization tool for viewing performance data, both in real time and from log files. With it, you can examine performance data in a graph, histogram, or report. Accessing the following link you can learn more about this tool, how it works and also when should be used: https://technet.microsoft.com/en-us/library/cc749115.aspx.

The following links can be also very useful if you are a beginner with this tool:

 

4.       SQLDIAG 

If you are already familiar with the tools presented above, than you can use SQLDIAG tool. The SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLdiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers.

SQLdiag can collect the following types of diagnostic information:

  • Windows performance logs
  • Windows event logs
  • SQL Server Profiler traces
  • SQL Server blocking information
  • SQL Server configuration information

 

5.       SQLNEXUS

After you collected all necessary data you can start analyzing manually or you can use the tool SQLNexus to automatically analyze the data https://sqlnexus.codeplex.com/  

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

 

6.       Database Engine Tuning Advisor

The SQL Server Database Engine Tuning Advisor (DTA) analyzes a workload and the physical implementation of one or more databases. It can then recommend that you add, remove, or modify physical design structures in your databases. In addition, it recommends the statistics that you should collect to back up these physical design structures. An overview can be find https://msdn.microsoft.com/en-us/library/ms173494.aspx

 

7.      Activity Monitor

Activity Monitor supports real-time troubleshooting of performance issues in SQL Server 2005 or higher: https://msdn.microsoft.com/en-us/library/cc879320(v=sql.100).aspx

Activity Monitor has a refresh interval which is configurable from one second up to one hour. The default refresh interval is 10 seconds. You can change it by right-clicking the Overview chart area. Note that using the default refresh interval in Activity Monitor has a negligible performance impact; however, shorter intervals may cause the tool to start using a few percent of system CPU. You can also pause or resume the collection of data by Activity Monitor.

 

All the tools above can help you collect the data when the issue occurs and to analyze it in real time or to save logs for later analysis.