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.
- I might not get a approval for 2 sql server machines.
- Front schemas in a separate DB on the same sql instance. Place the front DB in separate drive.
- 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.