SQL Serverが遅い原因と高速化手法を解説

--行ストア インデックスの断片化とページ密度を確認する
SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') AS ips
     INNER JOIN sys.indexes AS i
         ON ips.object_id = i.object_id
        AND ips.index_id = i.index_id
ORDER BY page_count DESC;

SET NOCOUNT ON;

DECLARE 
    @schema_name NVARCHAR(128),
    @table_name NVARCHAR(128),
    @index_name NVARCHAR(128),
    @frag FLOAT,
    @sql NVARCHAR(MAX);

DECLARE cur CURSOR FOR
SELECT 
    s.name AS schema_name,
    o.name AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i 
    ON ips.object_id = i.object_id AND ips.index_id = i.index_id
INNER JOIN sys.objects o 
    ON ips.object_id = o.object_id
INNER JOIN sys.schemas s 
    ON o.schema_id = s.schema_id
WHERE 
    o.type = 'U'
    AND ips.page_count > 1000              -- 小さいページは無視
    AND i.index_id > 0
    AND ips.avg_fragmentation_in_percent >= 10; -- 無駄撃ち防止

OPEN cur;
FETCH NEXT FROM cur INTO @schema_name, @table_name, @index_name, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN

    -- 業務中はREORGANIZEのみ
    SET @sql = N'ALTER INDEX [' + @index_name + '] ON [' 
             + @schema_name + '].[' + @table_name + '] REORGANIZE';

    BEGIN TRY
        PRINT @sql;
        EXEC sp_executesql @sql;

        -- 負荷を分散
        WAITFOR DELAY '00:00:01';
    END TRY
    BEGIN CATCH
        PRINT 'ERROR: ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM cur INTO @schema_name, @table_name, @index_name, @frag;
END

CLOSE cur;
DEALLOCATE cur;

ALTER INDEX [インデックス名] ON [テーブル名] REBUILD

ALTER INDEX [インデックス名] ON [テーブル名] REBUILD WITH (ONLINE = ON)

SELECT
    t.name AS table_name,
    s.name AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
JOIN sys.tables t
    ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.last_updated IS NOT NULL
ORDER BY sp.last_updated desc;

EXEC sp_updatestats;

SELECT
 total_physical_memory_kb AS 物理メモリ合計KB,
 available_physical_memory_kb AS OSから見た空きメモリKB 
FROM sys.dm_os_sys_memory;

SELECT
 opm1.cntr_value as 理想とするメモリKB, 
 opm2.cntr_value as 確保しているメモリKB
FROM
 (SELECT counter_name, cntr_value  FROM sys.dm_os_performance_counters WHERE counter_name ='Target Server Memory (KB)')opm1
,(SELECT counter_name, cntr_value  FROM sys.dm_os_performance_counters WHERE counter_name ='Total Server Memory (KB)')opm2

SELECT MAX(cntr_value) as データがメモリに存在できる時間秒
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy';

SELECT cntr_value as メモリ待ちのクエリ数
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending';

set SERVER=localhost\SQLEXPRESS
set OUTPUT=C:\temp\sql_memory_log.csv
set DB=master

REM ファイルが無ければヘッダ作成
if not exist "%OUTPUT%" (
    echo 取得日時,物理メモリ合計KB,OSから見た空きメモリKB,理想のメモリKB,確保しているメモリKB,PageLifeExpectancy秒,メモリ待ちのクエリ数 > "%OUTPUT%"
)
REM -s ","：CSV区切り、-W：余計な空白削除、-E：Windows認証、
sqlcmd -S %SERVER% -d %DB% -E -s "," -W -h -1 -i memory.sql >> "%OUTPUT%"

SET NOCOUNT ON;
SELECT
    GETDATE() AS 取得日時,
    sm.total_physical_memory_kb AS 物理メモリ合計KB,
    sm.available_physical_memory_kb AS OSから見た空きメモリKB,
    opm1.cntr_value AS 理想のメモリKB,
    opm2.cntr_value AS 確保しているメモリKB,
    ple.PageLifeExpectancy秒,
    mgp.メモリ待ちのクエリ数
FROM sys.dm_os_sys_memory sm
CROSS JOIN (
    SELECT cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Target Server Memory (KB)'
) opm1
CROSS JOIN (
    SELECT cntr_value
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Total Server Memory (KB)'
) opm2
CROSS JOIN (
    SELECT MAX(cntr_value) AS PageLifeExpectancy秒
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Page life expectancy'
) ple
CROSS JOIN (
    SELECT cntr_value AS メモリ待ちのクエリ数
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Memory Grants Pending'
) mgp;

SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'PAGELATCH%';