curious_sqldba (5/7/2013)
I have a update statement which is killing my PLE( Page Life expectancy), it is dropping from 4000 to 120. Attached is the execution plan, would like to hear from other folks on how to optimize this.
Hover your mouse over the UPDATE icon and look at the memory grant value. You may want to sit down first.
Other folks might come up with suggestions for optimization if they could see the query. It looks overcomplicated from the plan - tables 12 and 5 are read up to 8 times each.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden