High disk I/O with long running queries surprising?

> Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
Unless all data can fit in the memory allocated to SQL Server, I would
expect significant disk activity during the process. Even then, modified
still data needs to be written to disk.
Hope this helps.
Dan Guzman
SQL Server MVP
<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
><sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
>
Assuming that SQL Server is using a buffer cache of significantly less than
50GB, and these queries need to read a large percentage of the 50GB of data,
then SQL Server will need to read the data from disk.
So no, I would not be surprised.
David|||Hello,
I am interested in opinions about a case that I recently ran into. We
have a server with SQL Server 2000 Standard where a 50 GB database is
run. A stored procedure is run every night that performs data updates
and perform several aggregations and calculations of the data for about
4 hours in this database. Based on this little information, would you
be surprised to find out that during the execution of this stored
procedure, SQL Server uses the disk to 100 % (or at least want to)?
/Stefan|||> Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
Unless all data can fit in the memory allocated to SQL Server, I would
expect significant disk activity during the process. Even then, modified
still data needs to be written to disk.
Hope this helps.
Dan Guzman
SQL Server MVP
<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||<sl@.invativa.se> wrote in message
news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
>
Assuming that SQL Server is using a buffer cache of significantly less than
50GB, and these queries need to read a large percentage of the 50GB of data,
then SQL Server will need to read the data from disk.
So no, I would not be surprised.
David|||In addition to what the others have said, if this is based on the readings
from the % Disk Time counter, you may want to check a few more disk counters
to be sure. This counter can be funky. It's more reliable to check I/O
latency counters such as Avg Disk Sec/Read and Avg Disk Sec/Write.
Linchi
"sl@.invativa.se" wrote:

> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||And, of course, WRITE the updates back to disk.
No, I wouldn't be at all surprised.
The next question 'should be', based upon the following DDL and stored
procedure code, is there a more efficient way to accomplish the task?
(include table DDL and stored procedure code.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eacg56dmGHA.660@.TK2MSFTNGP05.phx.gbl...
> <sl@.invativa.se> wrote in message
> news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Assuming that SQL Server is using a buffer cache of significantly less
> than 50GB, and these queries need to read a large percentage of the 50GB
> of data, then SQL Server will need to read the data from disk.
> So no, I would not be surprised.
> David|||In addition to what the others have said, if this is based on the readings
from the % Disk Time counter, you may want to check a few more disk counters
to be sure. This counter can be funky. It's more reliable to check I/O
latency counters such as Avg Disk Sec/Read and Avg Disk Sec/Write.
Linchi
"sl@.invativa.se" wrote:

> Hello,
> I am interested in opinions about a case that I recently ran into. We
> have a server with SQL Server 2000 Standard where a 50 GB database is
> run. A stored procedure is run every night that performs data updates
> and perform several aggregations and calculations of the data for about
> 4 hours in this database. Based on this little information, would you
> be surprised to find out that during the execution of this stored
> procedure, SQL Server uses the disk to 100 % (or at least want to)?
> /Stefan
>|||And, of course, WRITE the updates back to disk.
No, I wouldn't be at all surprised.
The next question 'should be', based upon the following DDL and stored
procedure code, is there a more efficient way to accomplish the task?
(include table DDL and stored procedure code.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eacg56dmGHA.660@.TK2MSFTNGP05.phx.gbl...
> <sl@.invativa.se> wrote in message
> news:1151406632.094352.37510@.u72g2000cwu.googlegroups.com...
> Assuming that SQL Server is using a buffer cache of significantly less
> than 50GB, and these queries need to read a large percentage of the 50GB
> of data, then SQL Server will need to read the data from disk.
> So no, I would not be surprised.
> David|||Thanks for all answers.
Great that you all confirmed that lots of disk I/O is no surprise in
this scenario.
/Stefan