Improving Bad Queries

The canned reports available from SSMS are great for starting to identify the worst of the Bad Queries. If you right click on an instance, then go to reports, standard reports, you can then see the top queries by total IO and by CPU (these are two separate reports). Be aware however these are based upon the underlying queries currently in the plan cache (they aren't the worst queries forever). One thing that's a bit frustrating is you can't copy and paste directly from these reports. A little hidden trick though is you can right-click on the report, and then choose Export to Excel. Now you've got some queries to optimize.

Another way to obtain this same information is to examine RedGate monitor; if you click to see the detail of an alert and then scroll down a bit, you can click on the Top-10 queries. This shows essentially the same thing as the SSMS report, except it's a snapshot from the time when the alert occurred. A great practice to follow is to view the RedGate graphs of disk reads over the last 24 hours, and then look for instances of high activity. Find an alert triggered around the same time and look at its detail for Top-10 queries: now you have more to fix.

Once you've identified a handful of troublemakers it's time to put each one under the microscope. One way of course is to examine the query plan, but first I like to paste the SQL for the query into a new-query window, and then highlight the whole thing, right click, and view in designer. Look at joins by moving the table boxes around. Is there a way to arrange them such that none of the lines representing the joins cross?

Once you've done that, look and see if there's a path around the joins taking you in a circle (SQL doesn't like to resolve such things). See if any of the joins show an f(x) symbol, another troublesome indicator. Now look at the tables out at the far reaches of the diagram, linked by a single join to the mass of tables. Do these extremities have any columns or filters selected? If not then you don't need the table in the query (but double check the SQL first before you delete it -- sometimes the table is in a Case clause). If no columns are selected and the filter is a foreign key, can you instead just specify its column value in the where clause on the table it joins?

Now examine the label grid toward the bottom of the designer. Are the filter criteria aligned properly in adjacent columns? Sometimes a developer will confuse the precedence of AND, OR, and parentheses (returning way more rows than they intended) and this will become apparent when looking at how the filters are set up. After all that it's time to now look at the query plan (fodder for a whole 'nother post).