SQLServer anomalies

The SQLServer collector allows to insert the following types of anomalies in an Anomalies Profile:

Buffer cache hit ratio

The Buffer Cache Hit Ratio counter shows how often SQL Server is able to pull data from the buffer cache. When SQL Server can find the data pages it needs in the buffer cache, it doesn't have to perform a physical I/O. If you notice that this ratio is dropping over time, consider adding memory. The default anomaly conditions are:

Severity 1: < 90%
Severity 2: < 80%
Severity 3: < 70%

CPU %

Percentage of CPU used by SQLServer. The default anomaly conditions are:

Severity 1: > 80%
Severity 2: > 85%
Severity 3: > 90%

Connection Rate

The connection rate represents the number of connections per second. The default anomaly conditions are:

Severity 1: > 50 logins/s
Severity 2: > 75 logins/s
Severity 3: > 100 logins/s

Deadlocks/sec

This counter shows the number of deadlocks per second. If you see anything above 0, your users and applications will experience problems. Their queries will abort and the applications may fail. The default anomaly conditions are:

Severity 1: > 0 deadlocks/s

Free Memory

When there are no free pages in the buffer pool, the memory requirements of your SQL Server may have become so intense that the lazy writer or the check pointing process is unable to keep up. Typical signs of buffer pool pressure are a higher than normal number of lazy writes per second or a higher number of checkpoint pages per second as SQL Server attempts to empty the procedure and the data cache to get enough free memory to service the incoming query plan executions. This is an effective detection mechanism that indicates that your procedure or data cache is starved for memory. Either increase the RAM that is allocated to SQL Server, or locate the large number of hashes or sorts that may be occurring.  The default anomaly conditions are:

Severity 1: < 20 MBytes
Severity 2: < 15 MBytes
Severity 3: < 10 MBytes

Log Flush Wait Time

Wait time to flush the log.  The default anomaly conditions are:

Severity 1: > 200 ms/s
Severity 2: > 500 ms/s
 

Percent log used

Percentage of space in the log that is in use. The default anomaly conditions are:

Severity 1: > 70%
Severity 2: > 80%
Severity 3: > 90%

Procedure cache hit ratio

Ratio between cache hits and lookups. The default anomaly conditions are:

Severity 1: < 80%
Severity 2: < 70%
Severity 3: < 60%

Re-Compile percentage
Compilation is a significant part of a query's turnaround time. The objective of the cache is to reduce compilation by storing compiled queries for later reuse, thus eliminating the need to recompile queries when later executed. However, each unique query must be compiled at least once. Query recompilations can be caused by the following factors:

The default anomaly conditions are:

Severity 1: > 70%
Severity 2: > 75%
Severity 3: > 80%

 





Go back to the Learning center:


Copyright © AgileLoad. All rights reserved.
Agile Load testing tool| Contact AgileLoad | Terms of Use | Sitemap