SQL Server Performance Counters

Question

SQL Server Performance Counters

alex.sqldba

SSChampion

Points: 10254
More actions
December 2, 2020 at 1:20 pm

#3816016

Here's a question that feels like I'm asking if a tree falls in the woods and nobody is there to see it, does it make a noise?
With that - Performance Counters (SQL Server specific ones), do these run in the background the entire time? Or does starting up Perfmon and tracking them start them at that point? Are they always there silently in the wings?
Cheers
Alex

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

They are there and loaded, but there is no historical information just the value that it is now, so when you start perfmon it gets the latest piece of information and then starts plotting.

Now you don't need perfmon per say, as the counters are all loaded you could get to this information from sys.dm_os_performance_counters in SQL and write your own little script to monitor what it is you need, store it and then alert/graph against it. Again this is current information only, nothing historic unless you have captured it before hand.

Now some of the values are current values at the point in time you look at them, some are cumulative, so you need to get cleaver and correspond the right type of math to the right value depending on the cntr_type.

This may help if you want to go down that route. http://sqlfool.com/2009/09/monitoring-process-for-performance-counters/

This reply was modified 4 years ago by Ant-Green. Reason: Added "Again this is current information only, nothing historic unless you have captured it before hand."

alex.sqldba SSChampion Points: 10254 More actions · Answer 2

Thanks! This is a great reply. Ive wondered if those in the system tables were derived from the actual performance counters or were different type only within SQL.

Edit: I am have re-written my question below for clarity. I made a mess of it originally.

If I want to collect these counters from within my own application and save them for later. Would I be better off using the SQL tables or the OS performance counters?

I see the pros of the system tables as being a trivial query to write, the data already being a result set that can be inserted elsewhere.

The cons of using the system tables are that IF the sql server is locked up as a result of a rogue query I won’t be able anymore to capture this performance data, whereas I’m assuming that I’d have more chance of collecting this data successfully from the OS?

Do you have thoughts on this?

Thanks again,

Alex

cheers

This reply was modified 4 years ago by alex.sqldba. Reason: Refactored for clarity :)

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Indeed, if the server is maxing out at 100% then obviously something has to give, if your capturing at perfmon as thats outside the SQL process that is more likely to suffer from not having any CPU ticks to capture the information. But that being said it could also slow your internal SQL capture down also if there is one rouge SQL process swamping the CPU then throwing the capture in to get the CPU is going to wait for CPU ticks also.

Nothing it going to be 100% when your hitting high limits even if you spent money on 3rd party monitoring, something does have to give which is why having things like alerts when CPU reaches >80% sustained for a few minutes is going to be a good idea to see an early warning that CPU is reaching high levels

alex.sqldba SSChampion Points: 10254 More actions · Answer 4

I edited my original question at the same time you wrote your reply. We must be on the same clock.

I don’t think my edits have messed up your reply.

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Believe it didn't its the old case of "6 of one, half dozen of the other", both will work, both will have issues if there is contention.

Personally I would go the SQL route, saves having to import perfmon csv files adding steps to the process for reporting etc, but the SQL way as I said you need the right math for the right cntr_type, the link should help you with that, in fact re-looking at it now, it does all the math for you, you just need to punch in the perfmon counters into the config table to capture and it works everything out for you.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

Since the perfmon counters are part of the OS, I'd lean heavily towards capturing them from the OS. It's one less layer of processing necessary. The values will be the same. The overhead of capturing those values is likely to be marginally higher within SQL Server than within the OS. Now, consumption from the query is a lot easier, but it has to come with a cost.

For what it's worth, this isn't an experiment I've run to measure it. I'm just thinking through the processes.

"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

WillemG SSC Veteran Points: 223 More actions · Answer 7

You can also collect the counters in Performance Monitor and configure the Data Collector Set to store the counter values in a SQL database. You can even do this for multiple servers, and collect the counter values in a central database.

For this, you need to create an ODBC entry, configure the Perfmon Data Collector Set to use 'Log Format - SQL' and specify the ODBC connection as the 'Date Source Name'. This will write the counter values to a database of your choice (Perfmon uses 3 tables for this, with fixed names) and enables you to report on these values.