Post reply

A significant part of sql server process memory has been paged out

  • December 15, 2014 at 6:15 am

    #292895

    I am running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4266.0 (X64) in a VM environment (Windows 2012)

    There is only 2GB of memory total on the Server.

    I have a max of 2147483647 MB (which is the default) configured for SQL Server and I added the domain account that runs the services to the group policy 'lock pages in memory'

    I am getting the following errors in my sql server log:

    A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 3603 seconds. Working set (KB): 50740, committed (KB): 120320, memory utilization: 42%.

    Do we just need to through some more memory at the VM or are there other things I can check in SQL Server?

  • December 15, 2014 at 6:47 am

    #1764713

    More memory.

    2gb is extremely low for a SQL Server instance (assuming we're not talking about SQL Server Express). The minimum requirement is 500mb, so you're just barely beyond that, and, let's not forget that memory has to be allocated for the operating system and, assuming you meant the physical box has 2gb, the hypervisor. There's almost no room left for SQL Server to operate within. Let's assume your database is really tiny, say, 3gb, which is practically invisible, it will take almost nothing for a query against that database to require significant paging of your memory. Let's say the database is of a decent size, 300gb, you're guaranteed constant paging on the memory with less than 2gb available.

    My laptop has 16gb. If you're going to run SQL Server within even a very modestly sized business, you'll need more than 2gb of memory for it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 15, 2014 at 6:49 am

    #1764714

    Thank you

  • December 15, 2014 at 4:57 pm

    #1764826

    If the server has only 2GB, then you'll have to limit SQL to 1.5GB. Otherwise it will use it all, causing serious memory problems. Yes, you almost certainly need more RAM, but you'll still need to prevent SQL from using all of it.

    EXEC sp_configure 'max server memory', 1536

    RECONFIGURE WITH OVERRIDE

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 16, 2014 at 6:10 am

    #1764910

    Thank you. I got them to throw more memory at the VM and I reconfigured SQL Server - error have stopped.

    Thank you!!

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

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