SQL Server性能计数器（批处理请求/秒或事务/秒）：要监视的内容以及原因

When maintaining SQL Server, it is essential to get an accurate perception of how busy it is. Two metrics that are often considered as indicators of how busy SQL Server is are Batch Requests/sec and Transaction/sec. When those metrics trend higher, they often affect all other metrics and make them go higher as well. While they could look similar, they are using a different type of starting point for measurement; the batches and transactions. So, to correctly understand those important metrics, lets first try to understand what the batches and transactions in SQL Server are and what are the differences between the two

维护SQL Server时，必须准确了解它的繁忙程度。 经常被认为是SQL Server繁忙程度的两个指标是批处理请求数/秒和事务处理数/秒。 当这些指标趋于更高时，它们通常会影响所有其他指标并使它们也更高。 尽管它们看起来很相似，但是它们使用不同类型的起点进行测量； 批次和交易。 因此，为了正确理解这些重要指标，让我们首先尝试了解一下SQL Server中的批处理和事务以及两者之间的区别是什么

SQL Server语句批处理 (SQL Server statement batches)

When executing multiple SQL Server statements as a group of statements, or when executing a single SQL statement that consists of a group of multiple SQL statements, it is considered as a SQL Server batch. That practically means that the result is returned after the entire batch statement executes. The advantage of such approach is that execution is more efficient compared to executing each containing statement independently. One aspect of better efficiency lies in the fact that network traffic is reduced in most cases, the second aspect is that targeted data source can often optimize execution of the statements in a SQL Server batch

当将多个SQL Server语句作为一组语句执行时，或者当执行由一组多个SQL语句组成的单个SQL语句时，它将被视为SQL Server批处理。 这实际上意味着在整个批处理语句执行后返回结果。 这种方法的优点是，与独立执行每个包含语句相比，执行效率更高。 效率更高的一方面在于在大多数情况下网络流量会减少，另一方面，目标数据源通常可以优化SQL Server批处理中语句的执行

Some facts about SQL batches:

有关SQL批处理的一些事实：

• A SQL batch is, in its essence, a collection of various SQL statements put together to be executed without a guarantee that it will either succeed or fail
  本质上，SQL批处理是各种SQL语句的集合，这些语句组合在一起执行即可，但不保证它会成功或失败
• SQL batches ensures creation of a single query execution plan
  SQL批处理可确保创建单个查询执行计划
• Variables created within a batch cannot be used outside of the batch
  批内创建的变量不能在批外使用
• Multiple batches that work with multiple objects can be folded within a single transaction block, though they are not allowed to violate execution plans of the individual batches
  可以将与多个对象一起使用的多个批处理折叠在一个事务块中，尽管不允许它们违反单个批处理的执行计划
• Multiple transactions can be folded in a single SQL batch, which has the advantage of granting the integrity of SQL Server objects (tables for example)
  可以在一个SQL批处理中折叠多个事务，这具有授予SQL Server对象（例如表）完整性的优点。

SQL Server Open Database Connectivity (ODBC) API supports three types of batches

SQL Server开放式数据库连接（ODBC） API支持三种类型的批处理

Explicit batches – this is when two or more SQL statements are combined in the single batch script and separated by semicolons (;)

显式批处理 –这是将两个或多个SQL语句合并到单个批处理脚本中并用分号（;）分隔的情况

INSERT INTO Storage (StorageID, CountyID, OpenDate, MercahanPerson, Status)  
   VALUES (9301, 1001, {fn CURDATE()}, 'Tod', 'CLOSED');  
INSERT INTO Loops (LoopId, Line, TransID, Quantity)  
   VALUES (135, 1, 124, 103);  
INSERT INTO Loops (LoopId, Line, TransID, Quantity)  
   VALUES (135, 2, 98, 78);  
INSERT INTO Loops (LoopId, Line, TransID, Quantity)  
   VALUES (207, 3, 589, 167);  
INSERT INTO Loops (LoopId, Line, TransID, Quantity)  
   VALUES (207, 4, 412, 120)

All statements in the previous batch example are separated with the semicolon (highlighted) except the last statement in the batch

上一个批处理示例中的所有语句都用分号（突出显示）分隔，除了该批处理中的最后一个语句

Stored procedures – every stored procedure that contains more than one SQL statement is considered by SQL Server as a batch of included SQL statements. The difference here is that the stored procedure contains the statements that are specific to SQL Server. Stored procedures do not use semicolons for separating statements. It is because the SQL Server specific CREATE PROCEDURE statement handles the statements within the stored procedure and therefore it does not need statement separators

存储过程 –每个包含多个SQL语句的存储过程都被SQL Server视为一批包含SQL语句。 此处的区别在于存储过程包含特定于SQL Server的语句。 存储过程不使用分号来分隔语句。 这是因为特定于SQL Server的CREATE PROCEDURE语句处理存储过程中的语句，因此不需要语句分隔符

CREATE PROCEDURE Test (@TestID INT) AS  
   SELECT * FROM Test WHERE TestID = @PartID  
   SELECT LoopID FROM Loops  
   WHERE TestID = @PartID
   AND State = 'DAMAGED'

It is important to understand that the CREATE PROCEDURE statement itself is not a batch statement, but the resulting procedure executes as a batch of SQL statements that it contains. So when executing the Test stored procedure from the example above, the two SELECT SQL statements will be executed as a batch of SQL statements

重要的是要了解CREATE PROCEDURE语句本身不是批处理语句，但是生成的过程将作为它所包含的一批SQL语句执行。 因此，在执行上述示例中的Test存储过程时，两个SELECT SQL语句将作为一批SQL语句执行

Arrays of parameters – When parametrized statements are used by the application, having an array of parameters is an effective way of to perform the execution of a single statement multiple times as a batch execution

参数数组 –应用程序使用参数化语句时，拥有参数数组是一种有效的方法 一个批处理执行多次执行一条语句的过程

INSERT INTO Test (TestID, Description, TimeElapsed, TimeCompleted) VALUES (?, ?, ? , ?)

In the above example, using question marks (?) or the properly called parameter marker means that they are bound to application variables. So, when application provides parameters to the SQL statement, it allows inserting multiple rows of data in a single execution. For data sources that do not support arrays of parameters, the ODBC driver can emulate the arrays thus passing the set by set of values to the statement. In that case, the statement executes as a batch of individual statements for each set of values

在上面的示例中，使用问号&#