November 5, 2007 at 8:01 am
After a suggestion from a previous poster, I have changed the process to drop all the indexes exept for the clustered index and then perform the insert statement, after that the relevant updates.
After running the INSERT statement only (with the clustered index on) I get the following:-
DBCC SHOWCONTIG scanning 'TRANSFORM_JOB_COST' table...
Table: 'TRANSFORM_JOB_COST' (754101727); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 194288
- Extents Scanned..............................: 24584
- Extent Switches..............................: 149062
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.29% [24286:149063]
- Logical Scan Fragmentation ..................: 39.48%
- Extent Scan Fragmentation ...................: 0.31%
- Avg. Bytes Free per Page.....................: 4132.0
- Avg. Page Density (full).....................: 48.95%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 5, 2007 at 8:13 am
Dropping all the indexes prior to the insert and then running the insert and then creating the clustered index produces this output....
DBCC SHOWCONTIG scanning 'TRANSFORM_JOB_COST' table...
Table: 'TRANSFORM_JOB_COST' (754101727); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 98599
- Extents Scanned..............................: 12366
- Extent Switches..............................: 12365
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.67% [12325:12366]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.17%
- Avg. Bytes Free per Page.....................: 302.8
- Avg. Page Density (full).....................: 96.26%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I assume as scan density is 99.67% this is better....
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 5, 2007 at 11:10 am
Fragmentation is caused by several factors. However, that wasn't the point of the suggestion. The point was to improve your loading performance. Was it faster? And if so by how much?
Now if the next step does a full scan on the table, it may be an issue, however, it may not be if the insert is faster.
November 7, 2007 at 2:13 am
The insert was much slower. Although I didn't measure the updates after the insert.
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 7, 2007 at 5:46 am
That would push us back to the I/O performance of you system. Have you done any tests with I/O Meter? Or using perfmeter checked to see your peak I/O Performance?
November 7, 2007 at 6:00 am
HI Bob,
The Physical Disk:Queue Length performance monitor shows between 10-167 when the process is running which indicates to me that the disk subsystem is being thrashed as I beleive the calculation should be value / disk spindles should equal around 2 well this machine has 6 spindles......
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 7, 2007 at 6:36 am
What about your disk write bytes/sec. You had mentioned that the queue is backing up. I am interested in raw numbers for the moment. I am trying to see if we can find any indication as to what is causing an index that should take between 10-20 min to build is taking hours. (and the rest of it too).
November 7, 2007 at 7:38 am
I think I have solved the issue with the index creation.... It seemed that the clustered index was becoming fragmented as each update was being run, this then caused new index creations later on in the process taking longer and longer.
I have now removed the clustered index completely, and replaced it with a normal index.
I have checked the fragmentation of this non clustered index using DBCC SHOWCONTIG and it seems fine.
I have just run one of the updates that is causing an issue and I am gettnig the following:-
Average Disk Write Bytes/sec = 5436013
Maximum disk write Bytes/sec = 31611804
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 7, 2007 at 8:29 am
Ok. Those are pretty good write performance numbers (could be better, but are definately better than your times showed). I had a similar process that after extensive testing ended up best working without any indexes at all.
November 7, 2007 at 8:31 am
This is one of the reasons I was asking about FK issues.
November 7, 2007 at 8:37 am
Thanks for the link, I will have a good read of it when I am at home, as it is very detailed...
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 19, 2007 at 6:33 am
Well we moved our ETL code onto a new machine.
This machine is the same processor/memory specification but has 6 15,000 RPM disks ( 300Gb each) setup as RAID-10.
The load on this machine takes 2.5 hours in total, instead of 13 hours on the other machine....
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 19, 2007 at 6:55 am
Now that's a very welcome feedback :Wow:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 19, 2007 at 7:15 am
Well I can tell you I am very relieved it is now running at that speed, we now need to transfer all of the users onto that machine...
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 19, 2007 at 9:58 am
Very nice Peter - way to stick with it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 61 through 74 (of 74 total)
You must be logged in to reply to this topic. Login to reply