Lock management and query cost in SQL Server

All we need is an easy explanation of the problem, so here it is.

In the execution of a query we have the estimated subtree cost of a statement and we can also look at e.g. CPU time or logical reads.

Does the estimated subtree cost incorporate the cost of acquiring lock and lock escalation in its formula? Also I guess the process of managing locks requires CPU. Does the CPU time of a query include the CPU time used for lock management and lock related tasks?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The estimated subtree cost is a rough estimate of query cost, based on the query execution time on a particular computer 20 years ago. It’s used by the cost based query optimizer to evaluate which query plan to choose.

It’s calibrated to account for locking cost, but probably is too rough an estimate to account for lock escalation.

CPU cost of actual query plans is the best single measure of query cost, as most query operations end up consuming CPU time. In particular CPU time accounts for logical IO, locking, reading data pages, performing calculations and comparisons, etc.

The only part of a query elapsed time that isn’t accounted for by CPU time is waits, and waits are normally not inherent to a query, but caused by the interaction with other sessions, or by the need to read data not in the page cache, or to commit a transaction to the log file, etc.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply