Database Contention Pressure
Category: Contention · Also known as: High Database Contention Metacounter score
WISdom’s composite contention pressure score for the database is high this minute - a single rolled-up health number built from the underlying counters.
What this finding means
Section titled “What this finding means”WISdom’s composite contention pressure score for this database, on a 0 to 100 scale, was high for the minute. It rolls up the latch, lock, and blocking-related counters and waits attributable to this database into one number.
Why it matters
Section titled “Why it matters”Contention is the pressure type that scales worst: it gets sharply worse as concurrency rises, so a workload that is fine at 50 sessions can fall over at 100 with no change in hardware or code. Catching the trend while it is still a score rather than an outage is the value here.
How WISdom detects it
Section titled “How WISdom detects it”WISdom rolls the contention performance counters and wait statistics for the database into one weighted score per minute, then compares it to the four-week maximum for this minute-of-day. The rule is always evaluated and returns a score every minute.
| Collected from | WISdom Database Contention metacounter - a weighted composite of the perf counters and wait stats for contention at the database level. |
| Compared with | Max value for category during 15 minutes before and 15 minutes after of the same minute in the past 4 weeks |
| Related screen | Performance Metrics |
How it is scored
Section titled “How it is scored”This rule is always evaluated, so it reports a value every minute whether or not it is elevated. It contributes an incident score of 2 when the value exceeds the four-week maximum for this minute-of-day.
How to read the numbers
Section titled “How to read the numbers”Score against History max. Open the counters to find out which kind of contention: page latch contention on allocation structures behaves nothing like lock contention on user data, and they have unrelated fixes. Correlate with the session activity for the minute, because contention concentrates on a small number of objects.
What normally causes it
Section titled “What normally causes it”- Page latch contention on hot pages, typically sequential inserts on an ascending key.
- tempdb allocation contention on system pages under high concurrent object creation.
- Lock contention on a small set of frequently updated rows, such as a counter or status table.
- Long transactions holding locks and widening the window for overlap.
What this finding is not
Section titled “What this finding is not”- Not the same as blocking. Blocking is one visible chain. Contention is the aggregate pressure, most of which never forms a chain long enough to be noticed.
- Not a hardware problem. Faster disks and more cores do not fix a hot page; spreading the key does.
- Not comparable between databases. The score is relative to each database’s own history.
What are some possible resolutions?
Section titled “What are some possible resolutions?”The options below are ranked. Option 1 fixes the cause where it lives and costs nothing; option 3 spends money or escalates, so try it last.
1. Find the object everything queues on
Section titled “1. Find the object everything queues on”Contention concentrates. Identify the hot object or page and you normally find one statement or one insert pattern behind it.
2. Spread the hotspot
Section titled “2. Spread the hotspot”Sequential inserts into one page, a single-row counter table, or a narrow index range all serialize. Partition, hash, or spread the key to break the queue.
3. Reduce transaction overlap
Section titled “3. Reduce transaction overlap”Shorter transactions, and lower isolation where correctness allows, mean fewer sessions contending for the same resource at the same moment.