stored procedure times out while subsequent runs take 1/6 the time

Question

So I have this stored procedure which is running very slow (and timing out) in one particular production environment. This stored procedure produces an xml of profile data. In all other environments, the sp runs very fast. After tracing the procedure in the particular environment, I found that the final "SELECT" statement was taking up 96% of the total time (should be under 30%). This statement involves multiple SELECTS, UNIONS, and JOINS. I will refer to this as the "Last Select" statement.

On subsequent runs, the "Last Select" statement, and subsequently the whole batch, took much less time. What is especially curious is that subsequent runs used many more profiles (15,000 new in the second run, 25,000 new in the third run).

Comparing the query plans, I found some interesting differences between 10000.trc and 25000.trc. Both of the following were seen in steps before the "Last Select"

1. The faster run (25K) utilizes many operations involving parallelism. I do not see any operations involving parallelism in the slow run.
2. The faster run uses “hash matches” and “merge joins” for inner and outer Joins (instead of nested loops). With a few exceptions, the slow run seems to always use nested loops.

The slow run uses many clustered index scans and clustered index seeks during the final select that the fast run does not. The slow run also uses a "concatenation" step (not found in the fast run) which appends multiple tables to make an output table.

Here is a summary of the times;

Any help is appreciated. Thank you!

Answer 1

Sounds like stats are out of state, and SQL is going a full scan and loading all the data into buffer pool on the first run, then accessing that data from buffer pool for the rest of the runs.

Try flushing the buffer pool between runs and see if the times increase to confirm this.

Also can you post the execution plans?