Parallelism problem?

Hi All,
My environment configuration is:
* 4 processor Xeon 2000 Intell Machine with hyperthreading on (OS sees 8
virtual processors) with 4GB memory
* Win 2K advanced
* SQL 2K Enterprise (sp3a)
This is the problem I experience. Consider this query on a table
(StatisticsConsultation) containing 1 mln records.
SELECT COUNT(*) FROM Clinic
join StatisticsConsultation on StatisticsConsultation.ClinicId = Clinic.ClinicId
join vwDoctor D on StatisticsConsultation.DoctorId = D.DoctorId
join Specialty on StatisticsConsultation.SpecialtyId = Specialty.SpecialtyId
join vwPerson P on StatisticsConsultation.PersonId = P.PersonId where
Clinic.RegionId = 345
the result of the query is the number about 800.000.
When the server is allocated only for that query it takes 4 seconds
(parallelism used)
When there is a background process which constantly uses only 1 processor
out of 8
(other resources are not highly utilised) the time rises up to 25 seconds.
1 .When I specify MAXDOP 1 option the query takes 7 seconds with the
backgroud process.
Can anyone explain me why this happens? Is it parallelism bag?
2. The above query when revriten
SELECT COUNT(*) FROM Clinic
join StatisticsConsultation on StatisticsConsultation.ClinicId = Clinic.ClinicId where Clinic.RegionId = 345
takes only 1 sec in comparison to 4 seconds. Why SQL Server doesn't optimize
it correctly?
Thank you in advance,
Regards,
Tomaszparallelism is a very operation to optimize for all cases.
this is one area where the SQL Server default setting
needs to be investigated.
I would test the query with MAXDOP 1, 2 & 4
most definitely, not allow use of more than the physical
processors.
I think you will find 1 or 2 is preferred when taking into
account overall response times for multiple users, and 4
for off hours maintainence
the last query is different, the extra joins could affect
row count
>--Original Message--
>Hi All,
>My environment configuration is:
>* 4 processor Xeon 2000 Intell Machine with
hyperthreading on (OS sees 8
>virtual processors) with 4GB memory
>* Win 2K advanced
>* SQL 2K Enterprise (sp3a)
>This is the problem I experience. Consider this query on
a table
>(StatisticsConsultation) containing 1 mln records.
>SELECT COUNT(*) FROM Clinic
> join StatisticsConsultation on
StatisticsConsultation.ClinicId =>Clinic.ClinicId
> join vwDoctor D on StatisticsConsultation.DoctorId =D.DoctorId
> join Specialty on StatisticsConsultation.SpecialtyId =>Specialty.SpecialtyId
> join vwPerson P on StatisticsConsultation.PersonId =P.PersonId where
>Clinic.RegionId = 345
>the result of the query is the number about 800.000.
>When the server is allocated only for that query it takes
4 seconds
>(parallelism used)
>When there is a background process which constantly uses
only 1 processor
>out of 8
>(other resources are not highly utilised) the time rises
up to 25 seconds.
>1 .When I specify MAXDOP 1 option the query takes 7
seconds with the
>backgroud process.
>Can anyone explain me why this happens? Is it parallelism
bag?
>2. The above query when revriten
>SELECT COUNT(*) FROM Clinic
> join StatisticsConsultation on
StatisticsConsultation.ClinicId =>Clinic.ClinicId where Clinic.RegionId = 345
>takes only 1 sec in comparison to 4 seconds. Why SQL
Server doesn't optimize
>it correctly?
>Thank you in advance,
>Regards,
>Tomasz
>
>.
>

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.