SQL Server ProfilerSQL Server Profiler

適用於: 是SQL Server是Azure SQL Database (僅限受控執行個體)否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database (Managed Instance only) NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

SQL Server ProfilerSQL Server Profiler 是一個介面,可以建立和管理追蹤,以及分析和重新執行追蹤結果。is an interface to create and manage traces and analyze and replay trace results. 事件會儲存於追蹤檔案中,稍後在診斷問題時,可用來進行分析或是重新執行特定的一連串步驟。Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when diagnosing a problem.

重要

SQL 追蹤和 SQL Server ProfilerSQL Server Profiler 已被淘汰。SQL Trace and SQL Server ProfilerSQL Server Profiler are deprecated. 包含 Microsoft SQL Server 追蹤和重新執行物件的 Microsoft.SqlServer.Management.Trace 命名空間也會被淘汰。The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated.

這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

請改用擴充事件。Use Extended Events instead. 如需延伸事件的詳細資訊,請參閱快速入門:SQL Server 中的延伸事件SSMS XEvent 分析工具For more information on Extended Events, see Quick Start: Extended events in SQL Server and SSMS XEvent Profiler.

注意

支援適用於 Analysis Services 工作負載的 SQL Server ProfilerSQL Server ProfilerSQL Server ProfilerSQL Server Profiler for Analysis Services workloads are supported.

注意

當嘗試從 SQL Server Profiler 連線到 SQL Azure 資料庫時,會擲回具誤導性的錯誤訊息,如下所示:When you try to connect to a SQL Azure database from SQL server profiler, it incorrectly throws a misleading error message as follows:

  • 若要對 SQL Server 執行追蹤,則必須為系統管理員固定伺服器角色的成員,或具有「改變追蹤」權限。In order to run a trace against SQL Server, you must be a member of sysadmin fixed server role or have the ALTER TRACE permission.

此訊息原本應該要說明 SQL Server Profiler 不支援 SQL Azure 資料庫執行個體。The message should have explained that SQL Azure Database instances are not supported by SQL Server profiler.

Profiler 位於何處?Where is the Profiler?

您可以從 SQL Server Management StudioSQL Server Management Studio 內以數種方式啟動 Profiler。You can start the Profiler in a number of ways from within SQL Server Management StudioSQL Server Management Studio. 以下主題將列出各種啟動 Profiler 的方式。Here is a topic that lists the ways to start the Profiler.

擷取並重新執行追蹤資料Capture and replay trace data

下表顯示我們建議在 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中用來擷取和重新執行追蹤資料的功能。The following table shows the features we recommend using in SQL Server 2019 (15.x)SQL Server 2019 (15.x) to capture and replay your trace data.

功能\目標工作負載Feature\Target Workload 關聯式引擎Relational Engine Analysis Services (英文)Analysis Services
追蹤擷取Trace Capture SQL Server Management StudioSQL Server Management Studio 中的擴充事件圖形化使用者介面Extended Events graphical user interface in SQL Server Management StudioSQL Server Management Studio SQL Server ProfilerSQL Server Profiler
追蹤重新執行Trace Replay Distributed ReplayDistributed Replay SQL Server ProfilerSQL Server Profiler

使用 SQL Server ProfilerUse SQL Server Profiler

Microsoft SQL Server ProfilerSQL Server Profiler 是「SQL 追蹤」的圖形化使用者介面,可用來監視 Database EngineDatabase Engine 或 Analysis Services 的執行個體。Microsoft SQL Server ProfilerSQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database EngineDatabase Engine or Analysis Services. 您可以擷取每一個事件的相關資料,並將資料儲存至檔案或資料表,以供稍後分析。You can capture and save data about each event to a file or table to analyze later. 例如,您可以監視生產環境,查看哪些預存程序由於執行速度過慢而影響效能。For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly. SQL Server ProfilerSQL Server Profiler 可用於下列活動:is used for activities such as:

  • 逐步執行問題查詢來找出問題原因。Stepping through problem queries to find the cause of the problem.

  • 尋找和診斷執行速度很慢的查詢。Finding and diagnosing slow-running queries.

  • 擷取造成問題的 Transact-SQLTransact-SQL 陳述式系列。Capturing the series of Transact-SQLTransact-SQL statements that lead to a problem. 之後,就可以利用已儲存的追蹤,在能夠診斷問題的伺服器上複製這個問題。The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.

  • 監視 SQL ServerSQL Server 的效能來微調工作負載。Monitoring the performance of SQL ServerSQL Server to tune workloads. 如需有關針對資料庫工作負載來微調實體資料庫設計的資訊,請參閱< Database Engine Tuning Advisor>。For information about tuning the physical database design for database workloads, see Database Engine Tuning Advisor.

  • 將效能計數器相互關聯以診斷問題。Correlating performance counters to diagnose problems.

另外,SQL Server ProfilerSQL Server Profiler 也支援稽核在 SQL ServerSQL Server 執行個體上執行的動作。SQL Server ProfilerSQL Server Profiler also supports auditing the actions performed on instances of SQL ServerSQL Server. 稽核會將安全性相關動作記錄下來,供安全性管理員稍後進行檢閱。Audits record security-related actions for later review by a security administrator.

SQL Server Profiler 概念SQL Server Profiler concepts

若要使用 SQL Server ProfilerSQL Server Profiler,您必須對於描述工具功能的專門用語有所了解。To use SQL Server ProfilerSQL Server Profiler, you need to understand the terms that describe the way the tool functions.

注意

在使用 SQL Server ProfilerSQL Server Profiler 時,了解 SQL 追蹤會對您非常有幫助。Understanding SQL Trace really helps when working with SQL Server ProfilerSQL Server Profiler. 如需詳細資訊,請參閱 SQL TraceFor more information, see SQL Trace.

事件Event

事件是在 SQL Server Database EngineSQL Server Database Engine之執行個體中產生的動作。An event is an action generated within an instance of SQL Server Database EngineSQL Server Database Engine. 範例如下:Examples of these are:

  • 登入連接、失敗及中斷連接。Login connections, failures, and disconnections.
  • Transact-SQLTransact-SQL SELECTINSERTUPDATE,以及 DELETE 陳述式。SELECT, INSERT, UPDATE, and DELETE statements.
  • 遠端程序呼叫 (RPC) 批次狀態。Remote procedure call (RPC) batch status.
  • 預存程序的啟動或結束。The start or end of a stored procedure.
  • 預存程序內部陳述式的啟動或結束。The start or end of statements within stored procedures.
  • SQL 批次的啟動或結束。The start or end of an SQL batch.
  • 寫入 SQL ServerSQL Server 錯誤記錄檔的錯誤。An error written to the SQL ServerSQL Server error log.
  • 資料庫物件上的鎖定被取得或釋放。A lock acquired or released on a database object.
  • 開放式資料指標。An opened cursor.
  • 安全性權限檢查。Security permission checks.

事件產生的所有資料,都會在追蹤中以單一資料列呈現。All of the data generated by an event is displayed in the trace in a single row. 另有資料行與此資料列交叉,用以詳細描述該事件。This row is intersected by data columns that describe the event in detail.

EventClassEventClass

事件類別是一種可追蹤的事件類型。An event class is a type of event that can be traced. 事件類別包含所有可由事件來報告的資料。The event class contains all of the data that can be reported by an event. 事件類別的範例如下:The following are examples of event classes:

  • SQL:BatchCompletedSQL:BatchCompleted
  • 稽核登入Audit Login
  • 稽核登出Audit Logout
  • Lock:AcquiredLock: Acquired
  • Lock:ReleasedLock: Released

EventCategoryEventCategory

事件類別目錄用來定義事件在 SQL Server ProfilerSQL Server Profiler中的分組方式。An event category defines the way events are grouped within SQL Server ProfilerSQL Server Profiler. 例如,所有的鎖定事件類別都會在 Locks 事件類別目錄中予以分組。For example, all lock events classes are grouped within the Locks event category. 然而,事件類別目錄僅存在於 SQL Server ProfilerSQL Server Profiler中。However, event categories only exist within SQL Server ProfilerSQL Server Profiler. 這個字詞無法反映 Engine 事件的分組方式。This term doesn't reflect the way Engine events are grouped.

DataColumnDataColumn

資料行是追蹤中所擷取事件類別的屬性。A data column is an attribute of an event class captured in the trace. 因為事件類別會決定可收集的資料類型,所以並不是所有的資料行都適用於所有的事件類別。Because the event class determines the type of data that can be collected, not all data columns are applicable to all event classes. 例如,在擷取 Lock:Acquired 事件類別的追蹤中,BinaryData 資料行包含鎖定分頁識別碼或資料列的值,但是 Integer Data 資料行則不包含任何值,因為其不適用於所擷取的事件類別。For example, in a trace that captures the Lock: Acquired event class, the BinaryData data column contains the value of the locked page ID or row, but the Integer Data data column doesn't contain any value because it isn't applicable to the event class being captured.

[範本]Template

範本可定義追蹤的預設組態。A template defines the default configuration for a trace. 它特別包含您要用 SQL Server ProfilerSQL Server Profiler來監視的事件類別。Specifically, it includes the event classes you want to monitor with SQL Server ProfilerSQL Server Profiler. 例如,您可以建立範本,其中指定要使用的事件、資料行及篩選。For example, you can create a template that specifies the events, data columns, and filters to use. 範本不能執行,但會儲存為 .tdf 副檔名的檔案。A template isn't executed, but rather is saved as a file with a .tdf extension. 一旦儲存之後,當您啟動以範本為依據的追蹤時,範本就會控制所要擷取的追蹤資料。Once saved, the template controls the trace data that is captured when a trace based on the template is launched.

追蹤Trace

追蹤會根據所選取的事件類別、資料行及篩選,來擷取資料。A trace captures data based on selected event classes, data columns, and filters. 例如,您可以建立追蹤來監視異常錯誤。For example, you can create a trace to monitor exception errors. 若要執行此作業,請選取 Exception 事件類別及 ErrorStateSeverity 資料行。To do this, you select the Exception event class and the Error, State, and Severity data columns. 必須同時收集這三個資料行的資料,追蹤結果才能提供有意義的資料。Data from these three columns needs to be collected in order for the trace results to provide meaningful data. 接著,您可以執行以此方式設定的追蹤,並收集出現在伺服器中之任何 Exception 事件的資料。You can then run a trace, configured in such a manner, and collect data on any Exception events that occur in the server. 追蹤資料可以儲存起來,也可以立即用於分析。Trace data can be saved, or used immediately for analysis. 稍後可以再重新執行追蹤,但有些事件 (例如 Exception 事件) 永遠也無法重新執行。Traces can be replayed at a later date, although certain events, such as Exception events, are never replayed. 您也可以將追蹤儲存成範本,以在未來建立類似的追蹤。You can also save the trace as a template to build similar traces in the future.

SQL ServerSQL Server 提供兩種方式來追蹤 SQL ServerSQL Server 的執行個體:您可以透過 SQL Server ProfilerSQL Server Profiler 追蹤,或是使用系統預存程序追蹤。provides two ways to trace an instance of SQL ServerSQL Server: you can trace with SQL Server ProfilerSQL Server Profiler, or you can trace using system stored procedures.

FilterFilter

建立追蹤或範本時,您可以定義條件來篩選由事件所收集的資料。When you create a trace or template, you can define criteria to filter the data collected by the event. 若不想讓追蹤變得太大,您可加以篩選,只收集某些事件資料的子集。To keep traces from becoming too large, you can filter them so that only a subset of the event data is collected. 例如,您可以在追蹤中將 Microsoft Windows 使用者名稱限制在特定的使用者,進而減少輸出資料。For example, you can limit the Microsoft Windows user names in the trace to specific users, thereby reducing the output data.

如果沒有設定篩選條件,則選定事件類別的所有事件都會傳回到追蹤輸出。If a filter isn't set, all events of the selected event classes are returned in the trace output.

SQL Server Profiler 工作SQL Server Profiler tasks

工作描述Task description 主題Topic
列出 SQL Server 提供用於監視事件特定類型的預先定義範本,以及用以重新執行追蹤的必要權限。Lists the predefined templates that SQL Server provides for monitoring certain types of events, and the permissions required to use to replay traces. SQL Server Profiler 範本和權限SQL Server Profiler Templates and Permissions
描述如何執行 SQL ServerSQL Server Profiler。Describes how to run SQL ServerSQL Server Profiler. 執行 SQL Server Profiler 所需的權限Permissions Required to Run SQL Server Profiler
描述如何建立追蹤。Describes how to create a trace. 建立追蹤 (SQL Server Profiler)Create a Trace (SQL Server Profiler)
描述如何指定追蹤檔案的事件和資料行。Describes how to specify events and data columns for a trace file. 指定追蹤檔案的事件及資料行 (SQL Server Profiler)Specify Events and Data Columns for a Trace File (SQL Server Profiler)
描述如何將追蹤結果儲存至檔案。Describes how to save trace results to a file. 將追蹤結果儲存至檔案 (SQL Server Profiler)Save Trace Results to a File (SQL Server Profiler)
描述如何將追蹤結果儲存至資料表。Describes how to save trace results to a table. 將追蹤結果儲存到資料表 (SQL Server Profiler)Save Trace Results to a Table (SQL Server Profiler)
描述如何篩選追蹤中的事件。Describes how to filter events in a trace. 篩選追蹤中的事件 (SQL Server Profiler)Filter Events in a Trace (SQL Server Profiler)
描述如何檢視篩選資訊。Describes how to view filter information. 檢視篩選資訊 (SQL Server Profiler)View Filter Information (SQL Server Profiler)
描述如何修改篩選。Describes how to Modify a Filter. 修改篩選 (SQL Server Profiler)Modify a Filter (SQL Server Profiler)
描述如何為追蹤檔案 (SQL Server ProfilerSQL Server Profiler) 設定檔案大小上限。Describes how to Set a Maximum File Size for a Trace File (SQL Server ProfilerSQL Server Profiler). 設定追蹤檔案的檔案大小上限 (SQL Server Profiler)Set a Maximum File Size for a Trace File (SQL Server Profiler)
描述如何設定追蹤資料表的資料表大小上限。Describes how to set a maximum table size for a trace table. 設定追蹤資料表的資料表大小上限 (SQL Server Profiler)Set a Maximum Table Size for a Trace Table (SQL Server Profiler)
描述如何啟動追蹤。Describes how to start a trace. 啟動追蹤Start a Trace
描述如何在連接至伺服器後自動啟動追蹤。Describes how to start a trace automatically after connecting to a server. 在連接伺服器之後自動啟動追蹤 (SQL Server Profiler)Start a Trace Automatically after Connecting to a Server (SQL Server Profiler)
描述如何根據事件開始時間篩選事件。Describes how to filter events based on the event start time. 根據事件開始時間篩選事件 (SQL Server Profiler)Filter Events Based on the Event Start Time (SQL Server Profiler)
描述如何根據事件結束時間篩選事件。Describes how to filter events based on the event end time. 根據事件結束時間篩選事件 (SQL Server Profiler)Filter Events Based on the Event End Time (SQL Server Profiler)
描述如何篩選追蹤中的伺服器處理序識別碼 (SPID)。Describes how to filter server process IDs (SPIDs) in a trace. 篩選追蹤中的伺服器處理序識別碼 (SPIDs) (SQL Server Profiler)Filter Server Process IDs (SPIDs) in a Trace (SQL Server Profiler)
描述如何暫停追蹤。Describes how to pause a trace. 暫停追蹤 (SQL Server Profiler)Pause a Trace (SQL Server Profiler)
描述如何停止追蹤。Describes how to stop a trace. 停止追蹤 (SQL Server Profiler)Stop a Trace (SQL Server Profiler)
描述如何在追蹤已暫停或停止之後執行追蹤。Describes how to run a trace after it has been paused or stopped. 在追蹤暫停或停止之後執行追蹤 (SQL Server Profiler)Run a Trace After It Has Been Paused or Stopped (SQL Server Profiler)
描述如何清除追蹤視窗。Describes how to clear a trace window. 清除追蹤視窗 (SQL Server Profiler)Clear a Trace Window (SQL Server Profiler)
描述如何關閉追蹤視窗。Describes how to close a trace window. 關閉追蹤視窗 (SQL Server Profiler)Close a Trace Window (SQL Server Profiler)
描述如何設定追蹤定義預設值。Describes how to set trace definition defaults. 設定追蹤定義預設值 (SQL Server Profiler)Set Trace Definition Defaults (SQL Server Profiler)
描述如何設定追蹤顯示預設值。Describes how to set trace display defaults. 設定追蹤顯示預設值 (SQL Server Profiler)Set Trace Display Defaults (SQL Server Profiler)
描述如何開啟追蹤檔案。Describes how to open a trace file. 開啟追蹤檔案 (SQL Server Profiler)Open a Trace File (SQL Server Profiler)
描述如何開啟追蹤資料表。Describes how to open a trace table. 開啟追蹤資料表 (SQL Server Profiler)Open a Trace Table (SQL Server Profiler)
描述如何重新執行追蹤資料表。Describes how to replay a trace table. 重新執行追蹤資料表 (SQL Server Profiler)Replay a Trace Table (SQL Server Profiler)
描述如何重新執行追蹤檔案。Describes how to replay a trace file. 重新執行追蹤檔案 (SQL Server Profiler)Replay a Trace File (SQL Server Profiler)
描述如何一次重新執行單一事件。Describes how to replay a single event at a time. 一次重新執行一個事件 (SQL Server Profiler)Replay a Single Event at a Time (SQL Server Profiler)
描述如何重新執行至中斷點。Describes how to replay to a breakpoint. 重新執行至中斷點 (SQL Server Profiler)Replay to a Breakpoint (SQL Server Profiler)
描述如何重新執行至資料指標。Describes how to replay to a cursor. 重新執行至資料指標處 (SQL Server Profiler)Replay to a Cursor (SQL Server Profiler)
描述如何重新執行 Transact-SQLTransact-SQL 指令碼。Describes how to replay a Transact-SQLTransact-SQL script. 重新執行 Transact-SQL 指令碼 (SQL Server Profiler)Replay a Transact-SQL Script (SQL Server Profiler)
描述如何建立追蹤範本。Describes how to create a trace template. 建立追蹤範本 (SQL Server Profiler)Create a Trace Template (SQL Server Profiler)
描述如何修改追蹤範本。Describes how to modify a trace template. 修改追蹤範本 (SQL Server Profiler)Modify a Trace Template (SQL Server Profiler)
描述如何設定全域追蹤選項。Describes how to set global trace options. 設定全域追蹤選項 (SQL Server Profiler)Set Global Trace Options (SQL Server Profiler)
描述如何在追蹤時尋找值或資料行。Describes how to find a value or data column while tracing. 在追蹤時尋找值或資料行 (SQL Server Profiler)Find a Value or Data Column While Tracing (SQL Server Profiler)
描述如何從執行中追蹤衍生範本。Describes how to derive a template from a running trace. 從執行中的追蹤衍生範本 (SQL Server Profiler)Derive a Template from a Running Trace (SQL Server Profiler)
描述如何從追蹤檔案或追蹤資料表衍生範本。Describes how to derive a template from a trace file or trace table. 從追蹤檔案或追蹤資料表衍生範本 (SQL Server Profiler)Derive a Template from a Trace File or Trace Table (SQL Server Profiler)
描述如何建立用於執行追蹤的 Transact-SQLTransact-SQL 指令碼。Describes how to create a Transact-SQLTransact-SQL script for running a trace. 建立 Transact-SQL 指令碼以執行追蹤 (SQL Server Profiler)Create a Transact-SQL Script for Running a Trace (SQL Server Profiler)
描述如何匯出追蹤範本。Describes how to export a trace template. 匯出追蹤範本 (SQL Server Profiler)Export a Trace Template (SQL Server Profiler)
描述如何匯入追蹤範本。Describes how to import a trace template. 匯入追蹤範本 (SQL Server Profiler)Import a Trace Template (SQL Server Profiler)
描述如何從追蹤擷取指令碼。Describes how to extract a script from a trace. 從追蹤中擷取指令碼 (SQL Server Profiler)Extract a Script from a Trace (SQL Server Profiler)
描述如何使追蹤與 Windows 效能記錄資料相互關聯。Describes how to correlate a trace with Windows performance log data. 使追蹤與 Windows 效能記錄資料產生相互關聯 (SQL Server Profiler)Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
描述如何組織追蹤中所顯示的資料行。Describes how to organize columns displayed in a trace. 組織追蹤內顯示的資料行 (SQL Server Profiler)Organize Columns Displayed in a Trace (SQL Server Profiler)
描述如何啟動 SQL Server ProfilerSQL Server ProfilerDescribes how to start SQL Server ProfilerSQL Server Profiler. 啟動 SQL Server ProfilerStart SQL Server Profiler
描述如何儲存追蹤及追蹤範本。Describes how to save traces and trace templates. 儲存追蹤及追蹤範本Save Traces and Trace Templates
描述如何修改追蹤範本。Describes how to modify trace templates. 修改追蹤範本Modify Trace Templates
描述如何使追蹤與 Windows 效能記錄資料相互關聯。Describes how to correlate a trace with Windows performance log data. 使追蹤與 Windows 效能記錄資料相互關聯Correlate a Trace with Windows Performance Log Data
描述如何使用 SQL Server ProfilerSQL Server Profiler 檢視及分析追蹤。Describes how to view and analyze traces with SQL Server ProfilerSQL Server Profiler. 使用 SQL Server Profiler 檢視和分析追蹤View and Analyze Traces with SQL Server Profiler
描述如何使用 SQL Server ProfilerSQL Server Profiler 分析死結。Describes how to analyze deadlocks with SQL Server ProfilerSQL Server Profiler. 使用 SQL Server Profiler 分析死結Analyze Deadlocks with SQL Server Profiler
描述如何在 SQL Server Profiler 中分析具有 SHOWPLAN 結果的查詢。Describes how to analyze queries with SHOWPLAN results in SQL Server Profiler. 在 SQL Server Profiler 中使用 SHOWPLAN 結果分析查詢Analyze Queries with SHOWPLAN Results in SQL Server Profiler
描述如何使用 SQL Server ProfilerSQL Server Profiler 篩選追蹤。Describes how to filter traces with SQL Server ProfilerSQL Server Profiler. 使用 SQL Server Profiler 篩選追蹤Filter Traces with SQL Server Profiler
描述如何使用 SQL Server ProfilerSQL Server Profiler 重新執行功能。Describes how to use the replay features of SQL Server ProfilerSQL Server Profiler. 重新執行追蹤Replay Traces
列出 SQL Server ProfilerSQL Server Profiler 的即時線上說明主題。Lists the context-sensitive help topics for SQL Server ProfilerSQL Server Profiler. SQL Server Profiler F1 說明SQL Server Profiler F1 Help
列出 SQL Server ProfilerSQL Server Profiler 用以監視效能及活動的系統預存程序。Lists the system stored procedures that are used by SQL Server ProfilerSQL Server Profiler to monitor performance and activity. SQL Server Profiler 預存程序 (Transact-SQL)SQL Server Profiler Stored Procedures (Transact-SQL)

另請參閱See also