SQL Server batching (batch requests/sec) high-trace processing

In recent time, monitoring has found that the number of batch requests per second (batch requests/sec) of a database often increases over a long period of time, hundreds of higher than usual, such as:

Because has been relatively high, thought is normal phenomenon, did not notice. Recently our boss asked to view the reason, so the tracking view, is indeed a database of abnormal requests caused!

To understand batching (batch REQUESTSC), batch processing is simply understood to be a batch of SQL processing statements that are executed concurrently, with multiple DML, multiple stored procedures, and so on. As in SSMs, each ' GO ' is a batch before execution.

Start processing, using the Stupidest method, to open SQL Server Profiler to monitor sql:batchcompleted (SQL batch completion record), but found not many, about 300 per second, looks normal, so with SQL Server Profiler is not OK.

Since there are too many batches, the batch is related to a transaction, and you can turn on Performance Monitor to monitor the following two counters:

Batch requests/sec

Tranactions/sec (_total)

Discover that Tranactions/sec (_total) is almost identical to Batch Requests/sec, continuing to view tranactions/sec, monitoring each database in detail, and determining that only tranactions/sec (master) is the largest. Related to master connection, if the job is not caused by the system, it may be caused by the connection, and the connection may be caused by the internal report connection. Exclude SQL Server jobs, and then monitor the following counters:

Connection reset/sec

Logins/sec

Logout/sec

Only Connection Reset/sec is the most frequent, logins/sec and Logout/sec average are around 3. Connection Reset/sec is connected to the pool.

For Connection Reset/sec, refer to the Song Connection Pool of ADO.

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

You can now monitor the following events with SQL Server Profiler:

Audit Login

Audit Logout

At this time the monitoring out of the data is more! ~ This user CDWBCB constantly connect and disconnect from the connection pool! Very frequently, it can be determined that the user caused it!

Then it's done, open SSMs connect to the database, execute the following statement.

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 user's 6 connection sessions are maintained in the database. Strangely, these sessions are closed (open_tran=0), Idle (status=sleeping), but there are still commands waiting to be executed (cmd=awaiting command)! If Open_tran=1 is understandable, this cannot be explained, and it is unclear what kind of state the connection is.

A temporary workaround: Kill several sessions of the account. Come down soon after execution!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL Server batching (batch requests/sec) high-trace processing