When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters? Do you run the query and view the actual execution plan? If so, what are the things you look for? Obviously table/index scans are the bit hits, but what else?

3 Answers 3


For a quick assessment, get the execution plan out of SSMS and in to Plan Explorer.

  • Review the most expensive operations for anything unexpected. Sorts, worktables, inappropriate join operators (e.g. nested loop where you expect a merge or hash).
  • Look at the rowcounts at each stage of the plan, are they broadly within the range you expected to see?
  • Look at the estimated vs actual rows. If you're actuals are close to the estimates, it's more likely you have a good plan. If there are big variations, find out why (missing and/or out of date statistics for instance).
  • Evaluate the potential for parameter sniffing issues. Look for areas where cardinality may vary and test against a range of input parameters.

Lots of freely available reference material out there, Grant Fitchley's SQL Server Execution Plans is a good start. I also found Joe Chang's blog posts and ebook on execution plan costs very useful.


Mostly, all that I do is just run the query and find out how it executes against real-world data. If there's a problem, then I take a look at the execution plans.

As for execution plans, Brad McGehee has an interesting article on the subject.

In it he says:

If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.

* Index or table scans: May indicate a need for better or additional indexes.

* Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.

* Filter: Remove any functions in the WHERE clause, don’t include wiews[sic] in your Transact-SQL code, may need additional indexes.

* Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently? 

It is not always possible to avoid these, but the more you can avoid them, the faster query performance will be.


Generally, the "number of logical reads" should be as low as possible. The few pages touched to complete the query, the better the plan as it will (typically) be faster, a lower impact on CPU, RAM and disk IO.

This will guide you when changing the indexes or re-factoring of the SQL is actually helping. Looking at "millisecond excecution time" will vary even with the same SQL and query plan - logical reads will stay consistent for any given query plan.

Also "physical reads" should be very low (and be zero and stay zero for subsequent executions). If this does not do this then look at your SQL Server memory usage (page life etc).

  • But for queries that are run when there not in the query cache the physical reads will be greater than zero, right? I mean, you can't always get around that, as not all queries (especially ad hoc queries) are cached. Am I correct? Aug 20, 2011 at 17:50
  • @Surfer513 to take care of the data caching , you can issue a CHECKPOINT followed by a DBCC DROPCLEANBUFFERS to clear out the buffer pool (data cache). Note that this will clear out the buffers for everybody, so use it accordingly (on test systems). Aug 21, 2011 at 12:48
  • @StanleyJohns, why would you want to clear the data/query cache? Aug 21, 2011 at 15:43
  • This way the physical IO will be the same each time, giving the consistency required for testing. This will help in fine tuning the query. Aug 21, 2011 at 17:13
  • I would ignore the physical IO stats as that is under the control of the underlying infrastructure and will incorporate SAN and OS buffering. Logical IO's is a measure of the amount of WORK the SQL statement had to do. If the SQL does less logical IO then it does less work.
    – Guy
    Aug 21, 2011 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.