{"id":2075,"date":"2015-08-14T00:00:00","date_gmt":"2015-08-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/basic-sql-server-performance-troubleshooting-for-developers\/"},"modified":"2022-05-06T17:37:20","modified_gmt":"2022-05-06T17:37:20","slug":"basic-sql-server-performance-troubleshooting-for-developers","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/basic-sql-server-performance-troubleshooting-for-developers\/","title":{"rendered":"Basic SQL Server Performance Troubleshooting For Developers"},"content":{"rendered":"
\n

Uncovering Indexing Problems with Execution Plans<\/h1>\n

Often, developers will use an Object-Relational Mapping (ORM) tool, such as Entity Framework or nHibernate, to auto-generate SQL code that SQL Server then executes to return the required data. Usually, this approach works well, and the productivity benefits for the developer are obvious. However, it can also result in SQL that returns too much data and has inefficiencies that causes SQL Server to perform far more work than necessary to return the required data. In such cases, the developer needs easy access to SQL Server tools that provide insight into how exactly SQL Server chose to execute their ORM-generated query, and possible causes of poor performance, without dragging them too deeply into SQL Server internals.<\/p>\n

One obvious candidate is the SQL Server execution plan<\/strong>. This article serves as a jumpstart into execution plans for the developer who occasionally has to troubleshoot SQL queries that currently don’t perform to specification, and needs deeper insight into the SQL their data access layer generated, and how SQL Server chose to execute it.<\/p>\n

It describes what an execution plan is and why it is important, and then looks at some examples of how execution plans can reveal query performance problems relating to m<\/strong>issing o<\/strong>r<\/strong> poorly-designed <\/strong>indexes, meaning that SQL Server has available inefficient data access paths and performs more IO than necessary to return the required data.<\/p>\n

Of course, execution plans can reveal a range of other potential query problems beyond index issues, and subsequent articles will drill deeper into specific problems in SQL, such as ad-hoc queries, data type mismatches, function misuse and so on, which cause excessive reads, plan cache bloat and other problems.<\/p>\n

What is an execution plan?<\/h1>\n

SQL Server executes each SQL query according to a set of instructions called an execution plan<\/strong>, which is devised by a component of the relational engine called the SQL Server Query Optimizer. An execution plan specifies the set of operators<\/strong> required to implement physically each logical step of the submitted query, and the order in which those operations will occur.<\/p>\n

Based on information it has about the available data access paths (table, indexes) and statistical information about the data in those tables, the optimizer makes judgements about how much data will be returned from each source, whether indexes are available that will reduce the cost of data retrieval, and so on. Based on these judgements, it selects a plan that it estimates will have the lowest overall execution cost.<\/p>\n

Figure 1 shows the graphical execution plan for a simple query against the AdventureWorks2012<\/code> database, as viewed in SQL Server Management Studio.<\/p>\n

\"1\"<\/p>\n

\u00a0<\/p>\n

Figure 1<\/p>\n

SQL Server has a large set of specialized operators from which to choose, in order to implement each task required in gathering the data and then manipulating it into the correct form to return to the user. Each operator in a plan performs one specific task. The plan in Figure 1 has six operators<\/strong>, connected by arrows, pointing right to left, which represent the flow of data from one operator to the next.<\/p>\n

The Basics of Reading Plans<\/h2>\n

The natural way to read a plan is to follow the data flow, right to left. The two operators on the right-hand side (an Index Seek<\/strong> and Clustered Index Scan<\/strong>), and the one at the bottom (Clustered Index Seek<\/strong>), are data access operators and represent various means of reading the data from the underlying table and indexes.<\/p>\n

\n

More on Reading Execution Plans<\/h4>\n

I can’t cover more than the absolute basics of reading graphical execution here. Please see Books Online<\/a> or Fabiano Amorim’s eBook<\/a> for a description of all execution plan operators, and Grant Fritchey’s SQL Server Execution Plans<\/a> book (available as a free download) for a full tutorial on capturing, reading and interpreting plans.<\/p>\n<\/div>\n

The optimizer chose first to implement the logical INNER JOIN<\/code> between the Location<\/code> and ProductInventory<\/code> tables, i.e.<\/em> only returning matching rows, and it chose to implement the join, physically, using a Nested Loops<\/strong> operation, though it can choose other physical join implementations (Hash Match<\/a> or Merge Join<\/a>) depending on the number of rows returned and ordering of the data. It seeks an index on the outer table (Location<\/code>) for 'Paint'<\/code> and for each row it finds, it uses that row’s LocationID<\/code> value to perform a search on the inner table (ProductInventory<\/code>) for matching rows. In this case, it performs a scan of ProductInventory<\/code> because the available index is ordered on ProductID<\/code>, not LocationID<\/code>. The optimizer uses another Nested Loops<\/strong> operation to join this data stream with matching rows in the Product<\/code> table. Finally, we see a SELECT<\/strong> operator, representing the final result set.<\/p>\n

While it’s natural to read a plan right-to-left, the actual execution occurs left-to-right. Each operator supports a method called GetNext<\/code>(<\/code>)<\/code>, which is simply a request for a row from the operators immediately to the right. Streaming operators pass on each row, to the left, as it becomes available. All the operators in Figure 1 are streaming. However some operators are partially blocking, meaning they must complete some part of their work before passing on the first row, such as building a hash table in memory (e.g. for a Hash Match<\/strong> join), or collecting all the rows in a grouping set in order to perform an aggregation (e.g. aggregating operators such as a Stream Aggregat<\/strong>e<\/strong>), and others, such as a Sort<\/strong> operator, are completely blocking and must collect the entire data set before passing on the first row.<\/p>\n

\n

Look out for Sort Warnings in Execution plans<\/h4>\n

A yellow exclamation mark on a Sort operator indicates that SQL Server had to spill the sort operation to disk, in tempdb<\/code>. These Sort spills are often very expensive operations. I’ll cover an example of this on the next article=”float:left”><\/p>\n<\/div>\n

In order to read a plan, we must understand what each operator does and how much data is flowing between each operator. In order to use a plan to uncover possible query problems, we must use the all the information in the plan to understand why the optimizer chose particular operators, and to spot common signs of trouble. This often means that we need to explore the Properties<\/strong> of each operator.<\/p>\n

Operator Properties<\/h2>\n

For each operator, the plan displays a wealth of property values. Some of these properties are operator-specific while others are common to all operators. The plan in Figure 1 (an estimated<\/em> plan; more on this shortly) shows the properties for the Index <\/strong>Seek<\/strong> operator on the Location<\/code> table. We can see properties such as the number of rows returned, the number of times the operator was called, various estimated costs associated with executing the operator, the cardinality of the table, and more. Notice that, in this case, the Seek Predicates<\/strong> property shows how our logical WHERE<\/code> clause was implemented as part for the Index Seek<\/strong> operation.<\/p>\n

\"2\"<\/p>\n

\u00a0<\/p>\n

Figure 2<\/p>\n

Why Execution Plans are important<\/h2>\n

Straight away, we can see that an execution plan can reveal very valuable information about how SQL Server chose to execute the query we submitted. In this simple example, we can immediately see the objects accessed by the query and the order in which they were accessed, which indexes were used (or not), and how joins were implemented. In more complex queries, the plan would also reveal when sorting occurred, how calculations and aggregation were performed, and more. If we know how to read a plan and can mine the information they divulge, we use the plans to look for common signs of potential trouble, which lead to poor query performance.<\/p>\n

More fundamentally, the mere existence of a component called the Query Optimizer tells us that SQL Server, not the database developer, decides how a query should be executed. We submit an SQL query to describe the required set of data. We do not tell SQL Server how to execute it. The optimizer is completely free to choose the set of operators for the plan, and set their order, within the overriding requirement that the resulting plan must guarantee to return exactly the same data set as would be returned according to the logical processing order<\/a> of the query. Except for very simple queries, the physical processing order, as expressed in the plan, will likely not match the logical processing order. We saw a very simple example of this with the previous example. Logically, the processing order of the WHERE<\/code> clause comes after the FROM<\/code> clause, but in practice it is more efficient to “push down” the predicate and retrieve only the rows that match the predicate condition, rather than retrieve all the rows and then filter out those that are not required.<\/p>\n

In the vast majority of cases, decisions on processing order should be left entirely in the hands of the optimizer. Developers need to be wary of bringing to SQL Server their standard, imperative approach to programming, with line-by-line control over what happens and when. It can lead to sub-optimal plans and poor performance. We won’t discuss this further here, but Peter Larsson, who writes SQL most can only dream of, explores these ideas in more detail in this article<\/a>.<\/p>\n

How the optimizer generates plans<\/h2>\n

When a query reaches the relational engine, it is parsed by the Parser, bound by the Algebrizer and then optimized by the Query Optimizer. Collectively, these processes are referred to as query compilation<\/strong> i.e.<\/em> the process of compiling a query into an execution plan.<\/p>\n

The optimizer generates a number of candidate execution plans for each query submitted, and chooses the plan that it estimates<\/em> will have the lowest overall <\/strong>execution <\/strong>cost<\/strong> in terms of CPU and IO. Of course, the optimizer cannot actually execute any queries, so its cost estimates and subsequent plan choices are based on its knowledge of the underlying data structures (i.e. the tables and available indexes), and on statistics<\/strong>, <\/strong>aggregated information based on a sample of the data, describing the volume and distribution of data in those data structures.<\/p>\n

\"3\"<\/p>\n

Figure 3<\/p>\n

The optimizer’s cost estimates depend largely on its cardinality estimations<\/em>, of how many rows are in a table, and how many of those rows satisfy the various search and join conditions.<\/p>\n

It uses all this information to answer questions such as:<\/p>\n