Post reply

Poor performance when all RAM allocated, but not in use

  • September 9, 2010 at 8:39 pm

    #240115

    Server 2008 R2 enterprise

    SQL 2008 R2 enterprise

    96 GB RAM

    SQL set to use all memory - 96 GB

    Only 5 GB actually in use by the process

    Query takes 30 seconds

    Drop SQL memory to between 12GB and 84GB range (any number in here works fine)

    Only 5 GB actually in use by the process

    Query takes 15 seconds

    I've read forums saying I need to be sure Windows isn't choking by not having ram available. Best I can tell this isn't the case, task manager shows plenty of ram free.

    Why does SQL crawl when I set it to be able to use all RAM in the system? Is there something going on behind the scenes wherein SQL is actually "claiming" the RAM without it being reflected in taskmanager?

    Appreciate the help.

  • September 10, 2010 at 4:44 am

    #1219071

    hirro (9/9/2010)

    Why does SQL crawl when I set it to be able to use all RAM in the system?

    I've not done a deep dive on what's actually going on but here's some shear speculation on my part...

    If Windows senses that all memory is being used (allocated, not necessarily used), what will it do? Swap File... and we all know what happens when usage of the swap file kicks in. Do you suppose SQL Server will cause ANY Windows functions (API's, whatever) to be used? I would think so. How fast are they going to run if they use the swap file for memory?

    Of course, I could be dead wrong. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 10, 2010 at 5:01 am

    #1219076

    There has be a similar Q last week.

    They pointed to a bug in W2008R2 and proposed the following hotfix a the solution: Win2008R2_Win7_HotFix_KB2265716 http://support.microsoft.com/kb/2265716

    Does this apply to your situation ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • September 10, 2010 at 5:23 am

    #1219086

    Indeed, setting max server memory is a must on 64-bit systems !

    If only I would have noted the source ref in my script :blush:

    --/* These settings are for x64, on a dedicated database server,

    -- only running the DB engine.

    -- default = 2147483647

    --Physical RAM MaxServerMem Setting

    --2GB 1500

    --4GB 3200

    --6GB 4800

    --8GB 6400

    --12GB 10000

    --16GB 13500

    --24GB 21500

    --32GB 29000

    --48GB 44000

    --64GB 60000

    --72GB 68000

    --96GB 92000

    --128GB 124000

    --*/

    edited: found a ref: "Suggested Max Memory Settings for SQL Server 2005/2008" by Glenn Berry http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • September 10, 2010 at 6:06 am

    #1219109

    ALZDBA (9/10/2010)

    There has be a similar Q last week.

    They pointed to a bug in W2008R2 and proposed the following hotfix a the solution: Win2008R2_Win7_HotFix_KB2265716 http://support.microsoft.com/kb/2265716

    Does this apply to your situation ?

    We tried that hotfix, no luck. We also tried kb 982383 and tweaking the power management settings. The original thought was that it was some bug in the OS. This build is a replacement for a 2003/sql 2008 DB.

    I guess when it comes to "claiming" RAM I'm used to *unix where it is all claimed and then returned as necessary. Seems odd that SQL will claim it all when it is not actually in use and cause Windows to choke.

  • September 10, 2010 at 6:20 am

    #1219117

    Heh... P.S. 15 Seconds for a query may still be too long. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 10, 2010 at 6:26 am

    #1219120

    Interesting note, if we change the max dop to 8 from 0 we can set RAM back to 2 TB and get that query down to 5-6 secs.

  • September 10, 2010 at 6:28 am

    #1219122

    ALZDBA (9/10/2010)

    Indeed, setting max server memory is a must on 64-bit systems !

    If only I would have noted the source ref in my script :blush:

    --/* These settings are for x64, on a dedicated database server,

    -- only running the DB engine.

    -- default = 2147483647

    --Physical RAM MaxServerMem Setting

    --2GB 1500

    --4GB 3200

    --6GB 4800

    --8GB 6400

    --12GB 10000

    --16GB 13500

    --24GB 21500

    --32GB 29000

    --48GB 44000

    --64GB 60000

    --72GB 68000

    --96GB 92000

    --128GB 124000

    --*/

    When we set RAM to anything more than ~84 GB (96 GB onboard) we get the same slow query results.

  • September 10, 2010 at 8:13 am

    #1219190

    Ran a test while tracking swap file disk usage.

    SQL set to use 84 GB max memory

    6 seconds for the query

    SQL set to use 92 GB max memory

    30 seconds for the query

    same swap file disk usage on both instances,

    I don't think has to do with memory not being available for Windows.

    Said this in an above post but if I change the maxdop to 8 instead of 0 (SQL decides and uses all 24) performance is perfect even with 92 GB of memory set. It has to be tied between the processor count and max memory settings.

    Installing 2008 instead of 2008 R2 to see if this is only tied to R2.

  • September 10, 2010 at 8:17 am

    #1219193

    Maxdop 0 is generally a bad setting regardless. If you have 24 logical CPUs, I would force maxdop 8 on the server.

    What is the total ram on the box, btw? Additional overhead (ram) is required when more threads are being run, so that makes sense.

  • September 10, 2010 at 8:33 am

    #1219218

    Derrick Smith (9/10/2010)

    Maxdop 0 is generally a bad setting regardless. If you have 24 logical CPUs, I would force maxdop 8 on the server.

    What is the total ram on the box, btw? Additional overhead (ram) is required when more threads are being run, so that makes sense.

    Agreed on the maxdop 0. Total RAM is 96 GB.

    Able to set max RAM to 2 TB on in SQL as long as maxdop is 8. If we set maxdop to 0 highest we can go is 84 GB (12 GB for OS).

    So basically you are saying that Windows needs the extra ram when maintaining extra threads for all the processor. If that were the case wouldn't I see a lot of page file access when I set max RAM to 2TB using maxdop 0 vs. maxdop 8? Page file access is the same when using either setting the query is just slower.

  • September 10, 2010 at 11:19 am

    #1219373

    Another interesting bit of information:

    (Physical RAM Config) SQL Allocation - Time it took

    (6x8GB) 48GB allocated to SQL - Query takes 6 seconds

    (12x8GB) 84 GB allocated to SQL – Query takes 6 seconds

    (12x8GB) 96 GB allocated to SQL - Query takes 30 seconds

    (18x8GB) 144 GB allocated to SQL – Query takes… still waiting… a long time. Ended up throwing an IO error before finishing.

    Msg 823, Level 24, State 2, Line 3

    The operating system returned error 1117(The request could not be performed because of an I/O device error.) to SQL Server during a read at offset 0x0000000314e000

  • September 10, 2010 at 11:22 am

    #1219378

    Yeah, sounds like the OS is running out of resources. I would leave 8gb for the OS on a 96gb box. Remember that SQL allocated ram is just for the cache...there is still other memory it uses outside that.

  • September 10, 2010 at 12:07 pm

    #1219404

    If the OS were running out of resources it would take 30 seconds to run the query when I had 48 GB installed and 48 GB allocated to SQL. It's only when we allocate more than 84 GB to SQL no matter how much ram we have installed.

  • September 10, 2010 at 6:59 pm

    #1219569

    stewartc-708166 (9/10/2010)

    Jeff Moden (9/10/2010)

    Of course, I could be dead wrong. 😉

    I recently had the "pleasure" of troubleshooting why a UAT SQL server regularly stopped working, with error "Insufficient disk space".

    SQL was allowed to use all the RAM, which it did, so the OS was using the swap file to do everything. However, being a distribution server, a lot of activity was taking place, all of it going into the swap file and filling up the C: drive, which eventually reached capacity and ......... (need I say more)

    Once SQL was restricted to 90% of available RAM, the issue disappeared

    Did I mention that I love instant gratification? Thanks for the reinforcing feedback. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 23 total)

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