SQL2008R2 memory configuration 1

[disturbedone]

I'll start off by saying that I know very little about SQL configuration But I'm looking for some assistance.

I have a SQL2008R2 Standard W2K8R2-64bit VMware VM. It was installed by a contractor who I hope had configured it correctly and knew what they were doing. It has 2x vCPU and 4GB vRAM. It has our vCentre database, a couple of minor application databases (tiny, barely used, not worth worrying about) and the finance application database (in a separate instance). It is a dedicated SQL box. Inside Windows it shows memory running at 90-95% and that, I understand, is how SQL works - it will take all it's given. But in vSphere it shows memory at ~20%.

The finance database was only recently moved to this server (it was previously on another W2K3 server with the finance application) and since then users have complained that running reports from the application takes exceedingly long (20-30mins instead of 3-4mins). The finance application vendor has suggested upping the vRAM from 4GB to 8GB.

The server has a page file of 4GB and looks to be default from the installation. With the current 4GB vRAM is the 4GB page file sufficient or would increasing it help? If I increase the vRAM to 8GB should I increase the page file to 8GB or more?

On the properties of the default instance AWE is disabled. I've read that this should be on - is that correct? Minimum server memory is 0MB and maximum is 2147483647MB (2097151GB, 2047TB)!
On the properties for the finance DB instance AWE is also disabled. Minimum memory is 0MB and maximum is 2048MB. Could the maximum in the default instance be causing the problem with the finance DB?

Any advice is appreciated.

 

[gmmastros]

AWE only affects 32-bit systems. If your machine is 64-bit, and the operating system is 64-bit, and SQL Server is 64-bit, you don't need to worry about AWE.

If the VM has 4 gigs, then you should set the max memory for the instance to approximately 2 gigs. Basically, the OS is going to take RAM. How much? Probably 1.5 gigs or more.

I would encourage you to check the SQL instance to see if it is suffering from memory pressure. To do this, start by downloading SQLCop. This is a free utility that I wrote. There's a ton of checks that SQLCop can perform, but I would encourage you to look at the "Health" section. There are 2 tests, one for Buffer Cache Hit Ratio, and another for Page Life Expectancy. The first test will tell you the percentage of queries where the data is already cached in memory. The second will tell you how long (in seconds) that the data is cached in memory. If these numbers are low, then it is a clear indication of memory pressure.

Personally, I would increase the RAM to 8gigs. Increase the page file. Modify each of the instances to have a max memory of 3 gigs.

That would be 2 for the OS and 3 for each of the instances, for a total of 8 gigs. After making this change, you should monitor the instances for the page life expectancy. If one instance is low, and the other is high, then you should modify the max server memory (2.5 & 3.5 for example). Eventually, you will find the right balance. After making these changes, you'll want to wait a little while for SQL to "settle down". I mean... at some point, certain data will be cached and other data won't. This is normal. It may take 20 minutes, or it may take 1/2 a day.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[disturbedone]

Interesting little tool that

I ran it on the SERVER\FINANCEAPP instance and it's multiple databases and got the following results

DB, Buffer cache hit ratio, Page life expectancy
DB1, 100 (also got 95.5), 33735
DB2, 100, 33735
DB3, 100, 33735
DB4, 100, 33735
DB5, 89.3 (also got 100), 33735
DB6, 100, 33735
DB7, 75.9 (also got 100), 33735

And on the SERVER\DEFAULT instance

DB, Buffer cache hit ratio, Page life expectancy
DB1, 97.1 (also got 100), 38956
DB2, 100, 38956
DB3, 100, 38956
DB4, 100, 38956
DB5, 100, 38956
DB6, 95.3 (also got 100), 38956
DB7, 100, 38956
DB8, 100, 38956
DB9, 100, 38956

So that all looks ok. Every time I run it though it gives slightly different results for the buffer cache - would running SQLCop when the slowness exists show the issue?

I'm waiting on the contractor to reply about the maximum memory being 2047TB. Would that be causing a problem?

 

[fredericofonseca]

Also check if the W2K3 server had more CPUS than this new VM - that can also cause queries to be slower even if the VM cpu´s are faster

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[disturbedone]

In doing some more digging I've found that the 2147483647MB (2097151GB, 2047TB) memory allocated to the default instance is how SQL is installed in default essentially saying Windows will handle the memory for a single instance. This may be causing the default instance to suck memory and leave none for the second instance.

I plan to alter memory for the default instance to 1.5GB, for the second instance to 1.5GB, leaving 1GB for the OS. I'll see how that goes. If it doesn't make much difference then I'll increase the vRAM on the server and also adjust the memory for each instance leaving some for the OS as you've suggested above.

That will probably happen this weekend so I'll post next week with the results.

 

[disturbedone]

@fredericofonseca, the old server had 1 vCPUs and the new one has 2x vCPUs. Both old & new have 4GB vRAM.

 

[biglebowski]

I thought MS still recommended setting up AWE although winders 64 didn't specifically need to.

Biglebowskis Razor - with all things being equal if you still can't find the answer have a shave and go down the pub.