Take the 2-minute tour ×
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.

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.

share|improve this question

migrated from stackoverflow.com Sep 8 '12 at 3:14

This question came from our site for professional and enthusiast programmers.

1  
Since you've already said the server is minimally used during the day why not simply try it and see what happens in the performance graphs? You could even make users aware of a maintenance activity the day before to set expectations that the environment could be slow. –  Chris Moutray Sep 7 '12 at 11:55
    
@ChrisMoutray, as i mentioned front-end queries should be highly responsive. So before spreading the back-end operations i wanted to know the pros and cons. –  Rockr Sep 7 '12 at 12:46

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
darin and @Neville thanks for the answers. the queryies were optimized and i looked in performance graphs. and disk is not stressed. its the cpu and the lock wait time which shows spike during the night when ETL processing jobs. 100s of thousands of rows are processed by the job. planning to evenly spread the jobs to ease the issue. –  Rockr Sep 10 '12 at 16:09
    
Are you seeing blocking during the jobs? If so, it is unlikely that spreading the jobs around timewise will ease your problem. –  darin strait Sep 12 '12 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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