都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

依頼事項をブログに書いてみる -その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

f:id:koogucc11:20151105142103p:plain

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

  • インデックス、『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

f:id:koogucc11:20151105142315p:plain

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

f:id:koogucc11:20151105142429p:plain
f:id:koogucc11:20151105142505p:plain

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

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

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

※そして、Xmas

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