@@cpu_busy and @@idle

[griffithsandrew]

Should these two stats added together give me the time (ms) since SQL Server has been restarted? Maybe I don't understand but @@cpu_busy is the amount of time sql server has accessed the cpu and @@idle is the amount of time sql server has no cpu activity. When I add these two figures together, I don't get the total amount of time (ms) SQL Server has been running. What am I not doing? Any help is appreciated.
Thanks

[rmiao]

Those are cpu time for sql service, and other processes on the server use cpu as well.

[griffithsandrew]

Thanks. I understand @@cpu_busy and @@idle don't represent all apps on the box, just SQL Server. @@cpu_busy and @@idle are both reset to 0 when SQL service is restarted. I restarted this service several hours ago but when I enter select (@@cpu_busy + @@idle) the time returned is only about 25 minutes (converted from milliseconds). I'm pretty sure I'm doing the conversion right but this number doesn't match the expected return time.

[Markus]

If it's just the time since server startup you're looking for try this:

DECLARE @Runtime as datetime
SET @runtime = (SELECT Getdate()- login_time from master..sysprocesses where spid = 1)

SELECT Year(@runtime)-1900 as Years,
Month(@runtime) as Months,
Day(@runtime) as Days,
Datepart(hour,@runtime) as Hours,
Datepart(minute,@runtime) as Minutes,
Datepart(second, @runtime) as Seconds

[griffithsandrew]

Thanks for the feedback! I'll use the sysprocesses to get uptime. I guess however, I'd still like to understand better the definition of idle. I understand cpu_busy; the # of ms the sql server process accessed the cpu. What does idle mean? Using the word idle in the definition doesn't help because to me, idle means no cpu activity...so my question is still out there...wouldn't cpu_busy + idle = total time in ms? Am I mnissing something? Thanks for your feedback!

[skhanal]

This is what I understand from BOL, @@IDLE is idle time for SQL Server, but CPU may be doing OS related things, so there is a overlap.

[griffithsandrew]

I think cpu_busy only tracks how much time sql server access the cpu. It doesn't track other applications. I ask you (fellow SQL types)to try this on your boxes. (@@cpu_busy + @@idle)/1000/60 and examine the results (in minutes). The number of minutes returned by this query are significantly less than the expected results. Not sure why...

[skhanal]

True.

But you can get total uptime if

@@IDLE = SQL idle time
@@CPU_BUSY = SQL busy time

which is not what SQL BOL says.

@@CPU_BUSY is CPU busy with SQL business

@@IDLE <> @@CPU_NOT_BUSY

[griffithsandrew]

Thanks. I get it now. But what does idle mean? Per BOL, "Returns the time in milliseconds (based on the resolution of the system timer) that Microsoft® SQL Server™ has been idle since last started." What does idle mean? If SQL Server is accessing the cpu, is it idle? Is it not running a job or open transaction? Can anyone define this? Thanks.