2

We have a HP MSA70 with 20x300gb 2,5" 10k disks allocated for our SQL2008 server.

Our usage of the SQL server is:
50 concurrent users of our ERP system(Visma business) - 70/30 read/write.
2500 users pr. day on our website (CMS system + eshop) - (projected 5000 user by the end of the year)
A BI system that extracts data 8 times a day with extremely heavy queries (including production ours) **the BI system doesn't do any writes to these disks - only reads.

Atm. the array is setup as an RAID10, and thats it - all data+log+tempdb is on this array. We periodically experience that our ERP system becomes very slow, and we found out that it's mainly when our BI system is getting data (which last's approx. 30 mins everytime.)

I've been researching for quite some time now, but im still in doubt of how we should provision our spindles. My idea is:

8 disks raid10 - OLTP db's, mainly our ERP system (which consists of 10 db's) + tempdb
8 disks raid10 - Log files for all databases
4 disks raid5 - OLAP db's, transactional replicated copies of our ERP db's where our BI system get's it's data from. ++ our website.

My biggest concern about the above is, if i should take 2 disks from the log array, and add them to the "OLTP", so it get's 10.

I know that's not exactly enough to go on to come up with an exact answer, but i am looking for some guidelines and good ideas :-)

MKO
  • 21
  • 2

2 Answers2

1

You're running into serious disk I/O contention issues when your BI process is doing all of those extracts. Considering how it works (query data in prod to move to warehouse) there isn't a whole lot you can do storage-wise with your config to make things better. You simply need more spindles. Doing some PerfMon runs to see if your problems are due to the BI database committing data (writes) or all of those queries consuming I/O ops. If the later, put more spindles into that database where possible to help smooth things along.

You may want to investigate seriously increasing the amount of RAM you have in your database server, as that will help reduce some of the disk-pressure you're seeing.

sysadmin1138
  • 133,124
  • 18
  • 176
  • 300
  • Thanks for the quick response! - actually our BI is not writing to these disks - it only reads (it has it's own scsi disk array for that). But don't you agree that it would be a good idea to split the BI I/O out on it's own array? – MKO Jan 24 '11 at 20:40
  • @MKO Thing is, all of that query traffic is affecting the whole ERP database. That's enough traffic to saturate your storage. RAM will reduce how often it needs to hit disk in the first place. Adding spindles to the OLTP array will improve things, but RAM will be better at improving performance. – sysadmin1138 Jan 24 '11 at 20:47
  • So you're saying that even if i trans. replicate my ERP data to a separate array, which the BI will read from - it will still affect the other arrays? Would you also then recommend i continue with my current setup, with only 1 array for all data/log/tempdb ? – MKO Jan 25 '11 at 08:15
  • @MKO I'm sorry, I misread your question. Actually separating the databases to different spindles will separate I/O. It'll make the BI runs take longer (possibly a lot longer) but at least production won't be directly affected by the reads. I'm not 100% sure of the role of TempDB in that process, though. – sysadmin1138 Jan 25 '11 at 14:56
1

What happens to the page life expectancy when the ETL process kicks in? As you add more RAM this number will go up, making your life better over all.

Odds are you don't need to be making changes to the storage system.

What does the sys.dm_io_virtual_file_stats show?

When you are seeing the performance problems what are the wait types?

When the ETL is running, are the indexes properly tuned?

mrdenny
  • 27,174
  • 4
  • 41
  • 69