SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Archiving from SQL tables


Data Archiving from SQL tables

Author
Message
Knowledge Hunter
Knowledge Hunter
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 403
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?
Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1327 Visits: 1033
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.
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1059 Visits: 902
your backup and restore will take less time Smile

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.
Knowledge Hunter
Knowledge Hunter
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 403
Thanks for your help.
Will creating partition for big tables in the same drive going to make any difference?
aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1059 Visits: 902
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search