Starting off your query performance troubleshooting session by identifying the high cost operators can be a good starting point, but you never want to rely on these cost numbers alone. I say sometimes because these execution plan costs are known to not always be accurate. These relative costs can sometimes help highlight where the major pain points in your execution plan are occurring. Under each operator you will notice the percentage cost of that operator relative to all other costs in the plan. I typically find myself looking in these properties to discover memory and CPU thread usage, and what transformations SQL Server is doing as part of my query execution. Not only does the hover overlay provide a description of an operator, it also shows us the number of rows SQL Server expected to read during this step versus what it actually read (if using the actual execution plan), what predicates were applied, warning messages, and more.īringing up the properties window (F4 by default) with an operator selected will provide even more information. Hovering over an operator (or an arrow) gives us additional information about what that operator is doing. When troubleshooting plans, these relative arrow sizes can be helpful to tip you off of where you might be seeing more data than you expected in your plans. In summary, the arrow size represents the estimated number of rows output from the source operator in estimated execution plans, and the number of rows read by the source operator in actual execution plans. Brent Ozar recently wrote a detailed post demoing the differences between arrow sizes between estimated and actual execution plans. In a SQL Server Management Studio execution plan, they also represent the relative size of the data at that step. ArrowsĪrrows identify the direction of data flow between operators. Each operator releases a row of data to the operator immediately to the left of it until all of the data rows have made their way to the left most operator. In general, this can be summed up as reading a plan right to left, top to bottom.īy following the arrows from one operator to the next, you are following the flow of data through the plan. When you reach a join or concatenation operator where multiple branches merge into one operator, you can proceed to the right-most operator of one of the lower branches and start the process of reading right to left again. Each icon in the graphical execution plan is known as an operator, and the most common way to read a plan is by starting with the top right most operator and following the arrows to the left. Execution Plan OrderĮxecution plans show the steps SQL Server takes to execute your query. This week, we'll finally dive into what you need to know to read an execution plan. Last week I threw you a curve ball and didn't show you any execution plans at all, instead focusing on the statistics data that helps SQL Server generate query plans. In the first part of this series I explained what an execution plan is and how to view one.
0 Comments
Leave a Reply. |