I have SQL query for Buffer manager Lazy writes/sec, can I use the same query in DPA and create a custom report in table format, if yes then how can I create a report in table format.?
Below is the SQL query:
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Lazy writes/sec'
Yes, you could plug this query into a Custom Metric in DPA. Keep in mind that this counter in SQL Server is incremental and represents the value since instance restart. In this case, to get a per second value, use the Rate type of metric. DPA will take 2 values based on frequency, do a delta and then divide by the frequency to get the value. Also note that I only include the cntr_value column in the query so it can be used in a custom metric.
