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