We currently have a SQL Server 2008R2 being accessed by a legacy ASP application.
Among different processes, one of them essentially SELECTs data from one database and INSERTs (or UPDATEs) them into another database, based on a set of business rules. This process generally runs at night after business hours, but on occasions it must be run during business hours to avoid task backlog.
We are now facing an I/O contention issue and so far my best guess is that this process is hurting the server.
Facts that support this hypothesis:
- When the process is NOT running concurrently with ordinary operation, the server runs smoothly;
- We generally experience I/O contention early in the morning; severely increased number of Page Reads/sec (avg 2000 reads/s), decreased Page Life Expectancy, some increase in LazyWrites. As far as my knowledge goes, this is generally a sign of SQL Server self-tuning to a different workload. This behaviour usually goes away after 20-something minutes.
I am now considering adding two other partitions (one for datafiles, one for logfiles) and move the database which receives the INSERTs to them, effectively distributing the I/O load.
Is this procedure worthwhile? Are there any other variables to look into before venturing into disk rearrangement? While not 100%, indices are fairly well in place, for instance.
Thanks in advance!
Having reviewed some monitoring graphs, I noticed this contention on some other occasions other than early in the morning. While I don't profile the server continuously, it's safe to say that the process that copies between database was NOT running during most of these occasions, which means there might be other variables behind the contention.
Again, this only adds up to the question on which other variables I should have a look before playing with disks.