您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2008

SQL Server 2008 資料庫效能優化常用腳本大全

編輯：SQL Server 2008

連線與等待統計

目前資料庫連線數

SELECT COUNT(*) FROM Master.dbo.SysProcesses WHERE dbid = DB_ID();

前 10 大等待類型

SELECT TOP 10 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;

PAGELATCH 與 LAZYWRITER 等待

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

CPU 壓力分析

SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers WHERE scheduler_id < 255;

效能最差的前 10 條查詢

SELECT TOP 10
    ProcedureName = t.text,
    ExecutionCount = s.execution_count,
    AvgExecutionTime = ISNULL(s.total_elapsed_time / s.execution_count, 0),
    AvgWorkerTime = s.total_worker_time / s.execution_count,
    TotalWorkerTime = s.total_worker_time,
    MaxLogicalReads = s.max_logical_reads,
    MaxPhysicalReads = s.max_physical_reads,
    MaxLogicalWrites = s.max_logical_writes,
    CreationDateTime = s.creation_time,
    CallsPerSecond = ISNULL(s.execution_count / DATEDIFF(second, s.creation_time, GETDATE()), 0)
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_physical_reads DESC;

訊號等待 vs 資源等待比例

SELECT
    SUM(signal_wait_time_ms) AS total_signal_wait_time_ms,
    SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms,
    SUM(signal_wait_time_ms) * 1.0 / SUM(wait_time_ms) * 100 AS signal_wait_percent,
    SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM(wait_time_ms) * 100 AS resource_wait_percent
FROM sys.dm_os_wait_stats;

若訊號等待佔比過高，表示 CPU 為瓶頸。

執行中進程與 SQL 文字

-- 檢查進程數超過 500 時列出所有 SQL
IF (SELECT COUNT(*) FROM master.dbo.sysprocesses) > 500
BEGIN
    SELECT text FROM master.sys.sysprocesses a
    CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle);
END

-- 查看特定 SPID 的 SQL 文字
SELECT text, a.* FROM master.sys.sysprocesses a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
WHERE a.spid = 51;

-- DBCC INPUTBUFFER 替代方案
DBCC INPUTBUFFER(53);

阻塞與會話詳細資訊

WITH tb AS (
    SELECT blocking_session_id, session_id, DB_NAME(database_id) AS dbname, text
    FROM master.sys.dm_exec_requests a
    CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 AS (
    SELECT a.*, login_time, program_name, client_interface_name, login_name,
           cpu_time, memory_usage * 8 AS memory_usage_KB,
           total_scheduled_time, reads, writes, logical_reads
    FROM tb a INNER JOIN master.sys.dm_exec_sessions b ON a.session_id = b.session_id
)
SELECT a.*, connect_time, client_tcp_port, client_net_address
FROM tb1 a INNER JOIN master.sys.dm_exec_connections b ON a.session_id = b.session_id;

目前進程 CPU 排序

SELECT * FROM master.dbo.sysprocesses ORDER BY cpu DESC;

活躍進程查詢

EXEC sp_who 'active';

偵測未釋放連線導致 CPU 高

SELECT * FROM master.dbo.sysprocesses
WHERE spid > 50 AND waittype = 0x0000 AND waittime = 0
  AND status = 'sleeping'
  AND last_batch < DATEADD(minute, -10, GETDATE())
  AND login_time < DATEADD(minute, -10, GETDATE());

強制釋放閒置連線 (產生 KILL 命令)

SELECT 'KILL ' + RTRIM(spid) FROM master.dbo.sysprocesses
WHERE spid > 50 AND waittype = 0x0000 AND waittime = 0
  AND status = 'sleeping'
  AND last_batch < DATEADD(minute, -60, GETDATE())
  AND login_time < DATEADD(minute, -60, GETDATE());

佔用 CPU 最高的會話與 SQL

SELECT spid, cmd, cpu, physical_io, memusage,
       (SELECT TOP 1 [text] FROM ::fn_get_sql(sql_handle)) AS sql_text
FROM master..sysprocesses ORDER BY cpu DESC, physical_io DESC;

快取計畫重用率與記憶體佔用

-- 重用次數少、佔用記憶體大的計畫
SELECT TOP 100 usecounts, objtype, p.size_in_bytes, [sql].[text]
FROM sys.dm_exec_cached_plans p
OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) sql
ORDER BY usecounts, p.size_in_bytes DESC;

-- 計畫重編次數 > 1 的查詢
SELECT TOP 25 qt.text, qs.plan_generation_num, qs.execution_count, dbid, objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE plan_generation_num > 1
ORDER BY qs.plan_generation_num;

-- 總 CPU 時間最高的查詢
SELECT TOP 50 qt.text AS SQL_text,
       SUM(qs.total_worker_time) AS total_cpu_time,
       SUM(qs.execution_count) AS total_execution_count,
       SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
       COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC;

可執行工作進程等待 CPU 數量

SELECT COUNT(*) AS workers_waiting_for_cpu, s.scheduler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s ON o.scheduler_address = s.scheduler_address
WHERE s.scheduler_id < 255 AND o.state = 'RUNNABLE'
GROUP BY s.scheduler_id;

資料表空間大小統計 (MB/GB)

CREATE TABLE #tb(表名 sysname, 記錄數 int, 保留空間 varchar(100), 使用空間 varchar(100), 索引使用空間 varchar(100), 未用空間 varchar(100));
INSERT INTO #tb EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?''';
SELECT
    表名, 記錄數,
    CAST(LTRIM(RTRIM(REPLACE(保留空間,'KB',''))) AS int)/1024.0 AS 保留空間MB,
    CAST(LTRIM(RTRIM(REPLACE(使用空間,'KB',''))) AS int)/1024.0 AS 使用空間MB,
    CAST(LTRIM(RTRIM(REPLACE(使用空間,'KB',''))) AS int)/1024.0/1024.0 AS 使用空間GB,
    CAST(LTRIM(RTRIM(REPLACE(索引使用空間,'KB',''))) AS int)/1024.0 AS 索引使用空間MB,
    CAST(LTRIM(RTRIM(REPLACE(未用空間,'KB',''))) AS int)/1024.0 AS 未用空間MB
FROM #tb
WHERE CAST(LTRIM(RTRIM(REPLACE(使用空間,'KB',''))) AS int)/1024 > 0
ORDER BY 使用空間MB DESC;
DROP TABLE #tb;

查詢執行統計詳細資訊

SELECT
    creation_time AS N'語句編譯時間',
    last_execution_time AS N'上次執行時間',
    total_physical_reads AS N'物理讀取總次數',
    total_logical_reads/execution_count AS N'每次邏輯讀次數',
    total_logical_reads AS N'邏輯讀取總次數',
    total_logical_writes AS N'邏輯寫入總次數',
    execution_count AS N'執行次數',
    total_worker_time/1000 AS N'所用CPU總時間ms',
    total_elapsed_time/1000 AS N'總花費時間ms',
    (total_elapsed_time / execution_count)/1000 AS N'平均時間ms',
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS N'執行語句'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;

以上腳本涵蓋連線監控、等待分析、CPU 壓力、慢查詢定位、阻塞偵測、快取效率、空間統計等核心優化場景，建議收藏備用。