A fast and efficient way to monitor and troubleshoot SQL Server performance (2023)

And:Rajendra Gupta|Updated: 05/23/2023|Comment|Related:more>Monitor

Ask

One of the challenges that database administrators often face is performance issues in SQL Server environments, such as: B. slow response times, high resource usage, and other issues like blocking and deadlocks. To troubleshoot these issues, most database administrators use various tools to monitor and troubleshoot performance issues. The downside to this approach is that if you don't collect the right data or use the right tools, you often lack the information you need to determine the root cause of a problem and determine next steps to optimize database performance.

In this article, we examine the Idera SQL Diagnostic Manager to address these issues and provide DBAs with insight and the only tool they can use to monitor and improve the performance of their entire SQL Server environment.

Solution

Identifying and resolving poorly performing queries is critical to ensuring the overall performance and efficiency of your database management system. To identify poorly performing queries, query execution times, resource consumption, and other metrics must be monitored and analyzed. Once identified, query optimization techniques can be used to improve performance. These techniques can include database indexing, query rewriting, and schema design optimization. We'll take a look at some of the SQL Diagnostic Manager features that you can use to monitor, troubleshoot, and improve the health of SQL Server.

Overview of the 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 offers various performance metrics, dashboards, and query monitoring to diagnose issues like slow running queries and excessive resource consumption.

Key features of SQL Diagnostics Manager include:

• Monitor query performance
• Dashboards and Reports
• Query optimization and tuning
• database index
• Notifications and Alerts

The image below shows the general view of the SQL DiagnosticManager, where you can view multiple metrics for a specific SQL Server instance at once (click the image for a larger view).

The user interface is divided into servers, reports, notifications and administration. In each section we will take a closer look at some of the features.

Server

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

It consists of the following subsections:

1. overview: A high-level overview of server components such as CPU, server latencies, memory, cache, network, disk, transactions, activity notifications, and configuration.
2. Conversation: Provides information about SQL Server sessions (Active\Idle\System), lock stats requests, blocked sessions, lock information, and block and deadlock reports.
3. Ask: Displays executed queries along with performance data, execution plan, CPU, reads and writes, and SQL text. You can also view historical query trends and query latencies.
4. Resource: Focuses on server resources such as CPU usage of SQL Server and OS processes, number of compilations, number of recompilations, memory usage (used/allocated), cache hit ratio, physical I/O, disk and file activity, and server latency.
5. Database: Provides a list of databases, their status, recovery model, size, and capacity usage.
6. surcharge: You can view the status, start time, service account and startup type of each SQL service.
7. Protocol: Provides access to SQL Server and SQL Agent log files.
8. analyze: This will help you analyze your SQL Server and alert you to any anomalies.

Reference

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

notify

The Alerts tab shows you many different views and you can configure and log many different alerts in the SQL Diagnostic Manager, for example:

• positive
• seriously
• from the server
• Metric system
• The SQL Server Agent job failed
• blocked session
• previous open deals
• Query trace events
• table hash

This allows you to take proactive action when issues arise before they become severe and impact performance.

Identify and process critical alerts

The SQL Diagnostic Manager has predefined thresholds and mechanisms to warn of anomalies in an instance of SQL Server. To see notifications that you should deal with, clickCriticalUnder Control Panel HomeOut of my sight.This view displays alerts that are critical to your entire SQL Server environment, allowing you to focus on issues that require immediate attention.

Alternatively, in the Alerts section, you can filter alerts by a specific time range, tag (you can assign each instance a different tag value for easy grouping), server, metric, or severity. The list of event notifications is as follows.

SQL Server and database health

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

• Health of SQL Server Instance: As shown in the following image, the health status of this server is critical and the following issues exist:
  • The SQL agent has stopped
  • The OS processor queue length is 5
  • The master database has not been backed up for 7314 days

Clicking on a server name brings up several helpful charts for you to highlight and troubleshoot.

There are many different interactive charts where you can select a timeline and filter data across all charts. Suppose I want to see CPU, memory and other information when a deadlock occurs in SQL Server. I can filter information from session table like this.

If the memory usage of the SQL Server process is high, you can use the Resources tab to get memory-related data. You can analyze various SQL memory usage items and their changes from baseline, including:

• Buffer cache: free and active pages
• Operating System Paging
• cache hit ratio
• Page life expectancy (seconds)

The details page in the SQL Diagnostics Manager has predefined metrics whose status is calculated based on the current data. For example, my processor's queue length is marginal, while other parameters like blocking sessions and queue length are fine. To view this data, go to Server > Overview > Details to view details, warnings, and critical thresholds.

Block

Another common performance issue with SQL Server is prolonged blocking. You can quickly get this data using the SQL Diagnostic Manager by viewing the Session Summary page.

The session graph shows the total number of deadlocks, starting blocks, blocked sessions, and active sessions. From the figure below, we can understand the following:

• no standstill
• There are 4 blocked sessions
• There is 1 blocking period
• There are 5 active sessions

This is another blocking view that you can get from Server > Session > Summary.

Click the Blocking tab (Server > Sessions > Blocking) to view the blocking tree with details for each blocking and blocking session. In this case we can see that the leading interceptor is session ID 54.

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

Below are session ID details including the last command run, tempdb usage and locking information.

To kill the root block, you can right-click the session ID and select KillSession.

Then confirm that you want to end the session.

You will be notified when the session ends.

If we look again at the block session diagram, we can see that the block has been resolved.

dead end

Another common problem in SQL Server is deadlock, which is caused by contention between two or more sessions for their lock requests. You can use the deadlock graph to identify and investigate deadlocks in SQL Diagnostic Manager. Below we see a list of deadlocks that have occurred and when they occurred.

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

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

Poorly performing query

SQL Diagnostic Manager uses query screens to capture SQL Server workloads. It can collect data in the following different ways:

If you experience performance issues, you can view queries against SQL text, application, or database. For example, the screenshot below shows the most common resource queries in my environment and their CPU time, reads, and writes.

To view the query details, right-click the query and select View Query Text to view the query history.

Query history provides historical data on execution, average duration, and average reads and writes, as shown below.

Clicking the Details column displays the query execution plan (estimated) in graphical and XML format.

You can also use diagnostic queries on the Query History page to provide suggestions on how to optimize your query.

Problems were identified during the analysis

The SQL Diagnostic Manager provides insight into your SQL Server by analyzing the server and making recommendations on issues to resolve. Click Analyze to view a list of configuration, query tuning, and tuning recommendations.

Here is a breakdown report of my SQL instance with each item sorted by priority.

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

clickscript optimizationYou will get a script like below which can then be copied or run directly on the SQL Server instance.

finally

This article describes how SQL Diagnostic Manager can identify and troubleshoot SQL Server performance issues. Some important conclusions are:

• SQL Diagnostic Manager helps database administrators monitor and troubleshoot SQL Server performance issues.
• The SQL Diagnostic Manager can help you find and fix poorly performing queries.
• You can use the SQL Diagnostic Manager to monitor blocking and deadlocks.
• With the SQL Diagnostic Manager you can manage SQL Server more proactively.
• With the SQL Diagnostic Manager you can view what is happening now or what has happened in the past to further improve troubleshooting.

Readers are encouraged to try SQL Diagnostic Manager to identify and troubleshoot performance-related issues in their own database environments. Idera offers a 14-day free trial.Click hereStart now.

Next Step

• Learn more about the SQL Diagnostics Manager
• start for free

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

About the author

Article last updated: 05/23/2023