February 23, 2010 at 3:53 pm
I've got a very large, well, 14 million rows, 1 clustered/10 non indexes in a transactional database that does have a couple of reports run directly from it (rather than the mirror)
When we combine generation of weekly stats for this table, we're inserting approx 4000 rows over two days of activity. During the process to insert, we're experiencing application slowness and obviously any time we run a report we run off either the production or a mirrored copy is slow when accessing this table.
Now, this application is up for replacement and except for this particular activity is pretty good so I'm not really interested in other approaches as the easy one seems to be archiving some of that data off to another table then reporting against a union result set.
My question here is around what are the technical explanations on why I should be archiving. I've got this (I'm hoping justifed) feel I should be archiving here- the data really isn't needed, its a *transactional*db but the developer has balked after previously agreeing to this.
Could someone either outline why overly large tables are problematic, or point me in the right direction for a technical description of why inserting and reporting off large tables is not optimal? I've done db subjects at uni, and developed a few dbs, but I'm left with this gap of not really understanding this level of the process. Thanks guys
February 23, 2010 at 4:07 pm
In general, a well-tuned large table shouldn't be a performance issue.
One question I would have is do you really need 11 indexes on the table, given that fact that you run just a couple of reports off if it? I would look to see if one of the bottlenecks is the maintaining of those indexes.
Also, you could look at partitioning the table.
February 23, 2010 at 4:37 pm
I guess those indices are a problem in your case.
Usually, too many indices hinders insert operations.
Once, when I faced this problem, I dropped the indices during the insert operation and recreated them after the insert. I was able to overcome the slowness.
M&M
February 23, 2010 at 8:40 pm
Its a fairly complex application, and has been under continual development for a number of years - but an external agency (government), and they believe all those indexes are required and checking the number of seeks on them, they are all used fairly extensively.
I had not considered taking in the indexes off line whilst the generation was happening, but as other work is going on in parallel , I'm afraid I'll crunch the system if I do that.
I had given some thought to partitioning the table, but have no experience with that. I'm assuming MSSQL2005 can horizontally partition tables - can that be done on a date range? I would have to organise my disk usage, but I think I've still got a spare spindle set in there somewhere.
Thanks for your thoughts, and if you've any more, please feel free to throw them in 🙂
February 24, 2010 at 10:51 am
SQL Server 2005 introduces automatic horizonal partitioning based on a column and specification of the ranges for the partition. If you have a date column you can partition based on it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply