June 1, 2017 at 12:32 pm
I've set up a report to give me a general idea of how busy my servers are by capturing the output of the Batch Requests/sec from sys.dm_os_performance_counters. Now, I'm interested in improving the report to get a better feel for what might be going on at a given time.
So, what other counters (for a general overview of activity) would you suggest?
I'm thinking possibly SQLServer:Databases / Transactions/sec / _Total might be worthwhile (again, I'm looking for a 1000ft view, not down in the weeds for this) but what else?
Thanks
June 1, 2017 at 3:36 pm
I think it depends on what you are hoping to capture.
For just watching how busy a server is, the Logins/sec and logouts/sec could be intereesting. As could Active Temp Tables.
Also, Stored Procedures Invoked/sec, Tasks Started/sec and Tasks Aborted/sec (all with the instance_name of _Total unless you want to monitor each DB individually).
If you have service broker enabled, then Enqueued Messages/sec could also help.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 1, 2017 at 4:04 pm
jasona.work - Thursday, June 1, 2017 12:32 PMI've set up a report to give me a general idea of how busy my servers are by capturing the output of the Batch Requests/sec from sys.dm_os_performance_counters. Now, I'm interested in improving the report to get a better feel for what might be going on at a given time.So, what other counters (for a general overview of activity) would you suggest?
I'm thinking possibly SQLServer:Databases / Transactions/sec / _Total might be worthwhile (again, I'm looking for a 1000ft view, not down in the weeds for this) but what else?Thanks
For me, sometimes it matters what the counter types are as mixing those up can sometimes cause confusion - cumulative, averages, last observed. And it likely could just be me being confused but I think some do need two samples or baselines to be of much value.
If I was just looking at counters with last observed values (type 65792), I would think about adding:
Target Server Memory
Total Server Memory
Page life expectancy
Processes blocked
Lock Waits/sec
User Connections
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply