Performance Recommendations for SQL Server - Paging Related

I just became in charge of a server running SQL Server 2000 Enterprise on a
Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB RAM
of the 32 GB RAM in use. The database on the server is 120GB. Approximately
60GB is actual data, 49GB of indexes and the rest is empty space. For
reference, this database is for our ClarifyCRM application.
I am starting to do performance monitoring and I am seeing a high amount of
system paging - 1,000-2000 or more pages/sec.
Even though RAM utilization is low with plenty to spare, I see that the
paging file on the server is only 2GB in size (40.8 MB of it is actually in
use). Other than increasing the size of the paging file (which I will be
doing soon) while is extremely small, are there any other recommendations to
decrease the amount of system paging that is occurring?What are the memory settings set to, especially the MAX Memory one? It
sounds odd that only 17.8 out of 32GB is being used. I assume this is 32 bit
so with AWE the memory should not be dynamic and I would expect it to be
much higher. What other apps are running on this server?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
of the actual memory you can get at.
Tony.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
> What are the memory settings set to, especially the MAX Memory one? It
> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
> bit so with AWE the memory should not be dynamic and I would expect it to
> be much higher. What other apps are running on this server?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
>> RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other recommendations
>> to
>> decrease the amount of system paging that is occurring?
>|||You may not actually have a problem.
Microsoft has an excellent (and short) article on paging and monitorint it,
I suggect you spend a few minutes reading it.
http://support.microsoft.com/kb/555223
Good luck,
Jay
"KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>I just became in charge of a server running SQL Server 2000 Enterprise on a
> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running on
> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8 GB
> RAM
> of the 32 GB RAM in use. The database on the server is 120GB.
> Approximately
> 60GB is actual data, 49GB of indexes and the rest is empty space. For
> reference, this database is for our ClarifyCRM application.
> I am starting to do performance monitoring and I am seeing a high amount
> of
> system paging - 1,000-2000 or more pages/sec.
> Even though RAM utilization is low with plenty to spare, I see that the
> paging file on the server is only 2GB in size (40.8 MB of it is actually
> in
> use). Other than increasing the size of the paging file (which I will be
> doing soon) while is extremely small, are there any other recommendations
> to
> decrease the amount of system paging that is occurring?|||Yes Tony probably hit the nail on the head here. You are using SQL2000 so
you most likely have SP4 without the Hotfix and it will only use approx half
the memory.
http://support.microsoft.com/kb/899761
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>of the actual memory you can get at.
> Tony.
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson
> [Ramblings from the field from a SQL consultant]
> http://sqlserverfaq.com
> [UK SQL User Community]
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it to
>> be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise on
>>a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high amount
>> of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is actually
>> in
>> use). Other than increasing the size of the paging file (which I will be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>|||Thats the one.
Thanks Andrew - I was being lazy, well, time constrained :(
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OhLBUbbDIHA.5976@.TK2MSFTNGP02.phx.gbl...
> Yes Tony probably hit the nail on the head here. You are using SQL2000 so
> you most likely have SP4 without the Hotfix and it will only use approx
> half the memory.
> http://support.microsoft.com/kb/899761
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Tony Rogerson" <tonyrogerson@.torver.net> wrote in message
> news:64222C7B-A187-4BE8-AB4D-AA309F75377A@.microsoft.com...
>>I don't recall the hotfix, but - SP4 rtm they broke AWE, you only see half
>>of the actual memory you can get at.
>> Tony.
>> --
>> Tony Rogerson, SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson
>> [Ramblings from the field from a SQL consultant]
>> http://sqlserverfaq.com
>> [UK SQL User Community]
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OvGjlPQDIHA.5328@.TK2MSFTNGP05.phx.gbl...
>> What are the memory settings set to, especially the MAX Memory one? It
>> sounds odd that only 17.8 out of 32GB is being used. I assume this is 32
>> bit so with AWE the memory should not be dynamic and I would expect it
>> to be much higher. What other apps are running on this server?
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "KnightFall1" <KnightFall1@.discussions.microsoft.com> wrote in message
>> news:0B03B003-FB37-4586-A379-0F14DF5A8586@.microsoft.com...
>>I just became in charge of a server running SQL Server 2000 Enterprise
>>on a
>> Dell server with 8 Xeon CPUs running 3 Ghz and 32 GB RAM. It is running
>> on
>> Windows 2003 Enterprise. Memory utilization is fairly steady with 17.8
>> GB RAM
>> of the 32 GB RAM in use. The database on the server is 120GB.
>> Approximately
>> 60GB is actual data, 49GB of indexes and the rest is empty space. For
>> reference, this database is for our ClarifyCRM application.
>> I am starting to do performance monitoring and I am seeing a high
>> amount of
>> system paging - 1,000-2000 or more pages/sec.
>> Even though RAM utilization is low with plenty to spare, I see that the
>> paging file on the server is only 2GB in size (40.8 MB of it is
>> actually in
>> use). Other than increasing the size of the paging file (which I will
>> be
>> doing soon) while is extremely small, are there any other
>> recommendations to
>> decrease the amount of system paging that is occurring?
>>
>