October 30, 2007 at 4:31 am
In that specific example, sure, but not necessarily for triggers in general. Depends what you're trying to do with them. They have their place.
But back to your problem. My gut feel, and others may disagree, is that you're over-stressing the IO system on the index builds. Could you maybe test just the index creation portion, with perfmon running with the following counters.
Physical disk:
Transfers/sec
sec/read
sec/write
%idle time
disk queue length
SQL wait stats
Lock waits (average wait time and waits started per second)
Log write waits (average wait time and waits started per second)
Page Latch waits (average wait time and waits started per second)
Page IO Latch Waits (average wait time and waits started per second)
Please either post the max, min and average of each or, if possible, attach the resulting trace file to your post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2007 at 4:46 am
Hi Gail,
Thanks... I can't do the stat check today, as I am on a different site, I should be there on Monday.
When I performed statistics last time, disk queue length averaged abour 40 and maxed out at 147. the machine has 6 disks RAID-5 so I assume those numbers should be divided by the number of spindles 6 so that would be between 6 ( 40 / 6 ) and 24.5 ( 147 / 6 ). Which is very high as I think this value should be 2.
I am going to change one of the stored procedures to drop all the indexes prior to the insert (Except for the clustered one) and insert the rows sorted in the same way as the clustered index as per one of the posts in this thread.
I am also going to modify the process so that it doesn't drop indexes from the other table it uses, but just adds the new ones. I think that will help the process a little bit (thanks for the constructive input from everyone).
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
October 30, 2007 at 5:27 am
Sergiy (10/29/2007)
This just shows how poor and limited is your T-SQL knowledge.
Another confirmation to my point about bad design.
Completely unnecessary comment. There are much more constructive ways to explain this issue.
There is no need here to tell the OP that they don't know what they are doing. They are asking for help, no need to throw insults.
As for the issue: The possiblity that the issue that the RAID 5 array is being taxed is likely but I am still not convinced that there isn't also some blocking going on.
I know that these are staging tables. Any Foreign keys (I wouldn't expect it)?
October 30, 2007 at 5:51 am
Hi Bob,
No foreign keys or triggers.
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
October 30, 2007 at 8:20 am
A few questions:
- Assuming you're using the DTS GUI to set these up - what do you have in the transfer task as far as Fetch size? Do you have anything tagged as far as SQL loading parameters (lock table/check constraints/etc)
- is the data being pulled OUT of the source system being filtered in some way? Would this benefit from using OPENQUERY syntax (where the filter can be done on the source system first)?
- is this a sorted insert?
- Not sure if I just missed it - but what is the source system we're pulling this data from? Is the communication channel between the two apps being taxed?
- how big is your tempdb getting to be? how about its log file?
- have you tried just the pull portion using something like BCP or BULK INSERT instead of DTS? They do tend to run the loading phase quite a bit faster.
Also - if you get a chance to test this - have you tried putting the DB and logs on their own single disk (no RAID whatsoever) and seeing what performance difference you get? If RAID-5 is being taxed, the local disk/single disk should work FASTER than RAID5.
----------------------------------------------------------------------------------
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?
October 30, 2007 at 8:38 am
Hi Matt,
To answer your questions:-
I am only using DTS to initially transfer the data from Oracle 9.2 to SQL Server. After that all other processes are performed using stored procedures called from DTS to allow the use of DTS workflow.
The extract in DTS is using a DTS Pump task, and the Fetch buffer size is currently set to 1. Use fast load and table lock are ticked.
The Oracle --> SQL Server dts pump tasks take no time at all, it is the stored procedures that are causing the problem.
No filtering on most of the Data pump except two tables which are restricting the data using a datetime (to incrementally load the data).
I'm not on site at the moment, so it is difficult to give you the exact size, but I know this database is not increasing in size because I have sized the data/log files for this database.
The disks are local disks but are RAID-5 it's not possible at the moment to move this database onto another server without RAID because the client doesn't have a server available. However they will shortly have a server I can use with a RAID-10 (0+1) configuration, so I am going to try it on that machine.
Hope the above makes sense, thanks for taking time to post a message to help.
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
October 30, 2007 at 9:04 am
Do you have a good read on what step of the process IS taking most of the time and/or has been increasing? What's happening at that moment.
Since it's running as long as it is - have you considered putting in a little table to keep track of execution progress/time? Just assign yourself some numbering scheme of the tasks, and insert a record (with a getdate()) telling you when you got to a specific point?
I mean - what's another few records inserted when you're dealing with hours? Should help figure out where we need to "focus" the efforts.
----------------------------------------------------------------------------------
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?
October 30, 2007 at 9:16 am
Hi Matt,
I already have a few tables that monitor each process that is running.
AUDIT_JOB - Which shows the overall job start/end time (13 hours)
AUDIT_TASK - Shows each task start/end time.
DEBUG_LOG - Shows the start time/end time of each statement within each stored procedure
ERROR_LOG - Shows the error that occurs (if one occurs)
The weird thing is it is not one particular step that is taking a long time, each step in the whole process is taking a longer, thats why I thought it might be a hardware issue.
I was looking at one specific stored procedure and when I looked at the debug logging, just creating a non clustered index was taking about an hour (on 800,000 rows !!!!! )
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
October 30, 2007 at 10:17 am
That's an insane number. An hour to create a non clustered index? I'd have to say that there IS something wrong. Sounds like resource starvation of some kind.
What do you get when you run DBCC Showcontig against that table?
----------------------------------------------------------------------------------
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?
October 30, 2007 at 11:01 am
I'll post that when I get back to the site on Monday...
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
October 31, 2007 at 3:21 am
- Maybe I've overread ... but did you apply Cumulative HofFix for sp4(build 2187)-KB916287 ?
It might be that due to increase of load or data, engine issues arrise ....
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
October 31, 2007 at 7:36 am
Hi ALZBA, I'm not sure if I have added that... Should I if I haven't?
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
October 31, 2007 at 7:44 am
have a look at the KB @ http://support.microsoft.com/kb/916287
Maybe the fix list rings a bell.
If you cannot find another reason why it slowed down, maybe it's worth to investigate .....
fixlist:http://support.microsoft.com/kb/894905/
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
October 31, 2007 at 8:04 am
I've just had a look at my original post and the version we are running is The SQL Server 2000 version is :- 8.00.2187 SP4 Standard Edition. So I assume we already have the cummulative patch 2187 on this server.
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
October 31, 2007 at 8:40 am
Indeed :ermm: I should have doublechecked (it) myself :blush:
back to the pile of perfmon/profiler files ...
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
Viewing 15 posts - 46 through 60 (of 74 total)
You must be logged in to reply to this topic. Login to reply