Hi there,
Was wondering what is the best way to add this perfmon counter as a custom metric in the DPA tool.
Thanks in advance!
If you have the query, you can create the custom metric. However, you need the query pulling the right info first from say SSMS.
That's the thing...it doesn't look like you can obtain data for this counter via t-sql since it's an OS-level metric , so I'm wondering if there's a way to tap into perfmon directly from the DPA tool.
I'm not sure if this is exactly what you need, but on SQL 2008 and higher you can use the sys.dm_os_process_memory DMV and examine the page_fault_count data "Indicates the number of page faults that are incurred by the SQL Server process."
sys.dm_os_process_memory (Transact-SQL)
@gcp: Thanks for the suggestion but I think there is a difference between what I'm looking for and the page_fault_count data in the sys.dm_os_process_memory DMV. The "Memory: Pages/Sec" perfmon counter “Shows the rate at which pages are read from or written to disk to resolve hard page faults. This counter is a primary indicator of the kinds of faults that cause system-wide delays. It is the sum of Memory\ Pages Input/sec and Memory\ Pages Output/sec. It is counted in numbers of pages, so it can be compared to other counts of pages, such as Memory\ Page Faults/sec, without conversion. It includes pages retrieved to satisfy faults in the file system cache (usually requested by applications) and noncached mapped memory files.”
You can pull that data, but you will have to do it using WMI calls via SQL Server. We had metrics using OLE automation in prior versions and went away from them due to security concerns and stability issues (WMI calls going out to lunch and never returning).
If you want to pursue this course, you can look at the default_metrics_queries.xml file located in <install_dir>\iwc\tomcat\webapps\iwc\WEB-INF\classes\resources\metrics
Do a search for "sp_oamethod" and you will see examples of how we were getting counters previously. Use at your own risk and just know that customization like this is not technically supported and could impact your monitored instance and our ability to continue monitoring it (we limit # of connections to minimize overhead).
