A couple of weeks ago I did a presentation to the DBA Fundamentals virtual chapter. The presentation title was “What execution plans can tell you about query performance”
The slides and recording are available at the Virtual Chapter’s home page
I didn’t manage to get all of the questions answered, so here are a couple of slightly more involved questions which didn’t get answered.
Does the order of table matter when doing an inner join?
Short answer: No.
Long answer: Maybe, but it shouldn’t.
The optimiser decides which table is joined in which order. Putting a table first in the join clause does not mean it will be the first one processed. In general (as in, in ~99% of cases), put the tables in the join clause in the order which makes logical sense for the query.
Changing table order can, in some cases, change the plan. This doesn’t mean that SQL uses the order which the tables are specified in to determine the plan, it just means that changing the query resulted in the optimiser searching through the plan search space in a different way and finding a different ‘good enough’ plan. It’s not going to be deterministic and hence shouldn’t be relied on.
Will moving a filter from the WHERE to the INNER JOIN improve performance?
No, but again it can change the plan generated as described above. Personally I prefer joins in the JOIN clause and filters in the WHERE clause, because that’s what’s normal and expected.
Please note that moving filters from/to the WHERE clause from an OUTER JOIN changes the logic of the query and likely the results.
If multiple users are running the same query with different parameter values, will it result in different plans or recompiles?
Neither.
There will be one plan in cache (unless the SET options differ, but let’s ignore that for now). No matter what the parameter values are, when the same query is run, the plan will be fetched from cache and used.
Does index fragmentation have an effect on the join type chosen?
The Query Optimiser has no idea what logical fragmentation is. It doesn’t base its choices on how the pages are laid out in the data file. Logical fragmentation affects large range scans from disk, that’s all. If the pages are in memory, then fragmentation has no further effect.
very nice Gail. Short and to the point. Thanks!
And in addition to different set options, you can get a different plan / execution on a qry comparing it in SSMS, to an .NET app as the set options are slightly different. I’ve seen a qry run fast in SSMS, but slow from the .NET app.
Yup, it’s because of the SET options I mentioned offhand. .Net’s default options are different from SSMS
I really enjoyed your ability to answer a question in one or two sentences where most people would need a blog post to be almost that complete, especially with fragmentation.