{"id":2072,"date":"2018-02-13T16:30:30","date_gmt":"2018-02-13T14:30:30","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=2072"},"modified":"2018-01-29T23:42:26","modified_gmt":"2018-01-29T21:42:26","slug":"obsessing-over-query-operator-costs-2","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2018\/02\/13\/obsessing-over-query-operator-costs-2\/","title":{"rendered":"Obsessing over query operator costs"},"content":{"rendered":"

A common problem when looking at execution plans is attributing too much meaning and value of the costs of operators.<\/p>\n

The percentages shown for operators in query plans are based on costs generated by the query optimiser. They are not times, they are not CPU usage, they are not IO.<\/p>\n

The bigger problem is that they can be completely incorrect.<\/p>\n

Before digging into the why of incorrect percentages, let\u2019s take a step back and look at why those costs exist.<\/p>\n

The SQL query optimiser is a cost-based optimiser. It generates good plans by estimating costs for each query operator and then trying to minimise the total cost of the plan. These costs are based on estimated row counts and heuristics.<\/p>\n

The costs we see in the query plan are these compilation time cost estimates. They\u2019re compilation-time estimations, which means that they won\u2019t change between one execution of a query using a particular plan and another query using the same plan, even if the parameter values are different, even if the row counts through the operators are different.<\/p>\n

Since the estimations are partially based on those row counts, that means that any time the query runs with row counts different to what were estimated, the costs will be wrong.<\/p>\n

Let\u2019s look at a quick example of that.<\/p>\n

\"Cost1\"<\/a><\/p>\n

There are no customers with an ID of 0, so the plan is generated with an estimation of one row being returned by the index seek, and one row looked up to the clustered index. Those are the only two operators that do any real work in that plan, and each is estimated to read and fetch just one row, so each gets an estimation of 50% of the cost of the entire query (0.0033 it be specific)<\/p>\n

Run the same query with a different parameter value, plans are reused and so the costs are the same.<\/p>\n

\"Cost2\"<\/a><\/p>\n

That parameter returns 28 rows, the index seek is probably much the same cost, because one row or 28 continuous rows aren\u2019t that different in work needed. The key lookup is a different matter. It\u2019s a single-row seek always, so to look up 28 rows it has to execute 28 times, and hence do 28 times the work. It\u2019s definitely no longer 50% of the work of executing the query.<\/p>\n

The costs still show 50%, because they were generated for the 0-row case and displayed here. They\u2019re not run-time costs, they\u2019re compile time, tied to the plan.<\/p>\n

Another thing can make the cost estimations inaccurate, and that\u2019s incorrect costing calculations by the optimiser. Scalar user-defined functions are the easiest example there.<\/p>\n

\"CostsOff\"<\/a><\/p>\n

The first query there, the one that\u2019s apparently 15% of the cost of the batch, runs in 3.2 seconds. The second runs in 270 ms.<\/p>\n

The optimiser gives scalar UDFs a very low cost (they have their own plans, with costs in them though) and so the costs for the rest of the query and batch are meaningless.<\/p>\n

The costs in a plan may give some idea what\u2019s going on, but they\u2019re not always correct, and should not be obsessed over, especially not when the plan\u2019s a simple one with only a couple of operators. After all, the cost percentages add to 100% (usually).<\/p>\n","protected":false},"excerpt":{"rendered":"

A common problem when looking at execution plans is attributing too much meaning and value of the costs of operators. The percentages shown for operators in query plans are based on costs generated by the query optimiser. They are not… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New blog post: Obsessing over query operator costs","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[23,15,16],"tags":[],"class_list":["post-2072","post","type-post","status-publish","format-standard","hentry","category-execution-plans","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-xq","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=2072"}],"version-history":[{"count":2,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2072\/revisions"}],"predecessor-version":[{"id":2216,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2072\/revisions\/2216"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=2072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=2072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=2072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}