Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_server_dba
Posting Yak Master
167 Posts |
 Posted - 2011-06-14 : 20:49:37
|
| All SQL Server Experts,need some suggestions in table design....Here is the description...I have a table called A of 500 Million records. everyday around 1/2 million records will be inserted(OLTP transactions). So we created an archive table to store the data which is older than 1 year. Now that reduced to 300 Million records in Table A. All the maintanance tasks are done(update statistics,indexes, etc...). Here is the process which i do....Every night: Run a stored procedure which inserts data in the archive table and deletes from original table.But this procedure needs to be changed because of some changes in the organisation as well as the performance which is degrading day by day. Anyone have a better idea of archiving the table which is other than the process which i do(Like splitting table A and creting a view or something which may have better performance)?I think programatically there will be some way.Any ideas would be appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sql_server_dba
Posting Yak Master
167 Posts |
Posted - 2011-06-14 : 22:50:58
|
| I looked into it but havent got good picture about that. Can you give me some example wit which it may be better understood. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-06-17 : 17:33:27
|
| The main advantage is maintenance. You can switch partitions to archive db without deletes and inserts. It saves resources (cpu, I/O). However, you have to be careful with index design and queries.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
|
|
|
Table Redesign Plan