Memory Management

Question

Memory Management

Alan_C

SSC Enthusiast

Points: 121
More actions
December 20, 2003 at 5:03 am

#65364

Hi
We have two SQL databases. One comes as part of a third party package, the other is an in-house job using an Access 2000 front end. The Access 2000 in house accesses the other database via linked tables to display data. There were serious problems accessing larger tables on the third party data via Access whenever the third party package was involved in major updates. Basically the Access database would freeze when users opened the report and all other users would be frozen out. The vendors of the third party package came along and changed the way that SQL manages memory from dynamic allocation to using a fixed allocation. This does seem to have fixed the problem but I personally don't understand the logic. SQL 2000 is installed on a Win 2K server, no other applications use this server. Can anyone enlighten me?
Thanks
Alan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jxflagg Hall of Fame Points: 3817 More actions · Answer 1

How much physical memory is on the server, how much memory is used during those peak times, and how much memory did the vendor set for fixed allocation?

If I had to take a guess, it sounds like there may not be that much physical memory on the server. During peak times, the Windows Server would take time allocation more virtual memory. It's also possible that SQL Server wasn't caching as much.

You could get the same effect by leaving it with dynamic allocation but setting a minimum.

Alan_C SSC Enthusiast Points: 121 More actions · Answer 2

There is 1 GByte of Ram and the vendor set a fixed size of 0.5 GB. There are no other applications running on the server so I would have expected that this would reduce the amount of memory available to SQL Server.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

I suspect that the third-party package is using the server as more than just a database server, i.e. they're using it as a file server or application server. When one of their non-SQL processes needs memory, the OS pressure caused SQL Server to reduce its memory footprint, slowing the entire system.

--Jonathan

jxflagg Hall of Fame Points: 3817 More actions · Answer 4

1 GB isn't very much. I've seen the OS routinely take up that much or more by itself.

I believe that it is as I suspected; the delay was caused by creating more virtual memory. By reserving a fixed block for SQL Server, you're avoiding this problem.