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

Hierarchy Problem

I need to create a stored procedure that creates a hierarchy for each employee in a large employee data table (CCINFORMATION).

Each entry in CCINFORMATION contains, among other things, the ID number of the employee, their title, their manager's ID, and their manager's title.
I need to use this table to build a string for each employee. That string should contain the ID of everyone in that employee's hierarchy up to the CEO, and then I need to place that string into a different table.

I don't know Transact-SQL really well, so I am at a little bit of a loss how to set up the logic to go through each record in CCINFORMATION, and for each one build the string as I run a series of queries based on the manager's ID until I reach the CEO. Any ideas?I managed to get this stored procedure to work, so I thought I would share, in case it helps anyone else:

Declare @.AWID varchar(50), @.Title varchar(2000), @.MgrAWID varchar(50), @.MgrTitle varchar(2000), @.hstring varchar(2000), @.hnew varchar(2000), @.SEARCHAWID varchar(50)

Declare cursor1 CURSOR FOR
Select AWID, Title, MgrAWID, MgrTitle
FROM CCINFORMATION

OPEN cursor1
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
Set @.hstring = @.AWID + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going in right away'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
END
ELSE
BEGIN
--PRINT @.hstring + ' begin subloop'
mgrloop:
Set @.SEARCHAWID = @.MgrAWID
Declare cursor2 CURSOR FOR
Select MgrAWID, MgrTitle
FROM CCINFORMATION
WHERE AWID = @.SEARCHAWID

OPEN cursor2

FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

WHILE @.@.FETCH_STATUS = 0
Begin
set @.hstring = @.hstring + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going into Hierarchy'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
CLOSE cursor2
Deallocate cursor2
set @.hstring=''
GOTO ceofound
END
ELSE
BEGIN
--PRINT @.hstring + ' isnt ceo'
CLOSE cursor2
Deallocate cursor2
GOTO mgrloop
END
END
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

CLOSE cursor2
Deallocate cursor2
--PRINT @.hstring + ' ends before ceo'
set @.hstring = @.hstring + ',fail'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
set @.hstring=''
END

ceofound:
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
END

CLOSE cursor1
DEALLOCATE cursor1

HIERARCHY

i have a problem.. which is the procedure to establish the hierarchies in my dimension.. the dimension wizard sends a message that the relationships were checked but did not create any hierarchies?

is there any propertie i should checke before running the wizard?

The cube and dimension wizards will attempt to create hierarchies based on relationships between tables and relationships found when sampling data. However, it may not discover many relationships, especially when the amount of data in the table is small. You can manually create hierarchies in the dimension editor by dragging attributes onto the "Hierarchies and Levels" pane.

HIERARCHY

i have a problem.. which is the procedure to establish the hierarchies in my dimension.. the dimension wizard sends a message that the relationships were checked but did not create any hierarchies?

is there any propertie i should checke before running the wizard?

The cube and dimension wizards will attempt to create hierarchies based on relationships between tables and relationships found when sampling data. However, it may not discover many relationships, especially when the amount of data in the table is small. You can manually create hierarchies in the dimension editor by dragging attributes onto the "Hierarchies and Levels" pane.