Many small queries can make one page slow despite short individual execution times. Chatty applications spend time coordinating many small requests, so the useful measurement includes how many requests one user action creates.
Measure Chatty Applications by User Action
Start with a specific action, such as opening one customer detail page. Define its start, completion, expected result, and application instance. Count database calls during that boundary rather than ranking isolated query durations alone.
I ask for a reproducible action before collecting request counts. I also compare client elapsed time with database execution time. The difference points toward connection work, network waiting, application processing, or other steps outside the individual query.
A small query repeated for every displayed row is a common pattern worth investigating. Repeated lookups and separate checks can create an unnecessarily long conversation. A fast index lookup does not eliminate the cost of each additional round trip.
Do not classify every high request rate as a problem. Many concurrent useful actions can legitimately produce high throughput. Relate the rate to completed business actions and the application's expected amount of work.
Calculate Batch Requests Per Second From Two Samples
The Batch Requests/sec performance counter describes received T-SQL command batches. Its raw DMV value is cumulative rather than an already calculated rate. Subtract samples and divide by the measured interval.
DECLARE @FirstValue bigint, @FirstTick bigint, @StartTime datetime;
SELECT @FirstValue = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Batch Requests/sec'
AND object_name LIKE N'%:SQL Statistics%';
SELECT @FirstTick = ms_ticks, @StartTime = sqlserver_start_time
FROM sys.dm_os_sys_info;
WAITFOR DELAY '00:00:05';
SELECT CONVERT(decimal(18,2),
(p.cntr_value - @FirstValue) * 1000.0 /
NULLIF(i.ms_ticks - @FirstTick, 0)) AS BatchRequestsPerSecond,
p.cntr_type, i.sqlserver_start_time
FROM sys.dm_os_performance_counters AS p
CROSS JOIN sys.dm_os_sys_info AS i
WHERE p.counter_name = N'Batch Requests/sec'
AND p.object_name LIKE N'%:SQL Statistics%'
AND @FirstValue IS NOT NULL
AND p.cntr_value >= @FirstValue
AND i.sqlserver_start_time = @StartTime;The object_name column is padded with trailing spaces, so its LIKE pattern ends with a wildcard. The startup comparison prevents comparing samples across an engine restart. The measured tick interval avoids assuming the wait returned at exactly the requested instant. Diagnostic requests themselves contribute a small amount of observation work.
This is an instance-wide signal, not an application attribution. It also does not equate every SQL statement with a separate network request. A single batch can contain several statements, while applications can use different protocol call shapes.
Capture the rate while reproducing the chosen action and compare it with a suitable baseline. Keep other workload activity documented. A shared-server spike cannot identify one application without additional evidence.
Find Frequently Executed Cached Statements
Cached execution statistics identify statements with many completed executions within their current cache lifetime. They are useful candidates for repeated small work. Compare worker time, reads, and execution frequency rather than selecting only the slowest average query.
SELECT TOP (25)
qs.execution_count, qs.creation_time, qs.last_execution_time,
qs.total_worker_time / NULLIF(qs.execution_count, 0) AS AverageCpuUs,
qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS AverageLogicalReads,
SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset) / 2 + 1) AS StatementText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.execution_count DESC;A high total can reflect a long-lived cache entry rather than an unusual current rate. Use interval snapshots matched by plan handle, statement offsets, and creation time for a focused comparison. New, evicted, or recompiled entries need separate treatment.
Cached statement counts do not identify the originating application session by themselves. Several applications can reuse the same query shape. Connect those candidates with a bounded application or session capture.
Large counts of inexpensive queries can still consume substantial aggregate work. Multiply observed frequency by relevant average cost only within the same measurement scope. Include client round-trip evidence before deciding that database CPU is the primary user-facing delay.
Count Requests by Session With Bounded Extended Events
sys.dm_exec_sessions exposes cumulative CPU and reads, but does not provide a universal total request-count column. Use a short, filtered Extended Events capture for completed request events. The following test targets an application name chosen for a controlled reproduction.
CREATE EVENT SESSION ChattyRequests ON SERVER
ADD EVENT sqlserver.rpc_completed
(
ACTION(sqlserver.session_id, sqlserver.client_app_name)
WHERE ([sqlserver].[client_app_name] = N'ChattyDemo')
),
ADD EVENT sqlserver.sql_batch_completed
(
ACTION(sqlserver.session_id, sqlserver.client_app_name)
WHERE ([sqlserver].[client_app_name] = N'ChattyDemo')
)
ADD TARGET package0.event_file
(SET filename = N'C:\SqlDiagnostics\ChattyRequests.xel',
max_file_size = 20, max_rollover_files = 3)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = OFF);
ALTER EVENT SESSION ChattyRequests ON SERVER STATE = START;
-- Reproduce the approved action, then stop the short capture.
ALTER EVENT SESSION ChattyRequests ON SERVER STATE = STOP;The directory must exist with suitable database-engine service permissions. Event-session administration requires appropriate permissions, and capture contents need protected handling. Even without a SQL-text action, event payloads can contain statement information.
Run start and stop at the actual reproduction boundaries, rather than executing both immediately without an intervening test. Read the files after stopping and flushing the capture. Do not leave an unrestricted high-volume session running indefinitely.
WITH Events AS
(
SELECT CONVERT(xml, event_data) AS EventXml
FROM sys.fn_xe_file_target_read_file
(N'C:\SqlDiagnostics\ChattyRequests*.xel', NULL, NULL, NULL)
), Requests AS
(
SELECT EventXml.value('(event/@name)[1]', 'nvarchar(128)') AS EventName,
EventXml.value('(event/action[@name="session_id"]/value)[1]', 'int')
AS SessionId
FROM Events
)
SELECT SessionId, EventName, COUNT_BIG(*) AS CapturedCompletions
FROM Requests
GROUP BY SessionId, EventName
ORDER BY CapturedCompletions DESC;These counts describe captured event completions, not an infallible client round-trip total. Rollover, dropped events, reused session identifiers, and multiple actions affect interpretation. Use a fresh capture scope and correlate it with the application's own request trace.
Interpret ASYNC_NETWORK_IO Without Jumping to a Diagnosis
ASYNC_NETWORK_IO indicates that SQL Server is waiting while the client consumes results. A slow consumer or oversized result can contribute to it. It is not direct proof that the application issued too many separate queries.
Check whether the application processes each row slowly before reading the next. Also check whether it retrieves many columns or rows that the screen never uses. Moving appropriate filtering and aggregation into the query can reduce unnecessary transfer.
Compare session CPU and logical reads with session lifetime to understand accumulated work. Do not reinterpret logical_reads as a request count or packet counts as query executions. Each metric needs its actual definition.
Chatty applications can combine excessive calls with excessive result handling. Investigate both patterns without forcing every network wait into one explanation. Network latency and client resource constraints remain separate possibilities.
Give Chatty Applications a Shorter Conversation
Present the chosen action with its call sequence, repeated statement shapes, rows transferred, and total elapsed time. Use observed counts instead of inventing a dramatic comparison. One explained action is more actionable than a server-wide chart without attribution.
Can one set-based request replace the repeated lookup without returning unrelated data? Consider joins, approved batching, and table-valued inputs for collections of keys. Preserve transaction semantics and authorization while reducing needless request coordination.
Avoid treating connection pooling as a cure for repeated query calls. Pooling can reduce connection-establishment work, but each command still crosses the application boundary. A conversation with fewer introductions can remain far too talkative.
Keep a unique reproduction identity in application logging so pooled sessions can be correlated with the chosen action. Application names identify a useful group rather than authenticating a request. Protect any request details included in the diagnostic package.
Document how chatty applications were measured before discussing the proposed reduction. Report client calls and database event completions as separate observed quantities. This prevents a statement count from becoming an unsupported network claim.
Compare the revised action using the same dataset and concurrency conditions. Verify returned results alongside request count and end-to-end latency. A shorter conversation is useful only when it preserves the required answer.
Related reading on this blog: Is It the Database? Reading ASYNC_NETWORK_IO and Capturing Stored Procedure Executions with Extended Events in SQL Server.
A fast query is not a fast user action, it is one part of the complete application conversation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
