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!