October 11, 2011 at 5:38 am
Whilst looking at perfmon our app DB normally has transactions / sec counter with a value of spiking up to 1000 but now is regularly 5000 or higher and the users are reporting performance issues.
Looking at sys.sysprocesses I cannot see a connection with extensive CPU or I/O
The drives queue length counter are almost zero and the CPU is not more than 30% busy so the server itself is ok.
What is the best way to find out who/what is causing all these transactions per second.
Thanks
October 11, 2011 at 5:44 am
Can you start a trace for 1 minute?
October 11, 2011 at 5:57 am
Hi,
thanks for the reply.
I have run it for about 3 seconds and got 10,000 rows when just have 'transactions' category selected.
I guess what would be good would be a query to run every second or so, and count this so for example, after 10 seconds of running, I would get a result like this
User Transactions
Adam 300
Dave 4000
So I guess the question becomes where is this info stored (which dmv) which will allow me to indeifity the person causing all the transactions /sec.
October 11, 2011 at 6:00 am
AFAIK there's no dmv for that (can you imagine all the ram it would take to log all that).
That's why traces where invented. I'd go with the default trace and then start asking questions.
I would not be surprised that an app has recently been upgraded or deployed and that a new report is being ran fetching 1 row at a time (and requreying after all rows) hence pegging the server in the process.
October 11, 2011 at 6:04 am
dmvs dont store information about activities carry out by a particular login.
Trace would be a good solution. Setup a server side trace for a certain duration and then alalyze the details of the transaction initiated by a particular login.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply