# How to know what to investigate and how for a VERY slow stored procedure

I have inherited a large and slow stored procedure and it's giving me a nightmare:

I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck.

I have SQL Server 2008 installed on my desktop with an exact copy of the production database. I am running everything from SSMS and I have tried straight SQL and SP. Time for SP vs SQL is close enough to the same to not worry unduly about - its the time of local vs server that's concerning me.

Local:

• 2Ghz dual-core
• 4Gb RAM
• SQL Server 2008 Sp1
• Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
• Enterprise Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

Server:

• 2Ghz dual-core
• 4Gb RAM
• Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)
• Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) (VM)

When I run "locally", it takes around 10 minutes to complete.

When I run against the server, it takes around 14 hours to complete!!!

[Total inserts approx = 14,000,000]

I have tried to tune the SP as much as I can and avoided parameter sniffing but not sure WHY this should be SO much slower on a pretty much equivalent machine (I know SQL Server version is slightly different, but can't see why it would make THAT much difference?)

I know the SP isn't fantastic, but if it runs in 10 minutes locally, then its not total pants! If it took 60 seconds on the server then that would still be fine.

Server has a pretty low load on it when these SP's are not running, and around 50% CPU when they are in progress (one core 100%?). Locally CPU never gets above 25%.

I've broken down the SP so it is called a number of times in the hope that might help...it did locally, but not by much, but made no difference on the server.

Script to call the SP

``````USE master
GO

ALTER DATABASE MISReportInterface
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE MISReportInterface
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

USE MISReportInterface

EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''CG'', @inClearTable=1, @inStartNum=0, @inEndNum=1000000'
print 'CG'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''CP'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'CP'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''DA'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'DA'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''DB'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'DB'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''ED'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'ED'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EE'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EE'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EF'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EF'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EG'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EG'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EH'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EH'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''ES'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'ES'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EX'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EX'

EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=0, @inEndNum=150000'
print 'EA 0 150000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=150000, @inEndNum=160000'
print 'EA 150000 160000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=160000, @inEndNum=170000'
print 'EA 160000 170000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=170000, @inEndNum=180000'
print 'EA 170000 180000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=180000, @inEndNum=190000'
print 'EA 180000 190000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=190000, @inEndNum=200000'
print 'EA 190000 200000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=200000, @inEndNum=210000'
print 'EA 200000 210000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=210000, @inEndNum=220000'
print 'EA 210000 220000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=220000, @inEndNum=230000'
print 'EA 220000 230000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=230000, @inEndNum=240000'
print 'EA 230000 240000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=240000, @inEndNum=1000000'
print 'EA 240000 1000000'

USE master

ALTER DATABASE MISReportInterface
SET MULTI_USER;
GO
``````

Apols for posting the whole SP...but can't see how else to show what its doing in case there is an option that can be set to improve things :(

``````ALTER PROCEDURE [dbo].[LoadProfitabilitySummaryByCustomer]
@inCustomerType char(2),
@inClearTable int = 0,
@inStartNum int,
@inEndNum int
WITH RECOMPILE
AS
BEGIN
SET ANSI_NULLS ON
DECLARE @customers TABLE (
CUSNUM CHAR(6) DEFAULT 0
,IILQAR DECIMAL(20,5) DEFAULT 0
,IILUSD DECIMAL(20,5) DEFAULT 0
,IILGPB DECIMAL(20,5) DEFAULT 0
,IILEUR DECIMAL(20,5) DEFAULT 0
,IILKWD DECIMAL(20,5) DEFAULT 0
,IILOTH DECIMAL(20,5) DEFAULT 0
,IILMTD DECIMAL(20,5) DEFAULT 0
,IILYTD DECIMAL(20,5) DEFAULT 0
,TCQAR DECIMAL(20,5) DEFAULT 0
,TCUSD DECIMAL(20,5) DEFAULT 0
,TCGPB DECIMAL(20,5) DEFAULT 0
,TCEUR DECIMAL(20,5) DEFAULT 0
,TCKWD DECIMAL(20,5) DEFAULT 0
,TCOTH DECIMAL(20,5) DEFAULT 0
,TCMTD DECIMAL(20,5) DEFAULT 0
,TCYTD DECIMAL(20,5) DEFAULT 0
)

DECLARE @CustomerType char(2)
DECLARE @ClearTable int
DECLARE @StartNum int
DECLARE @EndNum int

SET @CustomerType = @inCustomerType
SET @ClearTable = @inClearTable
SET @StartNum = @inStartNum
SET @EndNum = @inEndNum

DECLARE @sCustomerNumber char(6)

DECLARE @iDaysInMonth decimal
DECLARE @iDaysInYear decimal
DECLARE @dAvgCostQAR DECIMAL(20,5)
DECLARE @dAvgCostUSD DECIMAL(20,5)
DECLARE @dAvgCostGBP DECIMAL(20,5)
DECLARE @dAvgCostEUR DECIMAL(20,5)
DECLARE @dAvgCostKWD DECIMAL(20,5)
DECLARE @dAvgCostOTH DECIMAL(20,5)
DECLARE @dAvgCostTOT DECIMAL(20,5)
DECLARE @dTIIntIncLnsQAR DECIMAL(20,5)
DECLARE @dTIIntIncLnsUSD DECIMAL(20,5)
DECLARE @dTIIntIncLnsGBP DECIMAL(20,5)
DECLARE @dTIIntIncLnsEUR DECIMAL(20,5)
DECLARE @dTIIntIncLnsKWD DECIMAL(20,5)
DECLARE @dTIIntIncLnsOTH DECIMAL(20,5)
DECLARE @dTIIntIncLnsTOT DECIMAL(20,5)
DECLARE @dTIIntIncLnsYTD DECIMAL(20,5)
DECLARE @dtMaxDate varchar(30)

DECLARE @iReportYear int
DECLARE @START_DATE datetime
DECLARE @FIRST_MONTH datetime
DECLARE @END_MONTH datetime
DECLARE @iNumMonths int
DECLARE @iNumDaysToday int

INSERT INTO @customers (CUSNUM)
SELECT DISTINCT CUSNUM
FROM EQPRF_SUMMARY
WHERE CUSTYP = @CustomerType
AND CUSNUM >= @StartNum
AND CUSNUM < @EndNum
GROUP BY CUSNUM

UPDATE @customers
SET IILQAR = t2.Amount
FROM @customers c INNER JOIN
(
SELECT CUSNUM,
-SUM(ISNULL(CONVERT(decimal(20,5),t.TOTALMTDLCY),0)) as Amount
FROM TI_INTINCLOANS t
WHERE t.CCY = 'IILQAR'
AND t.LINEID = 'LN17'
GROUP BY t.CUSNUM
) AS t2
ON c.CUSNUM = t2.CUSNUM

UPDATE @customers
SET IILUSD = t2.Amount
FROM @customers c INNER JOIN
(
SELECT CUSNUM,
-SUM(ISNULL(CONVERT(decimal(20,5),t.TOTALMTDLCY),0)) as Amount
FROM TI_INTINCLOANS t
WHERE t.CCY = 'IILUSD'
AND t.LINEID = 'LN17'
GROUP BY t.CUSNUM
) AS t2
ON c.CUSNUM = t2.CUSNUM

--REPEAT FOR 14 OTHER CRITERIA

IF (@ClearTable = 1)
BEGIN
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_GROUP_ACCT') DROP INDEX IX_GROUP_ACCT ON PROFITABILITY_SUMMARY

TRUNCATE TABLE [PROFITABILITY_SUMMARY]

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_GROUP_ACCT')
CREATE NONCLUSTERED INDEX IX_GROUP_ACCT
ON dbo.PROFITABILITY_SUMMARY(CUST_TYPE,GROUP_CODE,ACCOUNT)
WITH (FILLFACTOR = 90,PAD_INDEX = ON);
END

IF MONTH(getdate())= 1 SET @iReportYear = YEAR(DATEADD(m,-1,getdate()))  -- get last month's year
ELSE SET @iReportYear = YEAR(getdate())

SET @START_DATE = CAST('1/1/'+cast(@iReportYear as varchar(4)) as datetime) --first day of report year (If Jan, it's last year)
SET @FIRST_MONTH = DATEADD(day,-DAY(getdate())+1,getdate())--first day of current month
SET @END_MONTH = DATEADD(day,-1,@FIRST_MONTH)--determine last day of the previous month
SET @FIRST_MONTH = DATEADD(M,-1,@FIRST_MONTH)--reset to first day of the previous month
SET @FIRST_MONTH = CAST(FLOOR( CAST( @FIRST_MONTH AS FLOAT ) ) AS DATETIME) --TRUNCATE HOURS
SET @END_MONTH = CAST(FLOOR( CAST( @END_MONTH AS FLOAT ) ) AS DATETIME) --TRUNCATE HOURS

SELECT TOP 1
@dAvgCostQAR = AVGCOSTQAR
,@dAvgCostUSD = AVGCOSTUSD
,@dAvgCostGBP = AVGCOSTGBP
,@dAvgCostEUR = AVGCOSTEUR
,@dAvgCostKWD = AVGCOSTKWD
,@dAvgCostOTH = AVGCOSTOTH
,@dAvgCostTOT = AVGCOSTTOT
FROM dbo.PRFCOSTF

SET @dtMaxDate = (select top 1 s.txnstmp from EQPRF_SUMMARY s)
SELECT @iDaysInMonth = dbo.ufn_GetDaysInMonth(CAST(@dtMaxDate as datetime))
SELECT @iDaysInYear = 360
SET @iNumDaysToday = (DATEDIFF(dd, @START_DATE, @END_MONTH)+1)

DECLARE CustomerCursor CURSOR FAST_FORWARD
FOR
SELECT CUSNUM
FROM @customers

OPEN CustomerCursor

FETCH NEXT FROM CustomerCursor
INTO @sCustomerNumber

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT
@dTIIntIncLnsQAR = c.IILQAR
,@dTIIntIncLnsUSD = c.IILUSD
,@dTIIntIncLnsGBP = c.IILGPB
,@dTIIntIncLnsEUR = c.IILEUR
,@dTIIntIncLnsKWD = c.IILKWD
,@dTIIntIncLnsOTH = c.IILOTH
,@dTIIntIncLnsTOT = c.IILMTD
,@dTIIntIncLnsYTD = c.IILYTD
FROM @customers c
WHERE c.CUSNUM = @sCustomerNumber

INSERT INTO PROFITABILITY_SUMMARY
SELECT SORT_ORDER = 1, LINE_NO = 'LN01', RPT_DATE=cast(TXNSTMP as datetime),
LINE_TITLE='Month Loans Outstanding',
ACCOUNT = RTRIM(CUSNUM),
CUST_TYPE = RTRIM(CUSTYP),
GROUP_CODE = (CASE
WHEN RTRIM(CUSGRP) IS NULL OR RTRIM(CUSGRP)='' THEN LEFT(CUSNAME,3)+'--'+RTRIM(CUSNUM)
ELSE RTRIM(CUSGRP)
END),
PARENT_COUNTRY = RTRIM(CUSNAP),
RM_CODE = RTRIM(CUSACO),
RM_NAME = RTRIM(CUSRNAM),
CUST_NAME = RTRIM(CUSNAME),
RISK_CODE = RTRIM(CUSRSKE),
QAR = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='QAR'),0),
USD = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='USD'),0),
GBP = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='GBP'),0),
EUR = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='EUR'),0),
KWD = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='KWD'),0),
OTHER = ISNULL((SELECT sum(CAST(CURDLAMT AS DECIMAL(20,5))) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY NOT IN ('QAR', 'USD', 'GBP', 'EUR', 'KWD')),0),
TOTAL = SUM(CAST(CURDLAMT AS DECIMAL(20,5))),
YTD = 0
FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY] where CUSNUM=@sCustomerNumber
GROUP BY CUSNUM,CUSTYP,CUSGRP,CUSNAP,CUSACO,CUSRNAM,CUSNAME,CUSRSKE,TXNSTMP

UNION ALL
SELECT SORT_ORDER = 2,LINE_NO = 'LN02', RPT_DATE=cast(TXNSTMP as datetime),
LINE_TITLE ='Average Loans Outstanding',
ACCOUNT = RTRIM(CUSNUM),
CUST_TYPE = RTRIM(CUSTYP),
GROUP_CODE = (CASE
WHEN RTRIM(CUSGRP) IS NULL OR RTRIM(CUSGRP)='' THEN LEFT(CUSNAME,3)+'--'+RTRIM(CUSNUM)
ELSE RTRIM(CUSGRP)
END),
PARENT_COUNTRY = RTRIM(CUSNAP),
RM_CODE = RTRIM(CUSACO),
RM_NAME = RTRIM(CUSRNAM),
CUST_NAME = RTRIM(CUSNAME),
RISK_CODE = RTRIM(CUSRSKE),
QAR = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='QAR'),0),
USD = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='USD'),0),
GBP = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='GBP'),0),
EUR = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='EUR'),0),
KWD = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='KWD'),0),
OTHER = ISNULL((SELECT SUM(CAST(LNAVGMTD AS DECIMAL(20,5))) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY NOT IN ('QAR', 'USD', 'GBP', 'EUR', 'KWD')),0),
TOTAL = SUM(CAST(LNAVGMTD AS DECIMAL(20,5))),
YTD = SUM(CAST(LNAVGYTD AS DECIMAL(20,5)))
FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY] where CUSNUM=@sCustomerNumber
GROUP BY CUSNUM,CUSTYP,CUSGRP,CUSNAP,CUSACO,CUSRNAM,CUSNAME,CUSRSKE,TXNSTMP
------------------------------------------------------------------------------
--THERE ARE 32 LINES IN TOTAL...ALL FOLLOWING ROUGHLY SAME PATTERN
------------------------------------------------------------------------------
UNION ALL
SELECT SORT_ORDER = 32,LINE_NO = 'GRANTOTAL',RPT_DATE=cast(TXNSTMP as datetime),
LINE_TITLE ='Total Income',
ACCOUNT = RTRIM(CUSNUM),
CUST_TYPE = RTRIM(CUSTYP),
GROUP_CODE = (CASE
WHEN RTRIM(CUSGRP) IS NULL OR RTRIM(CUSGRP)='' THEN LEFT(CUSNAME,3)+'--'+RTRIM(CUSNUM)
ELSE RTRIM(CUSGRP)
END),
PARENT_COUNTRY = RTRIM(CUSNAP),
RM_CODE = RTRIM(CUSACO),
RM_NAME = RTRIM(CUSRNAM),
CUST_NAME = RTRIM(CUSNAME),
RISK_CODE = RTRIM(CUSRSKE),
QAR = @dTITradeCommQAR+@dTIIntIncLnsQAR+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD  AS DECIMAL(20,5))+CAST(OTRINCMTD  AS DECIMAL(20,5))+CAST(EXCHINCMTD  AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='QAR'),0),
USD = @dTITradeCommUSD+@dTIIntIncLnsUSD+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD  AS DECIMAL(20,5))+CAST(OTRINCMTD  AS DECIMAL(20,5))+CAST(EXCHINCMTD  AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='USD'),0),
GBP = @dTITradeCommGBP+@dTIIntIncLnsGBP+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD  AS DECIMAL(20,5))+CAST(OTRINCMTD  AS DECIMAL(20,5))+CAST(EXCHINCMTD  AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='GBP'),0),
EUR = @dTITradeCommEUR+@dTIIntIncLnsEUR+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD  AS DECIMAL(20,5))+CAST(OTRINCMTD  AS DECIMAL(20,5))+CAST(EXCHINCMTD  AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='EUR'),0),
KWD = @dTITradeCommKWD+@dTIIntIncLnsKWD+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD  AS DECIMAL(20,5))+CAST(OTRINCMTD  AS DECIMAL(20,5))+CAST(EXCHINCMTD  AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='KWD'),0),
OTHER=@dTITradeCommOTH+@dTIIntIncLnsOTH+ISNULL((SELECT SUM(CAST(SUSINTMTD AS DECIMAL(20,5)))+ SUM(CAST(INTINCMTD AS DECIMAL(20,5)))-SUM(CAST(COSTLNMTD AS DECIMAL(20,5)))+SUM(CAST(COSTDPMTD AS DECIMAL(20,5))) - SUM(CAST(INTEXPMTD AS DECIMAL(20,5)))+SUM(CAST(LNFEEMTD AS DECIMAL(20,5)))+SUM(CAST(OTRINCMTD  AS DECIMAL(20,5)))+SUM(CAST(EXCHINCMTD  AS DECIMAL(20,5))) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY NOT IN ('QAR', 'USD', 'GBP', 'EUR', 'KWD')),0),
TOTAL = @dTITradeCommTOT+@dTIIntIncLnsTOT+ SUM(CAST(SUSINTMTD AS DECIMAL(20,5)))+SUM(CAST(INTINCMTD AS DECIMAL(20,5)))-SUM(CAST(COSTLNMTD AS DECIMAL(20,5)))+SUM(cast(COSTDPMTD as DECIMAL(20,5))) - SUM(CAST(INTEXPMTD AS DECIMAL(20,5)))+SUM(CAST(LNFEEMTD  AS DECIMAL(20,5)))+SUM(CAST(OTRINCMTD  AS DECIMAL(20,5)))+SUM(CAST(EXCHINCMTD  AS DECIMAL(20,5))),
YTD = @dTITradeCommYTD+@dTIIntIncLnsYTD+ SUM(CAST(SUSINTYTD AS DECIMAL(20,5)))+SUM(CAST(INTINCYTD AS DECIMAL(20,5)))-SUM(CAST(COSTLNYTD AS DECIMAL(20,5)))+SUM(cast(COSTDPYTD as DECIMAL(20,5))) - SUM(CAST(INTEXPYTD AS DECIMAL(20,5)))+SUM(CAST(LNFEEYTD  AS DECIMAL(20,5)))+SUM(CAST(OTRINCYTD  AS DECIMAL(20,5)))+SUM(CAST(EXCHINCYTD  AS DECIMAL(20,5)))

FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY] where CUSNUM=@sCustomerNumber
GROUP BY CUSNUM,CUSTYP,CUSGRP,CUSNAP,CUSACO,CUSRNAM,CUSNAME,CUSRSKE,TXNSTMP
ORDER BY 1

FETCH NEXT
FROM CustomerCursor
INTO @sCustomerNumber

END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

END
``````

It's definitely ugly - you could remove the cursor, which is going to be a significant culprit to the general slowness, creating a more set-based solution. But it depends on your general comfort in refactoring as to whether that's really a good idea.

But there are a number of things about servers you could investigate.

For example - does the database on the server have "AutoShrink" turned on? Is tempdb on a particularly slow disk?

You could compare the execution plans on the different servers to see if there's a massive difference between one and the other. One difficulty with cursors is that you can end up with a plan being worked out on an early iteration, despite it being a bad plan for later iterations. But I assume you have the same data on the two boxes, so this shouldn't really be a factor.

I'd be checking the other conditions on the server, such as disk, and other things which may be running. If your files keep growing and shrinking (for example), then this could be bad news for any query that's running there.

• Will check AutoShrink setting. You say "tempdb"...I don't reference any other DB's so is this where all temp tables are being stored? Is it better to "hard" create a table to hold my temp data and truncate it afterwards? I can re-factor the cursor into a "while" loop and re-test. Thx – BlueChippy Sep 24 '12 at 5:08
• No - a while loop is essentially the same as a cursor. Try to get all the rows in one fell swoop, instead of doing it one customer at a time. TempDB is likely to be used to store the intermediate results of your cursor, so it's important that it's not a bottleneck. Also, I wouldn't put it in SINGLE_USER mode. It won't make it faster to remove that, but you shouldn't turn on that setting without an excellent reason. – Rob Farley Sep 24 '12 at 5:13
• "Try to get all the rows in one fell swoop" - do you mean re-write to "join" my temp table with my results for all rows? – BlueChippy Sep 24 '12 at 5:16
• Yes - that's exactly what I mean. You can wrap the UNION ALL bits in parentheses, and join it to the CusNum field. But you don't really need that table variable at all - you could do it all in the one query, which could well run significantly quicker (and certainly be more tunable, since it doesn't have a reliance on so much programmatic code). – Rob Farley Sep 24 '12 at 5:24
{"ft_lang_label":"__label__en","ft_lang_prob":0.5085939,"math_prob":0.93013525,"size":18313,"snap":"2020-24-2020-29","text_gpt3_token_len":6416,"char_repetition_ratio":0.20405266,"word_repetition_ratio":0.27693143,"special_character_ratio":0.290504,"punctuation_ratio":0.1654894,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.97779304,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-06-01T05:46:45Z\",\"WARC-Record-ID\":\"<urn:uuid:0cfe64b3-83ab-42ae-9385-bde607bff39c>\",\"Content-Length\":\"166721\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:bc7ed7f7-7a10-4524-a760-c9ceca82fc72>\",\"WARC-Concurrent-To\":\"<urn:uuid:8b8899d4-4c69-4e89-a101-ea6904768f4a>\",\"WARC-IP-Address\":\"151.101.1.69\",\"WARC-Target-URI\":\"https://dba.stackexchange.com/questions/24787/how-to-know-what-to-investigate-and-how-for-a-very-slow-stored-procedure\",\"WARC-Payload-Digest\":\"sha1:BSC333FI7KX2NRQB3UD4T7NLK2U4L42V\",\"WARC-Block-Digest\":\"sha1:N2BK4LXZIGLJZMM5KNKUBVK42GXT4JYI\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-24/CC-MAIN-2020-24_segments_1590347414057.54_warc_CC-MAIN-20200601040052-20200601070052-00230.warc.gz\"}"}