都内で働くSEの技術的なひとりごと

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

SQL Server のチューニングについてまとめてみる - その21 - ( あるプロジェクト向けに書いてみた )

 最近、SQL Server の診断をする機会が増えてきています。時間がない中でやっていることもあり、超簡易診断しかできていませんが....その方法について説明します。

  • データベースを作成します。
    f:id:koogucc11:20160928105508p:plain
  • とりあえず 2G くらいにしておきましょう。
    f:id:koogucc11:20160928105601p:plain
  • データベースが作成されました。
    f:id:koogucc11:20160928105618p:plain
  • 下記のクエリを SQL Server Management Studio で実行します。
    USE [データベース名]
    SELECT
        st.text,
    	qp.query_plan,
    	qs.*
    INTO Perf.dbo.dm_exec_query_stats
    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
    ORDER BY
        qs.max_elapsed_time DESC
    
    SELECT
        *
    INTO Perf.dbo.dm_os_wait_stats
    FROM
        sys.dm_os_wait_stats
    
    SELECT
        *
    INTO Perf.dbo.dm_os_latch_stats
    FROM
        sys.dm_os_latch_stats
    
    SELECT
        i.name,
    	ius.*
    INTO Perf.dbo.dm_db_index_usage_stats
    FROM 
        sys.dm_db_index_usage_stats ius
    	INNER JOIN sys.indexes i ON
    	    i.object_id = ius.object_id AND
    	    i.index_id = ius.index_id
    
    SELECT
        *
    INTO Perf.dbo.dm_db_fts_index_physical_stats
    FROM
        sys.dm_db_fts_index_physical_stats
    
    SELECT
        *
    INTO Perf.dbo.dm_fts_index_population
    FROM
        sys.dm_fts_index_population
    
    SELECT
        *
    INTO Perf.dbo.dm_fts_memory_buffers
    FROM 
        sys.dm_fts_memory_buffers
    
    SELECT 
        *
    INTO Perf.dbo.dm_fts_memory_pools
    FROM
        sys.dm_fts_memory_pools
    
    SELECT
        s.name schemaname,
        t.name tablename,
        i.name indexname,
    	dips.*
    INTO Perf.dbo.dm_db_index_physical_stats
    FROM
        sys.tables t
        INNER JOIN
            sys.schemas s ON
            t.schema_id = s.schema_id
        INNER JOIN
            sys.indexes i ON
            t.object_id = i.object_id
        CROSS APPLY 
    	    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(s.name + '.' + t.name), i.index_id, NULL, 'DETAILED') dips
    WHERE
        dips.index_level = 0 AND
        dips.alloc_unit_type_desc = 'IN_ROW_DATA' AND 
        i.name IS NOT NULL AND
        dips.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX')
    ORDER BY
        s.name,
        t.name,
        i.index_id
    
    SELECT 
        * 
    INTO Perf.dbo.dm_db_index_operational_stats
    FROM 
        sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);    
    

    f:id:koogucc11:20160928105637p:plain

※下記もあったほうがいいですね。

SELECT 
    *
INTO Perf.dbo.dm_db_missing_index_columns
FROM
    sys.dm_db_missing_index_columns

SELECT 
    *
INTO Perf.dbo.dm_db_missing_index_details
FROM
    sys.dm_db_missing_index_details

SELECT 
    *
INTO Perf.dbo.dm_db_missing_index_group_stats
FROM
    sys.dm_db_missing_index_group_stats

SELECT 
    *
INTO Perf.dbo.dm_db_missing_index_groups
FROM
    sys.dm_db_missing_index_groups
  • テーブルが作成されたことが確認できます。
    f:id:koogucc11:20160928105655p:plain
  • データベースをバックアップします。
    f:id:koogucc11:20160928105712p:plain
  • 任意の場所にバックアップしましょう。
    f:id:koogucc11:20160928105729p:plain
  • 完了です。あとはバックアップファイルを診断環境に移して思う存分診断しましょう(笑) これだけの情報があれば、大体のチューニングはできると思います。
    f:id:koogucc11:20160928105740p:plain

 チューニング後に再度上記の内容を取得し、比較すると何が変化したか判断できるかと思います。もっときちんとした診断をご希望の場合は....きちんとしたエンジニアさんに依頼しましょう♪

 YOGA BOOK の予約が開始された。ポチってしまう...
http://shopap.lenovo.com/jp/tablets/lenovo/yoga/

 900S は持っているんだけど、どうしよう。