Fast and Efficient Way to Monitor and Solve SQL Server Performance Issues

By:   |   Updated: 2023-05-23   |   Comments   |   Related: More > Monitoring


Problem

One of the challenges that database administrators often face is performance issues in their SQL Server environment, such as slow response times, high resource utilization, and other issues, such as blocking and deadlocks. To resolve these issues, most database administrators use several different tools to monitor and troubleshoot performance problems. The downside to this approach is that if you are not collecting the right data or using the right tool, you often miss out on the necessary information to identify the root cause of the issues and what the next steps should be to optimize database performance.

In this article, we will explore Idera SQL Diagnostic Manager to address these problems and provide database administrators with insights and the one tool they can use to monitor and improve the performance of their entire SQL Server environment.

Solution

Identifying and resolving poorly performing queries is crucial for ensuring a database management system's overall performance and efficiency. Identifying poorly performing queries involves monitoring and analyzing query execution times, resource consumption, and other metrics. Once identified, query optimization techniques can be used to improve performance. These techniques may include database indexing, query rewriting, and schema design optimization. We will take a look at some of the features of SQL Diagnostic Manager that you can use to monitor, troubleshoot and improve the health of your SQL Servers.

Overview of SQL Diagnostic Manager

SQL Diagnostic Manager is a software tool that provides database administrators with comprehensive capabilities to monitor and manage the performance of Microsoft SQL Server instances. It gives various performance metrics, dashboards, and query monitor to diagnose issues such as slow-running queries and excessive resource consumption.

Some of the key features of SQL Diagnostic Manager include:

  • Query performance monitoring
  • Dashboard and reporting
  • Query tuning and optimization
  • Database indexing
  • Alerts and notifications

The following image shows a high-level glimpse of SQL Diagnostic Manager, where you can see several metrics at once for any given SQL Server instance (click on images for larger images).

sql diagnostic manager overview

The interface is broken down into Servers, Reports, Alerts, and Administration, and we will take a closer look at some of the features in each of these sections.

Servers

This section provides all useful information for each registered SQL Server.

sql diagnostic manager menu items

It has the following sub-sections:

  1. Overview: High-level overview of server components such as CPU, Server Waits, Memory, Cache, Network, Disk, Transactions, Active Alerts, and Configurations.
  2. Sessions: This gives information on SQL Server sessions (Active\Idle\System), Lock Statistics Requests, Blocked Sessions, Locking Information, and Blocking and Deadlock Reports.
  3. Queries: This shows queries executed along with their performance data, execution plan, CPU, Reads, Writes, and SQL text. You can also view query historical trends and query waits.
  4. Resources: Focuses on server resources such as CPU usage for SQL Server and OS processes, Number of compiles, Recompiles, Memory usage( used\allocated), Cache hit ratio, Physical IO, Disk and File activity, and server waits.
  5. Databases: Provides a list of the databases, their status, recovery model, size, and capacity usage.
  6. Services: You can check the status of each SQL service, start-up time, service account, and start-up type.
  7. Logs: Provides access to the SQL Server and SQL Agent logs.
  8. Analyze: This helps you analyze the SQL Server and alerts you of any abnormality.

Reports

This section provides information to monitor, analyze, and run several built-in reports.

sql diagnostic manager reports

Alerts

The alert tab shows you several different views for the many different alerts you can configure and capture in SQL Diagnostic Manager such as:

  • Active
  • By Severity
  • By Server
  • By Metric
  • SQL Server Agent Job Failures
  • Blocked Sessions
  • Oldest Open Transactions
  • Query Monitor Events
  • Table Fragmentation

This allows you to take proactive steps when there are issues before the issue becomes critical and impacts performance.

sql diagnostic managera alerts

Identifying and Addressing Critical Alerts

SQL Diagnostic Manager has pre-defined thresholds and mechanisms to raise alerts for any abnormality in SQL Server instances. To view the alerts you should address, click on Critical on the dashboard homepage under My Views. This view will show you alerts that are critical for your entire SQL Server environment, so you can focus on the issues that need immediate attention.

sql diagnostic manager analyze

Alternatively, from the Alerts section, you can filter alerts for a particular time frame, tag (you can assign different tag values to each instance for easier grouping), server, metric, or severity. Here is a list below of active alerts.

sql diagnostic manager alerts

SQL Server and Database Health

SQL Diagnostic Manager provides valuable charts to view and determine the health of the SQL Server. Once you click on a registered SQL Server instance, it gives the following information:

  • Health of SQL Server instance: As shown below, this server's health is in a critical state and has the following issues:
    • SQL Agent stopped
    • OS processor queue length is 5
    • Database master is not backed up for 7314 days
sql diagnostic manager server health

If you click on the server name, you get many useful charts to highlight and pinpoint issues.

sql diagnostic manager charts and graphs

There are many different interactive charts where you can choose a timeline and filter the data in all charts. For example, suppose I am interested to see CPU, memory, and other information when there is blocking in SQL Server. I can filter the information from the sessions chart as shown below.

sql diagnostic manager charts and graphs

If there is high memory usage in SQL Server processes, you can get memory-related data on the Resources tab. You can drill down into various SQL memory usage components and their variations from the baseline, including:

  • Buffer Cache: Free and Active pages
  • OS paging
  • Cache Hit Ratios
  • Page Life Expectancy (Sec)
sql diagnostic manager charts and graphs

The details page in SQL Diagnostic Manager has pre-defined metrics to calculate their state based on current data. For example, my processor queue length is in a critical state while other parameters, such as blocked session and queue length, are in good condition. To see this data, navigate to Servers > Overview > Details to view details, warnings, and critical thresholds.

SQL dm
sql diagnostic manager overview

Blocking

Another common performance problem with SQL Server is long-running blocking. You can quickly get to this data with SQL Diagnostic Manager by looking at the Sessions overview page.

sql diagnostic manager blocking

The sessions chart shows total deadlocks, lead blockers, blocked sessions, and active sessions. From the figure below, we can figure out the following:

  • No deadlocks have occurred
  • There are 4 blocked sessions
  • There is 1 blocking session
  • There are a total of 5 active sessions
sql diagnostic manager blocking graph

Here is another blocking view you can get from Servers > Sessions > Summary.

sql diagnostic manager blocking graph

Click on the blocking tab ( Servers > Sessions > Blocking) to get the blocking tree and the details of each blocked and blocking session. In this case, we can see the lead blocker is Session ID 54.

sql diagnostic manager blocking details

To get more details about any specific Session ID, right-click on it and choose View Session Details.

sql diagnostic manager session details

Below, you get detailed information about the session id, including the last executed command, tempdb usage, and lock information.

sql diagnostic manager blocking data

To kill the root blocker, you can right-click on the Session ID and choose Kill Session.

sql diagnostic manager blocking kill session

Then confirm you want to kill the session.

sql diagnostic manager blocking kill session

Then you will get a notification after the session has been terminated.

sql diagnostic manager blocking kill session

If we look at the blocking session chart again, we can see the blocking has been resolved.

sql diagnostic manager blocking chart

Deadlocks

Another common problem in SQL Server is deadlocks that are caused by two or more sessions in conflict based on their locking needs. You can identify and investigate deadlocks in SQL Diagnostic Manager using the deadlock graphs. Below we can see the list of deadlocks that have occurred and when they occurred.

sql diagnostic manager deadlocks

If we open one of the deadlock reports, we can see the details of the involved sessions, resources, and queries.

sql diagnostic manager deadlock details

You can also export this deadlock information in XDL format and open it in SQL Server Management Studio as a deadlock graph.

sql diagnostic manager deadlock graph

Poor Performing Queries

SQL Diagnostic Manager uses the Query monitor for capturing SQL Server workloads. It can collect data in these different ways:

sql diagnostic manager performance collection

If you are facing a performance issue, you can view queries by SQL Text, Application, or Database. For example, the screenshot below shows the top resource queries in my environment and their CPU time, Reads, and Writes.

sql diagnostic manager deadlocks query usage

To see the query details, right-click on a query and select Show Query Text or Show Query History.

sql diagnostic manager query details

The Query History provides historic data on execution, average duration, average reads, and writes, as shown below.

sql diagnostic manager query details

If you click on the Details column, it shows the query execution plan (Estimated) in Graphical and XML format.

sql diagnostic manager query execution plan

In the Query History page, you can also use Diagnose Query to give recommendations for tuning the query.

sql diagnostic manager query text

Analyze to Find Issues

SQL Diagnostic Manager provides insight into your SQL Servers by analyzing the server and providing recommendations for things that should be addressed. Click on Analyze to get a list of recommendations for configuration, query tuning, and optimization.

sql diagnostic manager analyze server

Here is the analysis report for my SQL instance, and each item is ordered by a priority level.

sql diagnostic manager priority items

SQL Diagnostics Manager also can generate scripts that you can use to make the recommended configuration changes. For example, SQL Diagnostic Manager shows that QUOTED_IDENTIFIER is currently ON for database AdventureWorks2019.

sql diagnostic manager priority items

Click on Optimize Script to get the script as shown below, which can then be copied or run directly on the SQL Server instance.

sql diagnostic manager scripting function

Conclusion

The article covered how SQL Diagnostic Manager can identify and address SQL Server performance issues. Some of the key takeaways are:

  • SQL Diagnostic Manager can help database administrators monitor and troubleshoot SQL Server performance issues.
  • SQL Diagnostic Manager can help you find and resolve poorly performing queries.
  • You can use SQL Diagnostic Manager to monitor for blocking and deadlocks.
  • SQL Diagnostic Manager can make you more proactive in your management of SQL Servers.
  • SQL Diagnostic Manager lets you look at what's happening right now or view what happened in the past to further enhance troubleshooting.

Readers are encouraged to try SQL Diagnostic Manager to identify and resolve performance-related issues in their own database environment. Idera offers a free 14-day trial. Click here to get started now.

Next Steps

MSSQLTips.com Product Spotlight sponsored by Idera makers of SQL Diagnostic Manager.

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-05-23

Comments For This Article