SQL Server Page Life Expectancy drops from 1500 to 144, then we get deadlocks on various tables

Duane Lawrence
Duane Lawrence used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Duane LawrenceSQL Server database administrator

Author

Commented:
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.
Duane LawrenceSQL Server database administrator

Author

Commented:
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
SQL Server database administrator
Commented:
I read the Stored Procedure (SP) that updates the tables involved in the deadlocks.  It is inserting data, then it gets the ID "@NewID = SCOPE_IDENTITY()", then it calls another SP that then reads the row that was just inserted.  To make matters worse, when they read the row, they do joins to 2 other tables and don't use the other tables.  I am now negotiating to remove the "read what I just inserted" or at least remove the un-needed joins in the "read what I just inserted".  I spent two 12 hour days on this and I am very disappointed with the resistance I am getting to a no brainer question, do I cut the IO operations in half with a simple new philosophy?
Duane LawrenceSQL Server database administrator

Author

Commented:
I tested it on our test server after I made the modifications myself and it did indeed cut the IO operations in half.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial