Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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]: http://i.imgur.com/y3dvm.png

Any help is appreciated. Thank you!

share|improve this question
1  
Usual questions: are the structures the same? Are the index up to date? Data quantities and distributions approximate between environments? –  billinkc Dec 1 '11 at 21:18
    
What do you mean when you say "many more profiles"? –  datagod Dec 1 '11 at 21:23
    
Are you sure the query is exactly the same? How is the query constructed and parametrized? –  Nick Chammas Dec 1 '11 at 21:28
add comment

migrated from serverfault.com Dec 1 '11 at 21:12

This question came from our site for professional system and network administrators.

1 Answer

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?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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