{"id":86,"date":"2008-08-28T20:33:34","date_gmt":"2008-08-28T18:33:34","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=86"},"modified":"2008-08-28T20:33:34","modified_gmt":"2008-08-28T18:33:34","slug":"an-example-exec-plan","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2008\/08\/28\/an-example-exec-plan\/","title":{"rendered":"An example exec plan"},"content":{"rendered":"

Back to the technical posts…<\/p>\n

I’m going to take an example of a very simple execution plan that I used in a presentation a few weeks back and go over it in some detail, pointing out information that is available and what can be read from the plan.<\/p>\n

The execution plan can be downloaded (in xml format) here – ExecPlanDem01.zip<\/a><\/p>\n

The query that the plan came from is a very simple two table query. It’s not a very optimal plan, but that’s because I forced an index hint in order to generate a more interesting plan. Without the hint, it’s a simple exec plan with two index seeks and a nested loop join.<\/p>\n

Overview<\/strong><\/p>\n

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

The first thing that can be seen from the execution plan is that most of the cost of the query is in two operations, a key lookup (formerly called a bookmark lookup) and a clustered index seek. The high cost of the key lookup is a good sign that the query is using an inappropriate index. (in a future post I’ll discuss using the exec plan to choose indexes)<\/p>\n

The second thing that can be seen is that there is a filter operator. This again suggests that there is not an appropriate index, as this plan means that SQL will first select rows from the table and later filter out rows that aren’t needed.<\/p>\n

Index seek<\/strong><\/p>\n

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

The first thing to notice about the index seek is that there are two seek predicates, one an equality, one an inequality. Having them both as seek predicates indicated that the columns in this index are in the correct order to support the two predicates. (I’ll do an entire post sometime on index key column ordering)<\/p>\n

The second thing to note is that the estimated and actual rows are identical. This is good, it indicates that the statistics on this index are up to date and that the optimiser managed to get an accurate estimate of the rows that would be returned.<\/p>\n

Key Lookup<\/strong><\/p>\n

\"\"<\/a>The presence of a key lookup operator indicates that the nonclustered index that was used to locate the rows affected by the query did not have all the columns required by the query. The missing columns must be looked up from the clustered index. The output list shows what columns were fetched by the key lookup. This can help when altering indexes to get a covering index.<\/p>\n

One important thing to note is that the estimated rows for this operator is 1. That’s not a cardinality problem, it’s because the estimated rows is per execution of the operator and the actual rows is total over all executions. The number of times an operator is executed is stored within the XML, however the SQL 2005 management studio doesn’t show it. Management studio in SQL 2008 does.<\/p>\n

This shows one of the reasons why the key lookup can be a bottleneck. For each row returned by the index seek, the key lookup does a clustered index seek returning a single row. For a small number of rows, that’s not too much or a problem, for hundreds of rows it can be a major bottleneck in the query.<\/p>\n

Filter<\/strong><\/p>\n

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

The last interesting part of the execution plan is the filter.<\/p>\n

Having a filter in the exec plan indicates that there was some condition that could not be evaluated as part of the index seek\/scan. This may be because in involves an aggregate, because it involves columns in multiple tables or, as in this case, because the column was not part of the index and had to be looked up separatly.<\/p>\n

As with the index seek, the estimated and actual row counts are very similar, indicating that the cardinality estimate is accurate for this query.<\/p>\n

So, that’s most of the interesting things that can be gleaned from the exec plan. I have a couple more posts planned in the series on reading exec plans, one showing a more complex plan and the other going into more detail on the % costs that the plan shows.<\/p>\n

That’s all for now. I’ll see if ‘i can dig up a more complex plan to work through.<\/p>\n","protected":false},"excerpt":{"rendered":"

Back to the technical posts… I’m going to take an example of a very simple execution plan that I used in a presentation a few weeks back and go over it in some detail, pointing out information that is available… (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":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[23,15],"tags":[],"class_list":["post-86","post","type-post","status-publish","format-standard","hentry","category-execution-plans","category-sql-server"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-1o","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/86","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=86"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}