依頼事項をブログに書いてみる -その1-

　今週、調査をしなきゃいけないので、使用するクエリを貼っておきます。

• まず、どのテーブル、インデックスでいけないことが起こっているか確認します。SQL Server Management Studio で実行します。スキャンという、イケない行為をしているクエリを見つけ出します。

SELECT
    [データベース名] = DB_NAME(),
    [スキーマ名] = s.name,
    [テーブル名] = t.name,
    [インデックス名] = i.name,
    [インデックスタイプ] = i.type_desc,
    [インデックス列] = STUFF(indnm, 1, 1, ''),
    [付加列] = STUFF(incnm, 1, 1, ''),
    [シーク回数] = ius.user_seeks,
    [最終シーク日] = ius.last_user_seek,
    [スキャン回数] = ius.user_scans,
    [最終スキャン日] = ius.last_user_scan,
    [ルックアップ回数] = ius.user_lookups,
    [最終ルックアップ日] = ius.last_user_lookup,
    [更新回数] = ius.user_updates,
    [最終更新日] = ius.last_user_update,
    [システムシーク回数] = ius.system_seeks,
    [システム最終シーク日] = ius.last_system_seek,
    [システムスキャン回数] = ius.system_scans,
    [システム最終スキャン日] = ius.last_system_scan,
    [システムルックアップ回数] = ius.system_lookups,
    [システム最終ルックアップ日] = ius.last_system_lookup,
    [システム更新回数] = ius.system_updates,
    [システム最終更新日] = ius.last_system_update
FROM
    sys.indexes i
    INNER JOIN sys.tables t ON
        i.object_id = t.object_id
    INNER JOIN sys.schemas s ON
        t.schema_id = s.schema_id
    INNER JOIN sys.objects o ON
        i.object_id = o.object_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON
        i.object_id = ius.object_id AND
        i.index_id = ius.index_id 
CROSS APPLY
(
    SELECT
        ',' + c.name
    FROM
        sys.index_columns ic
        INNER JOIN sys.columns c ON
            ic.object_id = c.object_id AND
            ic.column_id = c.column_id
    WHERE
        i.object_id = ic.object_id AND
        i.index_id = ic.index_id AND
        ic.is_included_column = 0
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS cra1(indnm) 
CROSS APPLY
(
    SELECT
        ',' + c.name
    FROM
        sys.index_columns ic
        INNER JOIN sys.columns c ON
            ic.object_id = c.object_id AND
            ic.column_id = c.column_id
        WHERE
            i.object_id = ic.object_id AND
            i.index_id = ic.index_id AND
            ic.is_included_column = 1
        ORDER BY
            ic.key_ordinal
        FOR XML PATH('')
) AS cra2(incnm)
ORDER BY
    [スキャン回数] DESC,
    [最終スキャン日] DESC

※スキャン回数、日時は下図の赤枠部分を参照してください。

• インデックス、『PK_SalesOrderHeader_SalesOrderID』でスキャンが発生しているので、下記のクエリを実行します。

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT
    [データベース名] = qp.query_plan.value('(//sp:ColumnReference/@Database)[1]', 'nvarchar(100)'),
    [スキーマ名] = qp.query_plan.value('(//sp:ColumnReference/@Schema)[1]', 'nvarchar(100)'),
    [テーブル名] = qp.query_plan.value('(//sp:ColumnReference/@Table)[1]', 'nvarchar(100)'),
    [SQL文] = SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1,((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1),
    [クエリプラン詳細] = qp.query_plan
FROM
    sys.dm_exec_query_stats qs 
CROSS APPLY 
    sys.dm_exec_query_plan(plan_handle) qp 
CROSS APPLY 
    sys.dm_exec_sql_text(sql_handle) st
WHERE
    qp.query_plan.exist('(//sp:RelOp[contains(@PhysicalOp, "Scan")])') = 1 AND
    qp.query_plan.exist('(//sp:Object[contains(@Index, "PK_SalesOrderHeader_SalesOrderID")])') = 1

• 赤枠部分をクリックし、実行プランを確認します。

• 気合でクエリを修正します。(こんなクエリ、スキャンしないようにしろと言われたら困りますが...)

SELECT
    [SalesOrderID],
    [RevisionNumber],
    [OrderDate],
    [DueDate],
    [ShipDate],
    [Status],
    [OnlineOrderFlag],
    [SalesOrderNumber],
    [PurchaseOrderNumber],
    [AccountNumber],
    [CustomerID],
    [SalesPersonID],
    [TerritoryID],
    [BillToAddressID],
    [ShipToAddressID],
    [ShipMethodID],
    [CreditCardID],
    [CreditCardApprovalCode],
    [CurrencyRateID],
    [SubTotal],
    [TaxAmt],
    [Freight],
    [TotalDue],
    [Comment],
    [rowguid],
    [ModifiedDate]
FROM
    [AdventureWorks2012].[Sales].[SalesOrderHeader]

　こんな感じでイケないことをやってしまっている、クエリを特定します。(この方法がすべてではないです。経験を積むごとに、複雑さをましていくものです。。。) ※電車とかでブログ書くのちょっとつらい。電車の中で SSMS を開いているのも少し抵抗がある、今日この頃....

※こんなんありました。

SQL Server 2016 High Availability Unleashed (includes Content Update Program)

※もうボジョレーの季節...

※そして、Xmas

※そして、おせちの予約。一年すぎるの早い。