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

Parallel operations in SQL 2000 Standard

Hi,
I have an IIS 6.0 application on one server using database SQL Derver 2000
Standard Edition on a 2 processor machine. In server properties it is set to
use all avaliable processors for parallelism.
As I understand differences between Standard and Enterpise editions,
Standard one can only execute 2 different queries in the samem time on 2
processors. Enterprise can also divide one long lasting queries on 2
processors if it has sens of course.
I have tested it and noticed that my SQL Server Standard still uses one
processor. Can it be caused by a fact that these two concurent queries are
executed by the same one application (IIS)?
Here is my test scenario:
1. I have a .aspx page which shows results of a query (it takes about 7 s)
2. I open this page from 2 different IE windows same time (almost same, but
the delay is about 1-2s, it is the time I need to click)
3. When I look at processor history in task manager on sql server, it never
goes more then 50 % (when I have 1 graph for all of 2 processors)
Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
caused by 1 client application (IIS).
PrzemoSQL Server 2000 SE supports up to 4 processors. Do you observe the same
behavior when you execute the same 2 queries concurrently from Query
Analyzer?
Hope this helps.
Dan Guzman
SQL Server MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Try disconnecting one of the connections and reconnect or adda new
connection and then try it again. Connections are bound to a UMS which is
basically tied to a processor. The connections get assigned in a round
robin fashion. If you have 3 connections it could have gone like this.
Connection 1 - Attached to UMS 1
Connection 2 - Attached to UMS 2
Connection 3 - Attached to UMS 1
If you run 2 queries, one on Connection 1 and the other on Connection 3 they
may share the same processor. If the optimizer chose to not use parallelism
for your query the third connection could simply be waiting on the first.
--
Andrew J. Kelly SQL MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Thank you.
This is the reason. I have tested and it works ok. Simple I have some
coincidence.
Przemo
"Andrew J. Kelly" wrote:

> Try disconnecting one of the connections and reconnect or adda new
> connection and then try it again. Connections are bound to a UMS which is
> basically tied to a processor. The connections get assigned in a round
> robin fashion. If you have 3 connections it could have gone like this.
> Connection 1 - Attached to UMS 1
> Connection 2 - Attached to UMS 2
> Connection 3 - Attached to UMS 1
> If you run 2 queries, one on Connection 1 and the other on Connection 3 th
ey
> may share the same processor. If the optimizer chose to not use paralleli
sm
> for your query the third connection could simply be waiting on the first.
> --
> Andrew J. Kelly SQL MVP
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
>
>

Parallel operations in SQL 2000 Standard

Hi,
I have an IIS 6.0 application on one server using database SQL Derver 2000
Standard Edition on a 2 processor machine. In server properties it is set to
use all avaliable processors for parallelism.
As I understand differences between Standard and Enterpise editions,
Standard one can only execute 2 different queries in the samem time on 2
processors. Enterprise can also divide one long lasting queries on 2
processors if it has sens of course.
I have tested it and noticed that my SQL Server Standard still uses one
processor. Can it be caused by a fact that these two concurent queries are
executed by the same one application (IIS)?
Here is my test scenario:
1. I have a .aspx page which shows results of a query (it takes about 7 s)
2. I open this page from 2 different IE windows same time (almost same, but
the delay is about 1-2s, it is the time I need to click)
3. When I look at processor history in task manager on sql server, it never
goes more then 50 % (when I have 1 graph for all of 2 processors)
Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
caused by 1 client application (IIS).
Przemo
SQL Server 2000 SE supports up to 4 processors. Do you observe the same
behavior when you execute the same 2 queries concurrently from Query
Analyzer?
Hope this helps.
Dan Guzman
SQL Server MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo
|||Try disconnecting one of the connections and reconnect or adda new
connection and then try it again. Connections are bound to a UMS which is
basically tied to a processor. The connections get assigned in a round
robin fashion. If you have 3 connections it could have gone like this.
Connection 1 - Attached to UMS 1
Connection 2 - Attached to UMS 2
Connection 3 - Attached to UMS 1
If you run 2 queries, one on Connection 1 and the other on Connection 3 they
may share the same processor. If the optimizer chose to not use parallelism
for your query the third connection could simply be waiting on the first.
Andrew J. Kelly SQL MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo
|||Thank you.
This is the reason. I have tested and it works ok. Simple I have some
coincidence.
Przemo
"Andrew J. Kelly" wrote:

> Try disconnecting one of the connections and reconnect or adda new
> connection and then try it again. Connections are bound to a UMS which is
> basically tied to a processor. The connections get assigned in a round
> robin fashion. If you have 3 connections it could have gone like this.
> Connection 1 - Attached to UMS 1
> Connection 2 - Attached to UMS 2
> Connection 3 - Attached to UMS 1
> If you run 2 queries, one on Connection 1 and the other on Connection 3 they
> may share the same processor. If the optimizer chose to not use parallelism
> for your query the third connection could simply be waiting on the first.
> --
> Andrew J. Kelly SQL MVP
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
>
>

Parallel operations in SQL 2000 Standard

Hi,
I have an IIS 6.0 application on one server using database SQL Derver 2000
Standard Edition on a 2 processor machine. In server properties it is set to
use all avaliable processors for parallelism.
As I understand differences between Standard and Enterpise editions,
Standard one can only execute 2 different queries in the samem time on 2
processors. Enterprise can also divide one long lasting queries on 2
processors if it has sens of course.
I have tested it and noticed that my SQL Server Standard still uses one
processor. Can it be caused by a fact that these two concurent queries are
executed by the same one application (IIS)?
Here is my test scenario:
1. I have a .aspx page which shows results of a query (it takes about 7 s)
2. I open this page from 2 different IE windows same time (almost same, but
the delay is about 1-2s, it is the time I need to click)
3. When I look at processor history in task manager on sql server, it never
goes more then 50 % (when I have 1 graph for all of 2 processors)
Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
caused by 1 client application (IIS).
PrzemoSQL Server 2000 SE supports up to 4 processors. Do you observe the same
behavior when you execute the same 2 queries concurrently from Query
Analyzer?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Try disconnecting one of the connections and reconnect or adda new
connection and then try it again. Connections are bound to a UMS which is
basically tied to a processor. The connections get assigned in a round
robin fashion. If you have 3 connections it could have gone like this.
Connection 1 - Attached to UMS 1
Connection 2 - Attached to UMS 2
Connection 3 - Attached to UMS 1
If you run 2 queries, one on Connection 1 and the other on Connection 3 they
may share the same processor. If the optimizer chose to not use parallelism
for your query the third connection could simply be waiting on the first.
--
Andrew J. Kelly SQL MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> Hi,
> I have an IIS 6.0 application on one server using database SQL Derver 2000
> Standard Edition on a 2 processor machine. In server properties it is set
> to
> use all avaliable processors for parallelism.
> As I understand differences between Standard and Enterpise editions,
> Standard one can only execute 2 different queries in the samem time on 2
> processors. Enterprise can also divide one long lasting queries on 2
> processors if it has sens of course.
> I have tested it and noticed that my SQL Server Standard still uses one
> processor. Can it be caused by a fact that these two concurent queries are
> executed by the same one application (IIS)?
> Here is my test scenario:
> 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> 2. I open this page from 2 different IE windows same time (almost same,
> but
> the delay is about 1-2s, it is the time I need to click)
> 3. When I look at processor history in task manager on sql server, it
> never
> goes more then 50 % (when I have 1 graph for all of 2 processors)
> Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> caused by 1 client application (IIS).
> Przemo|||Thank you.
This is the reason. I have tested and it works ok. Simple I have some
coincidence.
Przemo
"Andrew J. Kelly" wrote:
> Try disconnecting one of the connections and reconnect or adda new
> connection and then try it again. Connections are bound to a UMS which is
> basically tied to a processor. The connections get assigned in a round
> robin fashion. If you have 3 connections it could have gone like this.
> Connection 1 - Attached to UMS 1
> Connection 2 - Attached to UMS 2
> Connection 3 - Attached to UMS 1
> If you run 2 queries, one on Connection 1 and the other on Connection 3 they
> may share the same processor. If the optimizer chose to not use parallelism
> for your query the third connection could simply be waiting on the first.
> --
> Andrew J. Kelly SQL MVP
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:4498ECD3-D1F6-4687-AFDD-D538A6797AD4@.microsoft.com...
> > Hi,
> >
> > I have an IIS 6.0 application on one server using database SQL Derver 2000
> > Standard Edition on a 2 processor machine. In server properties it is set
> > to
> > use all avaliable processors for parallelism.
> > As I understand differences between Standard and Enterpise editions,
> > Standard one can only execute 2 different queries in the samem time on 2
> > processors. Enterprise can also divide one long lasting queries on 2
> > processors if it has sens of course.
> > I have tested it and noticed that my SQL Server Standard still uses one
> > processor. Can it be caused by a fact that these two concurent queries are
> > executed by the same one application (IIS)?
> > Here is my test scenario:
> > 1. I have a .aspx page which shows results of a query (it takes about 7 s)
> > 2. I open this page from 2 different IE windows same time (almost same,
> > but
> > the delay is about 1-2s, it is the time I need to click)
> > 3. When I look at processor history in task manager on sql server, it
> > never
> > goes more then 50 % (when I have 1 graph for all of 2 processors)
> >
> > Am I wrong in my opinion about possibilitie of SQL Serer Standard or it is
> > caused by 1 client application (IIS).
> >
> > Przemo
>
>