August 2, 2011 at 9:45 am
I'm managing a server that has about 140 mil records. The server has 8 cores, 16GB ram and I've optmised the Indexes as far as I can.
Yet, every week we can see performance goes down as production requirements increase.
It's clear that the limiting factor is the HDD I/O. What would be the next step to improve it. Currently I'm considering:
- A SAN (Would this really improve performance? Any recommendations re. brands / models?)
- Super fast SCSI disks in some fancy Raid configuration (suggestions welcome)
- SSD (Maybe the best solution)
- Clustering? (Though HDD is still an issue)
Any suggestions would be greatly appreciated.
Many thanks
August 2, 2011 at 10:17 am
Maybe I misread but when do you do your index maintenance and stats update?
Feels like you could get a lot of usefull info by reading this (instinct).
I'm sure you've done something similar but I use this to find "bad" queries and it works everytime :
If your IO "problems" are hitting tempdb this might help :
August 2, 2011 at 10:20 am
Forgot to add that I have a single xeon dual core with 4 GB ram 32 bit that handles a 25 Gb DB with 100 full times users + OLAP on prod DB
with 50+ millions rows and I have no issues whatsoever.
That's why I'm not really sure the issue is on the disks. How did arrive to that conclusion?
Have you used a software like this to trace the root cause of your problems? This one is really nice and with an extend you can have a 1 month free trial for however many servers you want :
August 2, 2011 at 11:46 am
Thanks Ninja
I'm doing a index rebuild weekly, at night, but there is almost no difference in performance from Monday to Friday. The main problem occurs when we do the frequent insert of 100k records and another user is drawing down 50k records or expensive reports are being run.
I've run sql monitor of redgate, but I've come to the same conclusion. For 2 to 15 minutes an import can run and during that time the only resource really in use is the hdd.
I'll try your other suggestions, much appreciated.
August 2, 2011 at 11:49 am
Can you enable snapshot isolation and put tempdb on it's own set of drives?
They will be able to run at the same time and NOT wait on locks... and split the loads to different spindles.
August 3, 2011 at 3:28 am
Ninja's_RGR'us (8/2/2011)
Can you enable snapshot isolation and put tempdb on it's own set of drives?They will be able to run at the same time and NOT wait on locks... and split the loads to different spindles.
Cool idea I will give it a try, thank you
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply