Duane Lawrence
asked on
SQL Server Page Life Expectancy drops from 1500 to 144, then we get deadlocks on various tables
Our customers are running year end activity and the memory is thrashing. Rarely used data (or data the Buffer Manager does not predict) is being called into RAM.
After PLE drops to 144 or 86 sometimes, it slowly rises, it can get up to 800 or 900 before in drops again to the lows. I have already removed many indexes that were bad. They were bad because they were speeding up 12k queries, but slowing down 2.7 million queries (see real production numbers below).
Total Writes Total Reads Difference
2,754,287 12,404 2,741,883
My production server has 8.3 Gig of RAM and SQL Server is set to 7.5 Gig. I worry that giving SQL Server more RAM will cause the context switching to rocket above 20k.
Can anyone give me a useful suggestion other than buying more RAM for my 5 year old server?
After PLE drops to 144 or 86 sometimes, it slowly rises, it can get up to 800 or 900 before in drops again to the lows. I have already removed many indexes that were bad. They were bad because they were speeding up 12k queries, but slowing down 2.7 million queries (see real production numbers below).
Total Writes Total Reads Difference
2,754,287 12,404 2,741,883
My production server has 8.3 Gig of RAM and SQL Server is set to 7.5 Gig. I worry that giving SQL Server more RAM will cause the context switching to rocket above 20k.
Can anyone give me a useful suggestion other than buying more RAM for my 5 year old server?
144 seconds on a busy system and 800-900 when idle sounds good to me. Why do you think this is related to the deadlocks?
ASKER
Because the deadlocks happen as or just after the PLE drops. I have Permon screen shots and times of the deadlock (with times) screen shots as well, but I can't post them here on Experts Exchange. I will see if I can post them on my Linkedin account and provide a link.
ASKER
I created a Google Docs presentation on Linkedin. I hope that others can see it, the title is the same as this.
http://www.linkedin.com/osview/canvas?_ch_page_id=1&_ch_panel_id=1&_ch_app_id=11140860&_applicationId=1400&_ownerId=989188&osUrlHash=cl8t&trk=hb_side_apps
http://www.linkedin.com/osview/canvas?_ch_page_id=1&_ch_panel_id=1&_ch_app_id=11140860&_applicationId=1400&_ownerId=989188&osUrlHash=cl8t&trk=hb_side_apps
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tested it on our test server after I made the modifications myself and it did indeed cut the IO operations in half.