August 8, 2007 at 4:01 am
Hi
We have a large sql 2000 datawarehouse. Our sales table consists of approx 50 million rows, and about 170 GB of data. We have a lot of other tables, but sales is the main table.. naturally. We retrive the last 3 days of data from our source system every night, and insert into sales.. normally this takes about 2-3 hours. I am not sure if it is possible to reduce this run time on sql 2000, unless we go for better hardware..
I have been told that onw can use horisontal and or vertical partitioning on sql 2005.. (these methods are new to me, so I can not say what they do...) Can this help run time, or does this mainly effect query time from end users?(we have some 200 users, at least 60-70 at any given time.)
Yet another problem is disk capacity... tempdb gets stuffed, so we have auto shrink, that helped.. but the log files gets huge... We have set up a maintenance plan that does backup, integrety check of both data and transaction log files. Data can grow as it wants, but log can not. Log have been divided into to disks, and can not grow beyond 20 GB in either of them. Recovery mode:Simple
We are now talking about using sql 2005.. Should ve wait until SQL 2008?
Does anyone have any good ides on mastering our issues on mainenance an optimization? I would love it if you could provide som url to good books or articles on the subject.
Thanks for all help in advance.
Regards
Dan
August 8, 2007 at 10:18 pm
If you can, wait until SQL 2008 comes out. And do lots of testing. There was an article or editorial on this site about this recently.
As for the other stuff, are you doing things in multiple transactions? If the insert is being done in one transaction, that will certainly blow out the logs. Try splitting it into smaller batches.
How many indexes do you have on the destination? You could maybe try dropping the indexes before the insert & recreating them afterwards, although that may take just as long. Testing would tell.
Is auto-statistics enabled (I would imagine so, given the size of the DB)? That might buy you some performance benefit if you turn it off.
As for the log file on multiple disks, not going to do much. SQL will only write to one of them at a time (the log file is sequential) so you'd be better off having it on one disk (and preferably not fragmented). Assuming you have the space for it (is that why it's on several disks?). And how can you be backing it up if you are in Simple recovery mode?
If you use horizontal partitioning in SQL 2005, you could get a performance benefit. Older [read-only] data can be put in separate filegroups, so when newer data is inserted, SQL doesn't have to process as many [existing] rows. I haven't played around with it myself, but there's some good stuff in 2005 BOL. Or you can look here: http://technet.microsoft.com/en-us/library/ms188706.aspx
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply