November 21, 2006 at 7:14 am
Hi,
I have a very strange issue with a client database. The system is a data warehouse system. The ETL process uses a mix of DTS and SQL Server stored procedures.
Previously this process took around 6 hours to complete, but recently the machine ran low on disk space, so 3 new disks were added to the disk array. The server is a Dell server, and Dell recommended that the server was rebuilt.
So far so good. On completion of the rebuild, the databases were restored.
On the first run the process took over 18 hours to complete, on the second run over 25 hours.
When looking at the server itself, I checked the Avg disk queue length during the load, and the maximum was around 100 (can't remember exactly). After reading up about this, this value should be <value> divided by number of spindles. if this value is greater than 2 you have an I/O bottleneck.
So on this information I assumed either there was an issue with the RAID configuration or the RAID controller.
So I copied all the databases/DTS packages etc to a new server and repeated the test.
Again the process took over 25 hours.
I have run explain plans against some of the larger queries, but they still seem to be correctly using indexes.....
The code base for this application hasn't changed during this time....
Any help would be really appreciated, as I am now at a loos to understand what is causing this problem.
Thanks
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 22, 2006 at 9:10 am
Hi
Sounds like fun...
The main things in my experience to cause these are Transaction Log size (being big - anything over a couple hundred meg or so is indicative of a potential issue), statistics out of date, and table/index fragmentation.
Occasionally a sysadmin might do something incredibly clever like install av software on the production box...
If you haven't checked these things out yet, you should.
Rich
November 22, 2006 at 9:16 am
Hi Rich,
Thanks for your reply... The Database is running in 'simple' logging mode, so the process shouldn't run out of log space. Both the log file size and the data file size have been sized correctly, and not set to autogrow. When I was monitoring the process both of these files never got anywhere near there maximum.
With regards to AV I did think that could be a problem, but the client insisted on this being on the machine!!! So I have got them to configure it so that it doesn't touch LDF, MDF and NDF files.
Also I run a statistics update every night on this machine so the statistics should not be out of date. Also weekly the indexes are rebuilt.
It is very frustrating...... AAAAAAGGGGGGHHHH!!!!!
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 22, 2006 at 12:52 pm
Perhaps the AV software is taking up good chunk of CPU and is blocking the resources for your SQL Server in order for it to run the DTS.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
November 23, 2006 at 5:56 am
With the av - is it running some heuristics/real time wotnot? Also did you manage to get an exemption for the import location - or is that all being scanned too?
Another thing that occurs to me - is whether any of the exciting flags need to be set - parallelism/memory etc. I gather you reinstalled the sqlserver - maybe OS as well - is it possible you ended up with something different - mcdacs/servicepacks/settings?
November 23, 2006 at 6:48 am
Hi all,
I don't think it is the AV software, because CPU is running ok I have also disabled it to check whether it was that.
I have now copied the MDF and LDF files to another server and run the process and it still takes a simlier amount of time. (this is the dev server which si the same spec as the live server).
I'm thinking that it might be a corrupt MDF, however I have run DBCC CHECKDB with no errors found.
To be on the safe side I have decided to create a new database and copy the data from the original database into the new one and run the process again. Just to make sure it isn't a dodgy MDF file.
Any other help very much appreciated.
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 24, 2006 at 9:40 am
Hi all,
Well it seems there must have been something wrong with the MDF file. I created a brand new empty databases, loaded the static data and then ran a full refresh. This took only 6.5 hours (instead of previously 25 hours). It was very strange considering I had run dbcc checkdb and it didn't find any errors....
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply