February 23, 2009 at 4:22 am
On a SQL 2005 platform, we have a rather peculiar problem:
We have 2 servers with identical specs. The environment is intended to be identical. The the test db on both servers has exactly the same data structures and actual data.
However a stored procedure on one runs consistently for 3 and a half minutes, while on the other the same procedure always exceeds 30 minutes. The procedure updates a table with approx 1m rows where the data has changed from a reference table. The checksum statement is used to compare the rows.
Can anyone think of why this would happen ? Are there any options that might be set differently that would cause this behavior?
February 23, 2009 at 4:32 am
Are the query plan on both servers identical? When you run both procedures using the same parameters with the recompile option, do they have difference in the execution? Also are both servers having the same activity when you check it? Could it be that one of them has blocking due to other processes and the other one not?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 23, 2009 at 4:41 am
Hi Adi,
Thanks for the reply.
The stored procedure does not take parameters.
We have carefully restricted all other processing on the machine when testing, and monitored to spot any potential blocking / conflict. However the problem still persists.
The SP is run by a system account.
February 23, 2009 at 4:47 am
You need to look at the execution plans.
February 23, 2009 at 6:26 am
I agree. I'd look at the execution plans. But you should also check the indexes on the two systems for differences in fragmentation. I'd also look at the statistics on the two systems (although statistics differences is likely to show up as different execution plans) to see if they're different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2009 at 6:57 am
I am afraid that it gets worse: The execution plans are identical. However, while using profiler I could see that on server 2 (the slow running one) the same piece of sql seems to be re-run (and a query plan logged) many thousands of times.
In addition, I have discovered that server 2 has recently had SP3 installed which server 1 (the faster server) has not.
The query is:
INSERT INTO D_Client ([CIF], [CIF_Check_Digit], [Create_Date_Key], [First_Loan_Date_Key], [First_Loan_Product_Key], [First_Salary_Date_Key], [Home_Branch_Key], [Salary_Branch_Key], [Last_Loan_Branch_Key], [Demographic_Key], [Client_Loan_Status_Key], [Salary_Info_Key], [Employer_Key], [Primary_Deposit_Account], [Client_Deposit_Status_Key], [Client_Type], [Converted_From_Omega], [Cancelled], [Create_Type], [Teller_Type], [Card_Create_Date_Key], [Teller_Key], [ABS_Score], [Internet_Banking_Start_Date_Key])
SELECT CIF, dbo.FN_Get_Check_Digit(CIF), date_key, 32767, 0, 32767, branch_key, 0, 0, 0, 0, 0, 0, 0, 0, 'Normal', 0, 0, 'Normal', 'Branch', 32767, 0, 0, 32767
FROM S_CUSM inner join D_Date ON S_CUSM.CREATE_DT = D_DATE.Bancs_Julian_Date INNER JOIN D_Branch ON S_CUSM.Home_Branch = D_Branch.Branch_Code
WHERE CIF NOT IN (SELECT CIF FROM D_CLIENT) AND D_Branch.Current_Record = 1
ORDER BY CIF
As you can see it calls a function dbo.FN_Get_Check_Digit which is defined as follows:
CREATE FUNCTION [dbo].[FN_Get_Check_Digit](@Num varchar(9))
RETURNS varchar(10)
AS
BEGIN
DECLARE @RetVal varchar(10)
DECLARE @CheckDigit int
DECLARE @Digit int
DECLARE @Length int
DECLARE @Counter int
SET @CheckDigit = 0
SET @Num = LTRIM(RTRIM(@Num))
SET @Length = LEN(@Num)
SET @Counter = 1
WHILE @Counter <= @Length
BEGIN
SET @CheckDigit = @CheckDigit + (CONVERT(int, SUBSTRING(@Num, @Counter,1)) * CONVERT(int, LEFT(((@Length + 2) - @Counter), 1)))
SET @Counter = @Counter + 1
END
SET @CheckDigit = @CheckDigit % 11
SET @CheckDigit = 11 - @CheckDigit
IF (@CheckDigit = 11) SET @CheckDigit = 0
SET @RetVal = @Num + CONVERT(CHAR(1), @CheckDigit)
RETURN @Num + CONVERT(CHAR(1), @CHECKDIGIT)
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply