SQL Server 追蹤檔導入資料庫兩大法：fn_trace_gettable 與 Read80Trace

方法一：原生函數 fn_trace_gettable（推薦，支援 SQL 2005+）

A. 直接導入建立新資料表

USE AdventureWorks;
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable('C:\temp\my_trace.trc', DEFAULT);
GO

DEFAULT 參數表示讀取所有 rollover 檔案（my_trace_1.trc、my_trace_2.trc...）。

B. 導入並自動加入身分識別欄

USE AdventureWorks;
GO
SELECT IDENTITY(INT, 1, 1) AS RowNumber, * INTO temp_trc
FROM fn_trace_gettable('C:\temp\my_trace.trc', DEFAULT);
GO

適合後續分頁查詢或做為主鍵參考。

方法二：Read80Trace 工具（僅 SQL 2000，已淘汰）

下載：Microsoft Download Center

注意：SQL 2005/2008 追蹤檔為 9.0 格式，Read80Trace 僅支援 8.0 格式，執行會報錯 is not a valid 8.0 trace file format。

SQL 2000 環境下使用範例

Read80Trace -f -Slocalhost -Usa -PpassWord -Dtracedb -IE:\profiler\mytrace.trc

Read80Trace 常見錯誤與解決

WARNING: The following trace events were not captured: SQL:BatchStarting, RPC:Starting, SP:StmtStarting...
ERROR: Event 10 does not contain the following required column(s): 15
ERROR: Performance analysis has been disabled because one or more events is missing required columns.

原因：追蹤事件/資料欄未勾選完整。

解決：Profiler 建立追蹤時，務必勾選以下事件與欄位：

必要事件

• TSQL：SQL:BatchStarting、SQL:BatchCompleted、RPC:Starting、RPC:Completed
• 預存程序：SP:StmtStarting、SP:StmtCompleted、SP:Completed
• 效能：Showplan Statistics
• 安全稽核：Audit Login、Audit Logout
• 工作階段：ExistingConnection

必要資料欄

• EventClass、TextData、ApplicationName、NTUserName、LoginName
• CPU、Reads、Writes、Duration
• ClientProcessID、SPID、StartTime、EndTime
• DatabaseID、ObjectID、NestLevel、IntegerData

內建分析預存程序範例

Read80Trace 匯入後產生的 tblBatches、tblUniqueBatches 表可搭配以下預存程式快速找出高耗能語句：

CREATE PROC usp_GetAccessPattern @duration_filter INT = -1
AS
BEGIN
    DECLARE @sum_total FLOAT, @sum_cpu FLOAT, @sum_reads FLOAT, @sum_duration FLOAT, @sum_writes FLOAT;
    SELECT @sum_total = COUNT(*) * 0.01,
           @sum_cpu = SUM(cpu) * 0.01,
           @sum_reads = SUM(reads) * 0.01,
           @sum_writes = SUM(writes) * 0.01,
           @sum_duration = SUM(duration) * 0.01
    FROM tblBatches WHERE duration >= @duration_filter;

    SELECT 
        COUNT(*) AS exec_count,
        STR(COUNT(*)/@sum_total,4,1)+'%' AS exec_ratio,
        SUM(cpu) AS total_cpu, STR(SUM(cpu)/@sum_cpu,4,1)+'%' AS cpu_ratio,
        SUM(reads) AS total_reads, STR(SUM(reads)/@sum_reads,4,1)+'%' AS reads_ratio,
        SUM(duration) AS total_duration, STR(SUM(duration)/@sum_duration,4,1)+'%' AS dur_ratio,
        textdata
    INTO #query_stats
    FROM (
        SELECT b.reads, b.cpu, b.duration, b.writes, CONVERT(VARCHAR(2000), u.NormText) AS textdata
        FROM tblBatches b
        INNER JOIN tblUniqueBatches u ON b.HashId = u.hashid
        WHERE b.duration > @duration_filter
    ) B
    GROUP BY textdata;

    PRINT 'Top 10 by CPU+Reads+Duration';
    SELECT TOP 10 * FROM #query_stats ORDER BY cpu_ratio+reads_ratio+dur_ratio DESC;
    -- 其餘 Top 10 分類略...
END

總結建議

現代環境請直接使用 Extended Events (XEvents) 取代 Profiler/Trace，效能開銷更低、功能更強，並可直接查詢 sys.fn_xe_file_target_read_file 導入分析。