Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment
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).

share|improve this answer
    
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? –  Thomas Stringer Aug 20 '11 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). –  StanleyJohns Aug 21 '11 at 12:48
    
@StanleyJohns, why would you want to clear the data/query cache? –  Thomas Stringer Aug 21 '11 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. –  StanleyJohns Aug 21 '11 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 '11 at 19:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.