SQL Server Performance

Archiving of older data

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Nitin.Chobhe, Oct 31, 2007.

  1. Nitin.Chobhe New Member

    Hi all,
    I'm working on Time & Attendance management application. In this application, daily thousand of records are inserted into the DB. Because of this performance of the SQL DB degrades after some period of time (Usually 6 months to a year). So I want to archive the data which is older than a particular period (Say 6 months). But this data shuold be available to view reports (which may be older than 6 months). I can't change the front end i.e. code.
    So can anybody help in this regard?? I've searched this forum nut couldn;t find a solution. There are some threads regarding archiving but in that case I'll not be able to view reports of the data which is archived.
    Thanks in advance!
  2. martins New Member

    Have you considered archiving old data into another table, and then creating a view that will combine the results for your reports? You can also partition data within a table in SQL2k5, which might also prove to useful in your situation
  3. Nitin.Chobhe New Member

    Hi Martins,
    Thanks for your quick reply. I'd tried to move the older data into new table but not created Views on it.
    Can you please elaborate on partitioning & how it can help in this scenario?
  4. martins New Member

    Well, in SQL2k5 you have something called horizontal partitioning which allows you to place certain data (like the older data you have mentioned) on a different filegroup. If you have a disk you can use primarily for the recent data needed in your application, partitioning the data could be beneficial. It will be best if you read up on it in Books Online for the full explanation.
    Creating a view will also work for reporting purposes, or even just creating a stored procedure that combines the data from your different tables (if you decide to archive the data into a different table). You can achieve this with the UNION ALL statement. At the end of the day you will need to take all factors into consideration and choose the best option for your environment.
  5. Nitin.Chobhe New Member

    Thanks Martins! I'll search & read the documnets on the topic you've suggested.
    Thanks again.
  6. anandchatterjee New Member

    This is the perfect DW scenario but you don't want to change the front-end source code. To enhance the performance as well as increase repository status, an upgraded server is needed. So why dont you introduce a now box for it?
  7. satya Moderator

Share This Page