Autonumber field: max

Showing posts with label max. Show all posts

Sunday, March 11, 2012

AWE, SQL Server Enterprise, W2k STANDARD + 4GB RAM

Hi
Enabling AWE on SQL Enterprise Ed on a W2K STANDARD ed does appear to work (there seemed to be some doubt). I have reserved 3.5GB max server memory, and it has been allocated. The AWE counters in perfmon also seem to indicate it is functioning.
Does anyone have any experience with this configuration (good or bad)?
Also, any tips as to monitor memory usage - SQLServer/SQL Cache Memory doesn't appear to be of any use.
RegardsHi Stuart,
Not sure what you have done yet, but refers to the following article for
configuring AWE on SQL Server
HOW TO: Configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/defaul...kb;en-us;274750
On 24 Mar 2004 08:04:17 -0800, Stuart wrote:

> Hi
> Enabling AWE on SQL Enterprise Ed on a W2K STANDARD ed does appear to
> work (there seemed to be some doubt). I have reserved 3.5GB max server
> memory, and it has been allocated. The AWE counters in perfmon also
> seem to indicate it is functioning.
> Does anyone have any experience with this configuration (good or bad)?
> Also, any tips as to monitor memory usage - SQLServer/SQL Cache Memory
> doesn't appear to be of any use.
> Regards
> Stuart|||Thanks - have read the MS articles (they often seem contradictory as
to whether AWE would work on W2k Server Standard)
However, even though AWE it does work on W2K server, there seems to be
some doubt as to whether there is any point in doing this, e.g.
http://www.sql-server-performance.com/awe_memory.asp says should leave
AWE off for my config.
To date, the config is looking quite good - enabling AWE has doubled
the available RAM to SQL and the Page Life Expectancy has gone up to
about 3 hours (up from a few seconds)

.
Regards
Stuart
Yih-Yoon Lee <yihyoon@.hotmail.com> wrote in message news:<1lywgm6zbxldi.sdsrs0dj91ss$.dlg@.4
0tude.net>...
> Hi Stuart,
> Not sure what you have done yet, but refers to the following article for
> configuring AWE on SQL Server
> HOW TO: Configure memory for more than 2 GB in SQL Server
> http://support.microsoft.com/defaul...kb;en-us;274750
> On 24 Mar 2004 08:04:17 -0800, Stuart wrote:
>

Thursday, March 8, 2012

AWE Question

What would be the optimal max server memory setting for the following
machine?
Dedicated SQL Server:
Intel Xeon MP 3.3 GHz (4 physical Processors)
136 GB (C Drive)
8.0 GB RAM
Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
SQL Server Enterprise 2000, with SP3
The machine has following database sizes. All databases are being accessed
all the time by around 300+ concurrent users.
DB#1 - 536.44 MB
DB#2 - 1.09 GB
DB#3 - 3.27 GB
DB#4 - 4.92 GB
DB#5 - 5.29 GB
DB#6 - 15.09 GB
DB#7 - 50.13 GB
I am still learning the ins and outs of configuring higher end db servers.
Are there any other non-default server settings that I should change?"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uqJkBN9uGHA.4756@.TK2MSFTNGP04.phx.gbl...
> What would be the optimal max server memory setting for the following
> machine?
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
> Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
> SQL Server Enterprise 2000, with SP3
> The machine has following database sizes. All databases are being accessed
> all the time by around 300+ concurrent users.
> DB#1 - 536.44 MB
> DB#2 - 1.09 GB
> DB#3 - 3.27 GB
> DB#4 - 4.92 GB
> DB#5 - 5.29 GB
> DB#6 - 15.09 GB
> DB#7 - 50.13 GB
> I am still learning the ins and outs of configuring higher end db servers.
>
Does each user access all databases or does each database have its own set
of users?
If the different databsaes represent seperate applications, you should
consider dividing the server into multiple SQL instances to divide up the
memory on the box and optionally the CPU's into 2 or 3 different resource
pools. 32bit SQL Server works more efficiently when it doesn't have to use
AWE to access memory, and multiple instances allow you to easilly partition
memory and CPU resources between the workloads.
David|||>
> Does each user access all databases or does each database have its own set
> of users?
All databases are the same application (different clients on each db similar
to an ASP service).
Each user usually accesses only a single database. Admin users would need
access to all.
> If the different databsaes represent seperate applications, you should
> consider dividing the server into multiple SQL instances to divide up the
> memory on the box and optionally the CPU's into 2 or 3 different resource
> pools. 32bit SQL Server works more efficiently when it doesn't have to
> use AWE to access memory, and multiple instances allow you to easilly
> partition memory and CPU resources between the workloads.
>
Interesting option. Your making me think about that.
You mention that 32bit SQL Server works more efficiently when it doesn't use
AWE. Does it affect performance that much?
My concern is the 50 GB database itself would benefit from more then 2 GB of
RAM.
You also mention that you can configure CPUs into different resource pools.
How does this work? Do you have a knowledge base link?|||"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
> >
>> Does each user access all databases or does each database have its own
>> set of users?
> All databases are the same application (different clients on each db
> similar to an ASP service).
> Each user usually accesses only a single database. Admin users would need
> access to all.
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up the
>> memory on the box and optionally the CPU's into 2 or 3 different resource
>> pools. 32bit SQL Server works more efficiently when it doesn't have to
>> use AWE to access memory, and multiple instances allow you to easilly
>> partition memory and CPU resources between the workloads.
> Interesting option. Your making me think about that.
> You mention that 32bit SQL Server works more efficiently when it doesn't
> use AWE. Does it affect performance that much?
> My concern is the 50 GB database itself would benefit from more then 2 GB
> of RAM.
Yes it would, but you have 7 databses and only 8G of ram. How much ram can
you afford to allocate for that one workload. And using the /3GB switch you
can give a SQL instance access to 3GB of memory without using AWE.
Also you will have one procedure cache per instance, and on 32bit SQL is
strictly limited in size. Multiple instances would have multiple procedure
caches.
"On 32-bit platforms configured with 4 GB or more of physical memory, you
can either use Address Windowing Extensions (AWE) or use multiple instances
of SQL Server as a way to fully utilize the large amount of physical memory.
AWE may work well for some scenarios; however, you should be aware that AWE
memory can only be used for data cache. The memory for procedure cache,
connections, locks, and other internal resources of SQL Server must come
from the 2 GB (or 3GB, depending on the settings used) portion of the
virtual memory. On systems needing to support a large number of databases
and user connections, multiple instances of SQL Server may be a better
approach to fully alleviate the 2 GB or 3GB memory constraint imposed by the
32-bit platform for these data structures."
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
> You also mention that you can configure CPUs into different resource
> pools. How does this work? Do you have a knowledge base link?
>
Simple partition of CPU's to SQL Instances should be done with the "affinity
mask" server option.
SQL Server 2005 Books Online
affinity mask Option
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Dynamic CPU allocation policies can be implemented with WSRM:
Windows System Resource Manager
http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would need
>> access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it doesn't
>> have to use AWE to access memory, and multiple instances allow you to
>> easilly partition memory and CPU resources between the workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2 GB
>> of RAM.
> Yes it would, but you have 7 databses and only 8G of ram. How much ram
> can you afford to allocate for that one workload. And using the /3GB
> switch you can give a SQL instance access to 3GB of memory without using
> AWE.
> Also you will have one procedure cache per instance, and on 32bit SQL is
> strictly limited in size. Multiple instances would have multiple
> procedure caches.
> "On 32-bit platforms configured with 4 GB or more of physical memory, you
> can either use Address Windowing Extensions (AWE) or use multiple
> instances of SQL Server as a way to fully utilize the large amount of
> physical memory. AWE may work well for some scenarios; however, you should
> be aware that AWE memory can only be used for data cache. The memory for
> procedure cache, connections, locks, and other internal resources of SQL
> Server must come from the 2 GB (or 3GB, depending on the settings used)
> portion of the virtual memory. On systems needing to support a large
> number of databases and user connections, multiple instances of SQL Server
> may be a better approach to fully alleviate the 2 GB or 3GB memory
> constraint imposed by the 32-bit platform for these data structures."
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
> Simple partition of CPU's to SQL Instances should be done with the
> "affinity mask" server option.
> SQL Server 2005 Books Online
> affinity mask Option
> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>
> Dynamic CPU allocation policies can be implemented with WSRM:
> Windows System Resource Manager
> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
> David
>
Thanks David. This info is very helpful.|||Isaac
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
I'd consider (based on your description) MAX Memory 7GB
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23$AxxK%23uGHA.4752@.TK2MSFTNGP02.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
>> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
>> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would
>> need access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it
>> doesn't have to use AWE to access memory, and multiple instances allow
>> you to easilly partition memory and CPU resources between the
>> workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2
>> GB of RAM.
>> Yes it would, but you have 7 databses and only 8G of ram. How much ram
>> can you afford to allocate for that one workload. And using the /3GB
>> switch you can give a SQL instance access to 3GB of memory without using
>> AWE.
>> Also you will have one procedure cache per instance, and on 32bit SQL is
>> strictly limited in size. Multiple instances would have multiple
>> procedure caches.
>> "On 32-bit platforms configured with 4 GB or more of physical memory, you
>> can either use Address Windowing Extensions (AWE) or use multiple
>> instances of SQL Server as a way to fully utilize the large amount of
>> physical memory. AWE may work well for some scenarios; however, you
>> should be aware that AWE memory can only be used for data cache. The
>> memory for procedure cache, connections, locks, and other internal
>> resources of SQL Server must come from the 2 GB (or 3GB, depending on the
>> settings used) portion of the virtual memory. On systems needing to
>> support a large number of databases and user connections, multiple
>> instances of SQL Server may be a better approach to fully alleviate the 2
>> GB or 3GB memory constraint imposed by the 32-bit platform for these data
>> structures."
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
>>
>> Simple partition of CPU's to SQL Instances should be done with the
>> "affinity mask" server option.
>> SQL Server 2005 Books Online
>> affinity mask Option
>> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>>
>> Dynamic CPU allocation policies can be implemented with WSRM:
>> Windows System Resource Manager
>> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
>> David
> Thanks David. This info is very helpful.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYF1sPEvGHA.724@.TK2MSFTNGP05.phx.gbl...
> Isaac
>> Dedicated SQL Server:
>> Intel Xeon MP 3.3 GHz (4 physical Processors)
>> 136 GB (C Drive)
>> 8.0 GB RAM
>
> I'd consider (based on your description) MAX Memory 7GB
>
Thanks Uri.

AWE on SQL 2005 and W 2003 Enterprise Server

I have 8 GB on a Windows 2003 Enterprise Server running SQL 2005. I have
added /PAE to the boot.ini file and changed the max memory on SQL to 6800
MB. I start the SQL Service with a separate account that has Lock Memory
privilege. How can I tell if I have set it up right. When I do a "dbcc
memorystatus" I am not sure what to look for. It seems that I am still only
using 1.7 GB of memory.
Thanks,
TomHello Tom,
It's a x86 system right? If so...
Connect to your Instance using SSMS and go to Server Properties. Go to
Memory from the left pane and click Running Values. Be sure AWE is still
selected. This will make you sure that AWE is being used by SQL Server.
You restarted your Server after adding PAE switch, right?
If you did not add 3GB switch, then your OS is still going to be using 2GB
of RAM. So you can not make your SQL Server use 7GB over 8GB of RAM.
If this stuff is OK and your server is SQL Server dedicated server (which
means there will not be running another application on that server and that
server serves only for queries against databases on SQL Server) then
everything should be fine.
Ekrem Önsoy
"tom" <tom@.pcreducator.com> wrote in message
news:%23tUja9WBIHA.4752@.TK2MSFTNGP04.phx.gbl...
>I have 8 GB on a Windows 2003 Enterprise Server running SQL 2005. I have
>added /PAE to the boot.ini file and changed the max memory on SQL to 6800
>MB. I start the SQL Service with a separate account that has Lock Memory
>privilege. How can I tell if I have set it up right. When I do a "dbcc
>memorystatus" I am not sure what to look for. It seems that I am still only
>using 1.7 GB of memory.
> Thanks,
> Tom
>|||Ekrem,
Thanks for your response.
Yes, it's 32-bit and I did reboot after adding the /PAE switch. It turns out
that after I did some more extensive testing, and watching the performance
monitor of "Total Server Memory" under "SQL Server: Memory Manager", I was
able to see the memory go higher than 1.7 GB.
Tom
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:909509D4-D31B-4ABD-9E38-980994F9C58F@.microsoft.com...
> Hello Tom,
>
> It's a x86 system right? If so...
> Connect to your Instance using SSMS and go to Server Properties. Go to
> Memory from the left pane and click Running Values. Be sure AWE is still
> selected. This will make you sure that AWE is being used by SQL Server.
> You restarted your Server after adding PAE switch, right?
> If you did not add 3GB switch, then your OS is still going to be using 2GB
> of RAM. So you can not make your SQL Server use 7GB over 8GB of RAM.
> If this stuff is OK and your server is SQL Server dedicated server (which
> means there will not be running another application on that server and
> that server serves only for queries against databases on SQL Server) then
> everything should be fine.
>
> --
> Ekrem Önsoy
>
> "tom" <tom@.pcreducator.com> wrote in message
> news:%23tUja9WBIHA.4752@.TK2MSFTNGP04.phx.gbl...
>>I have 8 GB on a Windows 2003 Enterprise Server running SQL 2005. I have
>>added /PAE to the boot.ini file and changed the max memory on SQL to 6800
>>MB. I start the SQL Service with a separate account that has Lock Memory
>>privilege. How can I tell if I have set it up right. When I do a "dbcc
>>memorystatus" I am not sure what to look for. It seems that I am still
>>only using 1.7 GB of memory.
>> Thanks,
>> Tom
>

AWE Memory: Seeing approx. 1 Gig less than expected

I am runnning a dedicated SQL Server 2005 Standard edition server on Win 200
3,
with 16 GB RAM.
I have AWE enabled, with a Max Server Memory of 14336 (14 GB).
I have /3GB /PAE set in the boot.ini.
When I run SQLServer:Memory Manager, Target Memory (KB) in Perfmon, I only
see 13722592 KB (or 13400.96875 MB, 13.086 GB) being used out of the 14 GB
specified.
Is this normal, or am I being robbed of a Gig of memory somewhere?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1If I recall correctly, AWE consumes about 1GB of memory as overhead.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:67a8999d3983c@.uwe...
>I am runnning a dedicated SQL Server 2005 Standard edition server on Win
>2003,
> with 16 GB RAM.
> I have AWE enabled, with a Max Server Memory of 14336 (14 GB).
> I have /3GB /PAE set in the boot.ini.
> When I run SQLServer:Memory Manager, Target Memory (KB) in Perfmon, I only
> see 13722592 KB (or 13400.96875 MB, 13.086 GB) being used out of the 14 GB
> specified.
> Is this normal, or am I being robbed of a Gig of memory somewhere?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||Thanks Arnie. I do not dispute that, but it does make me scratch my head.
On another dedicated SQL Server, identical to the current one I am building,
except this other one has only 8 GB of RAM, when I set the Max Server Memory
to 6144 (6 GB), I see all 6144 in Target Memory. If SQL Server keeps 1 GB to
itself, then I would expect to only see 5 GB being used in Target Memory, bu
t
no, I am seeing all 6 GB on my 8 GB machine. Just makes me wonder...makes me
feel like I am being cheated out of approximately 1 GB on my 16 GB server,
since I have 14 GB specified in Max Server Memory and am only seeing 13 GB i
n
Target Memory.
Arnie Rowland wrote:[vbcol=seagreen]
>If I recall correctly, AWE consumes about 1GB of memory as overhead.
>
>[quoted text clipped - 9 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1|||I really don't recall if that 1GB overhead is in addition to the AWE
allocation, or if it is part of the AWE allocation. (I always thought that
it was outside of the AWE allocation.)
So, back to your situation.
Is it possible that there is some other usage on the box that is grabbing
memory from the OS before AWE can get it's share (thereby leaving only 13GB
for AWE)?
Another instance of SQL Server with /3GB switch?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:67a9b4e454ccc@.uwe...
> Thanks Arnie. I do not dispute that, but it does make me scratch my head.
> On another dedicated SQL Server, identical to the current one I am
> building,
> except this other one has only 8 GB of RAM, when I set the Max Server
> Memory
> to 6144 (6 GB), I see all 6144 in Target Memory. If SQL Server keeps 1 GB
> to
> itself, then I would expect to only see 5 GB being used in Target Memory,
> but
> no, I am seeing all 6 GB on my 8 GB machine. Just makes me wonder...makes
> me
> feel like I am being cheated out of approximately 1 GB on my 16 GB server,
> since I have 14 GB specified in Max Server Memory and am only seeing 13 GB
> in
> Target Memory.
> Arnie Rowland wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200610/1
>|||I suspect that is the case (some other usage on the box is grabbing memory),
but it is not another instance. I am still investigating.
Arnie Rowland wrote:[vbcol=seagreen]
>I really don't recall if that 1GB overhead is in addition to the AWE
>allocation, or if it is part of the AWE allocation. (I always thought that
>it was outside of the AWE allocation.)
>So, back to your situation.
>Is it possible that there is some other usage on the box that is grabbing
>memory from the OS before AWE can get it's share (thereby leaving only 13GB
>for AWE)?
>Another instance of SQL Server with /3GB switch?
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200610/1

AWE Memory: Seeing approx. 1 Gig less than expected

I am runnning a dedicated SQL Server 2005 Standard edition server on Win 2003,
with 16 GB RAM.
I have AWE enabled, with a Max Server Memory of 14336 (14 GB).
I have /3GB /PAE set in the boot.ini.
When I run SQLServer:Memory Manager, Target Memory (KB) in Perfmon, I only
see 13722592 KB (or 13400.96875 MB, 13.086 GB) being used out of the 14 GB
specified.
Is this normal, or am I being robbed of a Gig of memory somewhere?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
If I recall correctly, AWE consumes about 1GB of memory as overhead.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:67a8999d3983c@.uwe...
>I am runnning a dedicated SQL Server 2005 Standard edition server on Win
>2003,
> with 16 GB RAM.
> I have AWE enabled, with a Max Server Memory of 14336 (14 GB).
> I have /3GB /PAE set in the boot.ini.
> When I run SQLServer:Memory Manager, Target Memory (KB) in Perfmon, I only
> see 13722592 KB (or 13400.96875 MB, 13.086 GB) being used out of the 14 GB
> specified.
> Is this normal, or am I being robbed of a Gig of memory somewhere?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||Thanks Arnie. I do not dispute that, but it does make me scratch my head.
On another dedicated SQL Server, identical to the current one I am building,
except this other one has only 8 GB of RAM, when I set the Max Server Memory
to 6144 (6 GB), I see all 6144 in Target Memory. If SQL Server keeps 1 GB to
itself, then I would expect to only see 5 GB being used in Target Memory, but
no, I am seeing all 6 GB on my 8 GB machine. Just makes me wonder...makes me
feel like I am being cheated out of approximately 1 GB on my 16 GB server,
since I have 14 GB specified in Max Server Memory and am only seeing 13 GB in
Target Memory.
Arnie Rowland wrote:[vbcol=seagreen]
>If I recall correctly, AWE consumes about 1GB of memory as overhead.
>[quoted text clipped - 9 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1
|||I really don't recall if that 1GB overhead is in addition to the AWE
allocation, or if it is part of the AWE allocation. (I always thought that
it was outside of the AWE allocation.)
So, back to your situation.
Is it possible that there is some other usage on the box that is grabbing
memory from the OS before AWE can get it's share (thereby leaving only 13GB
for AWE)?
Another instance of SQL Server with /3GB switch?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:67a9b4e454ccc@.uwe...
> Thanks Arnie. I do not dispute that, but it does make me scratch my head.
> On another dedicated SQL Server, identical to the current one I am
> building,
> except this other one has only 8 GB of RAM, when I set the Max Server
> Memory
> to 6144 (6 GB), I see all 6144 in Target Memory. If SQL Server keeps 1 GB
> to
> itself, then I would expect to only see 5 GB being used in Target Memory,
> but
> no, I am seeing all 6 GB on my 8 GB machine. Just makes me wonder...makes
> me
> feel like I am being cheated out of approximately 1 GB on my 16 GB server,
> since I have 14 GB specified in Max Server Memory and am only seeing 13 GB
> in
> Target Memory.
> Arnie Rowland wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200610/1
>
|||I suspect that is the case (some other usage on the box is grabbing memory),
but it is not another instance. I am still investigating.
Arnie Rowland wrote:[vbcol=seagreen]
>I really don't recall if that 1GB overhead is in addition to the AWE
>allocation, or if it is part of the AWE allocation. (I always thought that
>it was outside of the AWE allocation.)
>So, back to your situation.
>Is it possible that there is some other usage on the box that is grabbing
>memory from the OS before AWE can get it's share (thereby leaving only 13GB
>for AWE)?
>Another instance of SQL Server with /3GB switch?
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200610/1

AWE Memory: Seeing approx. 1 Gig less than expected

I am runnning a dedicated SQL Server 2005 Standard edition server on Win 2003,
with 16 GB RAM.
I have AWE enabled, with a Max Server Memory of 14336 (14 GB).
I have /3GB /PAE set in the boot.ini.
When I run SQLServer:Memory Manager, Target Memory (KB) in Perfmon, I only
see 13722592 KB (or 13400.96875 MB, 13.086 GB) being used out of the 14 GB
specified.
Is this normal, or am I being robbed of a Gig of memory somewhere?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1If I recall correctly, AWE consumes about 1GB of memory as overhead.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:67a8999d3983c@.uwe...
>I am runnning a dedicated SQL Server 2005 Standard edition server on Win
>2003,
> with 16 GB RAM.
> I have AWE enabled, with a Max Server Memory of 14336 (14 GB).
> I have /3GB /PAE set in the boot.ini.
> When I run SQLServer:Memory Manager, Target Memory (KB) in Perfmon, I only
> see 13722592 KB (or 13400.96875 MB, 13.086 GB) being used out of the 14 GB
> specified.
> Is this normal, or am I being robbed of a Gig of memory somewhere?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||Thanks Arnie. I do not dispute that, but it does make me scratch my head.
On another dedicated SQL Server, identical to the current one I am building,
except this other one has only 8 GB of RAM, when I set the Max Server Memory
to 6144 (6 GB), I see all 6144 in Target Memory. If SQL Server keeps 1 GB to
itself, then I would expect to only see 5 GB being used in Target Memory, but
no, I am seeing all 6 GB on my 8 GB machine. Just makes me wonder...makes me
feel like I am being cheated out of approximately 1 GB on my 16 GB server,
since I have 14 GB specified in Max Server Memory and am only seeing 13 GB in
Target Memory.
Arnie Rowland wrote:
>If I recall correctly, AWE consumes about 1GB of memory as overhead.
>>I am runnning a dedicated SQL Server 2005 Standard edition server on Win
>>2003,
>[quoted text clipped - 9 lines]
>> Is this normal, or am I being robbed of a Gig of memory somewhere?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1|||I really don't recall if that 1GB overhead is in addition to the AWE
allocation, or if it is part of the AWE allocation. (I always thought that
it was outside of the AWE allocation.)
So, back to your situation.
Is it possible that there is some other usage on the box that is grabbing
memory from the OS before AWE can get it's share (thereby leaving only 13GB
for AWE)?
Another instance of SQL Server with /3GB switch?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:67a9b4e454ccc@.uwe...
> Thanks Arnie. I do not dispute that, but it does make me scratch my head.
> On another dedicated SQL Server, identical to the current one I am
> building,
> except this other one has only 8 GB of RAM, when I set the Max Server
> Memory
> to 6144 (6 GB), I see all 6144 in Target Memory. If SQL Server keeps 1 GB
> to
> itself, then I would expect to only see 5 GB being used in Target Memory,
> but
> no, I am seeing all 6 GB on my 8 GB machine. Just makes me wonder...makes
> me
> feel like I am being cheated out of approximately 1 GB on my 16 GB server,
> since I have 14 GB specified in Max Server Memory and am only seeing 13 GB
> in
> Target Memory.
> Arnie Rowland wrote:
>>If I recall correctly, AWE consumes about 1GB of memory as overhead.
>>I am runnning a dedicated SQL Server 2005 Standard edition server on Win
>>2003,
>>[quoted text clipped - 9 lines]
>> Is this normal, or am I being robbed of a Gig of memory somewhere?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1
>|||I suspect that is the case (some other usage on the box is grabbing memory),
but it is not another instance. I am still investigating.
Arnie Rowland wrote:
>I really don't recall if that 1GB overhead is in addition to the AWE
>allocation, or if it is part of the AWE allocation. (I always thought that
>it was outside of the AWE allocation.)
>So, back to your situation.
>Is it possible that there is some other usage on the box that is grabbing
>memory from the OS before AWE can get it's share (thereby leaving only 13GB
>for AWE)?
>Another instance of SQL Server with /3GB switch?
>> Thanks Arnie. I do not dispute that, but it does make me scratch my head.
>[quoted text clipped - 20 lines]
>> Is this normal, or am I being robbed of a Gig of memory somewhere?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200610/1

Wednesday, March 7, 2012

AWE and the max momory setting

Having read this excellent post on AWE and the max momory setting:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55191

Which states:

"Optional to limit the amount of memory the SQL Server will use, because SQL Server will not dynamically allocate/deallocate memory when AWE is enabled.
Leave about 500 MB available for OS"

Subsequently I have read:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1210563,00.html

Which states:

"For instance, SQL Server 2005 will have memory buffer pool problems if the max server memory setting is not set to its default 2147483647 (i.e., all available memory)."

So what should I set my max. memory to on 2K5 when I have AWE enabled.

I am not sure about the comment in the second article that you have mentioned. That comment is not precise at all.

With regards to MAX Server Memory setting, it depends how much memory does your machine have and if you run other apps (Analysis/Reporting Services, etc) on the server.

Say if your server has 16GB and you only use the server for SQL Server, you should set the MAX Server Memory to 14GB.

regards

Jag

|||

I agree the 2nd article is not precise, which is why I ahve restricted by max memory. I have 8GB and have restricted SQL Server to 7.5GB as only the OS is running on that server.

|||

I would restrict SQL Server to 6.5GB if I had 8GB on my machine. With OS only having 500MB You might experience backup errors if SQL Server 2K5 uses all what it is configured for.

|||Thanks for the advice.

AWE and Target Server Memory

Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
This is the script we used to enable AWE and set Max Memory, essentially
leaving 2GB for the OS.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
Just so you are aware, we do have the boot.ini with the PAE and 3GB switches
set.
The question I have, is what I am seeing for Target and Total Server Memory:
Target Server Memory(KB) = 4087767
Total Server Memory(KB) = 4087747
I was expecting to see more Target Server Memory being used (closer to 6GB)
rather than only 4087767KB / 3.898GB.
Please explain.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1What version of SQL Server are you using?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:663faf808d6ce@.uwe...
Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
This is the script we used to enable AWE and set Max Memory, essentially
leaving 2GB for the OS.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
Just so you are aware, we do have the boot.ini with the PAE and 3GB switches
set.
The question I have, is what I am seeing for Target and Total Server Memory:
Target Server Memory(KB) = 4087767
Total Server Memory(KB) = 4087747
I was expecting to see more Target Server Memory being used (closer to 6GB)
rather than only 4087767KB / 3.898GB.
Please explain.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1|||SQL 2000
Tom Moreau wrote:
>What version of SQL Server are you using?
>Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
>This is the script we used to enable AWE and set Max Memory, essentially
>leaving 2GB for the OS.
>sp_configure 'show advanced options', 1
>RECONFIGURE
>GO
>sp_configure 'awe enabled', 1
>RECONFIGURE
>GO
>sp_configure 'max server memory', 6144
>RECONFIGURE
>GO
>Just so you are aware, we do have the boot.ini with the PAE and 3GB switche
s
>set.
>The question I have, is what I am seeing for Target and Total Server Memory
:
>Target Server Memory(KB) = 4087767
>Total Server Memory(KB) = 4087747
>I was expecting to see more Target Server Memory being used (closer to 6GB)
>rather than only 4087767KB / 3.898GB.
>Please explain.
Message posted via http://www.droptable.com|||Which SP level? What's the result of running:
SELECT @.@.VERSION
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:663fdb867f662@.uwe...
SQL 2000
Tom Moreau wrote:
>What version of SQL Server are you using?
>Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
>This is the script we used to enable AWE and set Max Memory, essentially
>leaving 2GB for the OS.
>sp_configure 'show advanced options', 1
>RECONFIGURE
>GO
>sp_configure 'awe enabled', 1
>RECONFIGURE
>GO
>sp_configure 'max server memory', 6144
>RECONFIGURE
>GO
>Just so you are aware, we do have the boot.ini with the PAE and 3GB
>switches
>set.
>The question I have, is what I am seeing for Target and Total Server
>Memory:
>Target Server Memory(KB) = 4087767
>Total Server Memory(KB) = 4087747
>I was expecting to see more Target Server Memory being used (closer to 6GB)
>rather than only 4087767KB / 3.898GB.
>Please explain.
Message posted via http://www.droptable.com|||Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Tom Moreau wrote:[vbcol=seagreen]
>Which SP level? What's the result of running:
>SELECT @.@.VERSION
>SQL 2000
>Tom Moreau wrote:
>[quoted text clipped - 27 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1|||What happens if you set the min server memory and restart SQL Server?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:664097d00a0ed@.uwe...
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Tom Moreau wrote:[vbcol=seagreen]
>Which SP level? What's the result of running:
>SELECT @.@.VERSION
>SQL 2000
>Tom Moreau wrote:
>[quoted text clipped - 27 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1|||I am away from the office and will not have access to the server until
tomorrow around 10:00 MST. Even then we will not be able to restart the
service until late tomorrow evening. Do you have a theory about setting "min
server memory"? I believe I recall on the "max server memory" the minimum is
set to 4. Anyway, just trying to follow your thought process on why the "min
server memory" might be inhibiting memory use.
Tom Moreau wrote:[vbcol=seagreen]
>What happens if you set the min server memory and restart SQL Server?
>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Window
s
>NT 5.2 (Build 3790: Service Pack 1)
>Tom Moreau wrote:
>[quoted text clipped - 7 lines]
Message posted via http://www.droptable.com|||In SQL 2005, server memory will gradually climb to the max server memory,
whether or not you're using AWE. In SQL 2000, it's supposed to take it in
one chunk, IIRC. I'm thinking that if max = min = whatever you really want,
then you may be forcing it up to that level straight away.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6641520a8602d@.uwe...
I am away from the office and will not have access to the server until
tomorrow around 10:00 MST. Even then we will not be able to restart the
service until late tomorrow evening. Do you have a theory about setting "min
server memory"? I believe I recall on the "max server memory" the minimum is
set to 4. Anyway, just trying to follow your thought process on why the "min
server memory" might be inhibiting memory use.
Tom Moreau wrote:[vbcol=seagreen]
>What happens if you set the min server memory and restart SQL Server?
>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
>Windows
>NT 5.2 (Build 3790: Service Pack 1)
>Tom Moreau wrote:
>[quoted text clipped - 7 lines]
Message posted via http://www.droptable.com|||This is the 'expected behavior' of 8.00.2039 and is fixed in 8.00.2040. See
http://support.microsoft.com/defaul...kb;en-us;899761
Linchi
"cbrichards via droptable.com" wrote:

> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windo
ws
> NT 5.2 (Build 3790: Service Pack 1)
>
> Tom Moreau wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
>

AWE and Target Server Memory

Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
This is the script we used to enable AWE and set Max Memory, essentially
leaving 2GB for the OS.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
Just so you are aware, we do have the boot.ini with the PAE and 3GB switches
set.
The question I have, is what I am seeing for Target and Total Server Memory:
Target Server Memory(KB) = 4087767
Total Server Memory(KB) = 4087747
I was expecting to see more Target Server Memory being used (closer to 6GB)
rather than only 4087767KB / 3.898GB.
Please explain.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
What version of SQL Server are you using?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:663faf808d6ce@.uwe...
Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
This is the script we used to enable AWE and set Max Memory, essentially
leaving 2GB for the OS.
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
Just so you are aware, we do have the boot.ini with the PAE and 3GB switches
set.
The question I have, is what I am seeing for Target and Total Server Memory:
Target Server Memory(KB) = 4087767
Total Server Memory(KB) = 4087747
I was expecting to see more Target Server Memory being used (closer to 6GB)
rather than only 4087767KB / 3.898GB.
Please explain.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
|||SQL 2000
Tom Moreau wrote:
>What version of SQL Server are you using?
>Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
>This is the script we used to enable AWE and set Max Memory, essentially
>leaving 2GB for the OS.
>sp_configure 'show advanced options', 1
>RECONFIGURE
>GO
>sp_configure 'awe enabled', 1
>RECONFIGURE
>GO
>sp_configure 'max server memory', 6144
>RECONFIGURE
>GO
>Just so you are aware, we do have the boot.ini with the PAE and 3GB switches
>set.
>The question I have, is what I am seeing for Target and Total Server Memory:
>Target Server Memory(KB) = 4087767
>Total Server Memory(KB) = 4087747
>I was expecting to see more Target Server Memory being used (closer to 6GB)
>rather than only 4087767KB / 3.898GB.
>Please explain.
Message posted via http://www.droptable.com
|||Which SP level? What's the result of running:
SELECT @.@.VERSION
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:663fdb867f662@.uwe...
SQL 2000
Tom Moreau wrote:
>What version of SQL Server are you using?
>Prior to enabling AWE, we had 8GB RAM but utilizing only 2.7GB.
>This is the script we used to enable AWE and set Max Memory, essentially
>leaving 2GB for the OS.
>sp_configure 'show advanced options', 1
>RECONFIGURE
>GO
>sp_configure 'awe enabled', 1
>RECONFIGURE
>GO
>sp_configure 'max server memory', 6144
>RECONFIGURE
>GO
>Just so you are aware, we do have the boot.ini with the PAE and 3GB
>switches
>set.
>The question I have, is what I am seeing for Target and Total Server
>Memory:
>Target Server Memory(KB) = 4087767
>Total Server Memory(KB) = 4087747
>I was expecting to see more Target Server Memory being used (closer to 6GB)
>rather than only 4087767KB / 3.898GB.
>Please explain.
Message posted via http://www.droptable.com
|||Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Tom Moreau wrote:[vbcol=seagreen]
>Which SP level? What's the result of running:
>SELECT @.@.VERSION
>SQL 2000
>Tom Moreau wrote:
>[quoted text clipped - 27 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
|||What happens if you set the min server memory and restart SQL Server?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:664097d00a0ed@.uwe...
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Tom Moreau wrote:[vbcol=seagreen]
>Which SP level? What's the result of running:
>SELECT @.@.VERSION
>SQL 2000
>Tom Moreau wrote:
>[quoted text clipped - 27 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
|||I am away from the office and will not have access to the server until
tomorrow around 10:00 MST. Even then we will not be able to restart the
service until late tomorrow evening. Do you have a theory about setting "min
server memory"? I believe I recall on the "max server memory" the minimum is
set to 4. Anyway, just trying to follow your thought process on why the "min
server memory" might be inhibiting memory use.
Tom Moreau wrote:[vbcol=seagreen]
>What happens if you set the min server memory and restart SQL Server?
>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
>NT 5.2 (Build 3790: Service Pack 1)
>Tom Moreau wrote:
>[quoted text clipped - 7 lines]
Message posted via http://www.droptable.com
|||In SQL 2005, server memory will gradually climb to the max server memory,
whether or not you're using AWE. In SQL 2000, it's supposed to take it in
one chunk, IIRC. I'm thinking that if max = min = whatever you really want,
then you may be forcing it up to that level straight away.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6641520a8602d@.uwe...
I am away from the office and will not have access to the server until
tomorrow around 10:00 MST. Even then we will not be able to restart the
service until late tomorrow evening. Do you have a theory about setting "min
server memory"? I believe I recall on the "max server memory" the minimum is
set to 4. Anyway, just trying to follow your thought process on why the "min
server memory" might be inhibiting memory use.
Tom Moreau wrote:[vbcol=seagreen]
>What happens if you set the min server memory and restart SQL Server?
>Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
>Windows
>NT 5.2 (Build 3790: Service Pack 1)
>Tom Moreau wrote:
>[quoted text clipped - 7 lines]
Message posted via http://www.droptable.com
|||This is the 'expected behavior' of 8.00.2039 and is fixed in 8.00.2040. See
http://support.microsoft.com/default...b;en-us;899761
Linchi
"cbrichards via droptable.com" wrote:

> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> Tom Moreau wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200609/1
>

Saturday, February 25, 2012

AWE and Max Server Memory (MB)

What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
Message posted via http://www.droptable.comYou want to set the max server memory to 6 GB = 6 * 1,204 MB = 7,224 MB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
Message posted via http://www.droptable.com|||You should also see my reply to your original post on this subject.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
> What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
> 1. I have the switch properly set in the boot.ini
> 2. AWE Enabled is set to 1
> 3. Max Server Memory (MB) set to 2147483647
> In other words, if I want to reserve 2 GB for my OS, then would not the
> Max
> Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
> --
> Message posted via http://www.droptable.com
>

AWE and Max Server Memory (MB)

What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
--
Message posted via http://www.sqlmonster.comYou want to set the max server memory to 6 GB = 6 * 1,204 MB = 7,224 MB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
1. I have the switch properly set in the boot.ini
2. AWE Enabled is set to 1
3. Max Server Memory (MB) set to 2147483647
In other words, if I want to reserve 2 GB for my OS, then would not the Max
Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
--
Message posted via http://www.sqlmonster.com|||You should also see my reply to your original post on this subject.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:65e80daa1f3f8@.uwe...
> What purpose does Max Server Memory (MB) serve, if I have 8 GB of RAM:
> 1. I have the switch properly set in the boot.ini
> 2. AWE Enabled is set to 1
> 3. Max Server Memory (MB) set to 2147483647
> In other words, if I want to reserve 2 GB for my OS, then would not the
> Max
> Server Memory (MB) set to 2147483647 allow SQL Server to use 6 GB?
> --
> Message posted via http://www.sqlmonster.com
>

Sunday, February 19, 2012

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

any help would be greatly appreciated.

Cheers,
Craig

|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

any help would be greatly appreciated.

Cheers,
Craig

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

regards

terence chua

Sunday, March 11, 2012

AWE, SQL Server Enterprise, W2k STANDARD + 4GB RAM

Thursday, March 8, 2012

AWE Question

AWE Question

AWE on SQL 2005 and W 2003 Enterprise Server

AWE Memory: Seeing approx. 1 Gig less than expected

AWE Memory: Seeing approx. 1 Gig less than expected

AWE Memory: Seeing approx. 1 Gig less than expected

Wednesday, March 7, 2012

AWE and the max momory setting

AWE and Target Server Memory

AWE and Target Server Memory

AWE and Target Server Memory

AWE and Target Server Memory

Saturday, February 25, 2012

AWE and Max Server Memory (MB)

AWE and Max Server Memory (MB)

Sunday, February 19, 2012

Avg, Min & Max of two date fields in a Table

Avg, Min & Max of two date fields in a Table

Avg, Min & Max of two date fields in a Table

Autonumber field

Blog Archive

About Me