Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a database in SQL Server 2008 with around 20 GB in size. And it is increasing rapidly.
Somehow I can not add multiple independent harddisks to increase IO performance.
If a add large table in separate file group, will it help to improve performance on single disk?
Or any one has tips to improve performance?
Thank you
First off, gather a performance baseline in order to know what the situation is now. Keep monitoring the performance to see if there is a trend for degeneration. If the performance seems to become an issue, there are a few things to do.
Identify the root cause for lack of performance. Is the server expiring memory pressure, IO waits or high CPU usage? The word on the street is to look at Brent's performance tuning pages for a quick start.
Don't overlook good DB design. Check the DB against missing indexes, outdated statistics and poorly written queries. Any of these issues can eat up all the iron you got and then some.
Edit:
Using multiple file groups can - in certain situations - increase performance. Even Microsoft's documentation claims performance advantages. Alas, in your scenario they do not, as you do not have extra disks.
The idea of file groups is to divide database contents to multiple files. File groups make it possible to locate hot tables to high performance storage. This, requires advanced storage configuration your system is lacking. Partitioning tables can take advantage of storage this way too.
Administration tasks like backup/restore can be more manageable with filegroups. Usually a VLDB (very large database) is needed to get such an advantage. Another a classic example is handling read-only data and separating it to its own filegroup. As the data doesn't change, it can be backed up more seldom than the rest of the database.
If you deploy multiple file groups to single a disk, there is no performance bonus. The reason is that you still got a single disk, a single spindle, single set of read/write heads. In order to access data, the disk has to move heads to proper a location. They can't be at two locations at the same time, so extra file group doesn't increase performance.
If your server is disk-bound (saturated), then splitting onto more files (on the same disk) will not increase your performance. However, under some conditions (if it is thread/CPU bound and you have ample disk through-put), it could help. You really won't know without doing a performance study (as @vonPryz already said).
If you are not allowed to expand your disk config (due to hardware constraints, etc) maybe you could consider adding RAM. It won't be a big gain, but sometimes, adding RAM can increase performance (depending on the type of traffic that you are getting). It mostly helps with reads (not much for writes). Considering that your DB is growing rapidly, the extra RAM will certainly help as your indexes grow or if your OS is swapping/paging a lot.
Of course, without knowing anything about your traffic or hardware, configs, etc, I should point-out that adding RAM might also be a waste of money if you have more serious bottlenecks elsewhere. You will only know that by doing a performance study. Spending money on a guess is not very professional.
There is nothing you can do at the disk/file to improve performance on a single spindle-set, other than making sure that the data is not badly fragmented (either internally or at the filesystem level).
You could try tweaking SQL Server's checkpointing behaviour to eek a little more performance out of how it performs writes to disk (see http://msdn.microsoft.com/en-us/library/ms189573.aspx and http://sqlblog.com/blogs/linchi_shea/archive/2007/11/01/best-and-worst-checkpoint-performance.aspx) but I would not recommend messing with any of that without copious benchmarking in a test environment to make sure you are actually making thinks better overall rather than worse, and you need to be careful not to blow your recovery model out of the water.
How much memory is in the machine and what sort of workload you see are important factors: if the majority of the data and indexes fit into RAM and your workload is very read heavy (which most are) then you'll be running mainly from RAM anyway and won't need to worry (if you are not hitting disk often, the speed of the file/disk arrangement are a relatively minor issue). If you make sure that your index choices are well thought out then even if the DB is much larger than the available RAM you'll hopefully find that the common working set fits in nicely so you'll still not have to worry too much about IO optimisation. 20Gb is not all that large for a DB on a modern machine so with good index design a mainly-read workload should be not be blocking on IO much unless your app regularly draws out hundreds of Mb or more in single queries.
You can try with partition of table and index. This method reduce network traffic and increase database performance and save time and space as well
for some tips for sql server performance you can refer these links
http://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/
