SQL Server Basic Monitoring

In monitoring servers you should first know what to look for. Based on my experience database problems can be categorized into five categories.

1. CPU
2. Memory
3. Network
4. I/O Bottlenecks
5. Slow Queries

These are the queries we can  use to troubleshoot where the problem is.

CPU

DECLARE @BatchRequests BIGINT;

 

SELECT @BatchRequests = cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

 

WAITFOR DELAY '00:00:10';

 

SELECT (cntr_value - @BatchRequests) / 10 AS 'Batch Requests/sec'

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec';

The Batch Requests/sec value depends on hardware used, but it should be under 1000. The recommended value for SQL Compilations/sec is less than 10% of Batch Requests/sec and for SQL Re-Compilations/sec is less than 10% of SQL Compilations/sec