|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Memory Configuration Question - SQL 2000 sp4I am currently running the following setup. -Windows Server 2003 Enterprise Edition (32-bit) -4 x 2.5 ghz Xeon Processors -12 gigs of Ram (with /PAE switch enabled) -Static 4092mb page file -SQL Server 2000 sp4 Enterprise (8.00.2040) -Memory is dynamically set for Minimum of 9124mb and Max of 10027mb -Reserve Physical Memory for SQL server checkbox is enabled We are having some performance issues today (CPU peaking at 90-100%) and I was looking at Task Manager and a couple of things caught my eye. The first is that the sqlservr.exe process is only using 146mb of memory. Then 2nd is that the PF Usage is showing 10.3 gigs. Why would my SQL install only be using 146mb of the 9gig minimum? Why is my PF Usage showing 10.3 gig when the static page file is set to 4 gig? Any advice is appreciated. Task manager cannot count over 2GB memory for a single process. Use
Performance Monitor to determine SQL memory usage. The PF use is a side effect of AWE memory. In short, AWE "looks" like PF usage to the OS. I use that as a quick and dirty memory check on large systems.. So, your are seeing exactly what you should be seeing, given your setup. -- Show quoteGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP "BH337" <BH***@discussions.microsoft.com> wrote in message news:260C2D7F-95BF-476C-B65C-309B38587006@microsoft.com... > Hello all. > > I am currently running the following setup. > > -Windows Server 2003 Enterprise Edition (32-bit) > -4 x 2.5 ghz Xeon Processors > -12 gigs of Ram (with /PAE switch enabled) > -Static 4092mb page file > > -SQL Server 2000 sp4 Enterprise (8.00.2040) > -Memory is dynamically set for Minimum of 9124mb and Max of 10027mb > -Reserve Physical Memory for SQL server checkbox is enabled > > > We are having some performance issues today (CPU peaking at 90-100%) and I > was looking at Task Manager and a couple of things caught my eye. The > first > is that the sqlservr.exe process is only using 146mb of memory. Then 2nd > is > that the PF Usage is showing 10.3 gigs. > > Why would my SQL install only be using 146mb of the 9gig minimum? Why is > my > PF Usage showing 10.3 gig when the static page file is set to 4 gig? > > Any advice is appreciated. > Thanks for the info. I appreciate it.
Last question. Do you know if 32-bit Windows will work with a page file over 4 gigs in size? thanks. Show quote "Geoff N. Hiten" wrote: > Task manager cannot count over 2GB memory for a single process. Use > Performance Monitor to determine SQL memory usage. The PF use is a side > effect of AWE memory. In short, AWE "looks" like PF usage to the OS. I use > that as a quick and dirty memory check on large systems.. > > So, your are seeing exactly what you should be seeing, given your setup. > > -- > Geoff N. Hiten > Senior SQL Infrastructure Consultant > Microsoft SQL Server MVP > > > > > "BH337" <BH***@discussions.microsoft.com> wrote in message > news:260C2D7F-95BF-476C-B65C-309B38587006@microsoft.com... > > Hello all. > > > > I am currently running the following setup. > > > > -Windows Server 2003 Enterprise Edition (32-bit) > > -4 x 2.5 ghz Xeon Processors > > -12 gigs of Ram (with /PAE switch enabled) > > -Static 4092mb page file > > > > -SQL Server 2000 sp4 Enterprise (8.00.2040) > > -Memory is dynamically set for Minimum of 9124mb and Max of 10027mb > > -Reserve Physical Memory for SQL server checkbox is enabled > > > > > > We are having some performance issues today (CPU peaking at 90-100%) and I > > was looking at Task Manager and a couple of things caught my eye. The > > first > > is that the sqlservr.exe process is only using 146mb of memory. Then 2nd > > is > > that the PF Usage is showing 10.3 gigs. > > > > Why would my SQL install only be using 146mb of the 9gig minimum? Why is > > my > > PF Usage showing 10.3 gig when the static page file is set to 4 gig? > > > > Any advice is appreciated. > > > > Dunno. You don't actually need or want a page file that large.
The OS kernel footprint cannot exceed 4GB since it is still a 32-bit process. Also, a well-tuned SQL server should not page. You may see paging but that is an artifact of how backups work and isn't "real" paging. -- Show quoteGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP "BH337" <BH***@discussions.microsoft.com> wrote in message news:8B5B0FBB-E739-49B9-9447-93590AA34246@microsoft.com... > Thanks for the info. I appreciate it. > Last question. Do you know if 32-bit Windows will work with a page file > over 4 gigs in size? thanks. > > "Geoff N. Hiten" wrote: > >> Task manager cannot count over 2GB memory for a single process. Use >> Performance Monitor to determine SQL memory usage. The PF use is a side >> effect of AWE memory. In short, AWE "looks" like PF usage to the OS. I >> use >> that as a quick and dirty memory check on large systems.. >> >> So, your are seeing exactly what you should be seeing, given your setup. >> >> -- >> Geoff N. Hiten >> Senior SQL Infrastructure Consultant >> Microsoft SQL Server MVP >> >> >> >> >> "BH337" <BH***@discussions.microsoft.com> wrote in message >> news:260C2D7F-95BF-476C-B65C-309B38587006@microsoft.com... >> > Hello all. >> > >> > I am currently running the following setup. >> > >> > -Windows Server 2003 Enterprise Edition (32-bit) >> > -4 x 2.5 ghz Xeon Processors >> > -12 gigs of Ram (with /PAE switch enabled) >> > -Static 4092mb page file >> > >> > -SQL Server 2000 sp4 Enterprise (8.00.2040) >> > -Memory is dynamically set for Minimum of 9124mb and Max of 10027mb >> > -Reserve Physical Memory for SQL server checkbox is enabled >> > >> > >> > We are having some performance issues today (CPU peaking at 90-100%) >> > and I >> > was looking at Task Manager and a couple of things caught my eye. The >> > first >> > is that the sqlservr.exe process is only using 146mb of memory. Then >> > 2nd >> > is >> > that the PF Usage is showing 10.3 gigs. >> > >> > Why would my SQL install only be using 146mb of the 9gig minimum? Why >> > is >> > my >> > PF Usage showing 10.3 gig when the static page file is set to 4 gig? >> > >> > Any advice is appreciated. >> > >> >> That is what I was thinking......but it is always good to double check with
those that have more experience. I am guessing tha the "Reserver Physical Memory for SQL Server" checkbox eliminates SQL Server from paging anyway? Show quote "Geoff N. Hiten" wrote: > Dunno. You don't actually need or want a page file that large. > > The OS kernel footprint cannot exceed 4GB since it is still a 32-bit > process. Also, a well-tuned SQL server should not page. You may see paging > but that is an artifact of how backups work and isn't "real" paging. > > -- > Geoff N. Hiten > Senior SQL Infrastructure Consultant > Microsoft SQL Server MVP > > > "BH337" <BH***@discussions.microsoft.com> wrote in message > news:8B5B0FBB-E739-49B9-9447-93590AA34246@microsoft.com... > > Thanks for the info. I appreciate it. > > Last question. Do you know if 32-bit Windows will work with a page file > > over 4 gigs in size? thanks. > > > > "Geoff N. Hiten" wrote: > > > >> Task manager cannot count over 2GB memory for a single process. Use > >> Performance Monitor to determine SQL memory usage. The PF use is a side > >> effect of AWE memory. In short, AWE "looks" like PF usage to the OS. I > >> use > >> that as a quick and dirty memory check on large systems.. > >> > >> So, your are seeing exactly what you should be seeing, given your setup. > >> > >> -- > >> Geoff N. Hiten > >> Senior SQL Infrastructure Consultant > >> Microsoft SQL Server MVP > >> > >> > >> > >> > >> "BH337" <BH***@discussions.microsoft.com> wrote in message > >> news:260C2D7F-95BF-476C-B65C-309B38587006@microsoft.com... > >> > Hello all. > >> > > >> > I am currently running the following setup. > >> > > >> > -Windows Server 2003 Enterprise Edition (32-bit) > >> > -4 x 2.5 ghz Xeon Processors > >> > -12 gigs of Ram (with /PAE switch enabled) > >> > -Static 4092mb page file > >> > > >> > -SQL Server 2000 sp4 Enterprise (8.00.2040) > >> > -Memory is dynamically set for Minimum of 9124mb and Max of 10027mb > >> > -Reserve Physical Memory for SQL server checkbox is enabled > >> > > >> > > >> > We are having some performance issues today (CPU peaking at 90-100%) > >> > and I > >> > was looking at Task Manager and a couple of things caught my eye. The > >> > first > >> > is that the sqlservr.exe process is only using 146mb of memory. Then > >> > 2nd > >> > is > >> > that the PF Usage is showing 10.3 gigs. > >> > > >> > Why would my SQL install only be using 146mb of the 9gig minimum? Why > >> > is > >> > my > >> > PF Usage showing 10.3 gig when the static page file is set to 4 gig? > >> > > >> > Any advice is appreciated. > >> > > >> > >> > > Pretty much. It requires "lock pages in memory" rights on the service
account, which does exactly what you think it does. -- Show quoteGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP "BH337" <BH***@discussions.microsoft.com> wrote in message news:319A812C-941A-437F-AF89-B9A079F9EF15@microsoft.com... > That is what I was thinking......but it is always good to double check > with > those that have more experience. I am guessing tha the "Reserver Physical > Memory for SQL Server" checkbox eliminates SQL Server from paging anyway? > > "Geoff N. Hiten" wrote: > >> Dunno. You don't actually need or want a page file that large. >> >> The OS kernel footprint cannot exceed 4GB since it is still a 32-bit >> process. Also, a well-tuned SQL server should not page. You may see >> paging >> but that is an artifact of how backups work and isn't "real" paging. >> >> -- >> Geoff N. Hiten >> Senior SQL Infrastructure Consultant >> Microsoft SQL Server MVP >> >> >> "BH337" <BH***@discussions.microsoft.com> wrote in message >> news:8B5B0FBB-E739-49B9-9447-93590AA34246@microsoft.com... >> > Thanks for the info. I appreciate it. >> > Last question. Do you know if 32-bit Windows will work with a page >> > file >> > over 4 gigs in size? thanks. >> > >> > "Geoff N. Hiten" wrote: >> > >> >> Task manager cannot count over 2GB memory for a single process. Use >> >> Performance Monitor to determine SQL memory usage. The PF use is a >> >> side >> >> effect of AWE memory. In short, AWE "looks" like PF usage to the OS. >> >> I >> >> use >> >> that as a quick and dirty memory check on large systems.. >> >> >> >> So, your are seeing exactly what you should be seeing, given your >> >> setup. >> >> >> >> -- >> >> Geoff N. Hiten >> >> Senior SQL Infrastructure Consultant >> >> Microsoft SQL Server MVP >> >> >> >> >> >> >> >> >> >> "BH337" <BH***@discussions.microsoft.com> wrote in message >> >> news:260C2D7F-95BF-476C-B65C-309B38587006@microsoft.com... >> >> > Hello all. >> >> > >> >> > I am currently running the following setup. >> >> > >> >> > -Windows Server 2003 Enterprise Edition (32-bit) >> >> > -4 x 2.5 ghz Xeon Processors >> >> > -12 gigs of Ram (with /PAE switch enabled) >> >> > -Static 4092mb page file >> >> > >> >> > -SQL Server 2000 sp4 Enterprise (8.00.2040) >> >> > -Memory is dynamically set for Minimum of 9124mb and Max of 10027mb >> >> > -Reserve Physical Memory for SQL server checkbox is enabled >> >> > >> >> > >> >> > We are having some performance issues today (CPU peaking at 90-100%) >> >> > and I >> >> > was looking at Task Manager and a couple of things caught my eye. >> >> > The >> >> > first >> >> > is that the sqlservr.exe process is only using 146mb of memory. >> >> > Then >> >> > 2nd >> >> > is >> >> > that the PF Usage is showing 10.3 gigs. >> >> > >> >> > Why would my SQL install only be using 146mb of the 9gig minimum? >> >> > Why >> >> > is >> >> > my >> >> > PF Usage showing 10.3 gig when the static page file is set to 4 gig? >> >> > >> >> > Any advice is appreciated. >> >> > >> >> >> >> >> >> FYI - check the below articles out , MS dont suggest the Set working set
size option , especially when running with AWE enabled..: 822164 A computer that is running SQL Server 2000 with the AWE option enabled does not support the set working set size option http://support.microsoft.com/default.aspx?scid=kb;EN-US;822164 319942 How to determine proper SQL Server configuration settings http://support.microsoft.com/default.aspx?scid=kb;EN-US;319942 Which says : Changing this setting does not typically provide any performance benefit. Based on actual support cases, the changing of this setting typically causes more damage than good. HTH Show quote "Geoff N. Hiten" <SQLCrafts***@gmail.com> wrote in message news:ut3JShYIIHA.4272@TK2MSFTNGP06.phx.gbl... > Pretty much. It requires "lock pages in memory" rights on the service > account, which does exactly what you think it does. > > -- > Geoff N. Hiten > Senior SQL Infrastructure Consultant > Microsoft SQL Server MVP > > > > > "BH337" <BH***@discussions.microsoft.com> wrote in message > news:319A812C-941A-437F-AF89-B9A079F9EF15@microsoft.com... >> That is what I was thinking......but it is always good to double check >> with >> those that have more experience. I am guessing tha the "Reserver >> Physical >> Memory for SQL Server" checkbox eliminates SQL Server from paging anyway? >> >> "Geoff N. Hiten" wrote: >> >>> Dunno. You don't actually need or want a page file that large. >>> >>> The OS kernel footprint cannot exceed 4GB since it is still a 32-bit >>> process. Also, a well-tuned SQL server should not page. You may see >>> paging >>> but that is an artifact of how backups work and isn't "real" paging. >>> >>> -- >>> Geoff N. Hiten >>> Senior SQL Infrastructure Consultant >>> Microsoft SQL Server MVP >>> >>> >>> "BH337" <BH***@discussions.microsoft.com> wrote in message >>> news:8B5B0FBB-E739-49B9-9447-93590AA34246@microsoft.com... >>> > Thanks for the info. I appreciate it. >>> > Last question. Do you know if 32-bit Windows will work with a page >>> > file >>> > over 4 gigs in size? thanks. >>> > >>> > "Geoff N. Hiten" wrote: >>> > >>> >> Task manager cannot count over 2GB memory for a single process. Use >>> >> Performance Monitor to determine SQL memory usage. The PF use is a >>> >> side >>> >> effect of AWE memory. In short, AWE "looks" like PF usage to the OS. >>> >> I >>> >> use >>> >> that as a quick and dirty memory check on large systems.. >>> >> >>> >> So, your are seeing exactly what you should be seeing, given your >>> >> setup. >>> >> >>> >> -- >>> >> Geoff N. Hiten >>> >> Senior SQL Infrastructure Consultant >>> >> Microsoft SQL Server MVP >>> >> >>> >> >>> >> >>> >> >>> >> "BH337" <BH***@discussions.microsoft.com> wrote in message >>> >> news:260C2D7F-95BF-476C-B65C-309B38587006@microsoft.com... >>> >> > Hello all. >>> >> > >>> >> > I am currently running the following setup. >>> >> > >>> >> > -Windows Server 2003 Enterprise Edition (32-bit) >>> >> > -4 x 2.5 ghz Xeon Processors >>> >> > -12 gigs of Ram (with /PAE switch enabled) >>> >> > -Static 4092mb page file >>> >> > >>> >> > -SQL Server 2000 sp4 Enterprise (8.00.2040) >>> >> > -Memory is dynamically set for Minimum of 9124mb and Max of 10027mb >>> >> > -Reserve Physical Memory for SQL server checkbox is enabled >>> >> > >>> >> > >>> >> > We are having some performance issues today (CPU peaking at >>> >> > 90-100%) >>> >> > and I >>> >> > was looking at Task Manager and a couple of things caught my eye. >>> >> > The >>> >> > first >>> >> > is that the sqlservr.exe process is only using 146mb of memory. >>> >> > Then >>> >> > 2nd >>> >> > is >>> >> > that the PF Usage is showing 10.3 gigs. >>> >> > >>> >> > Why would my SQL install only be using 146mb of the 9gig minimum? >>> >> > Why >>> >> > is >>> >> > my >>> >> > PF Usage showing 10.3 gig when the static page file is set to 4 >>> >> > gig? >>> >> > >>> >> > Any advice is appreciated. >>> >> > >>> >> >>> >> >>> >>> > In smaller installations (4GB or less) I would agree with their conclusions.
I disagree with it on large memory installations. You don't get dynamic memory allocation anyway so locking things down is preferred. Again, the article is correct but incomplete. It does not differentiate when to use and when not to use "Set Working Size". -- Show quoteGeoff N. Hiten Senior SQL Infrastructure Consultant Microsoft SQL Server MVP "Dylan Kruger" <dylan.kru***@online-btg.sa> wrote in message news:%23vtMb1DMIHA.4456@TK2MSFTNGP03.phx.gbl... > FYI - check the below articles out , MS dont suggest the Set working set > size option , especially when running with AWE enabled..: > > 822164 A computer that is running SQL Server 2000 with the AWE option > enabled does not support the set working set size option > http://support.microsoft.com/default.aspx?scid=kb;EN-US;822164 > > 319942 How to determine proper SQL Server configuration settings > http://support.microsoft.com/default.aspx?scid=kb;EN-US;319942 > > Which says : > > Changing this setting does not typically provide any performance benefit. > Based on actual support cases, the changing of this setting typically > causes more damage than good. > > > HTH > > "Geoff N. Hiten" <SQLCrafts***@gmail.com> wrote in message > news:ut3JShYIIHA.4272@TK2MSFTNGP06.phx.gbl... >> Pretty much. It requires "lock pages in memory" rights on the service >> account, which does exactly what you think it does. >> >> -- >> Geoff N. Hiten >> Senior SQL Infrastructure Consultant >> Microsoft SQL Server MVP >> >> >> >> >> "BH337" <BH***@discussions.microsoft.com> wrote in message >> news:319A812C-941A-437F-AF89-B9A079F9EF15@microsoft.com... >>> That is what I was thinking......but it is always good to double check >>> with >>> those that have more experience. I am guessing tha the "Reserver >>> Physical >>> Memory for SQL Server" checkbox eliminates SQL Server from paging >>> anyway? >>> >>> "Geoff N. Hiten" wrote: >>> >>>> Dunno. You don't actually need or want a page file that large. >>>> >>>> The OS kernel footprint cannot exceed 4GB since it is still a 32-bit >>>> process. Also, a well-tuned SQL server should not page. You may see >>>> paging >>>> but that is an artifact of how backups work and isn't "real" paging. >>>> >>>> -- >>>> Geoff N. Hiten >>>> Senior SQL Infrastructure Consultant >>>> Microsoft SQL Server MVP >>>> >>>> >>>> "BH337" <BH***@discussions.microsoft.com> wrote in message >>>> news:8B5B0FBB-E739-49B9-9447-93590AA34246@microsoft.com... >>>> > Thanks for the info. I appreciate it. >>>> > Last question. Do you know if 32-bit Windows will work with a page >>>> > file >>>> > over 4 gigs in size? thanks. >>>> > >>>> > "Geoff N. Hiten" wrote: >>>> > >>>> >> Task manager cannot count over 2GB memory for a single process. Use >>>> >> Performance Monitor to determine SQL memory usage. The PF use is a >>>> >> side >>>> >> effect of AWE memory. In short, AWE "looks" like PF usage to the >>>> >> OS. I >>>> >> use >>>> >> that as a quick and dirty memory check on large systems.. >>>> >> >>>> >> So, your are seeing exactly what you should be seeing, given your >>>> >> setup. >>>> >> >>>> >> -- >>>> >> Geoff N. Hiten >>>> >> Senior SQL Infrastructure Consultant >>>> >> Microsoft SQL Server MVP >>>> >> >>>> >> >>>> >> >>>> >> >>>> >> "BH337" <BH***@discussions.microsoft.com> wrote in message >>>> >> news:260C2D7F-95BF-476C-B65C-309B38587006@microsoft.com... >>>> >> > Hello all. >>>> >> > >>>> >> > I am currently running the following setup. >>>> >> > >>>> >> > -Windows Server 2003 Enterprise Edition (32-bit) >>>> >> > -4 x 2.5 ghz Xeon Processors >>>> >> > -12 gigs of Ram (with /PAE switch enabled) >>>> >> > -Static 4092mb page file >>>> >> > >>>> >> > -SQL Server 2000 sp4 Enterprise (8.00.2040) >>>> >> > -Memory is dynamically set for Minimum of 9124mb and Max of >>>> >> > 10027mb >>>> >> > -Reserve Physical Memory for SQL server checkbox is enabled >>>> >> > >>>> >> > >>>> >> > We are having some performance issues today (CPU peaking at >>>> >> > 90-100%) >>>> >> > and I >>>> >> > was looking at Task Manager and a couple of things caught my eye. >>>> >> > The >>>> >> > first >>>> >> > is that the sqlservr.exe process is only using 146mb of memory. >>>> >> > Then >>>> >> > 2nd >>>> >> > is >>>> >> > that the PF Usage is showing 10.3 gigs. >>>> >> > >>>> >> > Why would my SQL install only be using 146mb of the 9gig minimum? >>>> >> > Why >>>> >> > is >>>> >> > my >>>> >> > PF Usage showing 10.3 gig when the static page file is set to 4 >>>> >> > gig? >>>> >> > >>>> >> > Any advice is appreciated. >>>> >> > >>>> >> >>>> >> >>>> >>>> >> >  |
|||||||||||||||||||||||
Other interesting topics