April 5, 2012 at 10:49 am
Every night a series of stored procedures process nightly download (~10,000 records) and populate temporary tables. These stored procedures use cursors (see below). The procedures run without problems for 2 years. One week ago the database was moved to a new server. 5 days the procedures run without problem. Last night they stuck. One record is now processed ~ 1 min (normally 10,000 records are processed within 5 minutes). Any idea why?
Note that I am not an administrator (just a developer).
Please, help.
FUI: This is one of the procedures
ALTER PROCEDURE [dbo].[sp_CodeFour]
AS
BEGIN
SET NOCOUNT ON;
Declare
-- For CurCode4
@RowNum nvarchar(50),
@SITE nvarchar(2),
@Hospital nvarchar(50),
@LOCATION nvarchar(10),
@Real_Date_Time datetime,
@NURSE_ID nvarchar(10),
@PRIMARY_NAME nvarchar(255),
@ERROR_CODE nvarchar(3),
@PMP nvarchar(10),
@PAT_NUM nvarchar(20),
@UniqueID nvarchar(30),
@PTNAME nvarchar(50),
@SCANNED_DATA nvarchar(60),
@DRUG_DESC_SCAN_ERR nvarchar(255),
@Sig nvarchar(20),
@Sig_Desc nvarchar(30),
@Frequency nvarchar(10),
@SCH_PRN nvarchar(10),
-- for CurCodes
@RowNum1 nvarchar(50),
@SITE1 nvarchar(2),
@Hospital1 nvarchar(50),
@LOCATION1 nvarchar(10),
@Real_Date_Time1 datetime,
@NURSE_ID1 nvarchar(10),
@PRIMARY_NAME1 nvarchar(255),
@ERROR_CODE1 nvarchar(3),
@PMP1 nvarchar(10),
@PAT_NUM1 nvarchar(20),
@UniqueID1 nvarchar(30),
@PTNAME1 nvarchar(50),
@SCANNED_DATA1 nvarchar(60),
@DRUG_DESC_SCAN_ERR1 nvarchar(255),
@Sig1 nvarchar(20),
@Sig_Desc1 nvarchar(30),
@Frequency1 nvarchar(10),
@SCH_PRN1 nvarchar(10),
@i int,@j int, @Date datetime, @Minus30Min datetime
truncate table TempTblCode4Overrides
truncate table TempTblCode4OverridesOrphans
set @i=(select max(dbo.val(RowNum)) from tblMedsOverrides)
--############ Get all records with code 4 for the time period
declare curCode4 cursor for
Select [SITE], Hospital, Location, Real_Date_Time,NURSE_ID,PRIMARY_NAME,ERROR_CODE,
PMP, PAT_NUM, UniqueID, PTNAME, SCANNED_DATA,DRUG_DESC_SCAN_ERR, SIG,SIG_DESC, Frequency,SCH_PRN
FROM tblAllDataNoDuplicates Where Error_CODE IN ('4')
And Real_Date_Time > (Select Max(Real_Date_Time) from tblMedsOverrides)
Order by Real_Date_Time
open curCode4
Fetch next from CurCode4 into
@SITE,@Hospital,@LOCATION,@Real_Date_Time,@NURSE_ID,@PRIMARY_NAME,
@ERROR_CODE,@PMP,@PAT_NUM,@UniqueID,@PTNAME,@SCANNED_DATA,
@DRUG_DESC_SCAN_ERR,@Sig,@Sig_Desc,@Frequency,@SCH_PRN
while @@FETCH_STATUS=0
Begin --CurCode4
-- #### Check if there are other codes within 30 min before
set @Date=@Real_Date_Time
set @Minus30Min = DATEADD(minute,-30,@Real_Date_Time )
Declare CurCodes cursor for
Select [SITE], Hospital, Location, Real_Date_Time,NURSE_ID,PRIMARY_NAME,ERROR_CODE,
PMP, PAT_NUM, UniqueID, PTNAME, SCANNED_DATA,DRUG_DESC_SCAN_ERR, SIG,SIG_DESC, Frequency,SCH_PRN
FROM tblAllDataNoDuplicates Where Error_CODE not IN ('4')
And Real_Date_Time between @Minus30Min and @Date
And UniqueID = @UniqueID
Open CurCodes
Fetch next from CurCodes into
@SITE1,@Hospital1,@LOCATION1,@Real_Date_Time1,@NURSE_ID1,@PRIMARY_NAME1,
@ERROR_CODE1,@PMP1,@PAT_NUM1,@UniqueID1,@PTNAME1,@SCANNED_DATA1,
@DRUG_DESC_SCAN_ERR1,@Sig1,@Sig_Desc1,@Frequency1,@SCH_PRN1
if @@FETCH_STATUS=0
Begin -- if @@FETCH_STATUS=0
set @i = @i+1
While @@FETCH_STATUS =0
Begin --While @@FETCH_STATUS =0
Insert into TempTblCode4Overrides
(
RowNum,[SITE],Hospital,LOCATION,Real_Date_Time,NURSE_ID, PRIMARY_NAME, ERROR_CODE,
PMP,PAT_NUM,UniqueID, PTNAME, SCANNED_DATA,DRUG_DESC_SCAN_ERR,Sig,Sig_Desc,Frequency,SCH_PRN
) Values
(cast(@i as nvarchar)+'M',
@SITE1,@Hospital1,@LOCATION1,@Real_Date_Time1,@NURSE_ID1,@PRIMARY_NAME1,
@ERROR_CODE1,@PMP1,@PAT_NUM1,@UniqueID1,@PTNAME1,@SCANNED_DATA1,
@DRUG_DESC_SCAN_ERR1,@Sig1,@Sig_Desc1,@Frequency1,@SCH_PRN1
)
Fetch next from CurCodes into
@SITE1,@Hospital1,@LOCATION1,@Real_Date_Time1,@NURSE_ID1,@PRIMARY_NAME1,
@ERROR_CODE1,@PMP1,@PAT_NUM1,@UniqueID1,@PTNAME1,@SCANNED_DATA1,
@DRUG_DESC_SCAN_ERR1,@Sig1,@Sig_Desc1,@Frequency1,@SCH_PRN1
End
Insert into TempTblCode4Overrides
(
RowNum,[SITE],Hospital,LOCATION,Real_Date_Time,NURSE_ID, PRIMARY_NAME, ERROR_CODE,
PMP,PAT_NUM,UniqueID, PTNAME, SCANNED_DATA,DRUG_DESC_SCAN_ERR,Sig,Sig_Desc,Frequency,SCH_PRN
) Values
(cast(@i as nvarchar)+'M',@SITE,@Hospital,@LOCATION,@Real_Date_Time,@NURSE_ID,@PRIMARY_NAME,
@ERROR_CODE,@PMP,@PAT_NUM,@UniqueID,@PTNAME,@SCANNED_DATA,
@DRUG_DESC_SCAN_ERR,@Sig,@Sig_Desc,@Frequency,@SCH_PRN)
End --While @@FETCH_STATUS =0
Else-- if @@FETCH_STATUS<>0
Insert into TempTblCode4OverridesOrphans
(
[SITE],Hospital,LOCATION,Real_Date_Time,NURSE_ID, PRIMARY_NAME, ERROR_CODE,
PMP,PAT_NUM,UniqueID, PTNAME, SCANNED_DATA,DRUG_DESC_SCAN_ERR,Sig,Sig_Desc,Frequency,SCH_PRN
) Values
(@SITE,@Hospital,@LOCATION,@Real_Date_Time,@NURSE_ID,@PRIMARY_NAME,
@ERROR_CODE,@PMP,@PAT_NUM,@UniqueID,@PTNAME,@SCANNED_DATA,
@DRUG_DESC_SCAN_ERR,@Sig,@Sig_Desc,@Frequency,@SCH_PRN)
--end-- if @@FETCH_STATUS=0
Close CurCodes
Deallocate CurCodes
Fetch next from CurCode4 into
@SITE,@Hospital,@LOCATION,@Real_Date_Time,@NURSE_ID,@PRIMARY_NAME,
@ERROR_CODE,@PMP,@PAT_NUM,@UniqueID,@PTNAME,@SCANNED_DATA,
@DRUG_DESC_SCAN_ERR,@Sig,@Sig_Desc,@Frequency,@SCH_PRN
End --CurCode4
close CurCode4
deallocate curCode4
END --procedure
April 5, 2012 at 11:59 am
Best to tell us what changed between the 2 servers:
CPU change?
Memory change?
SQL Version change?
Windows Version change?
IO Subsystem change?
Activity higher?
More databases?
Server settings change?
SQL Server settings change?
Network change?
Then... What changed between the day that it ran ok and the day that it did not?
Jared
CE - Microsoft
April 5, 2012 at 12:02 pm
Check index fragmentation
Has the amount of data in any of the tables changed significantly
Have you checked for any other running jobs?
Check for blocking
And if possible - try and provide an execution plan
Has anybody by chance flushed the cache?
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
April 5, 2012 at 1:07 pm
Thank you guys. I trust that DBA moved the database but did not allocate additional memory. And the reason to move the db to a new server was that the memory on the old server was close to limit. Does it make sense? Trying to locate her (DBA) which is not an easy task ;-).
April 5, 2012 at 3:19 pm
if you can get rid of cursor, it would be better. (Seems the logic is not complicated in the cursor.)
April 6, 2012 at 6:09 am
Wildcat: I wish I knew another way to do it (without cursor): for each row we need to look back and check, if we have related records within 30 min - if yes - send all related records records in one table, otherwise save this "orphan" record in another table. Can you suggest something better - thank you in advance!
April 9, 2012 at 6:57 pm
Here is the code (not exactly) for you start with:
SELECT @i = max(dbo.val(RowNum)), @Date = Max(Real_Date_Time)
FROM tblMedsOverrides
INSERT INTO TempTblCode4Overrides
SELECT CAST(@i + ROW_NUMBER OVER (ORDER BY a.Real_Date_Time) AS nvarchar) + 'M' as RowNum, a.*
FROM tblAllDataNoDuplicates a
INNER JOIN tblAllDataNoDuplicates b ON a.UniqueID = b.UniqueID
WHERE b.Real_Date_Time > @Date
and a.Real_Date_Time between DateAdd(minute, -30, b.Real_Date_Time) and b.Real_Date_Time
and b.Error_Code = '4'
and a.Error_Code NOT IN ('4')
INSERT INTO TempTblCode4OverridesOrphans
..... (similiar as the above code except using LEFT JOIN ....)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply