Just recently, my local .NET user group asked me to present at their meeting on the topic of graphical execution plans. I love presenting for developers on SQL Server topics. I\u2019m often describing something well known by database administrators, but completely new to the devs. I think the group learned a lot from me that evening and were glad they attended.<\/p>\n
Microsoft provide graphical execution plans to show just what is going on behind the scenes when a query runs. For example, the plan tells you which tables, indexes, or views were accessed and how joins are physically performed. It\u2019s not a mystery once you know what is going on.<\/p>\n
Many people working with T-SQL don\u2019t think they need to care about execution plans. As long as the query returns the expected results, that\u2019s good enough. The problems begin, however, when just adding a column to a query causes it to slow down, sometimes slowing it down to an extreme degree. You wouldn\u2019t think adding a column would make such a difference, but it can cause the optimizer to come up with a completely different plan.<\/p>\n
In typical application development, the code tells the computer what to do. SQL queries, on the other hand, are declarative. You tell SQL Server what you want. It\u2019s up to the optimizer to figure out how to do it. A single query can be pages and pages long, made up of several levels of nested subqueries, common table expressions, unions, complex WHERE clauses with even more subqueries, calculations, and more. Imagine writing an algorithm to make sense of that! There are thousands of possible plans, and the optimizer must try<\/em> to find the best one in milliseconds.<\/p>\n Another thing that developers may not realize is that using LINQ or another tool for database access still ends up creating T-SQL queries. Often, these queries are less than optimal, and graphical execution plans can help you figure that out.<\/p>\n It\u2019s very frustrating when the query runs slowly in the application but runs fast when you copy and paste it into SSMS (SQL Server Management Studio) or SQL Operations Studio. It\u2019s likely that the optimizer has come up with a completely different plan in SSMS due to varying settings or parameter values. This is where a monitoring tool for SQL Server can really come in handy. These tools (one of them is Redgate\u2019s SQL Monitor<\/a>) will show you the worst performing queries and provide the actual execution plans to you without your attempts to recreate them.<\/p>\n There were over 40 developers in my session, and only a couple of them knew about graphical execution plans before the talk.\u00a0When you don\u2019t understand execution plans, SQL Server can be a total mystery. The performance of queries becomes unpredictable. Yet, everything can be explained when you know where to look.<\/p>\n Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.<\/p>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":" Just recently, my local .NET user group asked me to present at their meeting on the topic of graphical execution plans. I love presenting for developers on SQL Server topics. I\u2019m often describing something well known by database administrators, but completely new to the devs. I think the group learned a lot from me that……<\/p>\n","protected":false},"author":110218,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[47125],"tags":[5842],"coauthors":[11292],"class_list":["post-80284","post","type-post","status-publish","format-standard","hentry","category-editorials","tag-sql-monitor"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80284","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/110218"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=80284"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80284\/revisions"}],"predecessor-version":[{"id":80303,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/80284\/revisions\/80303"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=80284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=80284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=80284"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=80284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Commentary Competition<\/h4>\n