SQL Workload

Question

Experts ,If we need to look into the threshold for SQL workload :

For CPU , it shows a value in % as in 45% so we can look out for a threshold say 80%
For Memory , as the SQL occupies all the allocated max memory , how do we identify a threshold for the utilized memory so that an alarm can be raised
For Storage , probably it would depend on the vendor allocated throughput and the utilized one

Answer 1

Hi vishu-2318,
To monitor memory, we can use the following counters:

• Memory: Pages/sec

The average Pages/sec value should be below 50.

• Memory: Page Faults/sec

The normal values are 10 to 15, but even 1,000 page faults per second can be normal in specific environments.

• Memory: Available bytes

If the memory Available bytes value is constantly lower than 100 MB, it indicates that there is insufficient memory on the server, or that there is an application that is not releasing memory.

• SQL Server: Memory Manager: Total Server Memory (KB)

If the Total Server Memory (KB) value is consistently high, it means that SQL Server is constantly using a lot of memory and that the server is under memory pressure.

• SQL Server: Memory Manager: Target Server Memory (KB)

When the Total Server Memory and Target Server Memory values are close, there’s no memory pressure on the server.

• SQL Server: Buffer Manager: Buffer Cache Hit Ratio

the Buffer Cache Hit Ratio value would be 100. The recommended value for Buffer Cache Hit Ratio is over 90. When better performance is needed, the minimal acceptable value is 95. A lower value indicates a memory problem.

• SQL Server: Buffer Manager: Page life expectancy

The normal values are above 300 seconds (5 minutes) and the trend line should be stable. It’s recommended to monitor the values over time, as frequent quick drops indicate memory issues. Also, a value drop of more than 50% is a sign for deeper investigation.
Please refer to SQL Server memory performance metrics which might help.

To monitor Disk I/O, Disk counters that you can monitor to determine disk activity are divided into the following two groups:

• Primary

PhysicalDisk: Avg. Disk sec/Write
PhysicalDisk: Avg. Disk sec/Read

• Secondary

PhysicalDisk: Avg. Disk Queue Length
PhysicalDisk: Disk Bytes/sec
PhysicalDisk: Disk Transfers/sec

Please refer to Monitoring Disk Usage and SQL Server disk performance metrics which might help.

Best Regards,
Amelia