We help IT Professionals succeed at work.

SQL Server Performance and RAM

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:
something to try ...

right-click on the server, select properties / Memory

then set the Maximum memory user for the server to 90% of total ram size

This will leave 10% of your ram for the OS and other services.
AdamSenior Developer

Author

Commented:
I'm not sure it works like this. The server in question, our staging server, our dev server and the SQL Server instance on my laptop all have this figure set to a default, and very large 2147483647MB (2 Peta Bytes?) and, with the exception of the live database, all are working fine.

If I set it to just 90% of RAM, would that not stop it using Paging file space?
AdamSenior Developer

Author

Commented:
Well, having done some reading on the subject, seems I was wrong! ;-)

http://www.sqlservercentral.com/articles/Memory/74867/
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

I've reduced the SQL Server max ram from its default 2 Peta Bytes down to a more reasonable 20GB, and I'll monitor its performance over the next 24 hours (with a 2 hour SQL Doctor analysis from 8am tomorrow, as that's a busy time of day) and we'll see what difference it makes.

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
use the SQL profiler to see if you have repeating queries, which might be missing indexes or other kind of performance tuning..
AdamSenior Developer

Author

Commented:
AngelIII

I'm already using Idera SQL Doctor to make recommendations for indexes, poor SQL etc.
However, nothing really changed in regards to the load on the server on the date the performance declined so badly.

Some advice about memory tuning would be useful though.
There seems to be some conflicting advice on the use of AWE in a 64-bit (both OS & database) system.

The Server Properties (Memory Page) page in BOL states:
Use AWE to allocate memory
Specifies that SQL Server will take advantage of Address Windowing Extensions (AWE) in Microsoft Windows 2000 and Windows Server 2003 to support up to 64 gigabytes (GB) of physical memory. AWE only applies to 32-bit operating systems. To use AWE you must configure Windows settings in addition to this SQL Server setting. To set this option, you must configure the lock pages in memory policy. For instructions about setting the policy, see How to: Enable the Lock Pages in Memory Option (Windows).
However, plenty of other references say that using AWE & 'lock pages in memory' on a 64-bit system does have a positive effect. I know it's a complex subject, but what's your view on this?

Thanks
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
what is the setting of "parallel query execution"?
you may try to set it to 1, just to test that out.
AdamSenior Developer

Author

Commented:
Sorted the problem.
2 issues. A stored procedure, which was being executed far more often than planned because a caching mechanism had broken, was using DATEDIFF, which is a real killer when it comes to using indexes. These two problems combined were sending CPU usage through the roof
AdamSenior Developer

Author

Commented:
Although not the actual solution, both accepted answers are useful tips anyway, so points all round!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.