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;

sp times

Any help is appreciated. Thank you!

  • 1
    Usual questions: are the structures the same? Are the index up to date? Data quantities and distributions approximate between environments?
    – billinkc
    Dec 1, 2011 at 21:18
  • What do you mean when you say "many more profiles"?
    – datagod
    Dec 1, 2011 at 21:23
  • Are you sure the query is exactly the same? How is the query constructed and parametrized? Dec 1, 2011 at 21:28

1 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.