October 27, 2010 at 1:37 pm
Grant,
The point of what I was getting at is that I used this method to determine that there were 2 distinct sets of parameters. I had a situation where records were selected by country. All of the other countries in the world added up to less than the USA. When the procedure was run the first time it was for Egypt. The query plan as just fine. It remained just fine until someone ran it for the USA and it took forever. There were thousands of key lookups for the USA. The normal number of records for other countries was not more than 200 and the key lookups were just fine.
I used the technique described in one of Gail's blogs and split the thing up into 2 different sub procedures, each returning the same columns that populated a temp table. The parameter sniffing for the other country stored procedure worked just fine. The procedure for USA only also worked just fine and all customers were happy.
Todd Fifield
Viewing post 61 (of 60 total)
You must be logged in to reply to this topic. Login to reply