July 29, 2003 at 1:17 pm
I'm using a SQL 7 DTS that takes too much time (over 2 hours). We'd like to pin down exactly where that time is being taken up. Is there a way to find out that time?
I was originally thinking of creating a log text file with start & end times, but I'm open to any method.
TIA
AndreQ
July 29, 2003 at 2:02 pm
Run the package manually from Enterprise Manager. This will allow you to look at the Start Time and Finish Time of each step in the Progress box (you can see this by scrolling the display horizontally).
July 29, 2003 at 2:14 pm
Is there a way for me to save this information? The load on the server is lighter overnight when its run and that's the time I'd like to have knowledge of processing times.
July 29, 2003 at 2:37 pm
Turn on package logging and let the job run...the log will give you the start and stop times and duration for each step...or at least the duration for each step and the start/stop time for the package and total package duration time I believe.
Michael Weiss
Michael Weiss
July 29, 2003 at 2:40 pm
My SQL Server tells me that package logging is only available in 2000. I'm running SQL 7. Nice idea, though.
July 29, 2003 at 3:14 pm
We had the same problem but we needed to know what statements in the sql tasks themselves were long running. We ended up creating a table to hold all the start and stop times. We also created a little report via a stored proc that gives us the timings.
We then put in inserts in the code to this table where we thought the problems were and ran the report after.
Here is the table definition:
CREATE TABLE [dbo].[TIMINGS] (
[sequence_id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[inserted_dt] [datetime] NOT NULL ,
[Process_name] [varchar] (50) NOT NULL ,
[Occurance_id] [char] (1) NOT NULL ,
[occurance_dt] [datetime] NOT NULL
) ON [PRIMARY]
Here is the example of the inserts:
-- insert the before timing record
insert timings
select
end_dt
,'process name 1'
,'a' -- always start with a
,getdate()
from
end_of_day -- table that holds rundate so it doesn't change
-- do an sql query here
select * from tbl_whatever
-- insert the after timing record
insert timings
select
end_dt
,'process name 1'
,'b'
,getdate()
from
end_of_day
-- do another sql query here
select * from tbl_whatever2
-- insert the final timing record
insert timings
select
end_dt
,'process name 1'
,'z' --- always end with z
,getdate()
from
end_of_day
-- This is the stored proc that reports the data
create procedure timings
as
set nocount on
declare @inserted_dt_1 datetime
declare @inserted_dt_2 datetime
select @inserted_dt_1 = max(inserted_dt) from timings
select @inserted_dt_2 = max(inserted_dt) from timings where inserted_dt < @inserted_dt_1
select 'These timings are grouped by the 2 most recent runs, in descending order'
select case datepart(dw,@inserted_dt_1)
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
else'Saturday'
end
,convert(varchar(20),@inserted_dt_1)
select process_name, datediff(mi, min(occurance_dt), max(occurance_dt)) 'minutes'
from timings where inserted_dt = @inserted_dt_1
group by process_name
order by process_name
compute sum(datediff(mi, min(occurance_dt), max(occurance_dt)))
select case datepart(dw,@inserted_dt_2)
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
else'Saturday'
end
,convert(varchar(20),@inserted_dt_2)
select process_name, datediff(mi, min(occurance_dt), max(occurance_dt)) 'minutes'
from timings where inserted_dt = @inserted_dt_2
group by process_name
order by process_name
compute sum(datediff(mi, min(occurance_dt), max(occurance_dt)))
July 29, 2003 at 6:43 pm
Run it using a job and in the advanced tab of the job you should be able to create a log file. That file should have all the information you need. Can't remember if it was not available in SQL 7. I think it was.
July 30, 2003 at 6:47 am
jghoshal - that option is available in 2000 but was not available in 7.0
To use it in 2000 you must be inside a package (design package option) and open the properties for the package (package pull down -- properties) then click on the logging tab. Click the "log package execution to SQL Server" option and enter the server and connection info.
You can run the package interactively or scheduled through a job. Either way it will create a log.
To view the log, right click on the package and pick the "package log" option.
You can also view the log info directly from the system table "sysdtspackagelog" in the MSDB database. This method also gives more detail on the run and the ability to report on it systematically.
July 30, 2003 at 9:37 am
Maybe we're running a different kind of SQL server. The only related type tables in our MSDB database are sysdtscategories and sysdtspackages. There is nothing about any log file associated with a dts-type table.
There's been talk (for years now) about upgrading SQL server but I don't think its going to happen any time soon.
July 30, 2003 at 9:40 am
That table is only in 2000. It is not in 7.0
July 30, 2003 at 9:42 am
Yes, my statement in the first line of the first message was that I was running SQL 7.
Does anyone know of a way that I can do this time logging given the constraints I'm under?
July 30, 2003 at 10:06 am
Given your constraints, and excluding the manual run, you will need to add a logging task between each step of the package. This can be a simple SQL task with a statement like:
INSERT INTO LogTable (Step, StepTime) Values (<step # hard coded>, getdate())
Place before each step, and after the last step. That's the best I can see for an automatically run package.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply