SQL SERVER – System Function @@IDLE to Find System Ideal Time

Just the other day I got an email with user asking how does the @@idle function work with SQL Server as when he runs them it gives him some number but the number is not easy to interpret. I totally agree with the observation. When I was new to SQL Server, the best learning resource available was SQL Server Books On Line. I used to read Books On Line and learn quite a bit from it.

To understand how @@idle function works you will have to understand one more function and that is @@timeticks. Let us first understand how both the functions work and see a quick demonstration for the same.

Definitions

The CPU time increment is commonly known as ‘ticks‘.

@@IDLE – The number of time ticks that SQL Server has been idle since the last restart of SQL Server services. In SQL Server this function returns cumulative for all the CPUs available to SQL Server.

@@TIMETICKS – The number of microseconds per time ticks. This value is machine (CPU) dependent. The value for each tick for SQL Server on Windows is 31.25 milliseconds.

Find System Ideal Time

Now let us try to find system idle time with the help of two of the functions which we have just learned. To find Ideal time in SQL Server we will have to multiply ideal time with time tick.

SELECT @@IDLE 'Idle (Unit: Ticks)',
@@TIMETICKS 'Ticks (Microseconds per ticks)',
@@IDLE*CAST(@@TIMETICKS AS FLOAT) AS 'Idle (Unit: Microseconds)' 

Here is the result set of the query:

Question for you: Were you aware of this function? Do you know any such interesting usage of other system functions? Please leave a comment and I will publish it on the blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)