Query Performance Tuning

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

SET STATISTICS IO ON

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).