same stored procedures on similar DB delay

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

same stored procedures on similar DB delay

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 05:55:01

Hi.
I have 2 similar DB's with almost the same amount of data.
One is 35GB the other 40GB.
I have a stored procedure that is EXACTLY the same on both DB's.
The problem here is that running it in the first DB will take 2 second and running in on the other DB will take 4 minutes!!!!
I have checked and rebuilded the indexes, checked the setup of the actual HD's Drives the memory usage and everything seems the same.
Only difference i can find i that the execution plan seems different where run but this may have to do with the facts that some data in the tables is not exactly the same .
So any idea?

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-10-21 : 07:43:29

I guess parameter sniffing could be the cause. can you post execution plan not estimate but the actual execution plan. You can use option(recompile), OPTION (OPTIMIZE FOR (@VARIABLE=VALUE)) in proc which is taking long time and see if it helps. Again this is just speculation I would need complete execution plan and query to comment further

Hope this helps

Regards
Shanky
SQL Server MVP
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 08:50:35

Please mind that it is sql2005 so i cannot use Optimize for.

Do you want the xml execution plan?

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-10-21 : 09:34:00

Hey. So something new has come up.
What i see here is that the execution plain is EXACTLY the same but here is what happens. My previous colleague made a solution for running the stored procedures faster. So what he did was creating a stored procedure that would get the details of every incoming main stored procedure and with in a job, loop through every stored procedure and executed it.

Here is a cleaner explanation:
Job runner - sproc1 , sproc2

this would go to:

DECLARE	@DateFrom as DateTime, 
		@DateTo as DateTime

SET		@DateFrom		= CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE()-1,101))
SET		@DateTo			= CONVERT(DATETIME,CONVERT(VARCHAR,DATEADD(hh,-6,getdate()+1),101))--CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101))


EXEC ZZ_TicketTransactions @DateFrom, @DateTo,  '21', 0

The ZZ_TicketTransactions is like this:

 USE [HO]
GO
/****** Object:  StoredProcedure [dbo].[ZZ_TicketTransactions]    Script Date: 21/10/2014 4:24:34 µµ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER  PROCEDURE [dbo].[ZZ_TicketTransactions] 
	@DateFrom DATETIME, 
	@DateTo DATETIME,
	@Cinema VARCHAR(2),
	@DeleteOtherRecords AS BIT
AS
BEGIN

	IF @DeleteOtherRecords = 1
	BEGIN	
		DELETE FROM ZZ_rptTicketsSummary 
	END
		
	DECLARE @Parameters AS VARCHAR(1000)
	DECLARE @ServerName AS VARCHAR(50)
	SET	@Parameters = '''' + convert(varchar,@DateFrom,112)+''','''+convert(varchar,@DateTo,112) +''''
	
	DECLARE MY_CURSOR Cursor
	FOR 
		SELECT	DISTINCT Cinema_strServerName
		--SELECT	DISTINCT REPLACE(REPLACE(Cinema_strServerName,'SSRV2','CSQL') ,'SRV','SQL')
		From	tblCinema C JOIN tblCinema_Operator CO ON C.Cinema_strCode = CO.Cinema_strCode
		WHERE	C.Cinema_strCode = CASE
									WHEN
										@Cinema = 'CI'
									THEN
										C.Cinema_strCode
									ELSE
										@Cinema
									END
				AND CinOperator_strOnline = 'Y'
	Open MY_CURSOR 


	Fetch NEXT 
	FROM MY_CURSOR 
	INTO	@ServerName
			
	While (@@FETCH_STATUS <> -1)

	BEGIN

		EXEC ZZ_execRemoteProcedure @ServerName,'ZZ_TicketTransactions',@Parameters,'ZZ_rptTicketsSummary','ZZ_rptTicketsSummary'

		Fetch NEXT 
		FROM MY_CURSOR 
		INTO	@ServerName

	END

	CLOSE MY_CURSOR
	DEALLOCATE MY_CURSOR


	IF (@@ERROR <> 0)
	BEGIN
		DECLARE @MYERR AS NVARCHAR(MAX)
		
		SELECT @MYERR = ERROR_MESSAGE()
		RAISERROR(@MYERR, 20, 1)
		RETURN -1
	END
	
END

This is the line that runs the actual EXACTLY same stored procedures:
EXEC ZZ_execRemoteProcedure @ServerName,'ZZ_TicketTransactions',@Parameters,'ZZ_rptTicketsSummary','ZZ_rptTicketsSummary'

ZZ_TicketTransactions is the EXACT SAME SPROC.

When i run ZZ_TicketTransactions on the separate databases the execution plan compares EXACTLY the same and the execution time is give or take the same (one db is a little bigger)
When i run ZZ_TicketTransactions by using the job that runs the through the sproc (ZZ_execRemoteProcedure) then execution plan is not the same and one DB finishes in 1 second, while the other in 4 minutes. Bear in mind that the 4 minutes is the time that the sprocs will finish if i do not run them through the sproc. So the first sproc that does one second is down 4 minutes without using the ZZ_execRemoteProcedure sproc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources