MS SQL I/O Tuning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MS SQL I/O Tuning

Author

Topic

sdunn96
Starting Member

5 Posts

Posted - 2008-05-06 : 14:51:28

Hi all,
(Hope this is in the right spot)

Got a server
HP DL360 G5, 2 Dual Core Xeons 2.66GHz CPUs, 4 GB memory.
Disk Subsystem:
2x33GB, 15K rpm, SAS mirrored drives
3x68GB, 15K rpm, SAS RAID 5 drives

RAID Controller:
P400i 512MB memory
Read Cache set to 75%, Write Cache set to 25%

My question is this...
We run a query that handles a lot of data between 3 tables.....and I not sure of the amoutn of columns and rows, but it is a lot.
The query will run for over an hour...then stop.
DB guy goes in, changes a couple things (so processed records are not ran again) and then re-run....will run for about 30min and finish up fine.

I was watching it in PerfMon on the recent run and the Current Disk Queue Length is maxed out.
All the hammering is done on Reads, I view him he is maxed out....Writes are doing nothing during this time.

I can look at the controller through HP's controller tool and it shows the same thing.
Reads/sec on the RAID 5 array are at 2570/sec
Writes/sec on the RAID 5 array are at 2/sec (at most)

For SQL Server settings I have:
Dynamically configure SQL Server memory
Min: 2048
Max: 3326
Reserver physical memory for SQL Server is checked.
Min. query memory (KB): 10240 (so 10MB)

Processor tab:
all processors are available for use.
Max worker threads: 255
Boost SQL Server Priority is checked
Use Windows NT fibers is checked

Is there anything else I can do/should do/correct to help the performance bottle neck I am having?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 14:53:38

Have you considered partitioning the table(s)? How many rows are in the 3 tables?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-06 : 15:02:16

Are your indexes in 3 tables optimized?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-05-06 : 15:08:04

dont uses windows nt fibers
don't boost sql priority

i would look at trying to tune your query. look at the execution plan and try to eliminate things like full table scans and bookmark lookups.

-ec

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-06 : 15:15:42

Is this server dedicated for SQL server or other apps are runnning as well? Allow SQL server to dynamically allow memory. Look at your indexes.

sdunn96
Starting Member

5 Posts

Posted - 2008-05-06 : 15:26:26

Wow, thanks for the quick replies...I will try to answer the questions you asked best I can....I am an Network Admin, so knowledge of SQL is limited, I am trying to learn more, but don't know as much.

@tkizer
How is partioning done on tables? Have they done this, I don't know....what would it accomplish
I don't know the # of rows.....I just know one of the developers said it is a LOT.

@sodeep
optimized indexes.....that I am not sure...I would have to talk w/ the DB guy (he is out for the week)

@eyechart
Ok, I will uncheck those two options.
But from what I read, I thought they would be good to have turned on.....I must have misunderstood their purpose.
What do they do?

Re-tuning the query I think will be the big thing...the code hasn't really been touched for over 4yrs from what I understand.

@sodeep
Yes, all he does is SQL...nothing else.
I have him, the other SQL server and our 2 web servers on a GB ether-backchannel....so data x-fers are off the 100MB/production network.

So other than what I have done....hardware wise, I can't do much else for this thing?
It will come down to cleaing up the tables and re-tuning the query it seems.?

sdunn96
Starting Member

5 Posts

Posted - 2008-05-06 : 15:28:25

also, this query is done from a Cold Fusion web page....

Would a SP help out here?
I read that those are more efficient...but I don't know how much they would help in this situation.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 15:28:52

Partitioning would definitely help with performance if you have large enough tables. But that's not something that a network admin should touch.

It would be best to wait for the DBA to get back and have him/her help answer our questions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 15:29:38

quote:
Originally posted by sdunn96

also, this query is done from a Cold Fusion web page....

Would a SP help out here?
I read that those are more efficient...but I don't know how much they would help in this situation.

It depends on what it is doing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-06 : 15:30:59

Looks like you need to handover these tasks to experienced DBA. Is there only 1 DBA in your workplace?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-06 : 15:32:59

[quote]Originally posted by tkizer

Partitioning would definitely help with performance if you have large enough tables. But that's not something that a network admin should touch.

Are you talking about Partition view? Table partition is not supported in SQL 2000.

sdunn96
Starting Member

5 Posts

Posted - 2008-05-06 : 15:38:07

Yeah the one guy that does most of the DB setup and programming is out for the week....

I was just watching this one process because:
1) it was that time of the month to run it.
2) They have been having this issue for a bit now
3) I would have figured that with the hardware this thing was sitting on....it would not be hanging like this.

I can't implement any changes :(
I was just wanting to get some ideas of where to look on things that I can tune...or make a bit more efficient.
So when I do discuss w/ my boss and the DB guy we can get moving in a good direction.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 15:43:44

quote:
Originally posted by sodeep

[quote]Originally posted by tkizer

Partitioning would definitely help with performance if you have large enough tables. But that's not something that a network admin should touch.

Are you talking about Partition view? Table partition is not supported in SQL 2000.

Of course I'm talking about partitioned views.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sdunn96
Starting Member

5 Posts

Posted - 2008-05-06 : 16:03:44

Here is a piece of the Cold Fusion page....I think this is the big query...most of the rest of the stuff is calculations and updates to the tables.

quote:

<cfquery datasource="D2Transfer" name="SettleRecord">
SELECT tblFeeAdmin.*, tblApplication.numSettlementDelay, tblApplication.dolACHReserveAmount, tblApplication.numBatchingSystemsUsed
FROM tblFeeAdmin INNER JOIN tblApplication ON tblFeeAdmin.numAppID = tblApplication.dwAppID
WHERE tblFeeAdmin.fSettled = 0
AND tblFeeAdmin.numAppid NOT IN (0)
AND
(
(tblApplication.numBatchingSystemsUsed IN (2,3)
AND
EXISTS (SELECT dwFileLogID
FROM tblFileLog INNER JOIN tblBatch ON dwFileLogID = tblBatch.numFileLogID
WHERE numFileType = 1
AND tblBatch.numAppID = tblFeeAdmin.numAppID
AND datprocessingdate >= tblfeeadmin.datStartInvoicePeriod
AND datProcessingDate < '#dateformat(now(),'mm/dd/yyyy')#'
AND DateAdd(dd,tblApplication.numSettlementDelay -2, tblfilelog.datprocessingdate) < '#dateformat(now(),'mm/dd/yyyy')#')
)

OR

(tblApplication.numBatchingSystemsUsed=1
AND
EXISTS (SELECT dwFileLogID
FROM tblFileLog INNER JOIN tblBatch ON dwFileLogID = tblBatch.numFileLogID
WHERE numFileType = 11
AND tblBatch.numAppID = tblFeeAdmin.numAppID
AND datprocessingdate >= tblfeeadmin.datStartInvoicePeriod
AND datProcessingDate < '#dateformat(now(),'mm/dd/yyyy')#'
AND DateAdd(dd,tblApplication.numSettlementDelay -2, tblfilelog.datprocessingdate) < '#dateformat(now(),'mm/dd/yyyy')#'))
);
</cfquery>

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-06 : 21:43:24

One way to address high disk i/o issue is separating db files (data files, log file, and tempdb files) on their own disk array.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources