I need to improve the performance of our enterprise .Net search application from the database point of view.
The app already has 4 schemas on SQL Server 2008.
- 2 schemas for front-end operations consisting of relatively small tables.
- 2 schemas for back-end indexing, processing etc., many of these tables are huge.
Front and back end schemas do not share any foreign key relationships.
To be highly responsive during the day time, the backend operation only runs during the night.
The database server is heavily loaded at night and often we get timeout errors. I want to spread the backend operations throughout the day to overcome this issue. However, this has to have no impact on the front end operations.
When I check the performance during the day the SQL Server is under light load.
My knowledge and experience on database concepts is limited.
Following are the options which I've thought of:
- I might not get a approval for 2 SQL Server machines.
- Front-end schemas in a separate database on the same SQL Server instance. Place the front-end database on separate drive.
- Front-end schemas on a separate filegroup, and place the filegroup on separate drive.
Please let me know the pros and cons of my options and any other options which you think suits here.