This is a quick post about measuring query performance. How do you know that changes you’ve made to improve a query are actually beneficial? If all you’re doing is sitting there with a stop watch or watching the timer in the bottom right corner of SQL Server Management Studio then you’re doing it wrong!

Here’s a practical demonstration of how I go about measuring performance improvement. First let’s setup some demo data.

/* Create a Test database */

IF NOT EXISTS (SELECT * FROM sys.databases d WHERE d.name = 'Test')

CREATE DATABASE Test

GO
/* Switch to use the Test database */

USE test

GO
/* Create a table of first names */

DECLARE @FirstNames TABLE

(

 FirstName varchar(50)

)

INSERT INTO @FirstNames

SELECT 'Simon'

UNION ALL SELECT 'Dave'

UNION ALL SELECT 'Matt'

UNION ALL SELECT 'John'

UNION ALL SELECT 'James'

UNION ALL SELECT 'Alex'

UNION ALL SELECT 'Mark'
/* Create a table of last names */

DECLARE @LastNames TABLE

(

 LastName varchar(50)

)

INSERT INTO @LastNames

SELECT 'Smith'

UNION ALL SELECT 'Jones'

UNION ALL SELECT 'Davis'

UNION ALL SELECT 'Davies'

UNION ALL SELECT 'Roberts'

UNION ALL SELECT 'Bloggs'

UNION ALL SELECT 'Smyth'
/* Create a table to hold our test data */

IF OBJECT_ID('Test.dbo.Person') IS NOT NULL

DROP TABLE dbo.Person
/* Create a table of 5,764,801 people */

SELECT

 fn.FirstName,

 ln.LastName

INTO dbo.Person

FROM

 @FirstNames fn

 CROSS JOIN @LastNames ln

 CROSS JOIN @FirstNames fn2

 CROSS JOIN @LastNames ln2

 CROSS JOIN @FirstNames fn3

 CROSS JOIN @LastNames ln3

 CROSS JOIN @FirstNames fn4

 CROSS JOIN @LastNames ln4

GO

The features I use to track query performance are some advanced execution settings. These can be switched on using the GUI through Tools > Options > Query Execution > SQL Server > Advanced. The two we’re interested in are: SET STATISTICS TIME and SET STATISTICS IO.

These can be switched on using the following code:

/* Turn on the features that allow us to measure performance */

SET STATISTICS TIME ON

SET STATISTICS IO ON

Then in order to measure any improvement we make later it is important to benchmark the performance now.

/* Turn on the features that allow us to measure performance */

SET STATISTICS TIME ON

SET STATISTICS IO ON
/* Benchmark the current performance */

SELECT * FROM dbo.Person p WHERE p.FirstName = 'Simon'

GO

By checking the messages tab we can see how long the query ran for (elapsed time) how much CPU work was done (CPU Time) and how many reads were performed.

(823543 row(s) affected)

Table 'Person'. Scan count 5, logical reads 17729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

 CPU time = 1623 ms, elapsed time = 4886 ms.

So… now to make a change that should yield an improvement.

/* Create an index to improve performance */

CREATE INDEX IX_Name ON dbo.Person(FirstName) INCLUDE (LastName)

GO

Now re-measure the performance…

/* Turn on the features that allow us to measure performance */

SET STATISTICS TIME ON

SET STATISTICS IO ON
/* Benchmark the current performance */

SELECT * FROM dbo.Person p WHERE p.FirstName = 'Simon'

GO

(823543 row(s) affected)

 Table 'Person'. Scan count 1, logical reads 3148, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

 CPU time = 249 ms, elapsed time = 4488 ms.

Looking at the two sets of measurements we can see that we’ve saved 398ms of elapsed time, we’ve saved 1,374ms of CPU time and we’re doing 14,581 less reads. This is a definite improvement.

The critical thing about this approach is that we’re able to scientifically measure the improvement and therefore relay this onto anyone that may be interested whether that’s a line manager or end customer.

Happy performance measuring!