官术网_书友最值得收藏!

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 604字
  • 2021-06-24 14:38:18

Accessing a query plan

To access the estimated plans, which are the direct result of the optimization process, we can use either T-SQL commands or graphical tools. For the examples shown in this chapter, we use SQL Server Management Studio (SSMS).

For most users, query plans in text format are harder to read and analyze; therefore, we will use graphical query plan examples throughout the book.

The SHOWPLAN_TEXT, SHOWPLAN_ALL, and SHOWPLAN_XML commands provide text-based information on query plans with different degrees of detail. Using any of these commands means SQL Server will not execute the T-SQL statements, but show the query plan as produced by the Query Optimizer.

Take an example of a query that can be executed in the scope of the AdventureWorks sample database:

SELECT pp.FirstName, pp.LastName, pa.AddressLine1, pa.City, pa.PostalCode
FROM Person.Address AS pa
INNER JOIN Person.BusinessEntityAddress AS pbea ON pa.AddressID = pbea.AddressID
INNER JOIN Person.Person AS pp ON pbea.BusinessEntityID = pp.BusinessEntityID
WHERE pa.AddressID = 100;

Let's see what each of the following options provides in terms of query plan view:

  • SHOWPLAN_TEXT: This option shows all the steps involved in processing the query, including the type of join that was used, the order in which tables are accessed, and the indexes used for each table:
  • SHOWPLAN_ALL: This option shows the same estimated plan as SHOWPLAN_TEXT. This option represents a text output tree, but adds details on each of the physical operations that would be executed, such as the estimated size of the result rows, the estimated CPU time, and the total cost estimations. Notice the amount of information produced:
  • SHOWPLAN_XML: This option produces the same estimated plan but as an XML output tree:

Because it is generated as a link when used in SSMS, it can be interpreted by SSMS as a graphical estimated plan, and clicking the link will display this graphical plan:

Notice that because it is an estimated plan, the arrows are all the same width. This is because there's no actual data movement between operators given that this plan was not executed. To access all the properties returned by SHOWPLAN_ALL, plus many more, right-click the SELECT operator and click on Properties. We will see these properties in greater detail in the Query plan properties of interest section.

SHOWPLAN_XML is the option is used by SSMS when the Display Estimated Execution Plan (CTRL+L) button is clicked:

To access the actual plans, which are the optimized plans after being executed, we can again use either T-SQL commands or graphical tools. The STATISTICS PROFILE and STATISTICS XML commands provide text-based information on query plans with different degrees of detail. Using either of these commands means SQL Server will execute the T-SQL statements, and generate the actual plan, or query execution plan.

  • STATISTICS PROFILE: This option shows the same plan as SHOWPLAN_ALL, incremented with the actual rows and executes to display an actual plan, or a query execution plan:
  • STATISTICS XML: This option is the actual plan counterpart of SHOWPLAN_XML. In the following screenshot, we see what appears to be the same output as SHOWPLAN_XML:

However, expanding the XML (or if using SSMS, clicking on the link), we see we have the actual plan, or the query execution plan:

STATISTICS XML is the option used by SSMS when the Display Actual Execution Plan (CTRL+M) button is clicked:

To access all the properties already seen with SHOWPLAN_XML incremented with runtime statistics and warnings (if any), right-click the SELECT operator and click on Properties. Again, we will see these properties in greater detail in the Query plan properties of interest section.

主站蜘蛛池模板: 佛学| 兰西县| 阜城县| 柘荣县| 项城市| 新密市| 上林县| 子长县| 尼勒克县| 卫辉市| 沈阳市| 普格县| 民权县| 兴隆县| 眉山市| 新建县| 昌都县| 新平| 洪雅县| 临安市| 瓦房店市| 湾仔区| 曲水县| 全州县| 安远县| 莒南县| 内黄县| 昔阳县| 措勤县| 苏尼特右旗| 武平县| 常州市| 固原市| 扎赉特旗| 长子县| 栾城县| 江津市| 独山县| 连州市| 澄迈县| 克山县|