Capture PERFMON counters on fly

Could anyone help to capture perfmon counters including SQL Server related to
a table on fly. I'm aware of DBCC PERFMON but it doesn't give all the
counters that are related to SQL SErver.
Thanks in advance.
Check out master..sysperfinfo. Note though that the average values are not timeadjusted in this
table. Search KB and you will find an article on this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Satya SKJ" <satyaskj@.yahoo.co.uk> wrote in message
news:3433A607-BE42-4B8D-B99B-7CE406795C37@.microsoft.com...
> Could anyone help to capture perfmon counters including SQL Server related to
> a table on fly. I'm aware of DBCC PERFMON but it doesn't give all the
> counters that are related to SQL SErver.
> Thanks in advance.

Capture CPU Utilization in TSQL

Happy New Year everyone!

I would like to capture CPU Utilization % using TSQL. I know this can
be done using PerfMon but I would like to run TSQL command (maybe once
every 5 minutes) and see what is the CPU Utilization at that instant so
that I can insert the value in a table and run reports based on the
data.

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'

Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Any help would be appreciated.

ThanksSQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'
>
Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.

But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your quick response Erland. I ran the following script but I
don't think this is the correct value. But I cannot find anything
meaningful?

DECLARE
@.CPUBusy1 bigint
, @.CPUBusy2 bigint
, @.TimeTicks1 bigint
, @.TimeTicks2 bigint

SELECT
@.CPUBusy1 = @.@.CPU_BUSY
, @.TimeTicks1 = @.@.TIMETICKS

WAITFOR DELAY '0:00:01'

SELECT
@.CPUBusy2 = @.@.CPU_BUSY
, @.TimeTicks2 = @.@.TIMETICKS

SELECT
@.CPUBusy1 AS CPUBusy1
, @.CPUBusy2 AS CPUBusy2
, @.CPUBusy2 - @.CPUBusy1 AS CPUDiff
, @.TimeTicks1 AS TimeTicks1
, @.TimeTicks2 AS TimeTicks2
, @.TimeTicks2 - @.TimeTicks1 AS TimeTicksDiff

Thanks for your time and help!

Vishal

Erland Sommarskog wrote:

Quote:

Originally Posted by

SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'

Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

>
Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.
>
But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Thanks everyone for reading this and your responses. I was able to find
the correct solution in another Google post:

DECLARE
@.CPU_BUSY int
, @.IDLE int

SELECT
@.CPU_BUSY = @.@.CPU_BUSY
, @.IDLE = @.@.IDLE

WAITFOR DELAY '000:00:01'

SELECT
(@.@.CPU_BUSY - @.CPU_BUSY)/((@.@.IDLE - @.IDLE + @.@.CPU_BUSY - @.CPU_BUSY) *
1.00) *100 AS CPUBusyPct

This solution was posted by Gert-Jan Strik on Tues, Jan 14 2003 6:55
PM.
Here is the URL for the thread:
http://groups.google.com/group/comp...f67728586773e8b
Thanks again,

Vishal

SQLJunkie wrote:

Quote:

Originally Posted by

Thanks for your quick response Erland. I ran the following script but I
don't think this is the correct value. But I cannot find anything
meaningful?
>
DECLARE
@.CPUBusy1 bigint
, @.CPUBusy2 bigint
, @.TimeTicks1 bigint
, @.TimeTicks2 bigint
>
SELECT
@.CPUBusy1 = @.@.CPU_BUSY
, @.TimeTicks1 = @.@.TIMETICKS
>
WAITFOR DELAY '0:00:01'
>
SELECT
@.CPUBusy2 = @.@.CPU_BUSY
, @.TimeTicks2 = @.@.TIMETICKS
>
SELECT
@.CPUBusy1 AS CPUBusy1
, @.CPUBusy2 AS CPUBusy2
, @.CPUBusy2 - @.CPUBusy1 AS CPUDiff
, @.TimeTicks1 AS TimeTicks1
, @.TimeTicks2 AS TimeTicks2
, @.TimeTicks2 - @.TimeTicks1 AS TimeTicksDiff
>
Thanks for your time and help!
>
>
Vishal
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

SQLJunkie (vsinha73@.gmail.com) writes:

Quote:

Originally Posted by

I have spent a good amount of time scouring google groups but this is
all I have found:
SELECT
(CAST(@.@.CPU_BUSY AS float)
* @.@.TIMETICKS
/ 10000.00
/ CAST(DATEDIFF (s, SP2.Login_Time, GETDATE()) AS float)) AS
CPUBusyPct
FROM
master..SysProcesses AS SP2
WHERE
SP2.Cmd = 'LAZY WRITER'
>
Problem is this gives me total amount of time CPU in %) has been busy
since the server last started. What I want is the % for the instant -
the same number we see in Task Manager and PerfMon.

Performance counters are in sysperfinfo on SQL 2000 and
sys.dm_os_performance_counters on SQL 2005, but I could find the item
you are looking for in these views.

But I saw in Books Online for SQL 2005 that these values are cumultative. To
get the present value, sample with some interval. I guess you could to
the same: query @.@.CPU_BUSY twice with a second or so in between.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Capture counters to analyze disk I/O contention

Hi,
I'm trying to capture some counters in perfmon/sysmon that would help me
analyze if there is any disk I/O contention between SQL and other processes
(OS and/or others). Does anyone know if there is/are any counters that will
allow me to capture this info?
Thanks.Hi
TYou don't give the version of SQL Server you are using! For SQL 2000 you
may want to look at DBCC SQLPERF (WAITSTATS) or the DMVs in SQL 2005 see
http://blogs.msdn.com/sqlcat/archiv.../05/461199.aspx for examples.
For performance counters you can not separate the individual process usage
but you can see the general usage and
http://www.sql-server-performance.c...nce_article.asp
lists some of the counters you can look at. You may have to use emperical
observations to decide what is causing the contention, e.g. Make
observations; separate logs from data files; make observations; move tempdb
to it's own spindles; make observations;
separate databases onto own spindles; make observations; etc...
If you are using a SAN then you can also get stats from that (but it will
not identify the specific processes).
John
"Rob" wrote:

> Hi,
> I'm trying to capture some counters in perfmon/sysmon that would help me
> analyze if there is any disk I/O contention between SQL and other processe
s
> (OS and/or others). Does anyone know if there is/are any counters that wil
l
> allow me to capture this info?
> Thanks.

Capture counters to analyze disk I/O contention

Hi,
I'm trying to capture some counters in perfmon/sysmon that would help me
analyze if there is any disk I/O contention between SQL and other processes
(OS and/or others). Does anyone know if there is/are any counters that will
allow me to capture this info?
Thanks.Hi
TYou don't give the version of SQL Server you are using! For SQL 2000 you
may want to look at DBCC SQLPERF (WAITSTATS) or the DMVs in SQL 2005 see
http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx for examples.
For performance counters you can not separate the individual process usage
but you can see the general usage and
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
lists some of the counters you can look at. You may have to use emperical
observations to decide what is causing the contention, e.g. Make
observations; separate logs from data files; make observations; move tempdb
to it's own spindles; make observations;
separate databases onto own spindles; make observations; etc...
If you are using a SAN then you can also get stats from that (but it will
not identify the specific processes).
John
"Rob" wrote:
> Hi,
> I'm trying to capture some counters in perfmon/sysmon that would help me
> analyze if there is any disk I/O contention between SQL and other processes
> (OS and/or others). Does anyone know if there is/are any counters that will
> allow me to capture this info?
> Thanks.

Capture counters to analyze disk I/O contention

Hi,
I'm trying to capture some counters in perfmon/sysmon that would help me
analyze if there is any disk I/O contention between SQL and other processes
(OS and/or others). Does anyone know if there is/are any counters that will
allow me to capture this info?
Thanks.
Hi
TYou don't give the version of SQL Server you are using! For SQL 2000 you
may want to look at DBCC SQLPERF (WAITSTATS) or the DMVs in SQL 2005 see
http://blogs.msdn.com/sqlcat/archive...05/461199.aspx for examples.
For performance counters you can not separate the individual process usage
but you can see the general usage and
http://www.sql-server-performance.co...ce_article.asp
lists some of the counters you can look at. You may have to use emperical
observations to decide what is causing the contention, e.g. Make
observations; separate logs from data files; make observations; move tempdb
to it's own spindles; make observations;
separate databases onto own spindles; make observations; etc...
If you are using a SAN then you can also get stats from that (but it will
not identify the specific processes).
John
"Rob" wrote:

> Hi,
> I'm trying to capture some counters in perfmon/sysmon that would help me
> analyze if there is any disk I/O contention between SQL and other processes
> (OS and/or others). Does anyone know if there is/are any counters that will
> allow me to capture this info?
> Thanks.