wmi_mssql_transactionpersec is critical

Question

Post reply

wmi_mssql_transactionpersec is critical

Steve Dell

SSCarpal Tunnel

Points: 4623
More actions
April 6, 2010 at 10:32 am

#142435

My server xxxxxxxx with configuration as
Network card-1gbps
Computer:Intel(R)Xeon(R) CPU X5355@2.66GHz,2.66GHz 5.75GB of RAM,Phiysical Address extension
The Treshold is set on my serever as 200 warning, 400 crtical but i am getting the transactionspersec as 3124.00 approx. any suggestions how to find the culprit (query...user.. etc)which is causing my transaction per sec to be critical
thanks in advance

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

GregoryF SSCertifiable Points: 6296 More actions · Answer 1

Do you have any baseline to know the average number of transactions that you should get?

You may want to run SQL Profiler and see what is happening.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 2

Steve Dell

SSCarpal Tunnel

Points: 4623

April 6, 2010 at 12:35 pm

#1146712

not more than 200 transactions per sec

GregoryF SSCertifiable Points: 6296 More actions · Answer 3

Are you looking at total transactions, or just "user" transactions into a certain database?

Monitoring = extra transactions

Jobs = extra transactions

DBA's = extra transactions

Again, if you really want to see what is going on, use SQL profiler, capture all activity, then look at the trace results. You might be surprised.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 4

we were using the naigos tool for monitoring the performance of our server and the bounds for the total transactions per sec is set with the treshold 200-warning and 400-critical , so we keep on getting the alerts when ever the service wmi_mssql_transactionsper sec exceeds the treshold so they keep me asking the solution or precautions to be taken when ever the alert is being generated

thanks in advance if for your suggestions

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 5

ramuboddepalli (4/6/2010)
we were using the naigos tool for monitoring the performance of our server and the bounds for the total transactions per sec is set with the treshold 200-warning and 400-critical , so we keep on getting the alerts when ever the service wmi_mssql_transactionsper sec exceeds the treshold so they keep me asking the solution or precautions to be taken when ever the alert is being generated
thanks in advance if for your suggestions

Just out of curiosity, what caused you to choose the 200 and 400 numbers?

The Redneck DBA

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 6

You should be working on finding out where the extra load is coming from. SQL Profiler is probably going to be the best bet but looking at sp_who2 may be very handy as well. What to do after that will depend on where the extra load is coming from, what's causing it, and whether or not it's actually supposed to be running.

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 7

Q-what caused you to choose the 200 and 400 numbers?

Answer: i didn't choose that 200 and 400 that was set by our high level management:)

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 8

what specific events i need to consider for running the profiler

on this specific issue ?

Thanks in advance

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 9

I would focus on RPC Completed and Batch Completed.

Why did high level management pick those numbers? Was it based on trends seen on the system that's being monitored or something else? If it's anything else you may not have a problem on your hands at all but just normal load. Sorry if asking this seems demeaning but we get all sorts posting here so I don't want to assume this is something you know. A baseline is based on historical data and requires doing monitoring for a while before you can establish one. I would say that at least a week is required to get a general baseline but longer may be to notice jumps at specific times when scheduled jobs are run. If management just decided that 200 and 400 are good numbers to go with without establishing a baseline then time and effort are being wasted at this point. Effort that could be use better someplace else that will have a known business impact.

It's also important to keep in mind that a high number of transactions per second in and of itself isn't a problem or necessarily indicate a problem. If there are performance issues on the machine then it's a problem. And if there's a sudden jump in the number of transactions per second that's also something to look into but without a baseline there's no way to know if there's a jump or not.

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 10

what exactly we need to check in the RPC Completed and Batch Completed

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 11

I can see sudden jumps some times in the transactions per sec exceding to 3000 and some times 800 and what could be the reason for this sudden jumps after we had treshold set to 200-warning and 400-critical

GregoryF SSCertifiable Points: 6296 More actions · Answer 12

RamSteve (4/7/2010)
I can see sudden jumps some times in the transactions per sec exceding to 3000 and some times 800 and what could be the reason for this sudden jumps after we had treshold set to 200-warning and 400-critical

It's probably because you chose the thresholds are chosen out of thin air. To do the thresholds, you need to run profiler to get a baseline or what normal is. Then you need to understand what may elevate transactions, and determine if that is cause for concern.

What could be going on, is someone is doing "a transaction" that calls a SP, that spiders to several SP's with the possibility of triggers. But that's just a guess out of thin air, like your thresholds.

Another hypothetical, what if more people are using the system at the times when you exceed your thresholds?

You need to run profiler to see what is going on; you are asking us to blindly tell you why transactions are higher for a database/software package that we know knowing about. We don't even know what time of day these are occuring, are they during the day, at night, in the morning, on weekends?

Further, if the server is not stressed, these may not even be a problem. Are you experiencing slowness, deadlocks, time-outs?

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 13

so after running the profiler how can we set the baseline for tarnsactions per sec ? On looking in to the rpc complete and batch process what should be the factors to be taken into considration for setting baseline, I usally get alerts from naigos at 00:07:10 to 00:17:01 for the thresold s that was set 400 critical.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 14

Profiler is going to be more useful in tracking down the problem if there is one because it will let you see what's actually running. If Naigos lets you store what the average transactions per second are that's going to be the best way to establish the baseline.

I appreciate you giving a time that the alerts are going off. You can check scheduled jobs running in that time to see what they're doing. It's probably an off-hours maintenance job that should be running that's setting this off. If that's the case a higher threshold should be set for this, and any other maintenance, timeframe.