Parallelism query

Hi,
I have a sql 2000 server with 8 processors, server settings are as
default. I read on Technet that it is good practise to remove the
highest no. processors from being used for parallelism, corresponding
to the no. of NICs in the server. One of our 3rd party developers has
recommended only allowing one processor to be used as there is a
performance hit by the server working out which processor to use. Does
anyone have a definitive answer to this? I suspect he's wrong but I'd
like some hard evidence if possible, thanks.
Kev
Hi Kev,
Requirement for multi processors/Degree of parallelism depends upon the kind
of transaction thats been executed on the server.
If your nature of query includes more of insert, delete and update statments
(doesn't include heavy complicated queries), then query optimizer is wasting
its time evaluating each query to see if it can take advantage of
parallelism.
On the other hand, if the nature of queries include complex joins,
correlated subqueries, which takes maximum amount of time to execute the
query, then it makes sense to have DOP > 1.
You can read more about this at
http://www.sql-server-performance.co...n_settings.asp
- - - - - - - - -
Thanks
Yogish
"Network SI (UK) Ltd" wrote:

> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>
|||Your third party developer doesn't know what he is talking about. There is
overhead when you use query parallelism (of course) but that doesn't mean
you shouldn't use it. 2 processors working at 90% of their capacity still
return the results quicker than 1 processor working at 100%. Depending on
how your server is used, it is usually be a good idea to limit the maximum
number of processors that can be used per query, so that one query doesn't
block all the processors. What the ideal limit is, is something you have to
experiment with, depending on the number of large select queries vs.
insert/update/inserts and the performance you want of them respectively. For
example, if performance of updates is of the highest importance, and
performance of reporting on the server is of less importance, you would set
the maximum number of processors for parallelism to 1. On the other side of
the scale is something like a staging/processing server for a datawarehouse,
where there's mostly only one process running at a time. In that case you
set the maximum number of processors for parallelism to the number of
physical processors you have.
I don't completely agree with the Technet recommendation. Multiple
connections can share one NIC, and processor usage and network traffic are
usually somewhat correlated, but it is by no means straightforward.
SELECT * FROM multi_million_row_table
will cause a lot of network traffic, but not a lot of processor activity,
where
SELECT * FROM multi_million_row_table WHERE unindexed_column = <some value>
will cause less network traffic, but more processor activity.
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>
|||Following the logic of the 3rd party developer why not just scrap the big
iron and by a cheap server box?
Try a few configurations and use whichever works best for you. I suspect
that you should be able to enable 6,7, or even all 8 processors. I assume
that you are talking about 8 physical processors (not 4 physical with
hyperthreading enabled).
Keith
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>
|||Thank you for the 3 quick replies, confirming my feelings. Does anyone have
any 'Best Practise' recommendations or is it best to suck it and see?
Kev
Network SI (UK) Ltd
"Keith Kratochvil" wrote:

> Following the logic of the 3rd party developer why not just scrap the big
> iron and by a cheap server box?
> Try a few configurations and use whichever works best for you. I suspect
> that you should be able to enable 6,7, or even all 8 processors. I assume
> that you are talking about 8 physical processors (not 4 physical with
> hyperthreading enabled).
> --
> Keith
>
> "Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
> news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
>
>
|||Best thing would be to experiment and get some baseline. Get the a few of
the largest queries you have and run them with MAXDOP(1) to MAXDOP(8) (so
only those query are limited in the number of processors), and see what the
execution times are. It's pretty easy to create a script and run the whole
thing at a quiet time over the weekend or so
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <NetworkSIUKLtd@.discussions.microsoft.com> wrote in
message news:36784A55-E20F-4E2B-AE6B-BF38C4671F7E@.microsoft.com...[vbcol=seagreen]
> Thank you for the 3 quick replies, confirming my feelings. Does anyone
> have
> any 'Best Practise' recommendations or is it best to suck it and see?
> Kev
>
> --
> Network SI (UK) Ltd
>
> "Keith Kratochvil" wrote:

Parallelism query

Hi,
I have a sql 2000 server with 8 processors, server settings are as
default. I read on technet that it is good practise to remove the
highest no. processors from being used for parallelism, corresponding
to the no. of NICs in the server. One of our 3rd party developers has
recommended only allowing one processor to be used as there is a
performance hit by the server working out which processor to use. Does
anyone have a definitive answer to this? I suspect he's wrong but I'd
like some hard evidence if possible, thanks.
KevHi Kev,
Requirement for multi processors/Degree of parallelism depends upon the kind
of transaction thats been executed on the server.
If your nature of query includes more of insert, delete and update statments
(doesn't include heavy complicated queries), then query optimizer is wasting
its time evaluating each query to see if it can take advantage of
parallelism.
On the other hand, if the nature of queries include complex joins,
correlated subqueries, which takes maximum amount of time to execute the
query, then it makes sense to have DOP > 1.
You can read more about this at
http://www.sql-server-performance.c...on_settings.asp
- - - - - - - - -
Thanks
Yogish
"Network SI (UK) Ltd" wrote:

> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>|||Your third party developer doesn't know what he is talking about. There is
overhead when you use query parallelism (of course) but that doesn't mean
you shouldn't use it. 2 processors working at 90% of their capacity still
return the results quicker than 1 processor working at 100%. Depending on
how your server is used, it is usually be a good idea to limit the maximum
number of processors that can be used per query, so that one query doesn't
block all the processors. What the ideal limit is, is something you have to
experiment with, depending on the number of large select queries vs.
insert/update/inserts and the performance you want of them respectively. For
example, if performance of updates is of the highest importance, and
performance of reporting on the server is of less importance, you would set
the maximum number of processors for parallelism to 1. On the other side of
the scale is something like a staging/processing server for a datawarehouse,
where there's mostly only one process running at a time. In that case you
set the maximum number of processors for parallelism to the number of
physical processors you have.
I don't completely agree with the technet recommendation. Multiple
connections can share one NIC, and processor usage and network traffic are
usually somewhat correlated, but it is by no means straightforward.
SELECT * FROM multi_million_row_table
will cause a lot of network traffic, but not a lot of processor activity,
where
SELECT * FROM multi_million_row_table WHERE unindexed_column = <some value>
will cause less network traffic, but more processor activity.
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>|||Following the logic of the 3rd party developer why not just scrap the big
iron and by a cheap server box?
Try a few configurations and use whichever works best for you. I suspect
that you should be able to enable 6,7, or even all 8 processors. I assume
that you are talking about 8 physical processors (not 4 physical with
hyperthreading enabled).
Keith
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>|||Thank you for the 3 quick replies, confirming my feelings. Does anyone have
any 'Best Practise' recommendations or is it best to suck it and see?
Kev
Network SI (UK) Ltd
"Keith Kratochvil" wrote:

> Following the logic of the 3rd party developer why not just scrap the big
> iron and by a cheap server box?
> Try a few configurations and use whichever works best for you. I suspect
> that you should be able to enable 6,7, or even all 8 processors. I assume
> that you are talking about 8 physical processors (not 4 physical with
> hyperthreading enabled).
> --
> Keith
>
> "Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in messa
ge
> news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
>
>|||Best thing would be to experiment and get some baseline. Get the a few of
the largest queries you have and run them with MAXDOP(1) to MAXDOP(8) (so
only those query are limited in the number of processors), and see what the
execution times are. It's pretty easy to create a script and run the whole
thing at a quiet time over the weekend or so
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <NetworkSIUKLtd@.discussions.microsoft.com> wrote in
message news:36784A55-E20F-4E2B-AE6B-BF38C4671F7E@.microsoft.com...[vbcol=seagreen]
> Thank you for the 3 quick replies, confirming my feelings. Does anyone
> have
> any 'Best Practise' recommendations or is it best to suck it and see?
> Kev
>
> --
> Network SI (UK) Ltd
>
> "Keith Kratochvil" wrote:
>sql

Parallelism query

Hi,
I have a sql 2000 server with 8 processors, server settings are as
default. I read on Technet that it is good practise to remove the
highest no. processors from being used for parallelism, corresponding
to the no. of NICs in the server. One of our 3rd party developers has
recommended only allowing one processor to be used as there is a
performance hit by the server working out which processor to use. Does
anyone have a definitive answer to this? I suspect he's wrong but I'd
like some hard evidence if possible, thanks.
KevHi Kev,
Requirement for multi processors/Degree of parallelism depends upon the kind
of transaction thats been executed on the server.
If your nature of query includes more of insert, delete and update statments
(doesn't include heavy complicated queries), then query optimizer is wasting
its time evaluating each query to see if it can take advantage of
parallelism.
On the other hand, if the nature of queries include complex joins,
correlated subqueries, which takes maximum amount of time to execute the
query, then it makes sense to have DOP > 1.
You can read more about this at
http://www.sql-server-performance.com/sql_server_configuration_settings.asp
--
- - - - - - - - -
Thanks
Yogish
"Network SI (UK) Ltd" wrote:
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>|||Your third party developer doesn't know what he is talking about. There is
overhead when you use query parallelism (of course) but that doesn't mean
you shouldn't use it. 2 processors working at 90% of their capacity still
return the results quicker than 1 processor working at 100%. Depending on
how your server is used, it is usually be a good idea to limit the maximum
number of processors that can be used per query, so that one query doesn't
block all the processors. What the ideal limit is, is something you have to
experiment with, depending on the number of large select queries vs.
insert/update/inserts and the performance you want of them respectively. For
example, if performance of updates is of the highest importance, and
performance of reporting on the server is of less importance, you would set
the maximum number of processors for parallelism to 1. On the other side of
the scale is something like a staging/processing server for a datawarehouse,
where there's mostly only one process running at a time. In that case you
set the maximum number of processors for parallelism to the number of
physical processors you have.
I don't completely agree with the Technet recommendation. Multiple
connections can share one NIC, and processor usage and network traffic are
usually somewhat correlated, but it is by no means straightforward.
SELECT * FROM multi_million_row_table
will cause a lot of network traffic, but not a lot of processor activity,
where
SELECT * FROM multi_million_row_table WHERE unindexed_column = <some value>
will cause less network traffic, but more processor activity.
--
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>|||Following the logic of the 3rd party developer why not just scrap the big
iron and by a cheap server box?
Try a few configurations and use whichever works best for you. I suspect
that you should be able to enable 6,7, or even all 8 processors. I assume
that you are talking about 8 physical processors (not 4 physical with
hyperthreading enabled).
--
Keith
"Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> Hi,
>
> I have a sql 2000 server with 8 processors, server settings are as
> default. I read on Technet that it is good practise to remove the
> highest no. processors from being used for parallelism, corresponding
> to the no. of NICs in the server. One of our 3rd party developers has
> recommended only allowing one processor to be used as there is a
> performance hit by the server working out which processor to use. Does
> anyone have a definitive answer to this? I suspect he's wrong but I'd
> like some hard evidence if possible, thanks.
> Kev
>|||Thank you for the 3 quick replies, confirming my feelings. Does anyone have
any 'Best Practise' recommendations or is it best to suck it and see?
Kev
Network SI (UK) Ltd
"Keith Kratochvil" wrote:
> Following the logic of the 3rd party developer why not just scrap the big
> iron and by a cheap server box?
> Try a few configurations and use whichever works best for you. I suspect
> that you should be able to enable 6,7, or even all 8 processors. I assume
> that you are talking about 8 physical processors (not 4 physical with
> hyperthreading enabled).
> --
> Keith
>
> "Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in message
> news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
> > Hi,
> >
> >
> > I have a sql 2000 server with 8 processors, server settings are as
> > default. I read on Technet that it is good practise to remove the
> > highest no. processors from being used for parallelism, corresponding
> > to the no. of NICs in the server. One of our 3rd party developers has
> > recommended only allowing one processor to be used as there is a
> > performance hit by the server working out which processor to use. Does
> > anyone have a definitive answer to this? I suspect he's wrong but I'd
> > like some hard evidence if possible, thanks.
> >
> > Kev
> >
>
>|||Best thing would be to experiment and get some baseline. Get the a few of
the largest queries you have and run them with MAXDOP(1) to MAXDOP(8) (so
only those query are limited in the number of processors), and see what the
execution times are. It's pretty easy to create a script and run the whole
thing at a quiet time over the weekend or so
--
Jacco Schalkwijk
SQL Server MVP
"Network SI (UK) Ltd" <NetworkSIUKLtd@.discussions.microsoft.com> wrote in
message news:36784A55-E20F-4E2B-AE6B-BF38C4671F7E@.microsoft.com...
> Thank you for the 3 quick replies, confirming my feelings. Does anyone
> have
> any 'Best Practise' recommendations or is it best to suck it and see?
> Kev
>
> --
> Network SI (UK) Ltd
>
> "Keith Kratochvil" wrote:
>> Following the logic of the 3rd party developer why not just scrap the big
>> iron and by a cheap server box?
>> Try a few configurations and use whichever works best for you. I suspect
>> that you should be able to enable 6,7, or even all 8 processors. I
>> assume
>> that you are talking about 8 physical processors (not 4 physical with
>> hyperthreading enabled).
>> --
>> Keith
>>
>> "Network SI (UK) Ltd" <Kev Smart@.discussions.microsoft.com> wrote in
>> message
>> news:575D585F-2F48-4761-9479-1328DFE31E73@.microsoft.com...
>> > Hi,
>> >
>> >
>> > I have a sql 2000 server with 8 processors, server settings are as
>> > default. I read on Technet that it is good practise to remove the
>> > highest no. processors from being used for parallelism, corresponding
>> > to the no. of NICs in the server. One of our 3rd party developers has
>> > recommended only allowing one processor to be used as there is a
>> > performance hit by the server working out which processor to use. Does
>> > anyone have a definitive answer to this? I suspect he's wrong but I'd
>> > like some hard evidence if possible, thanks.
>> >
>> > Kev
>> >
>>

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you.
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.

> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:

> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>
|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.

Pagefile and Paging

My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
solution. He believes the pagefile settings should be 1.5 times the amount
of physical RAM which I agree but I seem to find it hard to correlate paging
with pagefile increase.
Also under what conditions would one need ot consider increasing the size of
the pagefile if its not set to 1.5 * Physical RAM ?
We are using SQL 2000/2005
Thank you."F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
>
He's 1/2 right. You need more memory. But increasing the pagefile won't
make a difference.
Basically SQL Server can page, or it can ask the OS to page to disk. Both
involve disk I/O.
Get more memory or rewrite your queries.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Increasing the page file size generally would not be the solution to hard
paging. I'd first try to determine whether hard paging is from the SQL Server
process (i.e. whether Windows is paging out the workign set of the SQL Server
process). If that's the case, try to find whether there is any other
processes that are consuming memory and caused paging.
It's also possible that you may be running into a SQL Server bug. For
instance, http://support.microsoft.com/kb/884593 or
http://support.microsoft.com/kb/918483 are examples. I'm not saying that they
apply to your case, but want to point this out as a possibility.
Linchi
"F" wrote:
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as a
> solution. He believes the pagefile settings should be 1.5 times the amount
> of physical RAM which I agree but I seem to find it hard to correlate paging
> with pagefile increase.
> Also under what conditions would one need ot consider increasing the size of
> the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.
>|||I too agree that increasing the pagefile size will not help at all. Your
DBA's first thought should have been "what is causing the paging" and not
how to get around it. SQL Server is designed to do as much as possible to
avoid paging to begin with. It is likely you have other applications on the
server than SQL Server that require some memory and SQL Server is set to use
most of it. If that is the case you may be able to avoid the paging by
setting the MAX Memory setting in SQL Server to leave room for the other
apps. Adding additional memory may also be an option but you still have to
consider how all the apps play together.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"F" <f@.hotmail.com> wrote in message
news:uBNVAg3lIHA.5684@.TK2MSFTNGP03.phx.gbl...
> My DBA sees paging ( high pages/sec) and thinks of increasing pagefile as
> a solution. He believes the pagefile settings should be 1.5 times the
> amount of physical RAM which I agree but I seem to find it hard to
> correlate paging with pagefile increase.
> Also under what conditions would one need ot consider increasing the size
> of the pagefile if its not set to 1.5 * Physical RAM ?
> We are using SQL 2000/2005
> Thank you.