Memory Usage Problem on 64 Bit SQL Server 2005 ent Cluster

My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
and there is only 16GB in the server.
I have the max memory for SQL server set at 12582912 (12GB) but the process
is running at 15,388,052 ignoring the maximum settings.
Thus the server is swapping memory to disk.
How can i make sure the SQL server is limited in its memory usgage if it is
ignoring the setting.
Regards
TobyDid you change the setting after the server was already running? Decreasing
memory won't necessarily release reserved mem without a reboot.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TobiR" <TobiR@.discussions.microsoft.com> wrote in message
news:147CE1A0-1D90-431F-960A-E44BF36B695F@.microsoft.com...
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of
> RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the
> process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it
> is
> ignoring the setting.
> Regards
> Toby
>|||Also note that max memory setting applies to the buffer pool only. The SQL
Server process consumes more memory than just its buffer pool, although the
buffer pool is often the largest, or at least should be, memory consumer.
Linchi
"TobiR" wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
>|||On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> and there is only 16GB in the server.
> I have the max memory for SQL server set at 12582912 (12GB) but the process
> is running at 15,388,052 ignoring the maximum settings.
> Thus the server is swapping memory to disk.
> How can i make sure the SQL server is limited in its memory usgage if it is
> ignoring the setting.
> Regards
> Toby
12GB is 12288MB (which is what the max memory setting is in). Where
are you seeing max memory set to 12582912?|||Thanks for spotting my math issues! I had it set to 12TB instead of 12GB,
noticed it the other day, no wonder it was using eything it could get.
Thanks
Toby
"Jeffrey Williams" wrote:
> On Jan 23, 2:12 am, TobiR <To...@.discussions.microsoft.com> wrote:
> > My SQL Server Ent 2005 64Bit Active / Passive Cluster is using 16.3GB of RAM
> > and there is only 16GB in the server.
> >
> > I have the max memory for SQL server set at 12582912 (12GB) but the process
> > is running at 15,388,052 ignoring the maximum settings.
> >
> > Thus the server is swapping memory to disk.
> >
> > How can i make sure the SQL server is limited in its memory usgage if it is
> > ignoring the setting.
> >
> > Regards
> >
> > Toby
> 12GB is 12288MB (which is what the max memory setting is in). Where
> are you seeing max memory set to 12582912?
>

Memory Usage

How much memory can SQL Server 2000 Enterprise utilize when installed on an
x64 system with Windows Server 2003 Enterprise 64 bit?
Thanks in advance
Dan512Gb on a 64bits system, but I don't know if this limit is for SQL 64bits +
Windows 64 Bits or SQL 32bits on Win 64Bits
the limit on a 32Bits system is 64Gb
read this:
http://download.microsoft.com/download/8/1/7/817bb6e6-9d97-4a5d-be8d-508256ae4045/ChoosEd.doc
"DanR" <DanR@.discussions.microsoft.com> wrote in message
news:16491266-A2ED-4850-8592-096EA5828A4B@.microsoft.com...
> How much memory can SQL Server 2000 Enterprise utilize when installed on
> an
> x64 system with Windows Server 2003 Enterprise 64 bit?
> Thanks in advance
> Dan

memory setting in SQL 2005 in Windows 2003

We have a SQL 2005 installed in Windows 2003 64 bit system, which has 16 CPU and 32GB RAM, but the performance is poor. SQL server is AWE enable and the sql start account with "Lock Pages In Memory". I checked the task manager and it looks that the SQL server used only about 300 MB memory. Here is what I found:

http://blogs.msdn.com/psssql/archive/2006/11/28/sql-server-becomes-sluggish-or-appears-to-stall-on-64-bit-installations.aspx?CommentPosted=true#commentmessage

What I need to do?

Thanks

Sounds like you have installed the 32 bit version of SQL Server, not the 64bit version of SQL Server.

Can you verify which version of SQL Server was installed?

|||

If I recall, AWE has no effect in 64 bit OS, and extended memory usage is not properly captured with Perf Monitor.

Please refer to this article:

Configuration -Memory, 32 bit SQL 2005 on 64 Bit Windows 2003
http://msdn2.microsoft.com/en-us/library/ms187499.aspx

And for reference on Memory, this is good starting point:

http://msdn2.microsoft.com/en-us/library/ms187499.aspx

|||

Hi Sandlu,

AWE will have no effect on 64 bit servers.

Yes you still need "LOCK PAGES IN MEMORY".

To check exactly how much memory is being used by SQL Server use Perfmon to check for SQL ServerMemoryManager:

Total Server Memory (KB)

Also check if you have capped the memory by using Max Server Memory under sp_configure.

With regards to slow performance, it could be any problem like disks, memory or no/out of date stats or Index defragmentation.

Jag

|||Thank you, everyone, will try.

memory question day..

SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
Windows Server 2003 R2, Enterprise, 64bit
40gb ram in the system, no other apps.
I'm looking for the optimal settings for this box with regards to Min/Max,
AWE, Boot.ini file etc.
Sp_configure shows that whoever built this box set AWE =1, min and max -
33000000 (32.6gb)
The properties of the server, memory tab show the memory fixed at the full
40gb
perfmon shows the SQL memory Manager:total server memory counter at
39973432kb (38gb).
Not sure which GUI to trust here.
Thanks!
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available./pae in boot.ini?
Set min memory a gig or so less than max.
watch pages/sec in perfmon occassionally to ensure not paging.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:%23lIXDm4XIHA.6140@.TK2MSFTNGP02.phx.gbl...
> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> Windows Server 2003 R2, Enterprise, 64bit
> 40gb ram in the system, no other apps.
> I'm looking for the optimal settings for this box with regards to Min/Max,
> AWE, Boot.ini file etc.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
> The properties of the server, memory tab show the memory fixed at the full
> 40gb
> perfmon shows the SQL memory Manager:total server memory counter at
> 39973432kb (38gb).
> Not sure which GUI to trust here.
> Thanks!
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
>|||In this situation...would you set max at the total ram on the box? Seems
like you should specifically allow some room for the O/S...but I'm just not
all that familiar with 64 bit architecture
there are two lines in the boot.ini (alternate startup options)...one with
/pae, one without. Anyway to tell which was used? The one without is the
default
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13pkgobj171ir65@.corp.supernews.com...
> /pae in boot.ini?
> Set min memory a gig or so less than max.
> watch pages/sec in perfmon occassionally to ensure not paging.
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
> news:%23lIXDm4XIHA.6140@.TK2MSFTNGP02.phx.gbl...
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max, AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>
>|||Note that the numbers for max/min server memory are in MB, not in KB.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
So you are setting max/min server memory to ~33,000GB, which basically
leaves it up to the SQL Server instance to grab as much memory as it needs
minus some for the OS. On a box like this, in most cases, I'd just leave 4GB
for the OS and set the rest for max/min server memory.
Linchi
"Kevin3NF" wrote:
> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> Windows Server 2003 R2, Enterprise, 64bit
> 40gb ram in the system, no other apps.
> I'm looking for the optimal settings for this box with regards to Min/Max,
> AWE, Boot.ini file etc.
> Sp_configure shows that whoever built this box set AWE =1, min and max -
> 33000000 (32.6gb)
> The properties of the server, memory tab show the memory fixed at the full
> 40gb
> perfmon shows the SQL memory Manager:total server memory counter at
> 39973432kb (38gb).
> Not sure which GUI to trust here.
> Thanks!
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
>
>|||:) I didn't build this box, and just noticed that someone set it to
32TB...too funny.
Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
enabled.
Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
Thanks,
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
> Note that the numbers for max/min server memory are in MB, not in KB.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
> So you are setting max/min server memory to ~33,000GB, which basically
> leaves it up to the SQL Server instance to grab as much memory as it needs
> minus some for the OS. On a box like this, in most cases, I'd just leave
> 4GB
> for the OS and set the rest for max/min server memory.
> Linchi
> "Kevin3NF" wrote:
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max,
>> AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full
>> 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>|||> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
No need for /PAE.
Linchi
"Kevin3NF" wrote:
> :) I didn't build this box, and just noticed that someone set it to
> 32TB...too funny.
> Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
> enabled.
> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
> Thanks,
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
> > Note that the numbers for max/min server memory are in MB, not in KB.
> >
> >> Sp_configure shows that whoever built this box set AWE =1, min and max -
> >> 33000000 (32.6gb)
> >
> > So you are setting max/min server memory to ~33,000GB, which basically
> > leaves it up to the SQL Server instance to grab as much memory as it needs
> > minus some for the OS. On a box like this, in most cases, I'd just leave
> > 4GB
> > for the OS and set the rest for max/min server memory.
> >
> > Linchi
> >
> > "Kevin3NF" wrote:
> >
> >> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
> >> Windows Server 2003 R2, Enterprise, 64bit
> >> 40gb ram in the system, no other apps.
> >>
> >> I'm looking for the optimal settings for this box with regards to
> >> Min/Max,
> >> AWE, Boot.ini file etc.
> >>
> >> Sp_configure shows that whoever built this box set AWE =1, min and max -
> >> 33000000 (32.6gb)
> >>
> >> The properties of the server, memory tab show the memory fixed at the
> >> full
> >> 40gb
> >>
> >> perfmon shows the SQL memory Manager:total server memory counter at
> >> 39973432kb (38gb).
> >>
> >> Not sure which GUI to trust here.
> >>
> >> Thanks!
> >>
> >> --
> >>
> >> Kevin3NF
> >> SQL Server dude
> >>
> >> You want fries with that?
> >> http://kevin3nf.blogspot.com/
> >>
> >> I only check the newsgroups during work hours, M-F.
> >> Hit my blog and the contact links if necessary...I may be available.
> >>
> >>
> >>
> >>
> >>
>
>|||/PAE is an operating system flag, that changes the amount of directly
addressable memory for a 32-bit OS, for all applications. It has nothing
specific to do with SQL Server. So if Windows is 64-bit, there is no need
for /PAE.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Kevin3NF" <kevin@.SPAMTRAP.3nf-inc.com> wrote in message
news:eFkqEi5XIHA.5980@.TK2MSFTNGP04.phx.gbl...
> :) I didn't build this box, and just noticed that someone set it to
> 32TB...too funny.
> Nice catch, thanks. We had just set it to 36gb for min/max, with AWE
> enabled.
> Is /PAE necessary for 32 bit SQL on 64 bit Windows? I'm thinking yes.
> Thanks,
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:F867817C-8307-4257-911B-ED5B9D51A5EC@.microsoft.com...
>> Note that the numbers for max/min server memory are in MB, not in KB.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> So you are setting max/min server memory to ~33,000GB, which basically
>> leaves it up to the SQL Server instance to grab as much memory as it
>> needs
>> minus some for the OS. On a box like this, in most cases, I'd just leave
>> 4GB
>> for the OS and set the rest for max/min server memory.
>> Linchi
>> "Kevin3NF" wrote:
>> SQL Server Enterprise edition 2000 sp4+.2187, 32 bit
>> Windows Server 2003 R2, Enterprise, 64bit
>> 40gb ram in the system, no other apps.
>> I'm looking for the optimal settings for this box with regards to
>> Min/Max,
>> AWE, Boot.ini file etc.
>> Sp_configure shows that whoever built this box set AWE =1, min and max -
>> 33000000 (32.6gb)
>> The properties of the server, memory tab show the memory fixed at the
>> full
>> 40gb
>> perfmon shows the SQL memory Manager:total server memory counter at
>> 39973432kb (38gb).
>> Not sure which GUI to trust here.
>> Thanks!
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>>
>

Memory problems & package performance

Hi,

I know the SSIS memory problem has probably been covered quite a bit, but being a newbie in using the SSIS, I'm not quite sure how to improved the performance of my SSIS package.

Basically, I've a package that loops through all the subdirectories within a specified directory, and it then loops through each file in the subdirectory and with the use of the Data Flow, process each file (according to their filenames) with a Script Component to insert data into a SQL DB.

Each subdirectory has up to 15 different csv files, but each is less than 5kB. I probably have about 100 subdirectories.

When I run the package, it functioned properly, but the package stalled (no error but just stuck in one Data Flow) after a while, and when I checked my CPU memory, it was running at 100%.

I'm not sure how I could fix it or improved the memory allocation. I was not expecting to have any memory problems as the file size is small and the number of rows of data going into and out of the Script Component is no more than 20.

Any advice? Thanks.

Just an update, I observed that running my package from the Visual Studio (development environment) takes up a large chunk of memory. So, I close VS and run the package through DTEXEC.

From the Windows Task Manager, I observe that the memory usage for DTEXEC.exe keeps increasing, so I'm guessing that all the previous process is still being stored in the memory. Is there a way I could flush the memory each time I process a new file, as I don't need the memory to keep data that has been processed.

I'm not keen to increase the PC memory size as a solution as I don't think the SSIS memory should keep increasing. Any suggestions?

Memory Problem or else.

Hi Derek.
I just started lessons to read Chinese last week, but I'm afraid the images
you sent were a bit beyond my limited abilities yet (c:
If you translated the messages to English for us, you'd probably have a
better chance at getting a good answer to your question.
Regards,
Greg Linwood
SQL Server MVP
"Derek" <dalikyim@.netvigator.com> wrote in message
news:bvch66$ji21@.imsp212.netvigator.com...

quote:

---

> Hi,
> When I runing the embedded Visual Basic 3.0 on Win2K computer,
> I create a form and add a button, and then run it.
> The program break with this error. (see attached picture)
> Anyone know what is this error. Memory problem, Window 2000 system error
> or Embedded Visual Basic 3.0 problem ?
> On the other hand, when I install MS SQL server 2000, I also got this

---

error

quote:

---

> during installation, the setup is break.
> Anyone me these such kind of case ?
> Please advise.
> Thanks.
> Derek
>
>

---

Try setting your locale on your PC to US when installing the Eval software,
the _MP error is an installshield error and others have reported that
switching to US locale allowed the install to go ahead. You may well be able
to switch back once the install has completed
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Derek" <dalikyim@.netvigator.com> wrote in message
news:bvch66$ji21@.imsp212.netvigator.com...

quote:

---

> Hi,
> When I runing the embedded Visual Basic 3.0 on Win2K computer,
> I create a form and add a button, and then run it.
> The program break with this error. (see attached picture)
> Anyone know what is this error. Memory problem, Window 2000 system error
> or Embedded Visual Basic 3.0 problem ?
> On the other hand, when I install MS SQL server 2000, I also got this

---

error

quote:

---

> during installation, the setup is break.
> Anyone me these such kind of case ?
> Please advise.
> Thanks.
> Derek
>
>

---

Memory not being used by SQL 2005 EE 64 bit instance

We have an I64 server with 12 GB of Ram. On this server we have an instance
of SQL 2000 Enterprise with max memory set to 6 GB and AWE is turned on from
SSMS. This instance seems to be running fine but is always using all the 6
GB of memory according to task manager (if I increase the max memory it will
use it). We also have an instance of SQL 2005 Enterprise with max memory set
to 3 GB and AWE is turned on from SSMS. I know you don't have to turn on AWE
on 64 bit servers but I did just in case. I hope this is enough server info
so I'll get on with the problem. The 2005 instance doesn't seem to be
running very well and from the task manager I've never seen it use more then
124 MB of memory. It seems to have way too much I\O writes and between the
two they keep the CPUs pegged. This server has 2 Itanium 2 ia64 Family 31
Model 2 Stepping 1 1596 MHz CPUs.

If anyone has a idea on what could be wrong please let me know. I changed the minimum to 2 GB but it still only shows it is using 124 MB.

See my post "Does anyone have sql2005 EE running on 64 bit Itanium server?"