[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Perfmon counters

Posted on 2011-05-03
5
Medium Priority
?
978 Views
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.

0
Comment
Question by:Krishyogi
4 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 35513545
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
 
LVL 18

Expert Comment

by:sventhan
ID: 35515900
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
 

Author Comment

by:Krishyogi
ID: 35793915
I used run as in win 2008 and now its working fine.
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 36016950
Question PAQ'd and stored in the solution database.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

830 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