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...
>
>
Showing posts with label operations. Show all posts
Showing posts with label operations. Show all posts
Wednesday, March 21, 2012
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...
>
>
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
>
>
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
>
>
Parallel Index Operations
Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
Johnny
We have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>
|||There is no errors in the system & application event log around the time of running index rebuild.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
Johnny
We have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>
|||There is no errors in the system & application event log around the time of running index rebuild.
Parallel Index Operations
Got the following error messages when rebuilding indexes on SQL Server 2000
+ SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has alread
y service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of
running index rebuild.
+ SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has alread
y service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of
running index rebuild.
Parallel Index Operations
Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of running index rebuild.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of running index rebuild.
Wednesday, March 7, 2012
PAGEIOLATCH_SH wait time is 80%
Hi,
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.
Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.
Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
PAGEIOLATCH_SH wait time is 80%
Hi,
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
PAGEIOLATCH_SH wait time is 80%
Hi,
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
--
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
One of my sql job contain 80% read & 20% insert /updates.
This job also contains number of cursor operations. It use to run for 2Hrs
or less and now it runs for 8hrs.
When I check the process wait time. The PAGEIOLATCH_SH is 80% and
PAGEIOLATCH_EX is 10%.
I am not sure what is causing this high PAGEIOLATCH_SH wait time?
Any thoughts on what could be the issue here?
Regards,
Sarav.Maybe http://sqldev.net/articles/WaitTypes.htm can help you.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||I'm going to venture a guess here, but I'd bet that your use of cursors is
at the root of your problem. Resource utilization by cursors has a way of
growing in non-linear ways. Some of the gurus here might be able to offer
set based solutions if you can succinctly describe the process.
Bob Castleman
SuccessWare Software
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||Hi Sarav
The process wait time % split seems normal to me given your estimate of 80%
read / 20% insert / updates. The PAGEIOLATCH_SH represents the shared locks
taken under the 80% read operations and thePAGEIOLATCH_EX represents the
exclusive locks required to perform the insert / updates.
Perhaps the post re cursors has some merit. I'd also be looking at general
resource availability (memory / cache stats / lock blocks) and whether the
sql job is being blocked by other processes.
HTH
Regards,
Greg Linwood
SQL Server MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>|||That means you have a heck of a lot of disk access going on. I would ensure
the operations are well tuned first.
--
Andrew J. Kelly SQL MVP
"sarav" <orumurai@.yahoo.cmom> wrote in message
news:ugiqcPlAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Hi,
>
> One of my sql job contain 80% read & 20% insert /updates.
> This job also contains number of cursor operations. It use to run for 2Hrs
> or less and now it runs for 8hrs.
>
> When I check the process wait time. The PAGEIOLATCH_SH is 80% and
> PAGEIOLATCH_EX is 10%.
>
> I am not sure what is causing this high PAGEIOLATCH_SH wait time?
>
> Any thoughts on what could be the issue here?
>
> Regards,
> Sarav.
>
Subscribe to:
Comments (Atom)