CPU % for a database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

CPU % for a database

Author

Topic

schinni
Yak Posting Veteran

66 Posts

Posted - 2004-06-03 : 15:25:43

Hello,

IS there a way to find CPU used by a database on a server,i know
we have cpu time in sysprocesses and in the trace but i am trying to
relate the cputime to the %CPU used is there a way to do that..

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-03 : 15:34:17

cputime is amount of time since the connection was established. You can't make a connection to %CPU. I know of no tool to find out how much CPU each database is consuming. You can only find this out per SQL instance using Process:%Processor Time (select each sqlservr.exe).

Tara

schinni
Yak Posting Veteran

66 Posts

Posted - 2004-06-03 : 21:54:48

So cputime is not the time taken by the sqlstatement or transaction.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-04 : 12:15:05

You can get a rough idea of CPU utilization for the server with T-SQL:

SET NOCOUNT ON
DECLARE @CPUCOUNT INT
DECLARE @OCPU INT
DECLARE @OIDLE INT
SET @CPUCOUNT = 4
SELECT @OCPU = @@CPU_BUSY, @OIDLE = @@IDLE
WAITFOR DELAY '00:00:05' -- pause 5 seconds
SELECT 'CPU Busy: ' + Convert(varchar,convert(decimal(4,2),((@@CPU_BUSY - @OCPU) * 31.25 / 5000 / @CPUCOUNT) * 100)) + '% CPU Idle: '
+ Convert(varchar,convert(decimal(4,2),((@@IDLE - @OIDLE) * 31.25 / 5000 / @CPUCOUNT) * 100)) + '%' AS Utilization

Utilization
----------------------------------
CPU Busy: 43.28% CPU Idle: 46.72%

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-04 : 12:22:28

CPUTime is the time taken for all queries since that spid connected to SQL.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources