Measuring Query Performance

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!

Advertisements

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