SQL Workload

Vishu 1,516 Reputation points
2021-07-07T04:39:10.783+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,585 questions
0 comments No comments
{count} votes

Accepted answer
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-07-07T07:39:07.307+00:00

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful