By: Rajendra Gupta | Updated: 2023-05-23 | Comments | Related: > 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).
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.
It has the following sub-sections:
- Overview: High-level overview of server components such as CPU, Server Waits, Memory, Cache, Network, Disk, Transactions, Active Alerts, and Configurations.
- Sessions: This gives information on SQL Server sessions (Active\Idle\System), Lock Statistics Requests, Blocked Sessions, Locking Information, and Blocking and Deadlock Reports.
- 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.
- 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.
- Databases: Provides a list of the databases, their status, recovery model, size, and capacity usage.
- Services: You can check the status of each SQL service, start-up time, service account, and start-up type.
- Logs: Provides access to the SQL Server and SQL Agent logs.
- 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.
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.
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.
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 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
If you click on the server name, you get many useful charts to highlight and pinpoint issues.
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.
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)
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.
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.
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
Here is another blocking view you can get from Servers > Sessions > Summary.
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.
To get more details about any specific Session ID, right-click on it and choose View Session Details.
Below, you get detailed information about the session id, including the last executed command, tempdb usage, and lock information.
To kill the root blocker, you can right-click on the Session ID and choose Kill Session.
Then confirm you want to kill the session.
Then you will get a notification after the session has been terminated.
If we look at the blocking session chart again, we can see the blocking has been resolved.
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.
If we open one of the deadlock reports, we can see the details of the involved sessions, resources, and queries.
You can also export this deadlock information in XDL format and open it in SQL Server Management Studio as a 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:
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.
To see the query details, right-click on a query and select Show Query Text or Show Query History.
The Query History provides historic data on execution, average duration, average reads, and writes, as shown below.
If you click on the Details column, it shows the query execution plan (Estimated) in Graphical and XML format.
In the Query History page, you can also use Diagnose Query to give recommendations for tuning the query.
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.
Here is the analysis report for my SQL instance, and each item is ordered by a priority level.
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.
Click on Optimize Script to get the script as shown below, which can then be copied or run directly on the SQL Server instance.
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
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