Best practices for monitoring Microsoft SQL Server performance

First of all, what does SELECT @@version report?

The first place to go to troubleshoot a situation like this is to go to the SQL Server errorlog. The current ERRORLOG file is not so much of interest, but the one to look in is the one that was active last time you had to restart SQL Server because of this condition. Say that your last restart of SQL Server was due to planned patching, but the restart before that was due to SQL Server being unresponsive. Then you should look in the file ERRORLOG.2. What you should look for? Stack dumps and other dumps. They are big and huge and impossible to miss. The type of dumps I primarily have in mind are out-of-memory conditions and hung schedulers. What to do if you find something? Come back and tell us what you find; there are so many alternatives that it would be useless to cover all beforehand.

One thing to try if you have this condition is to connect on the DAC, the Dedicated Admin Connection. Unless you have enabled the remote DAC, you need to connect from SSMS locally on the server itself, by prefixing the server/instance name with ADMIN:. You may get an error when you do this, because SSMS also wants to connect with Intellisense, but there is exactly one admin connection available. Even if SQL Server does not accept other connections, you may still be able to connect on the DAC, since it has a dedicated scheduler. Once connected, you can run diagnostic queries, for instance my beta_lockinfo or sp_whoisactive to see what is going on the server, and possibly kill queries that are taking up to much resources.

Again, there are so many alternatives for what could be the underlying reason for this condition that it would be useless to cover all in a single post, so I will stop here. But I will end with a few questions:

• How much RAM to you have?
• What is the value for max server memory?
• How many user databases are there on this server?
• Do you have Query Store enabled for one or more databases?

Start with establishing a performance baseline during normal operation. Use Performance Monitor (PerfMon), SQL Server Management Studio activity reports, and Dynamic Management Views (DMVs) to capture CPU, memory, disk I/O, waits, and query activity over time. Without a baseline, it is difficult to determine what “abnormal” looks like during an incident.

Monitor the key SQL Server bottlenecks consistently. Focus on CPU utilization, Page Life Expectancy (PLE), buffer cache hit ratio, disk latency, blocking, deadlocks, and wait statistics. High waits such as PAGEIOLATCH, CXPACKET/CXCONSUMER, WRITELOG, or LCK_M_* often indicate the root cause category. Disk latency above acceptable thresholds or sustained CPU saturation are common causes of intermittent freezes.

Enable and review SQL Server Extended Events or SQL Server Profiler carefully for troubleshooting. Extended Events are preferred because they have lower overhead. Capture long-running queries, deadlocks, login storms, excessive recompiles, and timeout events. Also review the SQL Server error log and Windows Event Viewer for storage, memory, or driver-related errors that coincide with outages.

Implement proactive monitoring and alerting instead of relying on manual checks. Use tools such as SQL Server Management Pack, Azure Monitor, SentryOne, Redgate SQL Monitor, or SolarWinds DPA to generate alerts for blocked sessions, failed jobs, low disk space, high waits, or memory pressure before the server becomes unresponsive.

Review query and index health regularly. Poorly optimized queries, missing indexes, parameter sniffing, outdated statistics, or fragmented indexes can cause severe intermittent slowdowns. Use Query Store to identify regressions and track plan changes over time. Ensure statistics updates and index maintenance are scheduled appropriately.

Verify the underlying infrastructure as well. Many “SQL Server” performance problems originate from virtualization, storage, antivirus exclusions, firmware, drivers, or insufficient RAM. Confirm that SQL data files, log files, and TempDB are placed on fast storage and that antivirus scanning excludes SQL Server directories and processes.

When the issue occurs, avoid immediately rebooting if possible. First capture diagnostic data using tools such as sp_WhoIsActive, DMVs, memory dumps, or PerfMon logs. Reboots clear the evidence needed to identify the actual root cause, making long-term resolution much harder.

More at https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-ver17

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin