I am working on a large stored procedure that returns about a dozen tables for a large business report. Some of the queries join 8 large tables, either to get all the data they need or to apply all the necessary selection conditions.

I want the query to run as quickly as possible, with a minimum of CPU spikes on the database server. Some of the queries run long and some are very short with high bursts (50%+ of CPU).

I am wondering if anyone can suggest a web resource or some general principles for reducing the CPU spike or required time for SPs, assuming they are of the sort:

SELECT TableA.Data1, TableA.Data2, 
       TableB.Data4, TableB.Data4,
       TableC.Data5
FROM   TableA
       INNER JOIN TableB
          ON TableA.Data7 = TableB.Data7   -- Data7 is an indexed field
       INNER JOIN TableC
           ON TableA.Data8 = TableC.Data8  -- Data8 is not an indexed field
       LEFT OUTER JOIN TableD
            ON TableB.Data9 = TableD.Data9 -- Data9 is an indexed field
WHERE  TableA.Data10 = value               -- Data10 is an indexed field
       AND TableA.Data11 = value1          -- Data11 is not an indexed field
       AND TableD.Data9 IS NOT NULL
       AND (TableB.Data12 = value2 OR TableC = value3)
       AND (TableD.Data13 IN (value4, value5, value6))
       AND (TableC <> value7)

(This is my attempt at a generic example of all the cases that seem relevant.) I would appreciate suggestions. I suspect that the most succinct way to write the SQL may not be the way that runs fastest.