Geek Answers Handbook

Query Cost in SQL Server Management Studio

How does SSMS calculate the cost of the request (relative to the batch) displayed when you select to include the actual execution plan?

Basically, I have several saved execution plan XML files that I want to compare with each other. It should calculate this number somehow from the data in the XML files, but I do not see how this is done. If I open one of my .sqlplan files that contains more than one query, it will correctly display those related to batch values. I just want to do it myself.

FYI: To get this XML, on the Execution Plan tab, right-click and select "Show XML Execution Plan ..."

+4
source share
2 answers

Each /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple has a StatementSubTreeCost attribute.

They are summarized through their parties and their share is calculated.

You can manually edit them in an XML file, save it with the SQLPLAN extension SQLPLAN and open it using SSMS . You will see that the relative costs will be recounted.

+3
source

This is a long story to say in simple words that cost is the amount of physical and logical reads performed by the database in bytes and the cost of the processor. For each operation, data must be retrieved, if reading is performed from the hard drive, we physically read it, otherwise the database reads it from the cache. This is the estimated cost of the operator . For each step, you have the cost of it and the cost of the subtree, if any. then the sum of costs is calculated as a percentage for the entire implementation plan and presented, this is the Estimated cost of the subcontract (The total cost of the query optimizer to perform this operation and all operations preceding it in the same subtree.)

More details

+3
source

More articles:


All Articles