November 10, 2008 at 1:14 pm
My DB is very simple but pretty unusual. I have about 10 tables that are all pretty much the same. They have 1 bigint column which is the primary key. Then they have about 50-200 int columns. Some of the tables have another bigint which leads to a customer table. The application does more writing than it does reading but the reading needs to be done very quickly whereas the writing is not as time critical. The size of my database is currently around 20 gb but it will grow to 100+ gb at some point.
Currently I have 8 gb of memory in my computer and 2 hard drives in a raid 0 configuration totalling ~500 gb (more than enough space). What I want is to speed up both reads and writes, but the major emphasis is on reads. For $500 I can do one of the following:
-get 8 more gb of memory
-get a second RAID controller card and four 7200 RPM drives for a total storage space of around 2 tb. I would then use the first RAID strictly for OS and swapping and the second RAID becomes the DB RAID.
More memory means bigger indexes (and therefore faster reads?) which is my argument for more memory. However, more drives means everything is read faster. I am simply not educated enough to determine what will give me more bang for my buck. Any thoughts are appreciated.
November 10, 2008 at 2:45 pm
This can be a really complex topic and many variables can change the approach, but here are a few things that may help you:
> On a 32-bit system adding memory doesn't always speed things up (AWE forces SQL Server manage that memory space directly - so the more memory the more work for SQL). This opens up a whole new set of problems. Unless you are already on 64-bit in which case AWE is no longer an issue. Look at the PLE (Page Life Expectancy) counter and see how often it goes below 300. If it's consistently below 300 you may have memory starvation.
> Do you use /3GB? This can offer an interesting performance boost if your memory is stressed
> Regarding your spindles I am a little surprised about RAID 0. Although it provides performance it does not provide redundancy. So if you loose one spindle you loose the data - but I assume you already know that. From a configuration standpoint I would say RAID 0 gives you the most throughput - RAID 5 may still be a good option for you - performance remains good for reads (as long as all the spindles are available).
I am not sure how your disks are configured but I have seen the following configuration work very well:
> Separate your Log, Indexes and TempDB on different spindles if possible - you may be able to get a good performance improvement by moving your indexes on separate spindles - however if your app do a lot of sorting/grouping perhaps moving TempDB would give you a higher performance boost
> Locking could also be a bottleneck - depending on your application you may be able to get a significant boost by forcing dirty reads
> Make sure your index statistics are updated and use most of your data for stastical purposes (using 10% of the data is fast but may not help SQL pick the right index and reduce I/O consumption)
> Look at indexing as a strategy to move some of your requests to different spindles for example. Consider creating covering indexes and place the heavily used indexes on their own spindles
There are many other things that can be done but I hope these ideas point you in the right direction.
Regards
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
November 10, 2008 at 4:28 pm
Something else you might want to consider... the best hardware in the world won't solve performance problems if the code isn't quite up to it. I'd probably start by looking at the code for hidden RBAR and index opportunities. I'd also look at possible data partitioning opportunies.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply