Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Archiving from SQL tables Expand / Collapse
Author
Message
Posted Tuesday, July 5, 2011 10:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:38 AM
Points: 90, Visits: 373
Hello Experts,

We have a reporting database of 300 GB hosted in a single disk.
There are multiple tables which contains archived data and this accounts more than 10% of the data size.

Will there be any improvement if the tables are moved to a new database in the same drive?
Post #1137005
Posted Wednesday, July 6, 2011 7:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
I wouldn't expect much (if any) performance gain from moving the archived tables to a separate database. If the archived data is not being accessed, then it's not generating I/O load, nor is it taking up memory (cache), and so long as it's archived to separate tables then it's not effecting the size of the indexes on the primary tables.
Post #1137263
Posted Wednesday, July 6, 2011 8:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
your backup and restore will take less time :)

You might want to look at partitioning your large tables, especially if you are only looking at a portion of the data (e.g. table holds 5 years transactions and you only normally report on last year and this year for comparisons)

You are more likely to get better performance from query optimisation and indexing. you don't say which version you are on (presume 2005) but you may want to look at covering indexes.

Any query that is using functions can be slow. Look at the speed differential between table functions (where you declare a table, fill it with data and return it) and in-line table functions (where the table format is as per the query results set). In my experience in-line functions are 10-100x faster.

Make sure SPs use prepared queries and parameters as the query plan optimiser can reuse the QP. If you use sp_execute on a fully formatted SQL query string then the plan has be be calculated each time.

More RAM and more CPU cores will also help.
Post #1137310
Posted Monday, July 11, 2011 12:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:38 AM
Points: 90, Visits: 373
Thanks for your help.
Will creating partition for big tables in the same drive going to make any difference?
Post #1139953
Posted Monday, July 11, 2011 4:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
It should do as long as most of your queries only hit a limited number or partitions, say this year and last year. I've not used them a lot as my datasets are not usually that big, but I am sure someone more knowlegeable will be along later.

You may also want to look at covering indexes. These allow you to include non indexed fields in the index that are often asked for and saves the system from having to bookmark the record and go and find the extra details. It makes the index bigger and therefore requires more space but may make the queries much faster.
Post #1140059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse