About Cache Memory On R2

[abitguru]

Hi, I'm not very into Windows Server Functionality.. so I have to ask if this behavior is normal or not.

 

There is the thing.

 

We have a Virtual Windows Server 2008 R2 with 32 GB of Ram

 

20 GB for SQL

12 GB for SO

 

In the server.

 

SQL Server 2008

Some DBF Tables (not larger than 500MB)

 

We noticed after restar that SQL grows up till 20 GB.. this is ok we set this option in SQL

 

After some time.. lets say une day. we see the SO is out of memory. In Task Manager we see "Free Memory" < 50MB and in cached Memory we see all the memory for the SO.

 

1 - The question is.. is this normal in Windows Server 2008 (and R2) ? I see a lot of people asking for this..

 

2 - If Windows says "there is no memory", he start swapping?

 

3 - Is there any way to see how is taking the memory of the SO.

 

 

I try a workaround (not a hotfix) but i'm not sure if this is my problem (Hi, I'm not very into Windows Server Functionality.. so I have to ask if this behavior is normal or not.

 

There is the thing.

 

We have a Virtual Windows Server 2008 R2 with 32 GB of Ram

 

20 GB for SQL

12 GB for SO

 

In the server.

 

SQL Server 2008

Some DBF Tables (not larger than 500MB)

 

We noticed after restar that SQL grows up till 20 GB.. this is ok we set this option in SQL

 

After some time.. lets say une day. we see the SO is out of memory. In Task Manager we see "Free Memory" < 50MB and in cached Memory we see all the memory for the SO.

 

1 - The question is.. is this normal in Windows Server 2008 (and R2) ? I see a lot of people asking for this..

 

2 - If Windows says "there is no memory", he start swapping?

 

3 - Is there any way to see how is taking the memory of the SO?

 

 

I try a workaround (not a hotfix) but i'm not sure if this is my problem (Installing and using the Microsoft Dynamic Cache Service)

 

Extra info about memory..

Total 32762

Cached 9543

Free 983

Available 10526

 

4 - The memory Cached, can be used by the SO?

 

Available is Cached + Free.. but in Windows Server 2008 (not R2) there is no Available.

 

 

 

 

 

 

Thanks so much for your commets.. (keep in mind i'm a soft developer, so Windows Admin is not my forte)

[abitguru]

another thing to comment is that using RAMMap the values are

 

Process Private ~ 21.000.000 K (SQL Server)

Mapped File ~ 9.000.000 K ( ?????? )

Unused ~ 790.00 K

 

What is Mapped File?? I suspect that is the problem.

[ICTCity]

Best practices say that your pagefile should be 1.5 greater than your ram. So if you have 32 gb of ram, your pagefile will be 48 GB.

 

To make this modification, run (as administrator) this command (in command prompt):

 

wmic computersystem where name="%computername%" set AutomaticManagedPagefile=False

wmic pagefileset where name="C:\\pagefile.sys" set InitialSize=48000,MaximumSize=48000

 

This will create a FIXED pagefile of 48 gb. Of course you must first be sure that you have 48 gb free on your disk.

 

I would like to know WHICH process is using so much RAM. Open the task manager and check the MEMORY column. The first value should be SQL, and the second should be the one related to the use of your 12 GB.

 

Let me know.

[abitguru]

Thanks ITC,

 

I see on task manager.. SQL Server is using ~ 20 GB, but the strange thing is that no other process is using much memory.

 

I try process explorer and resources monitor of Windows Server, and i can't figure out witch process is taking the memory.

 

I'll check the page file size.

[abitguru]

I'll post an image on my profile (waiting for 20 posts :) )

 

thanks!

[abitguru]

Right now Windows Server config is:

 

Page File Size 32762 MB

Minimun 16 MB

Recomended 49143 MB

 

http://imageshack.us.../pagefilec.png/

 

http://imageshack.us/photo/my-images/52/hardmonitor.png/

[ICTCity]

Everything looks fine 21 used ;)

 

set the pagefile as FIXED size (use the command provided before) just in case. When happens that you run out of ram, post a print screen with the ram utilization (by process of course).

 

A good point is to create a fixed pagefile, remember to reboot :)

[abitguru]

What do you mean with fixed?

 

http://imageshack.us/photo/my-images/138/imgih.png/

 

Now is set to automaticaly set the size of page file.

 

If you see previos 2 images, that is the situation, SQL consuming 21 GB (its ok) but we have 37 MB free in first image, and ~ 900 in second image.

[ICTCity]

Remove that check and set manually the value (48000) make sure you have enough space!

 

 

 

 

ahhhhh ok but this is normal! FREE is something different from AVAILABLE but actually you must check the AVAILABLE and not the FREE ;). You still have more than 10 gb ;)

 

Basically the difference between FREE and AVAILABLE is:

FREE: it contains 0 (zeros) and actually has never been used (this will always be a small number, some exceptions may apply...)

AVAILABLE: this memory has something inside but... it's not required. Some (too many) programs use the RAM and then they forget or don't want to free up the memory completely. So this part of ram is in stand-by. actually is not free (because it has something inside, not used but it's still something), BUT windows or other programs can use it to work ;)

 

Don't watch the free memory, better the available ;)

[abitguru]

Thanks so much!!

 

We make some tests and realise that the problem with performance is not the ram, is not SQL.

 

We make a simple process with a few users using the system, and the process take less than 10 seconds, now with more users the same process took 30 ~ 45 seconds.

 

I supouse there is a RAM problem, but with this test I can say there is no RAM problem.

 

I'm close to say the storage has the problem, but I cant prove it. (and the it guy don't do anything to test for his own)

 

Thanks for your help again! :D :D

[ICTCity]

(and the it guy don't do anything to test for his own)

 

So we will do his job :P

 

Can you please tell me which kind of test have you done? The more you are specific, the more I can help you :)

[abitguru]

Tested this simple way.

 

lask weekend we reinstall ALL,

Windows Server 2008 R2

SQL Server.

 

1 - Transfering Backup (80 GB) From old server to new Server. ~ 12 MB/s

 

No uses conected,

Old Server restarted

No Lan Traffic

 

~ 12 Mb/s for me its a joke.

 

2 - Test 2. Today, early in the morning we test a process (run on old legacy system in fox pro 2.6 with dbfs)

Results. The process finish in less than 10 seconds.

Test the same process at 8:00 am, with most users connected took ~ 45 sec

 

This makes me think there are a bottleneck somewhere.

 

Other test we will do at noon is, ask for users to close the system and test again same process and see if there changes.

 

Thanks a lot.!

[ICTCity]

WHAT? 12 mb/s??? You may experience timeout from SQL if you have that kind of speed on a query!

 

Well, what you can do is:

 

1) open performance monitor (you may also start logging and then post) and select the NETWORK STORAGE AND LAN. Then simply copy a medium file from a server to that server (and viceversa), when I say medium I mean no more than 2 gb. Do this 2 times, one time when you have "no load" and the second time when you're almost at full load.

 

2) if the storage has some kind of monitoring tool... check them too (if you have access). But we can already troubleshoot this problem only from the server side. We need something that proove that the network or the nas is slow. That's obvious, but to proove this, we must first exclude sql, that's because I want the test number 1, moving a file you don't involve SQL ;)

[abitguru]

We not experiment much timeouts because we have a lot of RAM assgned to SQL, and this compense. (I think)

 

~ 12MB/s Copy. Reading cound be better but not to much.

 

I will use the counter you say and post the results.

 

About the second point (i dont have access to storage tools, and at this point i think the admin dont know if this exists)

 

The point you mencioned in point 2 is what we want, prove that is a problem of the storage and SQL or Windows has nothing,

[abitguru]

Sorry to be soooo noob, but i cant find in performance monitor any counter with NETWORK STORAGE AND LAN.

I have

network interface

MSAS 2008: XXXXX

 

You mean log a specific counter or I misunderstod?

[ICTCity]

You must create that counter and when you create it add as "sensor" both network interface one for your LAN and the other for the storage network, you told me that you have two separate networks...

[abitguru]

yes rigth, we have two separeted lans

 

when we make the server change, we asign a network connection for each disk (1 for windows, 1 for sql data, 1 for log, 1 for tmpdb, 1 for system file(dbf) )

 

I will see if i can find what you say in performance monitor.

 

thanks a lot.

[abitguru]

Here is a pic of Performance Monitor

 

The only problem is in spanish :(

 

http://img100.imageshack.us/img100/7265/datosg.png

[ICTCity]

No problem.

 

Can you tell me how long do your recorded this?

[abitguru]

about 5 minutes (and we generate a little traffic :) )