execution 排除_使用SQL Server 2016 Live Execution統計信息對SQL查詢性能進行故障排除

execution 排除

SQL Server Management Studio a graphical interactive that allows you to interact with the databases hosted on your servers. SSMS provides you with the ability to write, edit, execute, analyze and monitor your SQL queries. It also helps database administrators answer important questions about the SQL query performance, such as why a query is slow or why an index is not used. The answer to these questions can be found simply by tracking the Query Execution Plan.

SQL Server Management Studio是一種圖形交互，它允許您與服務器上托管的數據庫進行交互。 SSMS使您能夠編寫，編輯，執行，分析和監視SQL查詢。 它還可以幫助數據庫管理員回答有關SQL查詢性能的重要問題，例如為什么查詢速度慢或為什么不使用索引。 通過跟蹤查詢執行計劃，可以找到這些問題的答案。

The Query Execution Plan is a graphical display for the query execution roadmap that is followed by the SQL Server Query Optimizer, identifying the cost of executing each piece of code within the query. The Execution Plans can be represented also in XML format, specified by the SET SHOWPLAN_ALL, SET SHOWPLAN_TEXT or SET SHOWPLAN_XML commands.

查詢執行計劃是查詢執行路線圖的圖形顯示，其后是SQL Server查詢優化器 ，標識了執行查詢中每段代碼的成本。 執行計劃也可以XML格式表示，由SET SHOWPLAN_ALL，SET SHOWPLAN_TEXT或SET SHOWPLAN_XML命令指定。

Before going too deep in the type of execution plans and the Live Execution Plan new feature which is the main focus of our article here, let’s briefly describe how SQL Server queries are executed.

在深入介紹執行計劃的類型和實時執行計劃的新功能（這是本文的重點）之前，我們先簡要介紹一下SQL Server查詢的執行方式。

When a SQL query is submitted, the query syntax will be checked, to make sure that it is written in the correct format, in a process called Query Parsing. The logical steps that are required to execute the query are the output of that parsing process. The output of the parsing process will be passed to the Algebrizer which is responsible for resolving different objects names within the databases used in the query. Once all objects names are resolved, the SQL Server Query Optimizer takes over. The Query Optimizer is responsible for specifying the best way to execute the submitted query, by generating many candidate execution plans and choose the lowest cost query in terms of CPU, Memory and IO resources consumption and the time required to execute the query. By finding the most optimal compromise between these factors the most efficient execution plan can be generated. The SQL Server Query Optimizer depends on the SQL Server Statistics, which describes the data and indexes distribution and metadata in the database, to take that decision. Once the Query Optimizer generates the best plan, it will store it in the Plan Cache storage. This plan will be used now by the SQL Storage Engine to execute the query and retrieve or manipulate the requested data.

提交SQL查詢時，將在稱為Query Parsing的過程中檢查查詢語法，以確保以正確的格式編寫查詢語法。 執行查詢所需的邏輯步驟是該解析過程的輸出。 解析過程的輸出將傳遞到Algebrizer ，該Algebrizer負責解析查詢所用數據庫中的不同對象名稱。 解析所有對象名稱后， SQL Server查詢優化器將接管。 查詢優化器負責通過生成許多候選執行計劃并根據CPU，內存和IO資源消耗以及執行查詢所需的時間來選擇成本最低的查詢，從而指定執行提交查詢的最佳方法。 通過找到這些因素之間的最佳折衷，可以生成最有效的執行計劃。 SQL Server Query Optimizer取決于SQL Server Statistics ，該統計信息描述數據庫中的數據和索引分布以及元數據，以做出決定。 查詢優化器生成最佳計劃后，它將把它存儲在計劃緩存中 。 SQL存儲引擎現在將使用該計劃來執行查詢以及檢索或操縱所請求的數據。

There are two main types of execution plans that can be displayed by SQL Server Management Studio; the Estimated Execution Plan that shows the approximate execution steps generated by parsing the query without executing it, and the Actual Execution Plan that shows the real steps followed in executing the query, which requires executing the query before generating that plan. In some cases, you may notice a difference between the actual and estimated execution plans due to a difference between the SQL Server Statistics and the actual data, which can be resolved by updating the database statistics.

SQL Server Management Studio可以顯示兩種主要的執行計劃。 估計執行計劃顯示了通過解析查詢而不執行查詢而生成的近似執行步驟，而實際執行計劃顯示了執行查詢所遵循的實際步驟，這需要在生成該計劃之前執行查詢。 在某些情況下，由于SQL Server統計信息與實際數據之間的差異，您可能會注意到實際執行計劃與估計執行計劃之間存在差異，可以通過更新數據庫統計信息來解決該差異。

SQL Server 2016 introduces a new feature that allows you to view the Live Execution Statistics for your active query, showing the run-time execution statistics for each execution plan operator such as the number of rows, the time required by each operator and the operator and overall execution progress, without waiting for the query execution to finish. It allows you to track the data flow between the operators in real time one by one till the query execution completed.

SQL Server 2016引入了一項新功能，使您可以查看活動查詢的實時執行統計信息 ，從而顯示每個執行計劃操作員的運行時執行統計信息，例如行數，每個操作員和該操作員所需的時間以及整體執行進度，而無需等待查詢執行完成。 它使您可以實時跟蹤操作員之間的數據流，直到查詢執行完成為止。

This feature is very useful in troubleshooting the performance issues of the bad queries with huge execution plans, where it will be difficult and time consuming to identify the part that is performing incorrectly. Make sure that you have SHOWPLAN database level permission in order to generate the Live Query Statistics plan, VIEW SERVER STAT permission to be able to see the live statistics and finally the proper access to execute your query.

此功能在解決具有龐大執行計劃的錯誤查詢的性能問題時非常有用，在這種情況下，識別性能不正確的部分將非常困難且耗時。 確保您具有SHOWPLAN數據庫級別的權限才能生成實時查詢統計信息計劃，并具有VIEW SERVER STAT權限才能查看實時統計信息，并最終具有執行查詢的正確訪問權限。

SQL Server Management Studio allows you to enable the Live Query Statistics for your query in many ways:

SQL Server Management Studio允許您通過多種方式為查詢啟用實時查詢統計信息：

• From the SQL Server Management Studio SQL Editor Toolbar click on the

  

  在SQL Server Management Studio SQL編輯器工具欄中，單擊

  

• Right-click on your query text and choose the Include Live Query Statistics option from the viewed list as below:

  右鍵單擊您的查詢文本，然后從查看的列表中選擇“ 包括實時查詢統計信息”選項，如下所示：

  

• Select the Query menu from the SQL Server Management Studio Menu bar and choose the Include Live Query Statistics option as follows:

  從SQL Server Management Studio菜單欄中選擇“ 查詢”菜單，然后選擇“ 包括實時查詢統計信息”選項，如下所示：

  

• From the SQL Server Management Studio Activity Monitor, right-click on the active query from the Active Expensive Query list and choose Show Live Execution Plan option as below:

  在“ SQL Server Management Studio 活動監視器”中 ，從“ 活動費用查詢”列表中右鍵單擊活動查詢，然后選擇“ 顯示實時執行計劃”選項，如下所示：

  

  Once the Live Execution Statistics feature is enabled in your query using one of the previously mentioned methods, the query can be run and the data flow between the operators can be tracked in the form of dashed lines during execution and one line arrow when the operator process completed, the time spent by each operator, the progress percentage on these operators, the actual and estimated number of rows processed by each operator and the overall query progress as in the detailed figure below:

  一旦使用上述方法之一在查詢中啟用了“實時執行統計”功能，就可以運行查詢，并且可以在執行期間以虛線的形式跟蹤操作員之間的數據流，而在操作員進行處理時可以以一條箭頭的形式跟蹤完成，每個操作員花費的時間，這些操作員的進度百分比，每個操作員處理的實際和估計的行數以及整體查詢進度，如下圖所示：

  

  The below figure also show you how the Live Execution Statistics changed with time till the query execution completed:

  下圖還顯示了實時執行統計如何隨時間變化，直到查詢執行完成：

  

  Let’s go through a simple demo to show how we can leverage the benefits of the Live Execution Statistics to track a query, allocate the problem and enhance the SQL query performance. Suppose that we have the below query from our SQLShackDemo database, that is running very frequently, and is slowing down the HR system, and we are requested as database administration team to allocate the issue and enhance the SQL query performance. To start troubleshooting that query, we will run the query after enabling the Live Execution Statistics feature on it:

  讓我們看一個簡單的演示，以展示如何利用實時執行統計信息的優勢來跟蹤查詢，分配問題并增強SQL查詢性能。 假設我們從SQLShackDemo數據庫中獲得以下查詢，該查詢運行頻率很高，并且正在降低HR系統的運行速度，并且要求我們作為數據庫管理團隊來分配問題并提高SQL查詢性能。 要開始對該查詢進行故障排除，我們將在啟用實時執行統計功能后運行查詢：

  ?
  USE SQLShackDemo 
  GO
  SELECT??DEP.[DepartmentID],DEP.[Name],DEP.[GroupName],DEP.[ModifiedDate]FROM [SQLShackDemo].[HumanResources].[Department] DEPJOIN [SQLShackDemo].[HumanResources].[EmployeeDepartmentHistory] EDHON DEP.[DepartmentID] =EDH.[DepartmentID]where DEP.[GroupName] like '%Quality%' and DEP.[ModifiedDate] not like'1999-10-30' 

  The Live Execution statistics resulted from running the query will be like:

  運行查詢產生的實時執行統計信息將類似于：

  

  As you can see from the previous result, the Table Scan operator on the Department table is the main part that slows down the query, as it takes longer time to complete than the EmployeeDepartmentHistory table scan and reads more records, which is clear from the previous live data flow and the result below:

  從上一結果中可以看到，Department表上的Table Scan運算符是減慢查詢速度的主要部分，因為與EmployeeDepartmentHistory表掃描相比，它需要更長的時間才能完成查詢并讀取更多記錄，這與上一章很清楚實時數據流及其結果如下：

  

  To enhance the Department table scan problem, the execution plan itself suggests a missing index that will enhance the SQL query performance with 99% percent as shown in green in the following plan snapshot:

  為了解決部門表掃描問題，執行計劃本身會建議一個缺少索引的索引，它將以99％的百分比提高SQL查詢性能，如以下計劃快照中的綠色所示：

  

  Right-click on the plan and choose Missing Index Details to show the script to create that missing index. Edit the name of the index to match your company naming convention then run it to create that index. The index creation script is similar to the below:

  右鍵單擊該計劃，然后選擇“ 缺少索引詳細信息”以顯示用于創建該缺少索引的腳本。 編輯索引名稱以匹配您的公司命名約定，然后運行它來創建該索引。 索引創建腳本類似于以下內容：

  ?
  USE [SQLShackDemo]
  GO
  CREATE NONCLUSTERED INDEX [IX_Department_DepartmentID]
  ON [HumanResources].[Department] ([DepartmentID])
  INCLUDE ([Name],[GroupName],[ModifiedDate])
  GO

  Let’s run the previous query again and check the Live Execution Statistics again, the new plan will be like:

  讓我們再次運行上一個查詢，然后再次檢查實時執行統計信息，新計劃將如下所示：

  

  As you can see from the previous plan, the Department Table Scan operator becomes Index Seek, and the operator time decreased from 0.339 seconds to 0.07 seconds only. You can also see the IO enhancement after creating the index, where the actual number of rows that is read from the Department decreased from 6877 records to only 32 records, as it will read from the index directly without the need to scan the overall table. The below figure show you what we gain from creating the new Index on the Department table:

  從上一個計劃中可以看到，“部門表掃描”操作員將變為“索引查找”，并且操作員時間從0.339秒減少到僅0.07秒。 您還可以在創建索引后看到IO增強，其中從Department讀取的實際行數從6877條記錄減少到只有32條記錄，因為它將直接從索引讀取而無需掃描整個表。 下圖顯示了在Department表上創建新索引所獲得的收益：

  

  結論 (Conclusion)

  SQL Server Live Execution Statistics is a useful feature that helps you in troubleshooting SQL query performance in real time and allocate and fix poorly performance parts of the queries without the need to wait for the query execution to complete. You can figure out the time and effort that you can save when using this feature to debug your queries specially the ones with huge execution plans that are not easy to allocate the most expensive part and the ones that take long time, which you don’t need to wait for the execution to complete in order to detect where the query stuck. Although Live Execution Statistics feature is introduced in SQL Server 2016, it can be run on SQL Server 2014 SP1 using the SQL Server 2016 Management Studio.

  SQL Server實時執行統計信息是一項有用的功能，可幫助您實時對SQL查詢性能進行故障排除，并分配和修復查詢中性能不佳的部分，而無需等待查詢執行完成。 您可以弄清楚使用此功能調試查詢時可以節省的時間和精力，特別是那些執行計劃龐大，不容易分配最昂貴部分的查詢和花費很長時間的查詢，而您卻不會需要等待執行完成才能檢測查詢卡在哪里。 盡管SQL Server 2016中引入了實時執行統計功能，但可以使用SQL Server 2016 Management Studio在SQL Server 2014 SP1上運行它。

  There are a few limitations in the Live Execution Statistics feature, where you can only use it against normal table, as it can’t be used to track the Memory-Optimized tables, the tables with ColumnStore index or the Natively Compiled stored procedures.

  實時執行統計信息功能有一些限制，您只能在普通表上使用它，因為它不能用于跟蹤內存優化表，帶有ColumnStore索引的表或本機編譯的存儲過程。

翻譯自: https://www.sqlshack.com/troubleshoot-query-performance-using-sql-server-2016-live-execution-statistics/

execution 排除