SQL Server計画ガイドおよびその他のベストプラクティス

通常、クエリの最適化に関する投稿は、クエリオプティマイザが最適な実行プランを選択するのに役立つ正しい方法を示しています。WHEREでSARGable式を使用し、必要な列のみを取得し、最適化された整形式インデックスを使用し、統計を更新します。



今日、私は他のことについて話したい-決してベストプラクティスに属していないものについて、足を踏みにじって以前に実行されたクエリを遅くしたり、エラーのためにまったく実行されなくなったりする助けを借りて...ヒントと計画ガイドについてです。



ヒントはクエリオプティマイザーへのヒントです。完全なリストはMSDNにあります。それらの一部は実際のヒントです（たとえば、OPTION（MAXDOP 4）を指定できます）。これにより、クエリは最大並列度= 4で実行できますが、SQL Serverがこのヒントを使用して並列プランを生成する保証はありません。



他の部分は、行動への直接のガイドです。たとえば、OPTION（HASH JOIN）と記述した場合、SQL ServerはNESTED LOOPSとMERGE JOINのないプランを構築します。そして、ハッシュ結合だけで計画を構築することが不可能であることが判明した場合、どうなるか知っていますか？オプティマイザーはそう言うでしょう-私は計画を構築できず、クエリは実行されません。



問題は、オプティマイザがハンマーで攻撃できるヒントが特定の（少なくとも私には）わからないことです。そして、どのヒントが手動のヒントであり、何か問題が発生した場合にリクエストをクラッシュさせる可能性があります。確かに、これが説明されている既製のコレクションはすでにいくつかありますが、これはいずれにしても公式情報ではなく、いつでも変更される可能性があります。



プランガイドは、特定のヒントのセットを特定の要求（テキストがわかっている）にバインドできるようにする（正しく翻訳する方法がわからない）ものです。これは、たとえば、ORMによって生成される要求テキストに直接影響を与えられない場合に関連する可能性があります。



ヒントと計画ガイドはどちらもベストプラクティスではなく、ヒントとこれらのガイドを省略することをお勧めします。これは、データの分布が変化し、データタイプが変化し、100万以上の事態が発生する可能性があるためです。ヒントを使用したクエリは、ヒントを使用しない場合よりも機能が低下し、場合によっては完全に機能しなくなります。あなたは自分が何をしているのか、そしてその理由を100％認識している必要があります。



なぜ私がこれに取り掛かったのかについての簡単な説明です。



私は、10から最大までのさまざまなサイズのnvarcharフィールドがたくさんある広いテーブルを持っています。そして、このテーブルへの一連のクエリがあり、CHARINDEXはこれらの列の1つ以上でサブストリングの出現を検索します。たとえば、次のようなリクエストがあります。

SELECT *
FROM table
WHERE CHARINDEX(N' ', column)>1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET x ROWS FETCH NEXT y ROWS ONLY

テーブルのIdにはクラスター化インデックスがあり、列には一意でない非クラスター化インデックスがあります。ご存知のとおり、ここではCHARINDEXを使用しているため、これはまったくSARGableではありません。SBの潜在的な問題を回避するために、ここで見つけることができる開いているデータベースStackOverflow2013でこの状況をシミュレートします。



Idによるクラスター化インデックスと次のようなクエリのみを持つdbo.Postsテーブルを考えてみます。

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Data', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

実際のデータベースと一致させるために、Title列にインデックスを作成します。

CREATE INDEX ix_Title ON dbo.Posts (Title);

もちろん、結果として、クラスター化インデックスを反対方向にスキャンすることからなる、完全に論理的な実行計画が得











られます。

テーブル「投稿」。スキャンカウント1、論理読み取り516、物理読み取り0、先読み読み取り0、lob論理読み取り0、lob物理読み取り0、lob先読み読み取り0。SQLServer

実行時間：

CPU時間= 16 ms

しかし、「データ」という一般的な単語の代わりに、もっと珍しいものを探すとどうなりますか？たとえば、N'Aptana '（それが何であるかわからない）。もちろん、計画は同じままですが、実行統計は多少異なります。

テーブル「投稿」。スキャンカウント1、論理読み取り253191、物理読み取り113、先読み読み取り224602、LOB論理読み取り0、LOB物理読み取り0、LOB先読み読み取り0。SQLServer

実行時間：

CPU時間= 2563ミリ秒

そしてこれも論理的です-言葉はあまり一般的ではなく、SQL Serverはそれを含む25行を見つけるためにより多くのデータをスキャンする必要があります。しかし、どういうわけか、それはクールではありませんよね？

そして、私は非クラスター化インデックスを作成していました。たぶん、SQL Serverがそれを使用する方が良いでしょうか？彼自身はそれを使用しないので、ヒントを追加します。

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Aptana', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
OPTION (TABLE HINT (dbo.Posts, INDEX(ix_Title)));

そして、何かがどういうわけか完全に悲しいです。実行統計：

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Posts'. Scan count 5, logical reads 109312, physical reads 5, read-ahead reads 104946, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 35031 ms

そして計画：







実行計画は並列で、2つの種類があり、どちらもtempdbに流出があります。ちなみに、非クラスター化インデックススキャンの後、キールックアップの前に実行される最初の並べ替えに注意してください。これは、ランダムI / Oの数を削減しようとする特別なSQL Serverの最適化です。キールックアップは、クラスター化インデックスキーの昇順で実行されます。詳細については、こちらをご覧ください。



2番目のソートは、降順のIDで25行を選択するために必要です。ちなみに、SQL Serverは、降順でのみIdで並べ替え、 "逆"方向でキールックアップを実行し、最初にクラスター化インデックスキーを昇順ではなく降順で並べ替える必要があると推測した可能性があります。



エントリ 'Data'による検索を使用して、非クラスター化インデックスに関するヒントを含むクエリの実行に関する統計を提供していません。ラップトップのハーフデッドハードドライブでは、16分以上かかり、スクリーンショットを撮ろうとは思いませんでした。申し訳ありませんが、これ以上長く待ちたくありません。

しかし、リクエストはどうですか？クラスター化インデックススキャンは究極の夢であり、何も速くすることはできませんか？



すべての種類を回避しようとすると、非クラスター化インデックスを考えて作成しましたが、これは通常、非クラスター化インデックスのベストプラクティスと通常見なされるものと矛盾します。

CREATE INDEX ix_Id_Title ON dbo.Posts (Id DESC, Title);

次に、ヒントを使用して、SQL Serverに使用するように指示します。

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Aptana', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
OPTION (TABLE HINT (dbo.Posts, INDEX(ix_Id_Title)));

ああ、それはうまくいきました：

テーブル「投稿」。スキャンカウント1、論理読み取り6259、物理読み取り0、先読み読み取り7816、LOB論理読み取り0、LOB物理読み取り0、LOB先読み読み取り0。SQLServer

実行時間：

CPU時間= 1734ミリ秒

プロセッサ時間の増加はそれほど大きくありませんが、読む必要があるのはそれほど多くはありません。頻繁な「データ」はどうですか？

テーブル「投稿」。スキャンカウント1、論理読み取り208、物理読み取り0、先読み読み取り0、LOB論理読み取り0、LOB物理読み取り0、LOB先読み読み取り0。SQLServer

実行時間：

CPU時間= 0 ms

うわー、それもいいです。さて、リクエストはORMからのものであり、そのテキストを変更することはできないので、このインデックスをリクエストに「ネイル」する方法を理解する必要があります。そして、計画ガイドが助けになります。



sp_create_plan_guide（MSDN）ストアドプロシージャは、プランガイドの作成に使用されます。



詳細に検討してみましょう。

sp_create_plan_guide [ @name = ] N'plan_guide_name'  
    , [ @stmt = ] N'statement_text'  
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'  
    , [ @module_or_batch = ]  
      {   
        N'[ schema_name. ] object_name'  
        | N'batch_text'  
        | NULL  
      }  
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }   
    , [ @hints = ] { 
                 N'OPTION ( query_hint [ ,...n ] )'   
                 | N'XML_showplan'  
                 | NULL 
      }  

名前 -明確で一意のプランガイド名

stmt-これは、ヒントを追加する必要があるリクエストです。このリクエストは、アプリケーションからのリクエストとまったく同じように記述する必要があることを知っておくことが重要です。奇妙なスペース？プランガイドは使用されません。間違った改行？プランガイドは使用されません。物事をより簡単にするために、少し後で戻る「ライフハック」があります（ここで見つけました）。



タイプ -指定されたリクエストの場所を示します stmt... ストアドプロシージャの一部である場合は、OBJECTにする必要があります。これが複数のリクエストのバッチの一部である場合、またはアドホックリクエストの場合、または1つのリクエストのバッチの場合は、SQLが必要です。ここでTEMPLATEが示されている場合、これは、MSDNで読むことができるクエリのパラメーター化に関する別の話です。



@module_or_batchは依存しますタイプ。もしタイプ= 'OBJECT'、これはストアドプロシージャの名前である必要があります。もしタイプ= 'BATCH'-バッチ全体のテキストがあり、アプリケーションから取得したものを単語ごとに指定します。奇妙なスペース？まあ、あなたはすでに知っています。NULLの場合、これは1つの要求からのバッチであり、stmt すべての制限付き。



パラメータ-リクエストに渡されるすべてのパラメータとデータタイプがここにリストされます。



最後に@hintsがいい部分です。ここで、リクエストに追加するヒントを指定する必要があります。ここでは、必要に応じて、必要な実行プランをXML形式で明示的に挿入できます。このパラメーターはNULLにすることもできます。これにより、SQL Serverは、クエリで明示的に指定されているヒントを使用しません。stmt...



そこで、クエリのプランガイドを作成します。

DECLARE @sql nvarchar(max) = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N''Data'', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY';

exec sp_create_plan_guide @name = N'PG_dboPosts_Index_Id_Title'  
    ,  @stmt =  @sql
    ,  @type = N'SQL'
    ,  @module_or_batch =  NULL          
    ,  @params = NULL
    ,  @hints =  N'OPTION (TABLE HINT (dbo.Posts, INDEX (ix_Id_Title)))'

そして、リクエストを実行しようとします：

SELECT *
FROM dbo.Posts
WHERE CHARINDEX (N'Data', Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

うわー、うまく







いきました。最後のSELECTステートメントのプロパティには、次のように表示







されます。すばらしい、計画ガイドが適用されました。今「Aptana」を検索するとどうなりますか？そして、すべてが悪くなります-すべての結果とともにクラスター化インデックスのスキャンに戻ります。どうして？また、プランガイドはSPECIFICクエリに適用されるため、そのテキストは実行中のテキストと1対1で一致します。



幸いなことに、私のシステム上のほとんどのリクエストはパラメーター化されています。パラメータ化されていないクエリを操作しなかったので、操作する必要がないことを願っています。それらについては、テンプレートを使用することができ（TEMPLATEについて少し上を参照）、データベースで強制パラメータ化を有効にできます（何をしているのかを理解せずにこれを行わないでください!!!）。おそらく、その後、計画ガイドをリンクできるようになります。しかし、私は本当にそれを試していません。



私の場合、リクエストは次のように実行されます：

exec sp_executesql  
N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;'
, N'@p0 nvarchar(250), @p1 int, @p2 int'
, @p0 = N'Aptana', @p1 = 0, @p2 = 25;

したがって、対応するプランガイドを作成します。

DECLARE @sql nvarchar(max) = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;';

exec sp_create_plan_guide @name = N'PG_paramters_dboPosts_Index_Id_Title'  
    ,  @stmt =  @sql
    ,  @type = N'SQL'
    ,  @module_or_batch =  NULL          
    ,  @params = N'@p0 nvarchar(250), @p1 int, @p2 int'
    ,  @hints =  N'OPTION (TABLE HINT (dbo.Posts, INDEX (ix_Id_Title)))'

そして、万事、すべてが必要に応じて機能します：











温室条件の外にいると、パラメーターを正しく指定できるとは限りませんstmtプランガイドをリクエストに添付するために、私は上記の「ライフハック」があります。プランキャッシュをクリアし、ガイドを削除し、パラメーター化されたクエリを再度実行して、キャッシュからその実行プランとplan_handleを取得します。



これに対する要求は、たとえば次のように使用できます。

SELECT qs.plan_handle, st.text, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp

これで、sp_create_plan_guide_from_handleストアドプロシージャを使用して、既存のプランからプランガイドを作成できます。



パラメータとして受け取ります名前-作成されたガイドの名前、@ plan_handle-既存の実行プランのハンドル、および@statement_start_offset-ガイドを作成する必要があるバッチ内のステートメントの開始を定義します。



試してみる：

exec sp_create_plan_guide_from_handle N'PG_dboPosts_from_handle'  
    , 0x0600050018263314F048E3652102000001000000000000000000000000000000000000000000000000000000
    , NULL;

そして今、SSMSでProgrammability-> Plan Guidesにあるものを確認し







ます。現在の実行プランは、Plan Guide 'PG_dboPosts_from_handle'を使用してリクエストに「釘付け」されていますが、何よりも、ほとんどすべてのオブジェクトと同様ですSSMSでは、必要な方法でスクリプトを作成して再作成できます。



RMB、スクリプト->ドロップして作成すると、@ hintsパラメータの値を必要なものに置き換える必要がある既製のスクリプトが得られるため、結果として次のようになります。

USE [StackOverflow2013]
GO

/****** Object:  PlanGuide PG_dboPosts_from_handle    Script Date: 05.07.2020 16:25:04 ******/
EXEC sp_control_plan_guide @operation = N'DROP', @name = N'[PG_dboPosts_from_handle]'
GO
/****** Object:  PlanGuide PG_dboPosts_from_handle    Script Date: 05.07.2020 16:25:04 ******/
EXEC sp_create_plan_guide @name = N'[PG_dboPosts_from_handle]', @stmt = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY', @type = N'SQL', @module_or_batch = N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;', 
@params = N'@p0 nvarchar(250), @p1 int, @p2 int', 
@hints = N'OPTION (TABLE HINT (dbo.Posts, INDEX (ix_Id_Title)))'
GO

リクエストを実行して再実行します。おっと、すべてが機能







します。パラメータ値を置き換えると、すべてが同じように機能します。



1つのステートメントに対応できるガイドは1つだけであることに注意してください。同じステートメントに別のガイドを追加しようとすると、エラーメッセージが表示されます。

メッセージ10502、レベル16、状態1、行1で

指定されたステートメントにより、プランガイド 'PG_dboPosts_from_handle2'を作成できませんstmtおよび@module_or_batch、または@plan_handleと@statement_start_offsetによって、データベース内の既存のプランガイド 'PG_dboPosts_from_handle'と一致します。新しいプランガイドを作成する前に、既存のプランガイドを削除してください。

最後に触れておきたいのは、sp_control_plan_guideストアドプロシージャです。



その助けを借りて、プランガイドを削除、無効化、有効化できます-一度に1つずつ、名前を示し、すべてのガイド（よくわかりません-またはすべて。またはプロシージャが実行されるデータベースのコンテキスト内のすべて）-このため、値@operationパラメーター-DROP ALL、DISABLE ALL、ENABLE ALL。特定のプランにHPを使用する例をすぐ上に示します。指定した名前の特定のプランガイドは削除されます。



ヒントと計画ガイドなしで実行できましたか？



一般に、クエリオプティマイザーが愚かである種のゲームを実行しているように思われ、99％の確率で（私の場合のように）ある種のゲームを実行していることがどれほど最良かを知っている場合。ただし、リクエストテキストに直接影響を与えることができない場合は、リクエストにヒントを追加できるプランガイドが命の恩人になることができます。必要に応じてリクエストテキストを書き換える能力があるとしましょう-これで何か変更できますか？承知しました！全文検索の形式で「エキゾチック」を使用しなくても、実際にはここで使用する必要があります。たとえば、そのようなクエリには、完全に通常の（クエリの）計画と実行統計があります。

;WITH c AS (
    SELECT p2.id
    FROM dbo.Posts p2
    WHERE CHARINDEX (N'Aptana', Title) > 1
    ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
    OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
)
SELECT p.* 
FROM dbo.Posts p
JOIN c ON p.id = c.id;

テーブル「投稿」。スキャンカウント1、論理読み取り6250、物理読み取り0、先読み読み取り0、LOB論理読み取り0、LOB物理読み取り0、LOB先読み読み取り0。SQLServer

実行時間：

CPU時間= 1500ミリ秒

SQL Serverは最初にix_Id_Titleの "クロールされた"インデックスによって必要な25の識別子を見つけ、それからクラスターインデックスで選択された識別子を検索します-ガイドよりも優れています！しかし、「データ」に対してクエリを実行し、20,000行目から25行を表示するとどうなりますか。

;WITH c AS (
    SELECT p2.id
    FROM dbo.Posts p2 
    WHERE CHARINDEX (N'Data', Title) > 1
    ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
    OFFSET 20000 ROWS FETCH NEXT 25 ROWS ONLY
)
SELECT p.* 
FROM dbo.Posts p
JOIN c ON p.id = c.id;

テーブル「投稿」。スキャンカウント1、論理読み取り5914、物理読み取り0、先読み読み取り0、LOB論理読み取り11、LOB物理読み取り0、LOB先読み読み取り0。SQLServer

実行時間：

CPU時間= 1453ミリ秒

exec sp_executesql  
N'SELECT *
FROM dbo.Posts
WHERE CHARINDEX (@p0, Title) > 1
ORDER BY ROW_NUMBER() OVER (ORDER BY Id DESC)
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY;'
, N'@p0 nvarchar(250), @p1 int, @p2 int'
, @p0 = N'Data', @p1 = 20000, @p2 = 25;

Table 'Posts'. Scan count 1, logical reads 87174, physical reads 0, read-ahead reads 0, lob logical reads 11, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 1437 ms

はい、charindexに費やされるため、プロセッサ時間は同じですが、ガイドを使用したリクエストでは、桁違いに多くの読み取りが行われ、これが問題になる可能性があります。



最終結果をまとめましょう。ヒントとガイドは、今ここであなたを助けることができますが、物事をさらに悪化させる可能性があります。リクエストテキストでインデックス付きのヒントを明示的に指定してからインデックスを削除すると、クエリを実行できなくなります。 SQL Server 2017では、インデックスを削除した後、ガイド付きのクエリは正常に実行されます-ガイドは無視されますが、SQL Serverのすべてのバージョンで常にこのようになるとは限りません。



ロシア語のプランガイドについてはあまり情報がないので、自分で書くことにしました。ここで読むことができますプランガイドの使用に関する制限について、特に、PGを使用してヒントを使用してインデックスを明示的に示すと、リクエストが失敗する場合があるという事実について。私はあなたがそれらを決して使用しないことを望みます。そして、もしあなたが-もし、幸運なら-これがどこにつながるのか知っているなら。