Today’s script is based on performance counters and in particular the sys.dm_performance_counters DMV. There are many ways in which to view and collect performance counters the most common of which is to use perfmon. Powershell is another method, I am currently working on a project where I will be collecting a defined set of performance counters using Powershell, I have used Allen White’s example as a starting point. The counters will be recorded locally on each server for a month and archived to a central repository for reporting purposes.
The script below however uses as I mentioned earlier the sys.dm_performance_counters DMV I have not been selective here as different counters wil be useful in different environments.
If the installation instance of SQL Server fails to display the performance counters of the Windows operating system, use the following Transact-SQL query to confirm that performance counters have been disabled.
SELECT
COUNT(*)
FROM
sys.dm_os_performance_counters;
GO
If the return value is 0 rows, this means that the performance counters have been disabled. You should then look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions." This denotes that performance counters were not enabled. The errors immediately before the 3409 error should indicate the root cause for the failure of performance counter enabling.
/*
-----------------------------------------------------------------
Performance Counters
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Change database context
USE master;
GO
-- Get performance counters
SELECT
[object_name],
counter_name,
instance_name,
cntr_value,
cntr_type
FROM
sys.dm_os_performance_counters;
GO
Enjoy!
Chris
