SQL Server T-SQL User-Defined Function of the Week

How Much of the CPU is SQL Server Using?

Volume 1 Number 39    August 12, 2003

Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

In an article published last week on www.DatabaseJournal.com 
I wrote about using system statistical functions, sp_monitor, 
and fn_virtualfilestats for accumulating Input/Output statistics
about a SQL Server instance.  You'll find the article at:
http://databasejournal.com/article.php/2244381

One of the tricks about @@CPU_BUSY, @@IDLE, and @@IO_BUSY is that
the Books Online appears to be incorrect when it states that
these functions return a time in milliseconds.  It appears that
they return a time in timer ticks instead.  

To convert from timer ticks to seconds, it's necessary to multiply
by the value of another system statistical function @@TIMETICK,
which returns the number of microseconds (millionths of a second)
in a tick.

The formula for CPU seconds is:

   @@CPU_BUSY * @@TIMETICK / 1000000
   
On Intel 386, 486, and Pentium based systems, a timer tick is 
1/32nd of a second or 31250 microseconds.  I suspect that it's 
different on Itanium, Alpha, and XScale processors.

This week's UDF, udf_Instance_CPU_BUSY_Percent shows the percent
of CPU time used by SQL Server since it was started.  To compute
it we first have to know the number of seconds since SQL Server
started.  The function udf_SQL_StartDT provides the date time
that the server started.  The function is available in Issue 11,
which you'll find at this URL:
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-11-udf_SQL_StartDT.htm

udf_Instance_CPU_BUSY_Percent relies on three non-deterministic built-in
functions: getdate, @@CPU_BUSY, and @@TIMETICK.  Normally, a
non-deterministic function can't be used in a UDF.  There's a 
way around it that you can read about in Issue 2. It involves
creating a view that invokes the function.  You'll need one
view for each function.  The one for getdate is in Issue 2, which
you can get at this URL:
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-2-udf_DT_CurrTime.htm

The script to create the other two views follows:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE  VIEW dbo.Function_Assist_TIMETICKS

    WITH SCHEMABINDING
/*
* A view to return one row, with one column, the current 
* value of the built-in function @@TIMETICKS.  This view 
* allows a UDF to bypass the restriction on access to 
* the non-deterministic @@TIMETICKS function.
*
* Example:
DECLARE @dtVar int
select @dtVAr = [TIMETICKS] from Function_Assist_TIMETICKS
**********************************************************/

AS 
    SELECT @@TIMETICKS as [TIMETICKS]
GO

GRANT SELECT on dbo.Function_Assist_TIMETICKS to PUBLIC
GO


CREATE  VIEW dbo.Function_Assist_CPU_BUSY

    WITH SCHEMABINDING
/*
* A view to return one row, with one column, the current 
* value of the built-in function @@CPU_BUSY.  This view 
* allows a UDF to bypass the restriction on access to 
* the non-deterministic @@CPU_BUSY function.
*
* Example:
DECLARE @dtVar int
select @dtVAr = [CPU_BUSY] from Function_Assist_CPU_BUSY
**********************************************************/

AS 
    SELECT @@CPU_BUSY as [CPU_BUSY]

GO

GRANT SELECT on dbo.Function_Assist_CPU_BUSY to PUBLIC
GO
\-------Stop copying above this line ---------------------------/


Once the views are defined, the function can be created. Here's 
the CREATE FUNCTION script:
/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO


CREATE  FUNCTION dbo.udf_Instance_CPU_BUSY_Percent (

)   RETURNS numeric (18,2) -- Percentage of the 
    -- NO SCHEMABINDING 
/* 
* Computes the percentage of time that the CPU has been busy with
* SQL Server work since the SQL Server Instance was started.
*
* Example:
select dbo.udf_Instance_CPU_BUSY_Percent()
* 
* History:
* When          Who     Description
* ------------- ------- -----------------------------------------
* 2003-08-04    ASN     Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as the T-SQL UDF of the Week Vol 1 #39  8/12/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

DECLARE @Percent_CPU_BUSY numeric (18,2)
      , @SecondsFromStart int -- sec since the instance started

SELECT @SecondsFromStart = DATEDIFF (s
                                   , dbo.udf_SQL_StartDT()
                                   , [getdate]
                                    )
    FROM Function_Assist_Getdate

SELECT @Percent_CPU_Busy = CAST([cpu_busy] as float) 
                         * [timeticks] 
                         / 10000.0
                         / CAST(@SecondsFromStart as float)
    FROM FUNCTION_Assist_CPU_BUSY
       CROSS JOIN FUNCTION_Assist_TimeTicks

RETURN @Percent_CPU_BUSY

END

GO

GRANT EXEC on [dbo].[udf_Instance_CPU_BUSY_Percent] to PUBLIC
GO
\-------Stop copying above this line ---------------------------/

Now that the function is ready, let's try it out:
/------- Start copying below this line -------------------------\
select dbo.udf_Instance_CPU_BUSY_Percent() as [CPU_BUSY_Percent]
GO
\-------Stop copying above this line ---------------------------/
(Results)
CPU_BUSY_Percent     
-------------------- 
                9.05 

That looks like a reasonable number for CPU percentage to me. On 
a server that's dedicated to SQL Server, I don't worry about the
number until it gets over 70 percent.  Of course, the percentage
here is an average since the instance started.  What's more 
important is peak usage.  To get a quick look at that, use 
sp_monitor.  There's more about how it works in the article.


+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+