I work in MI Team. System we work on is SQL Server 2000 and has 3 databases set up for reporting purposes.
The first one, Impact, is a replication of the company Progress database. This is easily the largest of the 3 and is about 130000 mb.
The other 2, DB_MIS and BO_MIS, are DBs set up with data pulled from Impact and are about 10200mb and 7000mb.
These 2 are updated on a daily basis via jobs running stored procedures and seem to be taking longer to finish each day to the point where we are struggling to get the data we need to send out reports to Directors/Management etc before lunch-time.
The data files and the transaction logs for the 3 DBs are all on the same disk. With live systems I know that it's a golden rule that the two should be on separate disks as otherwise the Read/writer is constantly being interrupted every time a front end user inserts/updates/deletes data.
Does this also hold true for an MI system? Would putting the transaction logs for the 3 DBs on a separate disk drastically reduce the amount of time that the SQL jobs/Sprocs would take to run?
One thing that should be noted is that the performance benefit is only goven by having separate physical drives. Drawing a line down the middle of a drive, and calling one side C:\ and the other D:\ gives you no benefit for performance.
And seriously, disk drives are the one moving part of a computer. They fail. They fail spectacularly. And usually they fail at 2:00 AM.
The system has been set up this way for the last 3 years - long before I started 6 mths ago.
On first day I said to manager we should have data & transaction files on separate physical disks & would drastically improve performance but we don't look after the actual server - IT Team do.
New manager has just started in new position above existing manager & he's keen for us to push to get new disk so just wanted to make sure that it would drastically improve the system performance.
The system is used for MI reporting purposes. We have 3 databases on it, 2 of which are updated once a day (between midnight & 8am) and the main DB is updated every 2 hours, so we don't have constant updating by front end users - users run the reports on Business Objects from data on it.
When you say "updated", do you mean a total refresh of data or are you only updating based on inserts/updates/deletes from the source database? What is you transaction load? Eight hours, or nearly 12 if you can barely get reports out by lunch, seems extreme, even with your physical setup. Are the a lot of indexes on the tables you are affecting? As stated previously, are your data or log files auto-growing? How much memory do you have and how is it allocated (don't starve the OS)? What else is running on the server? There are so many variables that you need to explore.
And as MCrowley so eloquently put it - one disk is a disaster waiting to happen. Get more drives, as much as your company is willing to spend. Get them up and running and if you need more advice about what you should do at that point, post back.
And by all means, be sure you're have full, VALID, backups and appropriate log backups, if applicable. Good luck.
The system may have been set up poorly, but it is your responsibility, now. "It's not my fault" carries a lot less weight than "I told you so". The system has been running for 3 years. How much money would you be willing to bet it survives the next month? 2 months?
As for the performance increases, you will only see the performance increases for separating log from data during updates. And then only if you have a disk bottleneck. Run perfmon, and check to see if this disk is suffering disk queues of 1 or more during updates. Remember, to also measure the write queue, and the read queue, so you can tell wht the actual bottleneck is.
Ironically, by moving to a raid 1 system (mirrored drives), you would have 2 disks available for read activity, thus reducing any read latency. And I suspect that you have more read latency than write latency.