How to get current CPU/memory usage
-
I have several SQL Server instances. I want to know, at any given time, the CPU time and system memory used by each instance. How can I get this info ?
-
Are you talking about getting the live, or historical values?
Live performance data can be seen in Windows System Monitor in Administrative Tools.
For historical data, you would need to capture that information first using Performance Logs, or you could try the new SQL Health and History tool from Microsoft.
--------------------
Colt 45 - the original point and click interface
-
Live performance data can be seen in Windows System Monitor in Administrative Tools.
To add to that, this information can also be collected directly from the master.dbo.sysperfinfo table.
-
I thought it was just the SQL Server performance counters that were stored in sysperfinfo
--------------------
Colt 45 - the original point and click interface -
Oops, apparantly I didn't read the question properly..
-
Here's a query that will show the CPU utilisation, if that helps (adjust for your hardware):
/* This query will measure SQL server CPU utilization over a brief timespan. */
declare @timespan char(9)
set @timespan = '000:00:05'
declare @numberOfCPUs int
set @numberOfCPUs = 4
declare @ticksPerMS decimal
set @ticksPerMS = @@timeticks / 1000
declare @startcpu decimal
declare @startidle decimal
declare @starttime datetime
declare @endcpu decimal
declare @endidle decimal
declare @endtime datetime
set @startcpu = @@cpu_busy
set @startidle = @@idle
set @starttime = getdate()
waitfor delay @timespan
set @endcpu = @@cpu_busy
set @endidle = @@idle
set @endtime = getdate()
select (@endcpu - @startcpu) * @ticksPerMS as SQL_busy_proc_ms
, cast(
round(
(@endcpu - @startcpu) * @ticksPerMS/@numberOfCPUs / datediff( ms, @starttime, @endtime) * 100 , 0
) as int )
as SQL_CPU_Percent
, (@endidle - @startidle) * @ticksPerMS as SQL_idle_proc_ms
, cast(
round(
(@endidle - @startidle) * @ticksPerMS/@numberOfCPUs / datediff( ms, @starttime, @endtime) * 100 , 0
) as int )
as SQL_Idle_Percent
, datediff( ms, @starttime, @endtime) * @numberOfCPUs as elapsed_proc_ms
,@endtime as [At]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply