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
Go to Top of Page

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?
Go to Top of Page

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.




Go to Top of Page
   

- Advertisement -