Perfmon counters

I have setup perfmon counter on cluster server to collect data in SQL database. Its running but not inserting data in SQL databases.

KrishyogiAsked:
Who is Participating?
 
ee_autoConnect With a Mentor Commented:
Question PAQ'd and stored in the solution database.
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

•Connect to SQL Server and select a database (to create the procedure and table)
•Create the Performance counter table using the script below

--Table to store perf counter values
IF EXISTS (SELECT OBJECT_ID('Perfcounters'))
DROP TABLE Perfcounters
GO
CREATE TABLE Perfcounters(CapturedTime DATETIME NOT NULL,SQLProcessorUtilization BIGINT, AvailablePhysicalMemory_Kb BIGINT,Buffercachehitratio BIGINT, Pagelifeexpectancy BIGINT,UserConnections BIGINT, FullScans_p_sec BIGINT, PageSplits_p_sec BIGINT)
GO

Open in new window



•After creating the table run the below script to create the procedure, run it in same db.

--Stored Procedure to get counter values to table
IF EXISTS (SELECT OBJECT_ID('Perfcounter'))
DROP PROCEDURE Perfcounter
GO
CREATE PROCEDURE Perfcounter
AS
WITH ring_buffers(ring_id,SQLProcessorUtilization)
AS
(SELECT record.value('(./Record/@id)[1]', 'int') as record_id,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
FROM (SELECT CONVERT(XML, record) AS record 
from sys.dm_os_ring_buffers 
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%%') AS x) INSERT Perfcounters SELECT TOP 1 CURRENT_TIMESTAMP,ring_buffers.SQLProcessorUtilization ,a.available_physical_memory_kb,pt.* FROM(
SELECT RTRIM(object_name) + ' : ' + counter_name CounterName, cntr_value from sys.dm_os_performance_counters
where RTRIM(object_name) + ' : ' + counter_name in ('SQLServer:Buffer Manager : Buffer cache hit ratio','SQLServer:Buffer Manager : Page life expectancy','SQLServer:General Statistics : User Connections','SQLServer:Access Methods : Full Scans/sec','SQLServer:Access Methods : Page Splits/sec')) as SourceData
PIVOT(SUM(cntr_value)
FOR CounterName IN ([SQLServer:Buffer Manager : Buffer cache hit ratio],[SQLServer:Buffer Manager : Page life expectancy],[SQLServer:General Statistics : User Connections],[SQLServer:Access Methods : Full Scans/sec],[SQLServer:Access Methods : Page Splits/sec])) pt, ring_buffers,sys.dm_os_sys_memory a

Open in new window



•Now Create a SQL job and just execute this procedure and make this job to run every 5 or 10 or 15 seconds (based on your requirement) to capture the data.
That’s it you are done with it. You can use the data in the table “Perfcounters” to analyze the server status. For sample I’m capturing below counter values in this script, however  you can add any SQL performance counters (all counter details can be found in DMV page, click on the link given above) to get the data.

1.SQL Server processor Utilization
2.Free memory in KB
3.SQLServer:Buffer Manager : Buffer cache hit ratio
4.SQLServer:Buffer Manager : Page life expectancy
5.SQLServer:General Statistics : User Connections
6.SQLServer:Access Methods : Full Scans/sec
7.SQLServer:Access Methods : Page Splits/sec
To retrieve the data just select data from “Percounters” database


select * from perfcounters order by capturedtime desc

Open in new window


Read more: http://sql-articles.com/scripts/capture-sql-performance-counters-through-tsql
0
 
sventhanCommented:
you could also use the DMV to get the same information....

SELECT *
FROM sys.[dm_os_performance_counters]  
ORDER BY[object_name];

http://www.mssqltips.com/tip.asp?tip=2042
0
 
KrishyogiAuthor Commented:
I used run as in win 2008 and now its working fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.