SQL Server Batch processing (Batch Requests/sec) high tracking processing, batchrequests

SQL Server Batch processing (Batch Requests/sec) high tracking processing, batchrequests

Recent observations showed that the number of Batch processing Requests per second (Batch Requests/sec) of a database often increased for a long time, several hundred times higher than usual, such:

Because it has been relatively high, I think it is a normal phenomenon and I did not pay attention to it. Our boss recently asked us to check the cause, so tracking and viewing is indeed caused by abnormal database requests!

First, let's look at Batch Requestsc. Batch processing is simply a Batch of SQL statements executed at the same time. One Batch can have multiple DML and multiple stored procedures. For SSMS operations, each 'go' is a batch before execution.

Start processing. Use the dumbest method to enable SQL Server profiler to monitor SQL: BatchCompleted (SQL batch processing completion record). However, it is found that not many, about 300 per second, and it looks normal, therefore, SQL Server profiler cannot be used.

Since the batch processing is too large, the batch processing is related to the transaction, you can enable the performance monitor to monitor the following two counters:

Batch Requests/sec

Tranactions/sec (_ Total)

It is found that Tranactions/sec (_ Total) and Batch Requests/sec are almost the same. Continue to view Tranactions/sec and monitor each database in detail. It is determined that only Tranactions/sec (master) is supported) is the largest. The connection is related to the master connection. If it is not caused by a system job, the connection may be caused by an internal report connection. Exclude SQL server jobs and monitor the following counters:

Connection Reset/sec

Logins/sec

Logout/sec

Only Connection Reset/sec is the most frequent, and the average of Logins/sec and Logout/sec is about 3. Connection Reset/sec is connected by the Connection pool.

For details about Connection Reset/sec, refer to the Connection pool of Ado.net of Song Daxia.

  "The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool."

You can use SQL Server profiler to monitor the following events:

Audit Login

Audit Logout

At this time, more data will be monitored !~ This userCdwbcbConstantly connect and disconnect from the connection pool! Very frequent. It can be determined that this user is causing this problem!

Next, you can easily open SSMS to connect to the database and execute the following statements.

select p.*,s.text from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) swhere nt_username='cdwbcb'

Find related data:

The database maintains the user's six connection sessions. The transactions of these sessions have been closed (open_tran = 0) and are in idle state (status = sleeping), but there are still commands waiting for execution (cmd = awaiting command )! That is, the user is still in the connection status, because there are commands waiting for execution, so the connection continues. Currently, not many users are connected. Why does the user keep connecting? Is it because the physical connection is interrupted and the connection pool is still connected, but data cannot be returned? Solving ......

Temporary solution: kill several sessions of the account. Soon after the execution, it fell down! (The spid monitored by profiler is deleted during normal activities)

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.