Symptoms: The number of SQL batch requests executed per second has risen over the threshold level.
Impact: Medium
This metric represents the number of SQL requests that are being executed per second and is an indication server load.
Default settings are:
| CRITICAL : | ||
| MEDIUM : | ||
| LOW : | 2000 | |
| Timespan : | more than 15 minutes |
Expected behavior :
There is no standard metric for a number of requests. You should set your own thresholds after reviewing past behavior.
Possible causes
Poor coding Priority: Medium
Best practices for coding should be strictly enforced, to ensure that all queries optimally consume and then release resources.
Recommended action :
Using AimBetter monitor, identify any queries that have remained open for an inordinate length of time. Recover the query plan, and see whether any improvements are available.
Index missing or corrupted Priority: Medium
Missing or corrupted indexes can lead to queries working inefficiently and consuming resources.
Recommended action :
See our recommendations regarding memory optimization here.
Background
SQL Server Batch Requests metric represents the number of SQL requests that are being executed per second. This metric is a strong representation of how the server is performing, but should not be viewed in isolation.
High activity as indicated by this metric might tend to cause bottlenecks and should be treated accordingly, either (or both) by improvements in coding/indexing, or by the faster network, disks, and additional CPU capacity, because sometimes low duration CPU with a high number of executions may cause problems.
It is necessary to correlate with other Observer resources, especially CPU usage, disk, and network traffic, memory levels, etc. in order to get an overall understanding of server’s load. The target for good SQL Server behavior is to achieve the highest number of requests per second while consuming the lowest level of resources.
Peak throughput is heavily dependent on the architecture of the system, coding standard of queries and procedures, tables and index design. A persistent high reading of batch requests will be an indicator of a bottleneck or hardware deficiency in one or more areas.
AimBetter provides a comprehensive overview of all the relevant metrics that make Batch Request levels such a powerful gauge, compared to the raw information that PerfMon will return, which is restricted to the single picture of request behavior without reference to all the other contributing factors to overall performance.
