SQL Server 的 SQL Statistics 物件SQL Server, SQL Statistics Object

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

SQLServer:SQL Statistics SQL ServerSQL Server 物件會提供計數器,可用來監視編譯以及傳送給 SQL ServerSQL Server執行個體的要求類型。The SQLServer:SQL Statistics object in SQL ServerSQL Server provides counters to monitor compilation and the type of requests sent to an instance of SQL ServerSQL Server. 監視查詢編譯和重新編譯的次數,以及 SQL ServerSQL Server 執行個體所收到的批次數目,可讓您了解 SQL ServerSQL Server 處理使用者查詢的速度,以及查詢最佳化工具處理查詢的效率。Monitoring the number of query compilations and recompilations and the number of batches received by an instance of SQL ServerSQL Server gives you an indication of how quickly SQL ServerSQL Server is processing user queries and how effectively the query optimizer is processing the queries.

編譯是查詢回覆速度的重要部份。Compilation is a significant part of a query's turnaround time. Database EngineDatabase Engine 為了節省編譯成本,會將編譯過的查詢計畫儲存在查詢快取中。In order to save the compilation cost, the Database EngineDatabase Engine saves the compiled query plan in a query cache. 快取的目標是減少編譯,透過儲存編譯過的查詢以供日後重複使用,以後執行時便可以省去重新編譯查詢的步驟。The objective of the cache is to reduce compilation by storing compiled queries for later reuse, therefore ending the requirement to recompile queries when later executed. 不過,每個不同的查詢至少都需要編譯一次。However, each unique query must be compiled at least one time. 下列因素均可能導致查詢重新編譯:Query recompilations can be caused by the following factors:

  • 結構描述變更,包括基底結構描述變更,例如將資料行或索引加入資料表,或統計資料結構描述變更,例如在資料表內插入或刪除大量的資料列。Schema changes, including base schema changes such as adding columns or indexes to a table, or statistics schema changes such as inserting or deleting a significant number of rows from a table.

  • 環境 (SET 陳述式) 變更。Environment (SET statement) changes. 工作階段設定的變更,例如 ANSI_PADDING 或 ANSI_NULLS 可能導致查詢重新編譯。Changes in session settings such as ANSI_PADDING or ANSI_NULLS can cause a query to be recompiled.

如需簡單參數化與強制參數化的詳細資訊,請參閱 ALTER DATABASE (Transact-SQL)For more information about simple and forced parameterization, see ALTER DATABASE (Transact-SQL).

以下是 SQL ServerSQL Server SQL Statistics 計數器。These are the SQL ServerSQL Server SQL Statistics counters.

SQL Server SQL Statistics 計數器SQL Server SQL Statistics counters 描述Description
Auto-Param Attempts/secAuto-Param Attempts/sec 每秒的自動參數化嘗試次數。Number of auto-parameterization attempts per second. 此總數應該是所有失敗的、安全的與不安全的自動參數化之總和。Total should be the sum of the failed, safe, and unsafe auto-parameterizations. SQL ServerSQL Server 執行個體嘗試以參數來取代部份常值,以將 Transact-SQLTransact-SQL 要求參數化時,就會發生自動參數化;如此將可以在多個相似的要求中,重複使用所產生的快取執行計畫。Auto-parameterization occurs when an instance of SQL ServerSQL Server tries to parameterize a Transact-SQLTransact-SQL request by replacing some literals with parameters so that reuse of the resulting cached execution plan across multiple similar-looking requests is possible. 請注意,自動參數化在 SQL ServerSQL Server更新版本中也稱為簡單參數化。Note that auto-parameterizations are also known as simple parameterizations in newer versions of SQL ServerSQL Server. 此計數器不包含強制參數化。This counter does not include forced parameterizations.
Batch Requests/secBatch Requests/sec 每秒接收的 Transact-SQLTransact-SQL 命令批次數目。Number of Transact-SQLTransact-SQL command batches received per second. 此統計資料受到所有條件約束的影響 (例如 I/O、使用者數目、快取大小、要求複雜性等),This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). 批次要求數目大,表示輸送量高。High batch requests mean good throughput.
Failed Auto-Params/secFailed Auto-Params/sec 每秒失敗的自動參數化嘗試。Number of failed auto-parameterization attempts per second. 這應該很小。This should be small. 請注意,自動參數化在 SQL ServerSQL Server更新版本中也稱為簡單參數化。Note that auto-parameterizations are also known as simple parameterizations in later versions of SQL ServerSQL Server.
Forced Parameterizations/secForced Parameterizations/sec 每秒成功的強制參數化次數。Number of successful forced parameterizations per second.
Guided Plan Executions/secGuided Plan Executions/sec 每秒計畫執行的次數 (已經使用計畫指南產生查詢計畫)。Number of plan executions per second in which the query plan has been generated by using a plan guide.
Misguided Plan Executions/secMisguided Plan Executions/sec 每秒計畫執行的次數 (在計畫產生期間無法接受計畫指南)。Number of plan executions per second in which a plan guide could not be honored during plan generation. 計畫指南已被忽略而且使用一般編譯來產生執行的計畫。The plan guide was disregarded and normal compilation was used to generate the executed plan.
Safe Auto-Params/secSafe Auto-Params/sec 每秒的安全自動參數化嘗試。Number of safe auto-parameterization attempts per second. 「安全」是一項判斷,認定可在不同但相似的 Transact-SQLTransact-SQL 陳述式之間共用快取執行計畫。Safe refers to a determination that a cached execution plan can be shared between different similar-looking Transact-SQLTransact-SQL statements. SQL ServerSQL Server 可進行多項自動參數化嘗試,當中有些是安全的,有些是失敗的。makes many auto-parameterization attempts some of which turn out to be safe and others fail. 請注意,自動參數化在 SQL ServerSQL Server更新版本中也稱為簡單參數化。Note that auto-parameterizations are also known as simple parameterizations in later versions of SQL ServerSQL Server. 此項目不包含強制參數化。This does not include forced parameterizations.
SQL Attention rateSQL Attention rate 每秒的注意事項數目。Number of attentions per second. 注意事項是用戶端的要求,用來結束目前執行中的要求。An attention is a request by the client to end the currently running request.
SQL Compilations/secSQL Compilations/sec 每秒的 SQL 編譯次數,Number of SQL compilations per second. 表示輸入編譯程式碼路徑的次數。Indicates the number of times the compile code path is entered. 包括 SQL ServerSQL Server中由陳述式層級的重新編譯所造成的編譯。Includes compiles caused by statement-level recompilations in SQL ServerSQL Server. SQL ServerSQL Server 使用者活動穩定之後,此值會達到一個穩定的狀態。After SQL ServerSQL Server user activity is stable, this value reaches a steady state.
SQL Re-Compilations/secSQL Re-Compilations/sec 每秒的陳述式重新編譯次數,Number of statement recompiles per second. 此值計算觸發陳述式重新編譯的次數。Counts the number of times statement recompiles are triggered. 通常,您會希望重新編譯的次數降低。Generally, you want the recompiles to be low.
Unsafe Auto-Params/secUnsafe Auto-Params/sec 每秒不安全的自動參數化嘗試次數。Number of unsafe auto-parameterization attempts per second. 例如,查詢具有一些特性,會阻止共用快取計畫。For example, the query has some characteristics that prevent the cached plan from being shared. 這些特性就稱為不安全的。These are designated as unsafe. 強制參數化的次數不算在內。This does not count the number of forced parameterizations.

另請參閱See Also

SQL Server 的 Plan Cache 物件 SQL Server, Plan Cache Object
監視資源使用狀況 (System Monitor)Monitor Resource Usage (System Monitor)