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?