Slow & Lots of memory

Surprisingly I've got a problem. Partly compounded by the fact that my production box is in a colo and I can't get great stats on it.

The symptoms are under certain conditions the sql engine grows to about 80% of physical memory and then sits there with about 10Mb free, able to perform very light queries but totally unable to run larger queries.

Memory is currently set to dynamic and my suspicion is that I am getting stuck on the edge of paging but unused pages are not being aged out because we have plenty of hard drive for virtual memory to grow into.

Does anyone have a good reference on the pros & cons of dynamic vs static allocation and any metrics on how much to allocate on a server dedicated to SQLServer.

On a dedicated SQL Server, leave the memory settings to default, which is dynamic and with the maximum memory set to the default setting, which is to total amount of RAM in your server.

SQL Server does not use the OS virtual memory for the most part. It will use some for backups and restores, but not for regular day to day queries. This is because paging would slow down SQL Server too much.

One of the first things you will want to do is to find out what the buffer hit cache ratio is for your server. If it is 99% or higher, most likely you don't have a memory problem. But if it is less than 99%, then adding more RAM will most likely boost performance some.

Ideally, you need to use Performance Monitor and Profiler to find out what is going on in your server. I know you said this is hard because is is co-located. If you can access your server via Enterprise Manager, the you can perform a Profiler trace. In order to run Performance Monitor, you would have to have an account on the co-located box. If you can't do this, perhaps you can get your co-location company to perform a Performance Monitor trace for you. Only until you get some good data will you be able to identify your actual problems.

------------------
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com