October 12, 2004 at 12:50 am
Right,
I have a job running on a (test)sql server 7, which waits tremendously long as a result of pageiolatch_ex waittypes. I've googled the internet (nothing to be found in BOL..) and I'm fairly at home what this means and what may cause this.
Quote:'a significant waittime for these waittypes suggests disk I/O subsystem issues'
Fine, good to know. What I don't know is how to solve this problem. I've tried a complete reindex and optimize process on the database, alas, there is insufficient diskspace to do a complete dbcc checkdb action; there is 12 Gb free space, which is not enough. I wanted to bring the fillfactor down from 100% to 50 or 70.
The process is taking 24 hours minimum, which will increase in the near future. Upgrading to SQL Server 2000 is not an option because of companypolicy. Internal memory is 2048 Mb, OS is Windows NT, 4(!) processors, albeit 2 or 3 years old. There are no other processes running. Now I wonder, how could I remedy this behaviour? Obviously I wish to increase diskspace, but would a reindexing help? Is there any opther way to get rid of this behaviour?
Greetz,
Hans Brouwer
October 12, 2004 at 10:16 am
The issue with having slow IO and causing the page latches is something you'd fix with hardware changes. You are accumulating latches (lightweight locks) because it is taking too long to perform the IO. While reindexing and moving things around might help, it won't solve the main issues. You've also got a slower OS that doesn't handle the IO as efficiently as W2K or W2K3 would.
In short, there isn't much you can do unless someone wants to spend money. You might try reworking the job as well. What does it do that takes 24 hours?
October 13, 2004 at 9:02 am
Tnx for responding, Steve.
What happens is basically a monthly process on 7.000.000 records, which are updated on 60 busynessrules. I've used Perfmon and Profiler to check all the possible causes for pageiolatch waittypes, and none of them is a problem: memoryusage is ok, disk-io is ok, no waitingques to speak of, server and sqlserver activities are low, no indexingproblems show, etc. Hardware is about 2 years old, 4 processors, 2Gb RAM.
The datamodel is neat, the technical implementation is pretty good, use of indices is good, showplan has been used to check on statements.
Currently I am working on a test on W2K and SQL2K. I'm curious if the same problems occur.
If there is anything I've missed, please let me know.
Greetz,
Hans Brouwer
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply