MS SQL Server - SQL 2005 Memory Usage

Author

SQL 2005 Memory Usage

Galtygreen

2006-12-01, 12:12 am

Hello,

I am trying to setup a new server with 4 processor, 16 GB of memory.
The server is installed with Windows 2003 R2 Enterprise x64 and SQL
2005 Standard Edition (64-bit) on it. I ran one of the complex query
and expected it should be faster than our production server, which is a
less powerful server. However that query took about 12 minutes to
finish running in the new server, while it took about 1.5 minutes to
run in production server.

I checked task manager, it showed "sqlservr.exe" only consumed 95MB
memory. How does this possible?

I have tried setting up the MAX Server Memory and AWE using
SP_CONFIGURE by doing the following.
1. Grant "Lock pages in memory" right to SQL service account
2. Run sp_configure to set awe to enable
3. Run sp_configure to set max server memory to 14GB

I thought the above will ensure the system have 14GB memory for SQL to
use. Why "sqlservr.exe" only shows 95MB in Task Manager? Am I missing
something here? With 16GB RAM, is 2GB for the OS too little (this is a
dedicated sql server)?

Your comment is appreciated. Thanks in advance.

Roy Harvey

2006-12-01, 12:12 am

A difference that large sounds like different query plans, not a
difference in server hardware. Try updating statistics and comparing
query plans.

Roy Harvey
Beacon Falls, CT

On 30 Nov 2006 18:29:08 -0800, "Galtygreen" <galtygreen@gmail.com>
wrote:

>Hello,
>
>I am trying to setup a new server with 4 processor, 16 GB of memory.
>The server is installed with Windows 2003 R2 Enterprise x64 and SQL
>2005 Standard Edition (64-bit) on it. I ran one of the complex query
>and expected it should be faster than our production server, which is a
>less powerful server. However that query took about 12 minutes to
>finish running in the new server, while it took about 1.5 minutes to
>run in production server.
>
>I checked task manager, it showed "sqlservr.exe" only consumed 95MB
>memory. How does this possible?
>
>I have tried setting up the MAX Server Memory and AWE using
>SP_CONFIGURE by doing the following.
>1. Grant "Lock pages in memory" right to SQL service account
>2. Run sp_configure to set awe to enable
>3. Run sp_configure to set max server memory to 14GB
>
>I thought the above will ensure the system have 14GB memory for SQL to
>use. Why "sqlservr.exe" only shows 95MB in Task Manager? Am I missing
>something here? With 16GB RAM, is 2GB for the OS too little (this is a
>dedicated sql server)?
>
>Your comment is appreciated. Thanks in advance.

Galtygreen

2006-12-01, 7:13 pm

Hi Roy,

Thanks for pointing me the right direction. After comparing the
execution plan, I tuned the query using Tuning Advisor and applied the
recommendation. It has a huge improvement. It only took 23 seconds to
complete compare to 12 minutes.

I have read lots of messages about SQL 2005 large memory support. I am
still confuse about what needs to be done with my current system to
utilize all the memory for SQL? Is "sqlservr.exe" showing 95MB in Task
Manager normal? How can I tell if SQL is using all the possible memroy?

Thanks all.

-Galty

Roy Harvey wrote:[color=darkred
]
> A difference that large sounds like different query plans, not a
> difference in server hardware. Try updating statistics and comparing
> query plans.
>
> Roy Harvey
> Beacon Falls, CT
>
> On 30 Nov 2006 18:29:08 -0800, "Galtygreen" <galtygreen@gmail.com>
> wrote:
>

Roy Harvey

2006-12-01, 7:13 pm

On 1 Dec 2006 11:24:44 -0800, "Galtygreen" <galtygreen@gmail.com>
wrote:

>I have read lots of messages about SQL 2005 large memory support. I am
>still confuse about what needs to be done with my current system to
>utilize all the memory for SQL? Is "sqlservr.exe" showing 95MB in Task
>Manager normal? How can I tell if SQL is using all the possible memroy?

If SQL Server needs more memory it will use it, up to whatever limit
may be set or available. If it uses less it simply means it has not
needed it yet.

Roy

Andrew J. Kelly

2006-12-01, 7:13 pm

In addition to what Roy stated you should use the perfmon counters to
monitor memory usage and not task manager.

--
Andrew J. Kelly SQL MVP

"Galtygreen" <galtygreen@gmail.com> wrote in message
news:1164940148.886232.62980@16g2000cwy.googlegroups.com...
> Hello,
>
> I am trying to setup a new server with 4 processor, 16 GB of memory.
> The server is installed with Windows 2003 R2 Enterprise x64 and SQL
> 2005 Standard Edition (64-bit) on it. I ran one of the complex query
> and expected it should be faster than our production server, which is a
> less powerful server. However that query took about 12 minutes to
> finish running in the new server, while it took about 1.5 minutes to
> run in production server.
>
> I checked task manager, it showed "sqlservr.exe" only consumed 95MB
> memory. How does this possible?
>
> I have tried setting up the MAX Server Memory and AWE using
> SP_CONFIGURE by doing the following.
> 1. Grant "Lock pages in memory" right to SQL service account
> 2. Run sp_configure to set awe to enable
> 3. Run sp_configure to set max server memory to 14GB
>
> I thought the above will ensure the system have 14GB memory for SQL to
> use. Why "sqlservr.exe" only shows 95MB in Task Manager? Am I missing
> something here? With 16GB RAM, is 2GB for the OS too little (this is a
> dedicated sql server)?
>
> Your comment is appreciated. Thanks in advance.
>

Galtygreen

2006-12-01, 7:13 pm

Thank you both of you. I did some testing using Performance Monitor.
The result was good.

One more question. We have two SQL with the same hardware and software
configuration. We are going to mirror the database with a Witness
server (third server). The principal and the mirror servers are running
on Windows Server 2003 R2 Enterprise Edition. Does the Witness server
need to be the same OS as the pricipal and mirror server? We are
planning to use SQL 2005 Express as the Witness server. Will Windows XP
with sp2 work? Thanks.

Andrew J. Kelly wrote:[color=darkred
]
> In addition to what Roy stated you should use the perfmon counters to
> monitor memory usage and not task manager.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Galtygreen" <galtygreen@gmail.com> wrote in message
> news:1164940148.886232.62980@16g2000cwy.googlegroups.com...

Andrew J. Kelly

2006-12-01, 7:13 pm

SQL Express is just fine for a witness and should run on XP with no
problems.

--
Andrew J. Kelly SQL MVP

"Galtygreen" <galtygreen@gmail.com> wrote in message
news:1165011493.419236.306550@16g2000cwy.googlegroups.com...
> Thank you both of you. I did some testing using Performance Monitor.
> The result was good.
>
> One more question. We have two SQL with the same hardware and software
> configuration. We are going to mirror the database with a Witness
> server (third server). The principal and the mirror servers are running
> on Windows Server 2003 R2 Enterprise Edition. Does the Witness server
> need to be the same OS as the pricipal and mirror server? We are
> planning to use SQL 2005 Express as the Witness server. Will Windows XP
> with sp2 work? Thanks.
>
>
>
>
> Andrew J. Kelly wrote:
>

Galtygreen

2006-12-04, 7:12 pm

Thank you all. It's very helpful.

Andrew J. Kelly wrote:[color=darkred
]
> SQL Express is just fine for a witness and should run on XP with no
> problems.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Galtygreen" <galtygreen@gmail.com> wrote in message
> news:1165011493.419236.306550@16g2000cwy.googlegroups.com...

Sponsored Links