January 7, 2015 at 10:52 am
During our workday we have the general base set of transactions at a rate of about 300 transactions per second. Every once in a while a process will run generating transactions of 1800 transactions per second for 5 minutes in the monitoring tools (SolarWinds).
How can a find out which process generates the transactions? It the activity lines up on a job execution in can be relatively straightforward. I have index rebuild / reorg each night at 2 AM, I see a spike there often. Most time we are not that lucky to have an obvious smoking gun.
I have reviewed the DMV's, DMF's, and replication meta data to try relating the bursts of transactions to a user or process without success.
Is there a way to identify the 1600 trans/sec at 4:50 PM are produced by this process or user?
Thanks,
John
January 7, 2015 at 11:53 am
I'm not familiar with the Solarwinds tools, but I'd think there might be some way to do it with them.
However, if I were doing this with the native tools, I'd set up Extended Events to capture connection events. See where they're coming from that way. There's an event for Login. Captures all connections, even if they're from the connection pool. You can add actions for client_host_name, client_app_name, nt_username and username. With that, you ought to be able to see where things are coming from.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2015 at 11:56 am
Thanks, that is a great idea!
January 7, 2015 at 11:59 am
Not a problem. Happy to help. Let me know how it works out. I'm curious.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2015 at 12:19 pm
You seem to know the time this occurs so you can set up an XE capture to get the batches submitted. You can do the same with sql trace to local disk. I have done this on systems with over 10K transactions per second with minimal overhead with a very narrow trace column set I use for tuning. YMMV
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 7, 2015 at 12:55 pm
Can you share the minimal set to capture? I brought production to a stand still when I tried to run a trace during a time period. I was capturing the full SQL! Silly me!!
January 7, 2015 at 1:37 pm
john.petrey (1/7/2015)
Can you share the minimal set to capture? I brought production to a stand still when I tried to run a trace during a time period. I was capturing the full SQL! Silly me!!
You can start from here:
CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id,sqlserver.username)
WHERE (([package0].[greater_than_uint64]([sqlserver].[database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0))))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id,sqlserver.username)
WHERE (([package0].[greater_than_uint64]([sqlserver].[database_id],(4))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0)))))
ADD TARGET package0.event_file(SET filename=N'test.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
January 7, 2015 at 1:45 pm
Great, I will keep you informed on the results.
January 7, 2015 at 2:42 pm
I bet you were doing STATEMENT completed (which the default Tuning trace in Profiler traps), in addition to unnecessary bloating columns like application name, database name, etc.
Here is my Profiler to Disk script:
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 10
exec @rc = sp_trace_create @TraceID output, 2,
N'fullpathandfilenamehere', --this should be a local drive, not a network share
@maxfilesize,
NULL, --no stop time
10 --10 max files of 10MB each
if (@rc != 0) goto error
--for below, 10 is RPC:Completed, 12 is SQL:BatchCompleted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on --textdata
exec sp_trace_setevent @TraceID, 10, 3, @on --databaseid
exec sp_trace_setevent @TraceID, 10, 10, @on --applicationname
exec sp_trace_setevent @TraceID, 10, 12, @on --spid
exec sp_trace_setevent @TraceID, 10, 13, @on --duration
exec sp_trace_setevent @TraceID, 10, 14, @on --starttime
exec sp_trace_setevent @TraceID, 10, 16, @on --reads
exec sp_trace_setevent @TraceID, 10, 17, @on --writes
exec sp_trace_setevent @TraceID, 10, 18, @on --cpu
exec sp_trace_setevent @TraceID, 10, 48, @on --rowcounts
exec sp_trace_setevent @TraceID, 12, 1, @on --textdata
exec sp_trace_setevent @TraceID, 12, 3, @on --databaseid
exec sp_trace_setevent @TraceID, 12, 10, @on --applicationname
exec sp_trace_setevent @TraceID, 12, 12, @on --spid
exec sp_trace_setevent @TraceID, 12, 13, @on --duration
exec sp_trace_setevent @TraceID, 12, 14, @on --starttime
exec sp_trace_setevent @TraceID, 12, 16, @on --reads
exec sp_trace_setevent @TraceID, 12, 18, @on --writes
exec sp_trace_setevent @TraceID, 12, 17, @on --cpu
exec sp_trace_setevent @TraceID, 12, 48, @on --rowcounts
-- Set the Filters
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'exec sp_reset_connect%'
/*
--see BOL for various things to filter on
exec sp_trace_setfilter @TraceID, 3, 0, 1, specifydatabaseidtoEXcludehere
exec sp_trace_setfilter @TraceID, 3, 0, 0, specifydatabaseidtoINcludehere
*/
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
/*
--to turn off:
exec sp_trace_setstatus traceidfromabove, 0
--to delete trace definition:
exec sp_trace_setstatus traceidfromabove, 2
*/
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply