--- title: Measuring Query Performance description: Measuring Query Performance image: https://blog.coeo.com/hubfs/Imported_Blog_Media/194-2.png --- [![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com) # Measuring Query Performance # The Coeo Blog ![Simon Osborne](https://blog.coeo.com/hubfs/SimonOsCircle.jpg) 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! [![](https://blog.coeo.com/hubfs/Imported_Blog_Media/194-2.png)](http://feeds.wordpress.com/1.0/gocomments/simonosbornesql.wordpress.com/194/) ![](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/b-84.gif?width=1&height=1&name=b-84.gif) ### Subscribe to Email Updates ## Related posts --- ### [Troubleshooting Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/troubleshooting-ola-hallengrens-maintenance-solution) ### [Domain-Independent Windows Failover Cluster for SQL Server AlwaysOn Availability Group](https://blog.coeo.com/domain-independent-windows-failover-cluster-for-sql-server-alwayson-availability-group) ### [SQLBits 2022 session - Field Testing Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/sqlbits-2022-session-field-testing-ola-hallengrens-maintenance-solution) ### [Windows Active Directory Detached Cluster with SQL Server Basic Availability Group](https://blog.coeo.com/advanced-sql-server-windows-cluster-options) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | info@coeo.com ## Contact Us By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested. You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**. ## Upcoming Events [See all events](https://www.coeo.com/events/) #### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB [![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png) ![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png) ![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png) +44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) - [Solutions](https://www.coeo.com/solutions/) - [Next Steps](https://www.coeo.com/next-steps/) - [Dedicated Support](https://www.coeo.com/dedicated-support/) - [Case studies](https://www.coeo.com/case-studies/) - [Technologies](https://www.coeo.com/solutions/technologies/) - [Industries](https://www.coeo.com/industries/) - [Finance](https://www.coeo.com/industries/finance/) - [Retail](https://www.coeo.com/industries/retail/) - [Technology](https://www.coeo.com/industries/technology/) - [The Team](https://www.coeo.com/people/) - [Join Us](https://www.coeo.com/careers/) - [Graduate Programme](https://www.coeo.com/graduate-programme/) - [About Coeo](https://www.coeo.com/about-coeo/) - [The Coeo Blog](https://www.coeo.com/blog/) - [Contact us](https://www.coeo.com/contact-us/) - [Privacy Notice](https://www.coeo.com/privacy/) - [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy) - [Events](https://www.coeo.com/events) Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png) Back to top