Latching Errors

Hi all,

We are trying to load a batch in a huge table ( 150 GB) , but sometimes we are getting timeout errors .

When i see in activity monitor, i can see some entries like PAGEIOLATCH_EX, PAGEIOLATCH_SH.

Can somebody suggets me the workaround for this problem.

Thanks for all your help.

Configuration:

Sql Server 2005 SP1, 4GB Memory, Windows 2003 standard Edition SP1

Could you post an example of the error you are getting?

Can you check what level of growth is specified for you data/log files please? eg 10% or 100MB....
|||

Hi Rich,

Growth for the data file = 2000 MB , Unrestricted Growth. and log file = 10% Unrestricted growth.

And about error, when i see in the activity monitor , i can see PAGEIOLATCH_EX, PAGEIOLATCH_SH, in the wait type column. ( its taking a long time, for rolling back the batch)

Is there anything about indexes , which affects this kind of error.

Thanks.

|||

Are you getting errors in the SQL Error Log? If so, could you copy and paste one of these errors into the thread as it may help diagnose the problem?

|||

In general, Significant wait times on page latches is often related to disk issues. You'd generally want to watch your disk counters in Perf Mon.

But what about your log during this process - is it large enough to handle the load? What is 10% growth - meaning, what size is it trying to grow to if this is the problem. I generally don't have grow logs by a percent - you have no control over what size increments it's growing. If it's a large file you are trying to load, you would also want to try batching the inserts.

-Sue

Latching Errors

Hi all,

We are trying to load a batch in a huge table ( 150 GB) , but sometimes we are getting timeout errors .

When i see in activity monitor, i can see some entries like PAGEIOLATCH_EX, PAGEIOLATCH_SH.

Can somebody suggets me the workaround for this problem.

Thanks for all your help.

Configuration:

Sql Server 2005 SP1, 4GB Memory, Windows 2003 standard Edition SP1

Could you post an example of the error you are getting?

Can you check what level of growth is specified for you data/log files please? eg 10% or 100MB....
|||

Hi Rich,

Growth for the data file = 2000 MB , Unrestricted Growth. and log file = 10% Unrestricted growth.

And about error, when i see in the activity monitor , i can see PAGEIOLATCH_EX, PAGEIOLATCH_SH, in the wait type column. ( its taking a long time, for rolling back the batch)

Is there anything about indexes , which affects this kind of error.

Thanks.

|||

Are you getting errors in the SQL Error Log? If so, could you copy and paste one of these errors into the thread as it may help diagnose the problem?

|||

In general, Significant wait times on page latches is often related to disk issues. You'd generally want to watch your disk counters in Perf Mon.

But what about your log during this process - is it large enough to handle the load? What is 10% growth - meaning, what size is it trying to grow to if this is the problem. I generally don't have grow logs by a percent - you have no control over what size increments it's growing. If it's a large file you are trying to load, you would also want to try batching the inserts.

-Sue

Large table query 99% results streamed quickly, huge pause for las

My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is that
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either.
It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.
|||Thanks for the reply, I agree ... I think it is doing a full table scan.
I am determining the number of records being returned by looking at the
bottom of the management studio where it tells you how many records are
returned, also when it has a record id column, which I see increments for
each new row of data is returned. Yes I am scrolling through the data.
Display the results as text? Not sure what you mean there sorry.
I am not reading the records. Just wanting to see how long it takes to
retrieve different sets of data from this table.
"Roger Wolter[MSFT]" wrote:

> It's difficult to speculate without knowing what your query and data look
> like. A likely possibility is you're returning 29000 records out of a few
> million and the records you selected are toward the front of the table scan
> order. When you retrieve that many records the query processor likely will
> be doing a table scan and unless there's something in the query to indicate
> when it's done, every row in the table will be read. Looking at the query
> plan will help you understand what the query processor is doing.
> How are you determining how many records have been returned? Are you
> actually scrolling through the data so you know the records have been
> returned and rendered in the UI? Do you get different results if you
> display the results as text?
> I have to ask - do you really read 29000 records in Management Studio? Even
> if you read a record a second that's almost an hour of reading. If you're
> pulling back that many records to page through a look at a few records, you
> using a lot of processing power, memory, and bandwidth on the server for no
> reason.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
>
|||If you're not actually looking at the results as they come in, you may be
seeing a UI painting delay. The results may all be retrieved and in memory
but not rendered in the grid yet. If you look at the Management Studio
options and change the result display option to text or a file instead of
grid, the results will appear significantly faster. Bottom line is that
with that many results, creating and formatting the grid cells generally
takes more time than retrieving the results.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:89E01783-910F-4549-94BB-1567701DB175@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I agree ... I think it is doing a full table scan.
> I am determining the number of records being returned by looking at the
> bottom of the management studio where it tells you how many records are
> returned, also when it has a record id column, which I see increments for
> each new row of data is returned. Yes I am scrolling through the data.
> Display the results as text? Not sure what you mean there sorry.
> I am not reading the records. Just wanting to see how long it takes to
> retrieve different sets of data from this table.
> "Roger Wolter[MSFT]" wrote:

Large table query 99% results streamed quickly, huge pause for las

My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is that
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.|||Thanks for the reply, I agree ... I think it is doing a full table scan.
I am determining the number of records being returned by looking at the
bottom of the management studio where it tells you how many records are
returned, also when it has a record id column, which I see increments for
each new row of data is returned. Yes I am scrolling through the data.
Display the results as text? Not sure what you mean there sorry.
I am not reading the records. Just wanting to see how long it takes to
retrieve different sets of data from this table.
"Roger Wolter[MSFT]" wrote:
> It's difficult to speculate without knowing what your query and data look
> like. A likely possibility is you're returning 29000 records out of a few
> million and the records you selected are toward the front of the table scan
> order. When you retrieve that many records the query processor likely will
> be doing a table scan and unless there's something in the query to indicate
> when it's done, every row in the table will be read. Looking at the query
> plan will help you understand what the query processor is doing.
> How are you determining how many records have been returned? Are you
> actually scrolling through the data so you know the records have been
> returned and rendered in the UI? Do you get different results if you
> display the results as text?
> I have to ask - do you really read 29000 records in Management Studio? Even
> if you read a record a second that's almost an hour of reading. If you're
> pulling back that many records to page through a look at a few records, you
> using a lot of processing power, memory, and bandwidth on the server for no
> reason.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> > My Query looks like this.
> > select * from table
> > where field = 'abc'
> >
> > In SQL Server 2005 I see that there is some new data retrieval mechanism
> > in
> > the query analyser tool so that results are streamed in. What I mean is
> > that
> > if I do a query on my large table I get first 1000 rows in about 1
> > seconds,
> > and next 1000 in the next 1s and this continues right up to about 2 mins ,
> > where I have about 29000 rows sitting in query analyser, but it hasn't
> > finished yet, it sits there churning for another 10 mins before it
> > finishes
> > and the total row count is 29052 records.
> >
> > I don't understand why 99% of results are returned quickly (15% of the
> > time)
> > and then the last 1% of the results are returned in a huge amount of time
> > (85%)
> >
> > In SQL 2000 doing this query has query analyser sitting there bored for
> > quite a while.
> >
> > I have changed the query to return a smaller set of data like 15000
> > records
> > and the same thing happens where it returns 99% of records quite quickly
> > but
> > the last 1% of data still takes about 10 mins to return.
> >
> > I have tried creating a stored procedure to do this thinking it might work
> > better but no, and have tried doing a top 100 PERCENT with no success
> > either.
>|||If you're not actually looking at the results as they come in, you may be
seeing a UI painting delay. The results may all be retrieved and in memory
but not rendered in the grid yet. If you look at the Management Studio
options and change the result display option to text or a file instead of
grid, the results will appear significantly faster. Bottom line is that
with that many results, creating and formatting the grid cells generally
takes more time than retrieving the results.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:89E01783-910F-4549-94BB-1567701DB175@.microsoft.com...
> Thanks for the reply, I agree ... I think it is doing a full table scan.
> I am determining the number of records being returned by looking at the
> bottom of the management studio where it tells you how many records are
> returned, also when it has a record id column, which I see increments for
> each new row of data is returned. Yes I am scrolling through the data.
> Display the results as text? Not sure what you mean there sorry.
> I am not reading the records. Just wanting to see how long it takes to
> retrieve different sets of data from this table.
> "Roger Wolter[MSFT]" wrote:
>> It's difficult to speculate without knowing what your query and data look
>> like. A likely possibility is you're returning 29000 records out of a
>> few
>> million and the records you selected are toward the front of the table
>> scan
>> order. When you retrieve that many records the query processor likely
>> will
>> be doing a table scan and unless there's something in the query to
>> indicate
>> when it's done, every row in the table will be read. Looking at the
>> query
>> plan will help you understand what the query processor is doing.
>> How are you determining how many records have been returned? Are you
>> actually scrolling through the data so you know the records have been
>> returned and rendered in the UI? Do you get different results if you
>> display the results as text?
>> I have to ask - do you really read 29000 records in Management Studio?
>> Even
>> if you read a record a second that's almost an hour of reading. If
>> you're
>> pulling back that many records to page through a look at a few records,
>> you
>> using a lot of processing power, memory, and bandwidth on the server for
>> no
>> reason.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
>> > My Query looks like this.
>> > select * from table
>> > where field = 'abc'
>> >
>> > In SQL Server 2005 I see that there is some new data retrieval
>> > mechanism
>> > in
>> > the query analyser tool so that results are streamed in. What I mean
>> > is
>> > that
>> > if I do a query on my large table I get first 1000 rows in about 1
>> > seconds,
>> > and next 1000 in the next 1s and this continues right up to about 2
>> > mins ,
>> > where I have about 29000 rows sitting in query analyser, but it hasn't
>> > finished yet, it sits there churning for another 10 mins before it
>> > finishes
>> > and the total row count is 29052 records.
>> >
>> > I don't understand why 99% of results are returned quickly (15% of the
>> > time)
>> > and then the last 1% of the results are returned in a huge amount of
>> > time
>> > (85%)
>> >
>> > In SQL 2000 doing this query has query analyser sitting there bored for
>> > quite a while.
>> >
>> > I have changed the query to return a smaller set of data like 15000
>> > records
>> > and the same thing happens where it returns 99% of records quite
>> > quickly
>> > but
>> > the last 1% of data still takes about 10 mins to return.
>> >
>> > I have tried creating a stored procedure to do this thinking it might
>> > work
>> > better but no, and have tried doing a top 100 PERCENT with no success
>> > either.

Large table query 99% results streamed quickly, huge pause for las

My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is tha
t
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either
.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.