Perfmon counters

Posted on 2011-05-03
Last Modified: 2012-05-11
I have setup perfmon counter on cluster server to collect data in SQL database. Its running but not inserting data in SQL databases.

Question by:Krishyogi
    LVL 15

    Expert Comment

    by:Aaron Shilo

    •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
    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)

    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
    CREATE PROCEDURE Perfcounter
    WITH ring_buffers(ring_id,SQLProcessorUtilization)
    (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
    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:
    LVL 18

    Expert Comment

    you could also use the DMV to get the same information....

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

    Author Comment

    I used run as in win 2008 and now its working fine.

    Accepted Solution

    Question PAQ'd and stored in the solution database.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now