“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.
“Total Lock Promotions” 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.
“Lock Promotions” data can:
Help you to detect whether lock promotion in your application 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 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.
For more information about configuration parameters, see Chapter 5, “Setting Configuration Parameters,” in the System Administration Guide, Volume 1.
