Tell me more ×
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's 100% free, no registration required.
up vote -1 down vote favorite

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:

  1. I might not get a approval for 2 SQL Server machines.
  2. Front-end schemas in a separate database on the same SQL Server instance. Place the front-end database on separate drive.
  3. 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.

share|improve this question

closed as not a real question by jcolebrand♦ Sep 7 '12 at 19:41

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, see the FAQ.

1 Answer

up vote 3 down vote

Are you really asking about a performance solution w/o ever telling us what the performance bottleneck actually is? Did you measure? The server is 'heavely' loaded at night... why? What resources are stretched? Is it memory? IO? CPU? Contention? Is it poorly tuned maintenance? Bad data model? Misaligned schedule? Hardware bottleneck?

I recommend you use a performance troubleshooting methodology and identify the bottleneck first, before jumping to solutions. Waits and Queues is a great SQL Server performance investigation approach. The Performance Troubleshooting Flowchart offers a great syntheses of methods and resources for analyzing performance. Usage, Saturation, Errors is a great paradigm to approach any performance troubleshooting.

Measure first, cut later.

share|improve this answer
And read a big fat performance tuning book, this is a complex subject with more variables than you can put into a questions on the Internet. – HLGEM Sep 7 '12 at 14:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.