|
|
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
+--------------------------------------------------------------+
|
|
