Sql-server – Multiple schema or multiple databases for better performance

performancesql server

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.

Best Answer

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.

Related Question