Skip to content

shannonlowder.com

Menu
  • About
  • Biml Interrogator Demo
  • Latest Posts
Menu

SQL 202-Performance Tuning

Posted on January 5, 2011February 9, 2011 by slowder

Honestly, choosing which level to put this content under was one of the harder decisions I had to make when putting this content together.  Before you get into this stuff, You need to understand queries pretty well.  You should also have the fundamentals on what an index is, and how to manage them (add, alter, and drop).  Let’s jump right in with the very basics of performance tuning.

How can you tell if a query needs to be tuned?

Pretty easy, right?  How do you know if a query is performing poorly?  At this point in your career, you’re pretty much depending on your users to complain about the performance of a certain query.  Eventually we’re going to move you out of that.  Look into products like Confio Ignite, redgate Monitor, or SQL Sentry Performance Advisor (I list them in alphabetical order, to try and show no favoritism) to start moving from reactive to proactive in performance monitoring. There are a few basic indicators you can use to evaluate the performance of a query.  You can check the execution time, the execution plan (estimated or actual), and the page reads.

Execution Time

Stop, Query Time!
Stop, Query Time!

The first indicator you learn is execution time.  As soon as you run your first query, you notice in the bottom right corner of your SSMS a time. In this example, it shows 00:00:00.  Fast.  The bigger that number is, the worse your query is performing, easy right? Not always.  These numbers can be thrown off by locks or blocks.  You know what those are, right?  (If not, hit up google, I’ll write up articles on locking and blocking soon!)

More Detailed Execution Time
More Detailed Execution Time

If you’d like to see this time appear where you can copy and save it, include the following before your query:

SET STATISTICS TIME ON

That will cause information about the time a query took to run in your messages tab when the query completes. While this number is volatile.  I don’t suggest you ignore it, quite often it will be the indicator that is the deciding factor in choosing to put your time and energy into performance tuning that query.

Page Reads

One of the more useful statistics I use to determine if a query needs to be tuned is the IO STATISTICS.  What this will show me is how much data did the server have to sort through in order to answer the query I sent it?  When you add

SET STATISTICS IO ON

before your query, you’ll get extra information on your messages tab.

(1 row(s) affected)
Table 'Vehicles'. Scan count 1, logical reads 1006, physical reads 0, read-ahead reads 0
, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

While all of these numbers can be useful, the one I use most often is the logical reads.  This is the count of how many pages were read in memory to find the answer.  The physical reads refers to how many pages were read from disks. The scan count is the number of passes through the index or heap it took to answer the query.  The LOB statistics refer to Large Object data.  This is data stored ouside the row ( such as VARCHAR(MAX), text, and XML data). The IO statistics can’t be taken alone.  You need to look at other statistics, in the future, I’ll show you how you can also check the CPU usage of a query, for now, I want you to know it’s possible, but you should master these concepts first.

The Execution Plan

The real McCoy!

The last measurement I want to show you today is the execution plan.  There are two execution plans you have access to from SSMS.  The first is the Estimated Execution plan.  I’ve heard Grant Fritchey suggest a better description than estimated…”a complete guess”.  That’s really all it is.  But if you have a query that takes two hours to run, you really don’t want to wait two hours to get the actual execution plan.  That’s why we have both. In SSMS, you can choose either, and when you do, you’ll see an extra tab appear beside your results.  This is how the server actually retrieved your results.  There’s a ton of things I could tell you about execution plans…but for now, just know that it’s there, and you can use this tool to compare two queries. Simply write two versions of your query, highlight them both, then ask for the execution plan.  You’ll see a relative cost to run each of the queries.  If one query is far less than 50%, and one is far greater than 50%, you have a simple indicator that you have a better performing query. Soon, I’ll show you a lot more you can do with the execution plan in determining what needs to be tuned in your query.  I’ll also share with you a review of SQL Sentry’s Plan Explorer.  And I’ll cover why it’s better than the built in tools. In my next post, I want to cover query execution order.  I had a question about this from a coworker recently, so I just had to share some information with you about it too!  If you have any questions, send them in.  I’m here to help!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • A New File Interrogator
  • Using Generative AI in Data Engineering
  • Getting started with Microsoft Fabric
  • Docker-based Spark
  • Network Infrastructure Updates

Recent Comments

  1. slowder on Data Engineering for Databricks
  2. Alex Ott on Data Engineering for Databricks

Archives

  • July 2023
  • June 2023
  • March 2023
  • February 2023
  • January 2023
  • December 2022
  • November 2022
  • October 2022
  • October 2018
  • August 2018
  • May 2018
  • February 2018
  • January 2018
  • November 2017
  • October 2017
  • September 2017
  • August 2017
  • June 2017
  • March 2017
  • February 2014
  • January 2014
  • December 2013
  • November 2013
  • October 2013
  • August 2013
  • July 2013
  • June 2013
  • February 2013
  • January 2013
  • August 2012
  • June 2012
  • May 2012
  • April 2012
  • March 2012
  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009
  • January 2009
  • December 2008
  • November 2008
  • October 2008
  • September 2008
  • August 2008
  • July 2008
  • June 2008
  • May 2008
  • April 2008
  • March 2008
  • February 2008
  • January 2008
  • November 2007
  • October 2007
  • September 2007
  • August 2007
  • July 2007
  • June 2007
  • May 2007
  • April 2007
  • March 2007
  • February 2007
  • January 2007
  • December 2006
  • November 2006
  • October 2006
  • September 2006
  • August 2006
  • July 2006
  • June 2006
  • May 2006
  • April 2006
  • March 2006
  • February 2006
  • January 2006
  • December 2005
  • November 2005
  • October 2005
  • September 2005
  • August 2005
  • July 2005
  • June 2005
  • May 2005
  • April 2005
  • March 2005
  • February 2005
  • January 2005
  • November 2004
  • September 2004
  • August 2004
  • July 2004
  • April 2004
  • March 2004
  • June 2002

Categories

  • Career Development
  • Data Engineering
  • Data Science
  • Infrastructure
  • Microsoft SQL
  • Modern Data Estate
  • Personal
  • Random Technology
  • uncategorized
© 2025 shannonlowder.com | Powered by Minimalist Blog WordPress Theme