NTFS hard drives = slow virtual memory????

[LucyP]

Hi

I have an ongoing server configuration problem involving SQL Server (which helps itself to large amounts of memory).

When the memory is used up and SQL Server starts paging to disk, it runs painfully slow - much much slower than my test server does. One difference between the test and the live server is that the test disks are fat32 and the live server ones ntfs - could this cause the memory paging to run very slowly? If so is there any way of fixing it without risking a re-install? Are there any other security configurations that might cause slow virtual memory????

[please answer negatively if you can - I need to eliminate everything I can ]

 

[ensorg]

Are the test and the live servers the same spec?

How much ram does your server have? How many concurrent SQL users?

I haven't heard or experienced any problems with page files on NTFS. NTFS is a superior file system to FAT32 in many ways, I would NOT recommend running a server on FAT32.

 

[LucyP]

Maybe it's not the ntfs thing then. I'm grasping at straws now.

The online server is twice as powerful as the test server (1 GHz), with 4 times as much RAM (512Mb). Under normal usage concurrent connections are rare and probably only ever go as high as 3 at a time, but there are many permutations of searches which SQL Server seems to cache (I think this is normal) hence the high memory usage.

We've load-tested the test server over 3 hours with various levels of concurrent connections - so that it runs way over it's memory - but can't make it run as slowly as the online server.

I didn't configure the online server so there could be other differences, but I've run out of things to look for.

 

[ensorg]

Is the live server a delectated SQL server? Or does it preform another function.
Where is your page file? How much free space is on the drive where the page file resides?

 

[ensorg]

Opps forgot to ask this, what size is your paging file?

 

[LucyP]

delectated??? Am I showing my novice-ness or is that a typo?
I don't think it's very delect

The SQL server is on the same machine as the web server (as it is on the test server). I'm aware that this is inadvisable, but our host assures us that other companies do the same thing with no problems - the traffic is less than 1Gb per month. I cleaned up both the system (Windows) and my "data" disks before I installed SQL Server - they have 25% and 70% free space each. The paging file is set to
max 500Mb or so and resides on the C drive (so if it's full there's not much free space on the drive.) One of the first things I tried to fix the problem was to move the paging file to the E drive, but Windows wouldn't let me. I did put a second paging file on E though set to 200Mb but it didn't seem to make a difference. I didn't think this was relevant as it seems to have problems as soon as it runs out of RAM - when memory usage is only just above whatever maximum I have set for SQL SErver (and I've tried setting this to the actual RAM on the machine as is the default, and to various other levels).

 

[ensorg]

Sorry I mean dedicated!!!! This website in doesn't have a spell checker

What’s the maximum level of RAM you have set for SQL

Remember that the OS, the web server, and each user connecting will use up RAM i.e. 128mb just for the OS, approx 32mb for each user - that’s for a DC not sure about a member server.

It would do your server the world of good to slap another 512mb of RAM into it. If your going for speed you want the OS to be retrieving from the page file as little as possible (it is a lot slower to retrieve from the Hard Drive than from RAM)

In theory your page file should be 768mb i.e. 1.5 times the amount of RAM in the machine

Here are a couple of links to help you out.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q197379

- Configuring Page Files for Optimisation

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q123747

- Moving Page file location

 

[LucyP]

ta I'll try those (I don't think the max memory configuration will help much as I've tried various permutations to little or no effect - if I cut's SQL's memory it just starts running like a pig sooner).