Quick performance test in SQL Server

In order to test Performance in SQL Server, normally you would take a look at how may I/O or how many cycles a statement takes to complete. To make it less complex, you can take a look at how long a statements takes, just by looking at the execution-time. In most cases, network latency, SQL Server hardware and workstation performance are also included in this result (time to create result set, and time needed to send across the internet or internal network). And if a statement takes les then a second to execute, you need to perform some magic with dates, times, etc. It is what it sounds like: overkill. There is a simple way to test performance in SQL Server.
 
In order to understand the statement, first a small explanation of the keywords:
 

@@CPU_BUSY: Returns the ticks “spend” since the last execution of the statement
ISNULL: Replaces NULL with the specified replacement value
COALESCE: Returns the first non-NULL value from it’s replacement values

 
So basically ISNULL and COALESCE provide you with the same functionality( at least, the way I used it in the script below), except COALESCE can be used to prevent the inevitable bunch of nested ISNULL statements.
 
To test the statements, I used the following script:

DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY 
DECLARE @StartDate DATETIME SET @StartDate = GETDATE()

WHILE @Loops <= 1000000
BEGIN
    IF COALESCE('123', '456') = '456'
            PRINT 1
    SET @Loops = @Loops + 1
END

PRINT 'COALESCE, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO

--==================================================

DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY
DECLARE @StartDate DATETIME SET @StartDate = GETDATE() 

WHILE @Loops <= 1000000
BEGIN
    IF ISNULL('123', '456') = '456'
            PRINT 1
    SET @Loops = @Loops + 1
END

PRINT 'ISNULL, both non-NULL'
PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)
PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE()))
PRINT ''
GO

 
If you execute this statement, you will see the following results:

COALESCE, both non-NULL
Total CPU time: 16
Total milliseconds: 566

ISNULL, both non-NULL
Total CPU time: 19
Total milliseconds: 640

 
As you can see, the COALESCE takes less time to complete then the ISNULL function. The total CPU time needed is shorter, and looking at the used time, it saves you 74 milliseconds. In this case it isn’t that much, but with larger queries this can save you a lot of time.
 
The actual CPU time and milliseconds vary because of different hardware, caching, etc. But the outcome is the most important.
 
For those people who want to comment about COALESCE; I know! COALESCE is not exactly the same as ISNULL, but like I said, in this script it is!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: