Comments on: SQL Server memory configuration http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/ SQL Server posts that DBAs will (hopefully) find useful. Thu, 09 Sep 2010 02:08:24 +0000 http://wordpress.org/?v=2.6.1 By: Ajmer Dhariwal http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2463 Ajmer Dhariwal Sun, 05 Sep 2010 18:43:16 +0000 http://www.eraofdata.com/blog/?p=3#comment-2463 Please send me the dbcc memorystatus output from the configuration that results in long running queries. Also, are these ad-hoc/dynamic queries or stored procs? Please send me the dbcc memorystatus output from the configuration that results in long running queries.
Also, are these ad-hoc/dynamic queries or stored procs?

]]> By: Tonga http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2461 Tonga Thu, 26 Aug 2010 18:31:56 +0000 http://www.eraofdata.com/blog/?p=3#comment-2461 Very insteresting article. I have a very interesting scenary. Running W2003(4GB memory)/SQL 2005 When I set max memory to maximun, Im getting long times queries; but If I set max memory to round 200 MB then they take 2 seconds. Any suggestion? Very insteresting article.
I have a very interesting scenary. Running W2003(4GB memory)/SQL 2005
When I set max memory to maximun, Im getting long times queries; but If I set max memory to round 200 MB then they take 2 seconds. Any suggestion?

]]> By: Ajmer Dhariwal http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2459 Ajmer Dhariwal Tue, 08 Jun 2010 22:03:50 +0000 http://www.eraofdata.com/blog/?p=3#comment-2459 That looks about right to me. Monitoring the buffer cache size via the perfmon counters should validate those settings by showing memory usage eventually growing to 6GB and staying at that limit. That looks about right to me. Monitoring the buffer cache size via the perfmon counters should validate those settings by showing memory usage eventually growing to 6GB and staying at that limit.

]]> By: T_Brothers http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2458 T_Brothers Tue, 08 Jun 2010 20:57:02 +0000 http://www.eraofdata.com/blog/?p=3#comment-2458 Great post! Thanks! We have a Windows 2003 Ent server 64-bit with 8GB RAM. We are running SQL 2005 Ent 32-bit. We do not have /PAE or /3GB in the boot.ini and I will not add either of them. I will enable AWE wirthin SQL and set max memory to 6GB. I will enable "locked pages in memory" in gpedit.msc Then I will reboot the server and hope for the best. Does that sound correct? Thanks, T_Brothers Great post! Thanks!

We have a Windows 2003 Ent server 64-bit with 8GB RAM.
We are running SQL 2005 Ent 32-bit.

We do not have /PAE or /3GB in the boot.ini and I will not add either of them.
I will enable AWE wirthin SQL and set max memory to 6GB.
I will enable “locked pages in memory” in gpedit.msc
Then I will reboot the server and hope for the best.

Does that sound correct?

Thanks,
T_Brothers

]]> By: T Jacobs http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2456 T Jacobs Mon, 03 May 2010 10:39:16 +0000 http://www.eraofdata.com/blog/?p=3#comment-2456 Thanks. That was a big help Thanks. That was a big help

]]> By: Ajmer Dhariwal http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2455 Ajmer Dhariwal Thu, 29 Apr 2010 19:24:21 +0000 http://www.eraofdata.com/blog/?p=3#comment-2455 Thanks. You shouldn't have to enable PAE, but if it's 32-bit SQL you'll need to enable awe and because of that set a cap on memory usage, as it will use all the memory that edition can access. Thanks. You shouldn’t have to enable PAE, but if it’s 32-bit SQL you’ll need to enable awe and because of that set a cap on memory usage, as it will use all the memory that edition can access.

]]> By: T Jacobs http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2454 T Jacobs Thu, 29 Apr 2010 17:37:31 +0000 http://www.eraofdata.com/blog/?p=3#comment-2454 Hi Ajmer, This is a great post. I just to to resolve one final lingering in our mind. We are porting(until our sql 2008 is ready) our 32 bit SQL 2000 SP3 server (production version) to a 64 bit machine with 64 gig of ram with databases on a SAN. Based on the that there should be no PAE in the boot.ini. Correct? AWE should be disable. Correct? No max memory set. Correct? SQL 2000 with use all available ram. Correct? Thanks in Advance Hi Ajmer,

This is a great post. I just to to resolve one final lingering in our mind. We are porting(until our sql 2008 is ready) our 32 bit SQL 2000 SP3 server (production version) to
a 64 bit machine with 64 gig of ram with databases on a SAN. Based on the that there should be no PAE in the boot.ini. Correct?
AWE should be disable. Correct?
No max memory set. Correct?
SQL 2000 with use all available ram. Correct?

Thanks in Advance

]]> By: Ajmer Dhariwal http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2450 Ajmer Dhariwal Mon, 08 Feb 2010 09:03:07 +0000 http://www.eraofdata.com/blog/?p=3#comment-2450 Hi Jignesh, As indicatd in the blog, if no memory limits are set then SQL Server can eventually use up as much memory as it possibly can, if the workload is there. On a 32-bit system (without awe enabled) this will be @ 1.6 GB. There can be many reasons for the sluggish behaviour - how much RAM is there in total on the machine? If SQL Server is using most of what is available this may be a factor. Otherwise narrow down what is causing the issue using http://technet.microsoft.com/en-gb/library/cc966540.aspx Regards, Ajmer Hi Jignesh,

As indicatd in the blog, if no memory limits are set then SQL Server can eventually use up as much memory as it possibly can, if the workload is there. On a 32-bit system (without awe enabled) this will be @ 1.6 GB.
There can be many reasons for the sluggish behaviour - how much RAM is there in total on the machine? If SQL Server is using most of what is available this may be a factor.
Otherwise narrow down what is causing the issue using http://technet.microsoft.com/en-gb/library/cc966540.aspx

Regards,
Ajmer

]]> By: Jignesh http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2449 Jignesh Fri, 05 Feb 2010 08:12:48 +0000 http://www.eraofdata.com/blog/?p=3#comment-2449 Hi Thanks for the blog, i wanted to know is there any way that SQL server process release unused memory my problem is we have a single instance server containing a signle database to which atleast 10 users are conneted at a time now this SQL server process start consuming memory upto 1.5GB in no time and it resonses slow back to the client machines can you have any sugesion on this Hi
Thanks for the blog,

i wanted to know is there any way that SQL server process release unused memory
my problem is we have a single instance server containing a signle database to which atleast 10 users are conneted at a time

now this SQL server process start consuming memory upto 1.5GB in no time and it resonses slow back to the client machines

can you have any sugesion on this

]]> By: levidos http://www.eraofdata.com/blog/2008/10/sql-server-memory-configuration/#comment-2445 levidos Sat, 12 Dec 2009 12:04:03 +0000 http://www.eraofdata.com/blog/?p=3#comment-2445 Great post, thank you. Great post, thank you.

]]>