|
|
SQL Server T-SQL User-Defined Function of the Week
Calculate SQL Server Runtime Fees with a
User-Defined Function
Volume 1 Number 20 April 1, 2003
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.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
Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML, and security.
Microsoft's new execution time billing for SQL Server goes into
effect today 4/1/2003. This week's UDF of the Week helps you
calculate the bill as the meter ticks away.
I'm pretty sure execution billing is going to be a boon to SQL
Server outsourcing companies. Once you see the magnitude of the
charges there's a good chance you'll start considering
outsourcing your databases. The outsource companies can offer
lower execution time fees due to their very large volume purchase
agreements with Microsoft.
ORACLE and IBM are in the process of putting comparable fees into
place. The rumor is that they'll be even larger than what
Microsoft is charging and there won't be any incentive to switch.
Microsoft may even pick up some business.
The actual billing algorithm is complex but this UDF can
handle it. Most organizations will start in tier 1 so you can
use the default for both the parameters. Here's the
CREATE FUNCTION script:
/-------- Copy from below this line ----------------------------\
-- Script generated April 1, 2003
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_SQL_RuntimeFee (
@Tier int = 1 -- Billing Tier based on volume agreement
, @Edition nvarchar(30) = NULL -- Which SQL Server Edition
-- NULL for the current edition
) RETURNS money -- Fee for the current
-- no SCHEMABINDING due to use of fn_virtualfilestats
/*
* Calculates the SQL Server run time fees since SQL Server was
* started. Goes into effect 4/1/2003
*
* Example:
select dbo.udf_SQL_RuntimeFee(default, NULL)
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use and redistribute this UDF as you like so long
* as it remains intact and credit is given.
*
* Published - T-SQL UDF of the Week Newsletter Vol 1 #20 4/1/2003
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
DECLARE @Fee money -- Total Runtime fee for the time since sql
-- Server was started.
, @VolDiscount float -- Volume Discount Based on Tier
, @EditionMult float -- Multiplier based on edition
, @StorageRate float -- $/megabyte
, @IORate float -- $/1000 I/O operations
, @StorageUnits int -- Megabytes that can be billed
, @IOUnits int -- 1000 I/O operations
, @TimeUP int -- Second since the system started.
-- The tier determines the IO rate and
-- the storage rate.
-- 1st check for valid input.
IF @TIER IS NULL OR @Tier NOT BETWEEN 1 and 3 RETURN NULL
SELECT @StorageRate = CASE @Tier
WHEN 1 THEN 0.00004
WHEN 2 THEN 0.0000122
WHEN 3 THEN 0.0000043
END
, @IORate = CASE @Tier
WHEN 1 THEN 0.001
WHEN 2 THEN 0.0005
WHEN 3 THEN 0.00005
END
-- The edition creates a multiplier effect
IF @Edition IS NULL -- IF none supplied use current
SELECT @Edition = CAST (SERVERPROPERTY ('Edition')
as nvarchar(30))
SELECT @EditionMult = CASE @Edition
WHEN 'Desktop Edition' THEN 0.20
WHEN 'Personal Edition' THEN 0.20
WHEN 'Developer Edition' THEN 0.10
WHEN 'Standard Edition' THEN 0.7
WHEN 'Enterprise Evaluation Edition' THEN 0
WHEN 'Enterprise Edition' THEN 1.0
ELSE -1
END
IF @EditionMult = -1 RETURN NULL -- user supplied invalid input
-- Get the measurements since the system came up
SELECT @IOUnits = SUM(NumberReads + NumberWrites) / 1000
FROM ::fn_virtualfilestats(-1, -1)
select @StorageUnits = SUM(size)
FROM master..sysaltfiles
SELECT @VolDiscount = CASE
WHEN @StorageUnits BETWEEN 0 AND 100 THEN 1.0
WHEN @StorageUnits BETWEEN 101 and 500 THEN 0.8
WHEN @StorageUnits BETWEEN 501 and 2000 THEN 0.66667
WHEN @StorageUnits BETWEEN 2001 and 10000 THEN 0.3333
WHEN @StorageUnits BETWEEN 10001 and 100000 THEN 0.1
WHEN @StorageUnits BETWEEN 100000 and 1000000 THEN 0.05
ELSE 0.02
END
-- Combine the factors to calculate the fee
SELECT @Fee = @EditionMult * @VolDiscount
* ( @StorageRate * @StorageUnits
+ @IORate * @IOUnits
)
RETURN @Fee -- $ and lots of them to Redmond. Effective 4/1/2003
END
GO
GRANT EXEC ON [dbo].[udf_SQL_RuntimeFee] TO [PUBLIC]
GO
\-------- Stop copying above this line -------------------------/
Once you've created the UDF, start by checking out your current
fees with this query:
/-------- Copy from below this line ----------------------------\
select dbo.udf_SQL_RuntimeFee(default, NULL) as [SQL Server Fee]
GO
\-------- Stop copying above this line -------------------------/
(Results)
SQL Server Fee
---------------------
.1846
(1 row(s) affected)
The results are from my Developer Edition system so they're
pretty low, only 18 cents. The system had been up for about a
day at the time I ran the query.
I tried udf_SQL_RuntimeFee on the system used by of one of my
clients for whom I do a lot of SQL Server Consulting and they
were running at a rate close to $200.00 per day. That's on top
of the SQL Licensing fees and CALs.
Near the bottom of the UDF you'll see why I said that outsourcing
may be the way to go. The volume discount is steep with
terabyte size systems paying only 2 percent of what a small
system pays for the same service.
These fees are effective today April 1, 2003. Don't fool around.
Please don't forget to share this issue with anyone interested
in SQL Server. Thanks.
+--------------------------------------------------------------+
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
+--------------------------------------------------------------+
|
|
