Multiple schema or multiple databases for better performance

Question

I need to improve the performance of our entriprise search application(.net) from the database point of view. The app already has 4 schemas on a separate sql server 2008.

• 2 schemas for front-end operations ( the tables are small)
• 2 schemas for back-end indexing, processing etc. (the tables are very huge here).

Front and back schemas does not share any foreign key relation between each other. To be highly responsive during the day time, the backend operation run in night time only.

Now the problem is the database server is heavely loaded at night time and often we get timeout errors. I want to spread the backend operations through out the day to overcome this issue with no impact on the front end operations. when i see the performance graphs, during daytime the server and the DB in it is very minimally used.

My knowledge and exp on DB concepts is limited. Following are the options which i think of.

1. I might not get a approval for 2 sql server machines.
2. Front schemas in a separate DB on the same sql instance. Place the front DB in separate drive.
3. Front schemas is separate filegroup. and place the filegroup in saperate drive.

Please let me know the pros and cons of my options and any other options which you think suits here. Thanks in advance.

Answer 1

It's impossible to evaluate the options without knowing what the actual problem is.

Your first port of call should be to identify the reason the servers run slowly - it may be memory or CPU, rather than disk throughput. Perfmon will be able to tell you this; you can also run Microsoft's PAL tool to get a better insight. If it's not disk throughput that's limiting you, option 2 and 3 are pointless.

Secondly, it may well be possible to optimize the queries - this may be cheaper, quicker and less risky than changing the infrastructure. Find the query that's running slowly, work out what's constraining it, and see if you can optimize it.

If you do find out that the queries are optimally tuned, and that it is disk throughput that's the bottleneck, I'd put each of the two back-end DBs on separate drives - it doesn't sound like they are competing against the front DBs (as they run at different times), but they might be competing against each other if both DBs are used in overnight batch jobs.

Answer 2

Breaking the schemas into two separate databases will make backup and restore more difficult, particularly if you are trying to do a point-in-time restore. You can just put the tables into appropriate file groups and put them on different disks. It might be easier to move the frontend tables onto a different disk than the back end because they are smaller and more easily moved.

OTOH, you might get better performance by adding additional drives to your RAID set and making sure that the data is effectively spread around the RAID. Assuming that your RAID hardware supports the addition of new drives and volume expansion.

If the batch processing relies on data from the front end you may still run into blocking problems.

It's also possible that the backend tables aren't indexed very well, or that the statistics for existing indexes have gone stale.

As a few people have pointed out, it would be best to understand the problem before trying to fix it. There is a big difference between I/O timeouts caused by slow storage and query timeouts caused by a 60 second query timeout value.