July 13, 2010 at 10:03 pm
Hi Experts,
We have a job that selected data from two linked servers Oracle and DB2 and inserts into SQL server initially it was taking less than 10 hours but now its taking more than 22 hours..
Please Help
TIA
July 16, 2010 at 7:47 am
How much data are we talking about here ?
How big are the tables?
What is happening to the data before it is inserted ?
Need a bit more to play with I'm afraid
Graeme
July 16, 2010 at 10:36 am
There could be any number of points of concern. The Oracle tables might need a reindex. There could be issues with a network card. Really, you're going to have to break the steps of the job down and see what is taking the most time. Hopefully you have a baseline so you know how long each step was before to see which step or steps have gotten longer.
July 18, 2010 at 10:00 pm
Graeme100 (7/16/2010)
How much data are we talking about here ?How big are the tables?
What is happening to the data before it is inserted ?
Need a bit more to play with I'm afraid
Graeme
1.data is not more than a GB
2.no data manipulation
July 18, 2010 at 10:01 pm
jeff.mason (7/16/2010)
There could be any number of points of concern. The Oracle tables might need a reindex. There could be issues with a network card. Really, you're going to have to break the steps of the job down and see what is taking the most time. Hopefully you have a baseline so you know how long each step was before to see which step or steps have gotten longer.
I checked step by step and two step is taking around 7 hours..Those steps are nothing but calling a procedure with different parameters and both do almost the same task of inserting data
We have the same setup in Dev which is taking less than 8 hours
July 19, 2010 at 7:29 am
If I were you I would analyze that procedure in more detail. Work out if there is a particular component / section / statement / anything that is taking a significant amount of time. There are many ways to do this, depending on your setup & standards.
It could be as simple as inserting some print getdate() statements at various points and anaylzing the log file, through to creating alog table and inserting records with datetime stamps.
If your proc only has one operation in it then you have your answer right there (i.e the offending table(s) will be right there).
July 19, 2010 at 7:39 am
And I would also try to run that procedure to get the execution plan as well, as it will show you where it's working the hardest. If dev works fine and prod works bad, then either someone has dropped indexes or else the data distribution has changed and statistics are out of date/index is badly fragmented. This type of tuning is what a DBA's bread and butter job is all about. You need to dig deeply into what has changed on the prod server, and it's going to most likely be index or statistic-related if nothing else has changed.
July 19, 2010 at 8:41 am
By any chance is this step a foreach or foreach loop?
Or is the stored procedure using a cursor?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 16, 2010 at 12:12 am
CirquedeSQLeil (7/19/2010)
By any chance is this step a foreach or foreach loop?Or is the stored procedure using a cursor?
YES..
Please find the code below
ALTER PROCEDURE [dbo].[usp_RUN_PRODUCTION_LINE]
-- Add the parameters for the stored procedure here
@System_ID bigInt ,
@Batch_ID bigint
--@Batch_ID bigint = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@message varchar(MAX),
@SQL_Command varchar(MAX),
@b_ID bigint,
@s_ID bigint,
@result varchar(MAX),
@err int,
@Job_ID bigint,
@User_Namevarchar(200)
SELECT
@User_Name= USER_NAME()
-- Begin PRODUCTION LINE BEGIN in the procedure
BEGIN TRANSACTION trans_BEGIN_LINE
EXEC usp_INPUT_QUALITY_CONTROL
@Quality_Control_Code = '<<< BEGIN LINE >>>',
@System_ID = @System_ID,
@Batch_ID = @Batch_ID,
@Job_ID = 99999,
@Job_Statement = 'PRODUCTION LINE BEGIN',
@Error_Nbr = 0,
@Error_Statement = 'NULL',
@Security_Context = @User_Name
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION trans_BEGIN_LINE
END
ELSE
BEGIN
COMMIT TRANSACTION trans_BEGIN_LINE
END
-- Insert statements for procedure here
IF (@Batch_ID = -99)
BEGIN
DECLARE cur_RPL CURSOR
FOR
SELECT
CJ.SQL_Command ,
CJ.Job_ID,
CJ.System_ID,
CJ.Batch_ID
FROM CONTROL_SYSTEM CS
inner join CONTROL_BATCH CB ON (CS.System_ID = CB.System_ID)
inner join CONTROL_JOB CJ ON (CB.Batch_ID = CJ.Batch_ID)
WHERE CS.System_Switch = 1 and CB.Batch_Switch = 1
and CJ.Job_Switch = 1 and CS.System_ID = @System_ID
ORDER BY CJ.System_ID, CJ.Batch_Id, CB.Sequence_Nbr ,CJ.Sequence_Nbr
END
ELSE
BEGIN
DECLARE cur_RPL CURSOR
FOR
SELECT
CJ.SQL_Command,
CJ.Job_ID,
CJ.System_ID,
CJ.Batch_ID
FROM CONTROL_SYSTEM CS
inner join CONTROL_BATCH CB on (CS.System_ID = CB.System_ID)
inner join CONTROL_JOB CJ on (CB.Batch_ID = CJ.Batch_ID)
WHERE CS.System_Switch = 1 and CB.Batch_Switch = 1
and CJ.Job_Switch = 1 and CS.System_ID = @System_ID and CB.Batch_ID = @Batch_ID
ORDER BY CJ.System_ID, CJ.Batch_Id, CB.Sequence_Nbr ,CJ.Sequence_Nbr
END
-- Looping through Batches.
OPEN cur_RPL
FETCH NEXT FROM cur_RPL INTO @SQL_Command, @Job_ID, @s_ID, @b_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = @SQL_Command
-- Call the stored procedure usp_INPUT_QUALITY_CONTROL
BEGIN TRANSACTION trans_BEGIN_JOB
EXEC usp_INPUT_QUALITY_CONTROL
@Quality_Control_Code = '* BEGIN JOB *',
@System_ID = @s_ID,
@Batch_ID = @b_ID,
@Job_ID = @Job_ID,
@Job_Statement = @message,
@Error_Nbr = 0,
@Error_Statement = 'NULL',
@Security_Context = @User_Name
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION trans_BEGIN_JOB
END
ELSE
BEGIN
COMMIT TRANSACTION trans_BEGIN_JOB
END
-- Executing the Sql Command
BEGIN TRANSACTION JOB_SQL_COMMAND
BEGIN TRY
exec(@message)
BEGIN TRANSACTION trans_END_JOB
EXEC usp_INPUT_QUALITY_CONTROL
@Quality_Control_Code = '** END JOB **',
@System_ID = @s_ID,
@Batch_ID = @B_ID,
@Job_ID = @Job_ID,
@Job_Statement = @message,
@Error_Nbr = 0,
@Error_Statement = 'NULL',
@Security_Context = @User_Name
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION trans_END_JOB
END
ELSE
BEGIN
COMMIT TRANSACTION trans_END_JOB
END
COMMIT TRANSACTION JOB_SQL_COMMAND
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) ,@ErrorNumber NVARCHAR(100);
SELECT
@ErrorMessage = ERROR_MESSAGE()
SELECT
@ErrorNumber = ERROR_NUMBER()
ROLLBACK TRANSACTION JOB_SQL_COMMAND
-- Call the procedure to insert into QUALITY_CONTROL_LOG ,if there is error
BEGIN TRANSACTION trans_ERROR
EXEC usp_INPUT_QUALITY_CONTROL
@Quality_Control_Code = 'ERROR - JOB',
@System_ID = @s_ID,
@Batch_ID = @b_ID,
@Job_ID = @Job_ID,
@Job_Statement = @message,
@Error_Nbr = @ErrorNumber,
@Error_Statement = @ErrorMessage,
@Security_Context = @User_Name
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION trans_ERROR
END
ELSE
BEGIN
COMMIT TRANSACTION trans_ERROR
END
END CATCH;
FETCH NEXT FROM cur_RPL INTO @SQL_Command,@Job_ID, @s_ID, @b_ID
END
CLOSE cur_RPL -- closing the cursor
DEALLOCATE cur_RPL -- deallocating the cursor
--Call the stored procedure for PRODUCTION LINE END
BEGIN TRANSACTION trans_END_LINE
EXEC usp_INPUT_QUALITY_CONTROL
@Quality_Control_Code = '>>> END LINE <<<',
@System_ID = @System_ID,
@Batch_ID = @Batch_ID,
@Job_ID = -99999,
@Job_Statement = 'PRODUCTION LINE END',
@Error_Nbr = 0,
@Error_Statement = "NULL",
@Security_Context = @User_Name
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION trans_END_LINE
END
ELSE
BEGIN
COMMIT TRANSACTION trans_END_LINE
END
END
August 16, 2010 at 12:14 am
jeff.mason (7/19/2010)
And I would also try to run that procedure to get the execution plan as well, as it will show you where it's working the hardest. If dev works fine and prod works bad, then either someone has dropped indexes or else the data distribution has changed and statistics are out of date/index is badly fragmented. This type of tuning is what a DBA's bread and butter job is all about. You need to dig deeply into what has changed on the prod server, and it's going to most likely be index or statistic-related if nothing else has changed.
When i Checked the procedures execution plan its showing missing idexes:-) but am wondered the job is taking more time on friday morning EST only..
I checked for any other jobs conflicting with this but didnt find any
August 16, 2010 at 12:16 am
Is it required that the procedure be done via loop?
Could you bulk load the data into some staging tables in SQL server instead and then perform your operations entirely within SQL server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 16, 2010 at 1:39 am
CirquedeSQLeil (8/16/2010)
Is it required that the procedure be done via loop?Could you bulk load the data into some staging tables in SQL server instead and then perform your operations entirely within SQL server?
Not sure jason...
This is just one of 15 steps...
August 16, 2010 at 8:36 am
I would look into it from that angle. Ask the developers and dba's around you concerning the impact of changing it. Get a good grasp of the entire job from start to finish.
Looping across the wire to do this insert will be slower than doing a bulk load and then looping (if it must be done that way) internally on SQL server. In all likelihood you can change it from loops to sets and see significant gains in your processing speed (on all days and not just the problem day).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply