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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
For a quick assessment, get the execution plan out of SSMS and in to Plan Explorer.
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:
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).