Fast and Efficient Way to Monitor and Solve SQL Server Performance (2023)

By: Rajendra Gupta |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 asblocking and deadlocks. To resolvethese issues, most database administrators use several different tools to monitor andtroubleshoot performance problems. The downside to this approach is that if youare not collecting the right data or using the right tool, you often miss out onthe necessary information to identify the root cause of the issues and what thenext stepsshould be to optimize database performance.

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

Solution

Identifying and resolving poorly performing queries is crucial for ensuring adatabase management system's overall performance and efficiency. Identifyingpoorly performing queries involves monitoring and analyzing query execution times,resource consumption, and other metrics. Once identified, query optimization techniquescan be used to improve performance. These techniques may include databaseindexing, query rewriting, and schema design optimization. We will take a look at some of the features of SQL Diagnostic Manager thatyou 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 administratorswith comprehensive capabilities to monitor and manage the performance ofMicrosoft SQL Server instances. It gives various performance metrics, dashboards,and query monitor to diagnose issues such as slow-running queries and excessiveresource 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 DiagnosticManager, where you can see several metrics at once for any given SQL Serverinstance (click on images for larger images).

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

Servers

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

It has the following sub-sections:

1. Overview: High-level overview of server components suchas CPU, Server Waits, Memory, Cache, Network, Disk, Transactions, Active Alerts,and Configurations.
2. Sessions: This gives information on SQL Serversessions (Active\Idle\System), Lock Statistics Requests, Blocked Sessions, LockingInformation, and Blocking and Deadlock Reports.
3. Queries: This shows queries executedalong with their performance data, execution plan, CPU, Reads, Writes, and SQLtext. 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 Fileactivity, and server waits.
5. Databases: Provides a list of thedatabases, their status, recoverymodel, 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 andalerts 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 differentalerts 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 theissue becomes critical and impacts performance.

Identifying and Addressing Critical Alerts

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

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

SQL Server and Database Health

SQL Diagnostic Manager provides valuable charts to view and determinethe health of theSQL 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 acritical 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 informationwhen there isblocking in SQL Server. I can filter the information from the sessionschart as shown below.

If there is high memory usage in SQL Server processes, you can get memory-relateddata on the Resources tab. You can drill down into various SQL memory usagecomponents 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 calculatetheir state based on current data. For example, my processor queue length is ina 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 quicklyget to this data with SQL Diagnostic Manager by looking at theSessions overview page.

The sessions chart shows total deadlocks, lead blockers, blocked sessions, and activesessions. 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)toget the blocking tree and the details of each blocked and blocking session. Inthis case, we can see thelead blocker is Session ID 54.

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

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

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

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 hasbeen resolved.

Deadlocks

Another common problem in SQL Server is deadlocks that are caused by two ormore sessions in conflict based on their locking needs. You can identify and investigate deadlocks in SQL Diagnostic Manager usingthe deadlock graphs. Below we can see the list of deadlocks that have occurredand 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 ServerManagement Studio as a deadlock graph.

Poor Performing Queries

SQL Diagnostic Manager uses the Query monitor for capturing SQL Serverworkloads. 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, andWrites.

To see the query details, right-click on a query and select Show Query Textor 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 analyzingthe server and providing recommendations for things that should be addressed. Click on Analyzeto get a list ofrecommendations for configuration, query tuning, and optimization.

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

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

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 Serverperformance issues. Some of the key takeaways are:

• SQL Diagnostic Manager can help database administrators monitorand 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 anddeadlocks.
• SQL Diagnostic Manager can make you more proactive in your management ofSQL Servers.
• SQL Diagnostic Manager lets you look at what's happening right now orview what happened in the past to further enhance troubleshooting.

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

Next Steps

• Learn More about SQL Diagnostic Manager
• Start for free

MSSQLTips.com Product Spotlight sponsored by Idera makers of SQLDiagnostic Manager.

About the author

Article Last Updated: 2023-05-23