0

We are trying to measure SQL Server Performance metrics. Is it better to record Performance Monitor (PerfMon) or DMV Dynamic Management Views to measure CPU, Memory, Buffer pages, etc? There are certain metrics, that PerfMon can measure which DMV cannot, and other way around DMV can measure certain things Perfmon can't. I am referring to common shared metrics.

In general, does Microsoft have best practice, to use Perfmon or DMV in SQL Server? I assume answer is Perfmon with remote computer, since measuring things with DMV takes lag/pressure on the central server. Perfmon can be utilized from remote server, and reduce load.

Thank you,

closed as too broad by Erik Darling, Marco, hot2use, Tom V, mustaccio Dec 11 '17 at 14:22

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

2

I think the best advice is to use everything that is available, or at least the bits that work best for your workload.

Also consider extended events (often abbreviated to XE) which while harder to get into initially can offer a wealth of information about specific events rather than just counts of things happening as you'll get from PerfMon, and imposes less load than constantly polling DMVs.

Use PerfMon to monitor the general health of the SQL instances, and when that or other indications suggest a problem use information from DMVs to look at what is happening now or has been happening recently, and extended events to "catch things live" and record them for later analysis.

[SQL Profiler can do a fair amount of what XE does too, and is easier to get started with, but it is deprecated so hasn't been updated much since 2012, may vanish completely in later releases, and isn't available against Azure databases at all (apart of course from on-prem SQL Server running in VMs)]

1

Where do you think Perfmon gets it's data from? It's no coincidence that the SQL Server specific metrics are labelled the same as they are in SQL Server.

The DMVs are really lightweight, so reading dm_os_performance counters is not going to cause much, if any, contention. The only problem you will have in using the DMVs is calculating the difference for the counters that measure across time sample intervals. This is where Perfmon is more beneficial, it does all the math for you, and also has a GUI... everybody loves a GUI.

you can find all the information on counter types here: https://msdn.microsoft.com/library/aa394569.aspx

In order to capture my data, I use a data warehouse type setup. I log all the measurement data in to a local utility db and then use an ETL method to process it in to my data warehouse.

Also, extended events are good to understand, but they should really be used for short-term investigations in to performance. I personally wouldn't recommend using them for prolonged performance monitoring.