May 11, 2010 at 2:15 am
The rebuild would have updated stats, hence fixing the problem. An update stats would have worked too.
Option keepplan also fixed the problem...
May 11, 2010 at 2:22 am
kastros.george (5/11/2010)
Option keepplan also fixed the problem...
No, that just hid the symptoms. If the plan had been forced out of the cache due to memory pressure, DB maint operation, schema change or any number of other things, it's likely that the new plan would have not been optimal.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2010 at 2:37 am
delete from arch_table where case_month = @casE_month (non clustered index on Case_month)
That was the problematic statement... I cant see how sql can generate 2 execution plans that have such huge differences in Disk reads....
May 11, 2010 at 2:54 am
kastros.george (5/11/2010)
That was the problematic statement... I cant see how sql can generate 2 execution plans that have such huge differences in Disk reads....
I can see a number of reasons:
Does it use the nc index then join back to the cluster/heap to delete?
Does it scan the cluster/heap to identify rows to delete?
If it uses the nc index, what join type does it use to join back to the table?
All of those decisions are based on estimated costs of the operators, which are based on estimated row counts. If the estimates are wrong, a plan could look good but be terrible when executed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2010 at 4:19 am
GilaMonster (5/11/2010)
Does it use the nc index then join back to the cluster/heap to delete?
You mean to say Key Lookup ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 11, 2010 at 5:29 am
I'd dive in, but Gail's done a better job of keeping things clear than I would have.
The original post sounds like an issue around parameter sniffing and your issue sounds like it was related to out of data or sampled statistics.
Yes, using KEEPFIXED PLAN does force the plan to stay in memory despite normal recompile issues such as statistics updates, but it is a problematic tool, not an optimal method for solving an issue related to incorrect statistics and certainly not a good solution for parameter sniffing.
Again, I'll shut up because Gail's done her standard outstanding job of explaining the issues. I just wanted to chime in with agreement.
"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
May 11, 2010 at 5:52 am
Bhuvnesh (5/11/2010)
GilaMonster (5/11/2010)
Does it use the nc index then join back to the cluster/heap to delete?You mean to say Key Lookup ?
No, I did not. This is a delete not a select. It doesn't need additional columns from the cluster, it needs the actual row in order to delete it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2010 at 3:19 pm
This is what I was talking about. Ok, join was probably the wrong term to use, there's technically no join. Still, there's a choice between using the NC index to identify the rows, then mapping back to the cluster to delete and just scanning the cluster for matching rows. The first is more likely when the optimiser thinks the row count is low, the second when it's high.
In the attached example, there are 500 000 rows in the table, clustered pk on the ID column and a NC index on the Cond column. First delete deletes 20 rows, second deletes just over 300 000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply