We help IT Professionals succeed at work.
Get Started

SQL Server Performance and RAM

472 Views
Last Modified: 2012-05-12
Over the last few weeks, a website database I support has been struggling. Idera SQL Doctor often reports its state as 'Critical', and there are periods of 100% CPU usage.

Looking for the reason, I looked at the Site Speed graph in Google Webmaster tools, and noticed something odd. Page speeds increased dramatically mid September, and coincided exactly with a doubling of the amount of RAM in the server, from 12GB to 24GB. There was also a BIOS upgrade at the same time.

The server runs on Windows 2008 R2, the database is SQL 2008. The server is well spec'd - along with the 24GB RAM, it has 2 quad core xeon processors (so 16 cores with HT), separate (SAN based) drives for data, log & temp. There is 18GB in the paging file (again on its own drive). It is a node in a two node failover cluster. During the time the server has been struggling, it has been running on both nodes, with no difference in performance.

RAM is usually always all in use, with probably 98-99% allocated to SQL Server. Hard page faults, even when the CPU is running at 100% rarely go above 0

The extra RAM was supposed to speed it up - not slow it down.
No other changes were made at the time which could have caused such a fundamental change in the performance of the database. Traffic has increased a little from the middle September, but only what is seasonally expected. Nearer to Christmas, it will be far higher - as it was last year, but without these problems.
The main database in use is currently about 28GB in size

Anyone any ideas what's going on here?
Are there any database config changes that are recommended when extra RAM is added?
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 8 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE