July 5, 2011 at 10:57 pm
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?
July 6, 2011 at 7:31 am
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.
July 6, 2011 at 8:04 am
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.
July 11, 2011 at 12:15 pm
Thanks for your help.
Will creating partition for big tables in the same drive going to make any difference?
July 11, 2011 at 4:13 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply