“Lock Promotions” reports the number of times that the following escalations took place:
“Ex-Page to Ex-Table” – Exclusive page to exclusive table.
“Sh-Page to Sh-Table” – Shared page to shared table.
“Ex-Row to Ex-Table” – Exclusive row to exclusive table.
“Sh-Row to Sh-Table – Shared row to shared table.
“Sh-Next-Key to Sh-Table” – Shared next-key to shared table.
The “Total Lock Promotions” row reports the average number of lock promotion types combined per second and per transaction.
If no lock promotions took place during the sample interval, only the total row is printed.
If there are no lock promotions, sp_sysmon does not display the detail information, as the example for this section shows.
“Lock Promotions” data can:
Help you detect if lock promotion in your application to is a cause of lock contention and deadlocks
Be used before and after tuning lock promotion variables to determine the effectiveness of the values.
Look at the “Granted” and “Waited” data above for signs of contention. If lock contention is high and lock promotion is frequent, consider changing the lock promotion thresholds for the tables involved.
You can configure the lock promotion threshold either server-wide or for individual tables.
See information on locking in the System Administration Guide.
