Stored Procedure Memory Problems

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

Transact-SQL (2005)

Stored Procedure Memory Problems

Author

Topic

sjesweak
Starting Member

10 Posts

Posted - 2007-11-18 : 17:50:19

Hello All -

I'm having a problem with memory on my sql 2005 server. I am running a stored procedure to synchronize 2 tables. I run this procedure 1 time a day to update data for accounting because they utilize Sql Reporting Services to create reports for the data they wish to see. After running the following procedure I have to stop and start the sql services to free memory that is being utilized. After restarting the services I have 533 Mb of memory used but after the following procedure runs which has approx 400K rows of data, I have approx 1.2 Gb of memory used which never because freed.

I'm pulling data from a unidata server into my sql server table that I'm replicating with it.

Any thoughts.

ALTER PROCEDURE dbo.StoreDistributionHistory_MK

AS

CREATE TABLE #tempDistributionHistory (KEY_ID varchar(50), ACCT VARCHAR(50), APPL_SOURCE NCHAR(10), CREATE_DATE DATETIME, DIST_AMT FLOAT, DIST_ID VARCHAR(20), DOC VARCHAR(20),
FUNC VARCHAR(50), MAJOR_SEQ VARCHAR(20), MINOR_SEQ VARCHAR(20), PERIOD INT, TRANS_DATE DATETIME, RPS_RCPT VARCHAR(50))

CREATE TABLE #DIST_CHANGE_TABLE (TABLENAME VARCHAR(20), KEY_ID varchar(50), ACCT VARCHAR(50), APPL_SOURCE NCHAR(10), CREATE_DATE DATETIME, DIST_AMT FLOAT, DIST_ID VARCHAR(20), DOC VARCHAR(20),
FUNC VARCHAR(50), MAJOR_SEQ VARCHAR(20), MINOR_SEQ VARCHAR(20), PERIOD INT, TRANS_DATE DATETIME, RPS_RCPT VARCHAR(50))

INSERT INTO #tempDistributionHistory
SELECT * FROM OPENQUERY (CMISERVER, 'SELECT KEY_ID, ACCT, APPL_SOURCE, CREATE_DATE, DIST_AMT, DIST_ID, DOC, FUNC,
MAJOR_SEQ, MINOR_SEQ, PERIOD_NUM, TRANS_DATE, RPS_RCPT_KEY
FROM cmi.V_DIST_HIST_MK')

INSERT INTO #DIST_CHANGE_TABLE
SELECT MIN(TABLENAME) As TABLENAME, KEY_ID , ACCT , APPL_SOURCE , CREATE_DATE, DIST_AMT , DIST_ID, DOC ,
FUNC , MAJOR_SEQ , MINOR_SEQ , PERIOD, TRANS_DATE , RPS_RCPT
FROM
(
SELECT 'TABLE_SQL' AS TABLENAME, KEY_ID , ACCT , APPL_SOURCE , CREATE_DATE, DIST_AMT , DIST_ID, DOC ,
FUNC , MAJOR_SEQ , MINOR_SEQ , PERIOD, TRANS_DATE , RPS_RCPT
FROM [tbl_Distribution-History_MK]
UNION ALL
SELECT 'TABLE_MK' AS TABLENAME, KEY_ID , ACCT , APPL_SOURCE , CREATE_DATE, DIST_AMT , DIST_ID, DOC ,
FUNC , MAJOR_SEQ , MINOR_SEQ , PERIOD, TRANS_DATE , RPS_RCPT
FROM #tempDistributionHistory
) tmp
GROUP BY KEY_ID , ACCT , APPL_SOURCE , CREATE_DATE, DIST_AMT , DIST_ID, DOC ,
FUNC , MAJOR_SEQ , MINOR_SEQ , PERIOD, TRANS_DATE , RPS_RCPT
HAVING COUNT(*) = 1
ORDER BY KEY_ID

DECLARE @TABLENAME VARCHAR(20), @KEY_ID varchar(50), @ACCT VARCHAR(50), @APPL_SOURCE NCHAR(10), @CREATE_DATE DATETIME, @DIST_AMT FLOAT, @DIST_ID VARCHAR(20), @DOC VARCHAR(20),
@FUNC VARCHAR(50), @MAJOR_SEQ VARCHAR(20), @MINOR_SEQ VARCHAR(20), @PERIOD INT, @TRANS_DATE DATETIME, @RPS_RCPT VARCHAR(50)

DECLARE DIST_REC CURSOR FOR
SELECT TABLENAME, KEY_ID , ACCT , APPL_SOURCE , CREATE_DATE, DIST_AMT , DIST_ID, DOC ,
FUNC , MAJOR_SEQ , MINOR_SEQ , PERIOD, TRANS_DATE , RPS_RCPT
FROM #DIST_CHANGE_TABLE ORDER BY TABLENAME DESC

OPEN DIST_REC

FETCH NEXT FROM DIST_REC INTO @TABLENAME, @KEY_ID , @ACCT , @APPL_SOURCE , @CREATE_DATE, @DIST_AMT , @DIST_ID, @DOC ,
@FUNC , @MAJOR_SEQ , @MINOR_SEQ , @PERIOD, @TRANS_DATE , @RPS_RCPT

WHILE @@FETCH_STATUS = 0

BEGIN

IF @TABLENAME = 'TABLE_SQL'
BEGIN
DELETE FROM [tbl_Distribution-History_MK] WHERE KEY_ID = @KEY_ID
END
IF @TABLENAME = 'TABLE_MK'
BEGIN
INSERT INTO [tbl_Distribution-History_MK] (KEY_ID , ACCT , APPL_SOURCE , CREATE_DATE, DIST_AMT , DIST_ID, DOC ,
FUNC , MAJOR_SEQ , MINOR_SEQ , PERIOD, TRANS_DATE , RPS_RCPT)
VALUES (@KEY_ID , @ACCT , @APPL_SOURCE , @CREATE_DATE, @DIST_AMT , @DIST_ID, @DOC ,
@FUNC , @MAJOR_SEQ , @MINOR_SEQ , @PERIOD, @TRANS_DATE , @RPS_RCPT)
END

FETCH NEXT FROM DIST_REC INTO @TABLENAME, @KEY_ID , @ACCT , @APPL_SOURCE , @CREATE_DATE, @DIST_AMT , @DIST_ID, @DOC ,
@FUNC , @MAJOR_SEQ , @MINOR_SEQ , @PERIOD, @TRANS_DATE , @RPS_RCPT

END

CLOSE DIST_REC
DEALLOCATE DIST_REC

UPDATE tbl_TableUpdateTime
SET TIME_LAST_UPDATED = GetDate()
WHERE TABLE_NAME = 'DISTRIBUTION-HISTORY'

DELETE #tempDistributionHistory
DELETE #DIST_CHANGE_TABLE

RETURN

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-18 : 20:37:31

Is it dedicated sql server? Let sql use memory if so. Otherwise, you can set max memory to smaller number in sql.

sjesweak
Starting Member

10 Posts

Posted - 2007-11-19 : 01:49:18

It is a dedicated SQL server but it is on VMware which is sharing a domain controller for memory. How would the small memory setting work when sql needs memory? Can you explain how this works please?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-19 : 03:29:39

It is normal that SQL server is allocating memory.
SQL server has its own cache.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-19 : 23:39:44

Should you allocate memory for each VM?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-20 : 04:00:56

If your DC is on VM, then it should only need 128Mb to run correctly as I would guess you do not have very many users.