January 20, 2011 at 6:04 am
I have a sproc that is using a bad plan. The plan sql is choosing is causing it to have 1,000,000 to 4,000,000 reads depending on variables. I first rebuilt all associated indexes. I then issued an sp_recompile on the sproc. No luck. I created a new sproc, a copy of the original with a new name. When i call the new sproc, with the same variables, the reads are <20,000.
I created a trace and issued the sp_recompile again against the original sproc. I verified there is a cache remove, a cache miss and cache insert on the next call to the original sproc, yet still > 1,000,000 reads.
My next step is to drop and recreate the sproc but i did not want to do that if there is any other solution.
Has anyone seen this behavior before? Any ideas before dropping the sproc?
PS. I did find 1 blog\article of this happening on a 2000 SQL server but this is 2005 SP3.
Jimmy
"I'm still learning the things i thought i knew!"January 20, 2011 at 6:36 am
That's definitely odd behavior. Another thing to check, are there differences in the ANSI settings between the two procs? Other than that... I'm not sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply