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
>
>.
>