SQL Server Performance

Copying data to temp server to do nightly calcs?

Discussion in 'General DBA Questions' started by kenp2600, Jan 10, 2006.

  1. kenp2600 New Member

    We have 10 databases that get nightly calculations done on them. The routine that does it is a vb.net application that reads data from 8 large tables. It then does a lot of number crunching and writes the results back into 6 additional tables. It creates about 100k records per database per night (each night replaces the previous night's numbers).<br /><br />Currently the routine takes 12+ hours to run, I have tried running multiple copies of my app which tends to only cause SQL to max out the processors on the SQL server which seems to make the whole process take longer.<br /><br />The better long term solution would be to change the way the nightly routines work so that we're not doing as much work, and therefore, it doesn't take as long or work the server as hard (suggestions welcome [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] ) however, I need a quick fix because this is a production system.<br /><br />Since this basically runs on a snapshot of the data as of close of business (few or no users are online in the evenings.) Would it be possible (or a good idea) to copy the data to another server (or servers) and run the routines there, and then copy the results back to the main server? Would replication be the way to do this or is there a better way?<br /><br />I'm struggling with the best way to solve this problem quickly. Any suggestions are welcome.
  2. joechang New Member

    you could try backing up prod db, restoring it on alternate server, do the calc, then dts the results back.
    replication may cause as many problems as it solves if your appl is not properly designed and configured.

    i would suggest you focus on why the calcs take so long, i have had other people with night time batch jobs taking many hours, only to find that with recoding, indexes etc, the work is reduced less than 1 hr.
  3. kenp2600 New Member

    I agree that the better solution is to fix the nightly calculations, but since I'm the only developer/dba here and everything is top priority (as always) I don't have the luxury of doing that for some time.

    I don't know anything about log shipping, would that be an option here too?
  4. satya Moderator

    Log shipping wouldn't work here as the secondary server would be acting as a STANDBY server only for READONLY purpose between the time of log restore jobs. In any case you would need to run the calculation process on the primary server.

    During the time of calculation if you're not expecting any data updates, then you may try applying restoring this database to another server and perform required calculations there and report back the updates with a restore. During the time of restore your application wouldn't be able to read and you need to make sure the logins are properly managed between the 2 servers to avoid any orphaned logins issue.

    So at the end you need to chop down the problem on the calculation process in order to get optimum performance and database availablility.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page