SQL Server 2016 查詢存儲（Query Store）示例

英文原文：

<a href="https://www.mssqltips.com/sqlservertip/4046/sql-server-2016-query-store-example/" target="_blank">https://www.mssqltips.com/sqlservertip/4046/sql-server-2016-query-store-example/</a>

問題

SQL Server 2016的新特性之一是查詢存儲。在之前的文章中，我們回顧了如何在SQL Server Management Studio（SSMS）中配置和通路查詢存儲。我們也提供了SSMS中一個查詢存儲報表的詳細回顧。本文我們将顯示一個如何使用查詢存儲的示例，如何跟蹤多個執行計劃和如何強制一個有效執行計劃。

解決方案

我們将對示範建立一個帶有一個表和存儲過程的資料庫。這個表将會足夠的資料用以顯示執行計劃的不同。

建立示範查詢存儲資料庫

<code>CREATE</code> <code>DATABASE</code> <code>[qstore_demo]</code>

<code>ON</code> <code>PRIMARY</code>

<code>( </code><code>NAME</code> <code>= N</code><code>'qs_demo'</code><code>, FILENAME = N</code><code>'C:\DATA\qs_demo.mdf'</code> <code>, </code><code>SIZE</code> <code>= 102400KB ,</code>

<code>MAXSIZE = 1024000KB , FILEGROWTH = 20480KB )</code>

<code>( </code><code>NAME</code> <code>= N</code><code>'qs_demo_log'</code><code>, FILENAME = N</code><code>'D:\DATA\qs_demo_log.ldf'</code> <code>, </code><code>SIZE</code> <code>= 20480KB ,</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>AUTO_UPDATE_STATISTICS </code><code>OFF</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>AUTO_CREATE_STATISTICS </code><code>OFF</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>RECOVERY SIMPLE</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>QUERY_STORE = </code><code>OFF</code>

我們建立一個AUTO_UPDATE_STATISTICS為OFF和AUTO_CREATE_STATISTICS為OFF的資料庫盡可能少的優化。這隻是出于示範目的。

我們現在還沒有啟用查詢存儲。該表會生成資料并且我們不想要昂貴的INSERT查詢在查詢存儲中。

現在我們将建立表和存儲過程。我們将生成表并且在資料庫上啟用查詢存儲：

<code>USE qstore_demo</code>

<code>-- create a table</code>

<code>CREATE</code> <code>TABLE</code> <code>dbo.db_store (c1 </code><code>CHAR</code><code>(3) </code><code>NOT</code> <code>NULL</code><code>, c2 </code><code>CHAR</code><code>(3) </code><code>NOT</code> <code>NULL</code><code>, c3 </code><code>SMALLINT</code> <code>NULL</code><code>)</code>

<code>-- create a stored procedure</code>

<code>CREATE</code> <code>PROC dbo.proc_1 @par1 </code><code>SMALLINT</code>

<code>SET</code> <code>NOCOUNT </code><code>ON</code>

<code>SELECT</code> <code>c1, c2 </code><code>FROM</code> <code>dbo.db_store</code>

<code>WHERE</code> <code>c3 = @par1</code>

<code>-- populate the table (this may take a couple of minutes)</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2,c3) </code><code>SELECT</code> <code>'18'</code><code>,</code><code>'2f'</code><code>,2</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2) </code><code>SELECT</code> <code>'171'</code><code>,</code><code>'1ff'</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2,c3) </code><code>SELECT</code> <code>'172'</code><code>,</code><code>'1ff'</code><code>,0</code>

<code>INSERT</code> <code>INTO</code> <code>[dbo].db_store (c1,c2,c3) </code><code>SELECT</code> <code>'172'</code><code>,</code><code>'1ff'</code><code>,4</code>

<code>-- enable Query Store on the database</code>

<code>ALTER</code> <code>DATABASE</code> <code>[qstore_demo] </code><code>SET</code> <code>QUERY_STORE = </code><code>ON</code>

SQL Server 2016查詢存儲性能測試

測試1 – 表上沒有索引

在我們的第一個測試中我們在沒有任何索引的表上執行存儲過程。然後我們會在SSMS查詢存儲報表中回顧結果。我們會運作存儲過程20次隻會為了生成足夠的執行和I/O，以便更容易在查詢存儲的其他查詢中找到：

在SSMS中“Query Store”資料庫的容器下打開“Top Resource Consuming Queries”面闆：

修改縱坐标使用“exec count”并修改“Metric”下拉清單的“Duration”為“Logical Reads”：

這裡是我們的查詢（查詢#1）帶有一個單一表掃描計劃（計劃#1）：

測試2 – 使用一個非聚集索引測試

在這個測試中我們将建立一個非聚集索引并再次執行存儲過程：

<code>CREATE</code> <code>NONCLUSTERED </code><code>INDEX</code> <code>NCI_1</code>

<code>ON</code> <code>dbo.db_store (c3)</code>

注意，我們會建立一些索引（重複索引）隻用于示範目的為了生成多個執行計劃。

在SSMS中回到查詢存儲面闆并重新整理結果：

注意，新的執行計劃（計劃#13）被建立和使用。這個計劃現在有更少的邏輯讀。

測試3 – 建立另一個非聚集索引

現在我們将在新增列上建立新的索引：

<code>CREATE</code> <code>NONCLUSTERED </code><code>INDEX</code> <code>NCI_2</code>

<code>ON</code> <code>dbo.db_store (c3, c1)</code>

在SSMS中回顧結果并且注意到另一個執行計劃（計劃#20）被建立：

強制執行計劃（Force Plan）

讓我們運作一些表更新并再次執行存儲過程：

<code>UPDATE</code> <code>dbo.db_store </code><code>SET</code> <code>c1 =</code><code>'1'</code> <code>WHERE</code> <code>c3 = </code><code>'0'</code>

<code>UPDATE</code> <code>dbo.db_store </code><code>SET</code> <code>c2 =</code><code>'3ff'</code> <code>WHERE</code> <code>c3 = </code><code>'1'</code>

<code>DELETE</code> <code>FROM</code> <code>dbo.db_store </code><code>WHERE</code> <code>c3 = 3</code>

<code>INSERT</code> <code>INTO</code> <code>dbo.db_store (c1,c2,c3) </code><code>SELECT</code> <code>'173'</code><code>,</code><code>'1fa'</code><code>,0</code>

在SSMS中傳回查詢存儲面闆并點選“View plan summary in a grid format”按鈕：

你現在可以看到帶有詳細統計資訊的三個執行計劃：

注意，最新的計劃#20有比舊計劃#13更多邏輯讀。

在查詢存儲報表的“Plan Summary”或“Execution Plan”部分的下面，點選“Force Plan”按鈕。你也可以右擊計劃#13并強制這個計劃。确認對這個查詢你想強制這個計劃：

<code>EXEC</code> <code>sp_query_store_force_plan @query_id = 1, @plan_id = 13;</code>

再次運作存儲過程。然後在SSMS中傳回查詢存儲。更新計劃總結并注意到“plan forced”列值對于“plan id”#13現在被設為1。該計劃的“exec count”也增加了，意味着強制計劃#13這次被使用了而不是計劃#20：

這裡是以圖表格式（打勾的泡）顯示了強制計劃的樣子：

對于存儲過程建立最後的，最優索引。

<code>CREATE</code> <code>NONCLUSTERED </code><code>INDEX</code> <code>NCI_3</code>

<code>INCLUDE (c1,c2)</code>

如果你再次運作存儲過程，你會注意到強制計劃将會被再次使用而不是建立新的使用新索引的計劃。

現在我們不強制計劃#13并再次運作存儲過程：

<code>EXEC</code> <code>sp_query_store_unforce_plan @query_id = 1, @plan_id = 13</code>

這裡是結果：

新的計劃#23已經被建立和使用。它有最少的邏輯讀。

注意：本文基于SQL Server 2016 CTP 2.2版本所寫。一些截屏和行為在SQL Server 2016的最終版中可能會改變。

在接下來的文章中我們會提供大量針對查詢存儲運作的有用查詢。

接下來

l 閱讀關于查詢存儲的下一篇文章。

本文轉自UltraSQL51CTO部落格，原文連結： http://blog.51cto.com/ultrasql/1736757，如需轉載請自行聯系原作者

SQL Server 2016 查詢存儲（Query Store）示例

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述