Previously well-behaved query is now causing tempdb to choke up

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Previously well-behaved query is now causing...

16 posts, Page 1 of 2

1 2 »»

Previously well-behaved query is now causing tempdb to choke up

Rate Topic

Display Mode

Topic Options

Author

Message

Mark Dalley

Posted Wednesday, October 31, 2012 5:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:57 AM
Points: 39, Visits: 788

Hello Experts

Running (trying to run!) on Microsoft SQL Server Enterprise Edition (64-bit), version 10.0.2531.0 (SP1)
Virtual instance, 16384 MB memory.

I am "accidentally" responsible for the regular running of an 3rd-party SQL server DB (running on SQLSVR 2008). Up to last month it has performed pretty well, but this month one particular query (which used to complete in a matter of minutes) is now causing tempdb to expand until (after some hours) it fills up a 60 GB disk. Something is definitely not right.

The message being emitted by the application is as follows
"Could not allocate space for object 'dbo.SORT temporary run storage: 140737515421696' in database 'tempdb' because the PRIMARY filegroup is full. Create disk space by etc etc...
The transaction log for database 'tempdb' is full. To find out why the apace in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
Exception Target Site: RunLoadProcessStep

(The log_reuse_wait_desc field in sys.databases comes back with "NOTHING" for all DBs except model, which is "LOG_BACKUP".)

The internal application diagnostics indicate that the culprit is the following routine, which I have pasted from SSMS ... > Script Stored Prcedure To > ALTER To > New Query Editor window.

ALTER PROCEDURE [dbo].[spLoadOPPatientKeysUpdate]
AS

BEGIN
SET NOCOUNT ON

DECLARE @intError int

UPDATE dbo.LoadConsultationsOP
SET PatientKey = P.PatientKey
, AgeKey = PM.AgeKey
, GenderKey = P.GenderKey
, GenderCode = P.GenderCode
, GPPracticeKey = P.GPPracticeKey
, GPCodeKey = P.GPCodeKey
-- , PCTKey = P.PCTKey
--, PCTCode = P.PCTCode
--Added on 11/04/2007
, GPPracticeCode = P.GPPracticeCode
--
, LocationKey = P.LocationKey
, DeprivationIMDKey = PM.DeprivationIMDKey
, DeprivationNationalKey = PM.DeprivationNationalKey
FROM dbo.LoadConsultationsOP SCOP
INNER JOIN dbo.Patient P ON SCOP.PatientId = P.PatientId
INNER JOIN dbo.PatientMonths PM ON P.PatientKey = PM.PatientKey
AND SCOP.DatePeriodKey = PM.DatePeriodKey

SET @intError = @@ERROR
IF (@intError <> 0)
BEGIN
RETURN @intError
END

--OP Consultations AgeCode corrections
--Update AgeCode by matching agekey in ConsultationsOP with
--RegAge
UPDATE
dbo.LoadConsultationsOP
SET
AgeCode = RA.AgeInYears
FROM
dbo.LoadConsultationsOP LCOP
INNER JOIN dbo.RefAge RA ON LCOP.AgeKey = RA.AgeKey

UPDATE dbo.LoadConsultationsOP
SET HRGVersion='3.5' WHERE IsSourceSUS=0

UPDATE dbo.LoadConsultationsOP
SET HRGVersion='4.0' WHERE IsSourceSUS=1

SET @intError = @@ERROR
IF (@intError <> 0)
BEGIN
RETURN @intError
END

RETURN 0
END

GO

It seems innocuous enough, but as my SQL Server knowledge is strictly limited, I don't know what to do next. I suspect that the answer is obvious to some of you however.

Looking forward to your instruction and TIA

Mark Dalley

Post #1379245

RedDoorSQL

Posted Wednesday, October 31, 2012 5:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 09, 2013 6:15 AM
Points: 44, Visits: 131

How large are the data set which its trying to update? small at 2k or 2million.

you ,might want to look at carrying our the updates in a batched process happen which would stop the tempdb from growing

Post #1379248

Mark Dalley

Posted Wednesday, October 31, 2012 5:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:57 AM
Points: 39, Visits: 788

LoadConsultationsOP is 79037 rows, data space is 36.055 MB, plus 0.141 MB index space.

Sorry, don't understand your suggestion re batched process, but that's no doubt just me - please explain.

Thanks

Mark Dalley

Post #1379261

Jason-299789

Posted Wednesday, October 31, 2012 5:49 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803, Visits: 2,122

It seem that the server is running low on disk space are DB's, TempDb, Logs all on the same drive?

You might also want to try adding Where clauses to the Updates as at the moment you are running updates against a possible substantial dataset when really you only need to run them against those rows that have changed

Eg

UPDATE 
    SCOP 
SET PatientKey = P.PatientKey 
, AgeKey = PM.AgeKey 
, GenderKey = P.GenderKey 
, GenderCode = P.GenderCode 
, GPPracticeKey = P.GPPracticeKey 
, GPCodeKey = P.GPCodeKey 
-- , PCTKey = P.PCTKey 
--, PCTCode = P.PCTCode 
--Added on 11/04/2007 
, GPPracticeCode = P.GPPracticeCode 
-- 
, LocationKey = P.LocationKey 
, DeprivationIMDKey = PM.DeprivationIMDKey 
, DeprivationNationalKey = PM.DeprivationNationalKey 
FROM dbo.LoadConsultationsOP SCOP 
INNER JOIN dbo.Patient P ON SCOP.PatientId = P.PatientId 
INNER JOIN dbo.PatientMonths PM ON P.PatientKey = PM.PatientKey 
AND SCOP.DatePeriodKey = PM.DatePeriodKey 
WHERE 
	SCOP.PatientKey != P.PatientKey 
	OR SCOP.AgeKey != PM.AgeKey 
	OR SCOP.GenderKey != P.GenderKey 
	OR SCOP.GenderCode != P.GenderCode 
	OR SCOP.GPPracticeKey != P.GPPracticeKey 
	OR SCOP.GPCodeKey = P.GPCodeKey 
	OR SCOP.GPPracticeCode = P.GPPracticeCode 
	OR SCOP.LocationKey = P.LocationKey 
	OR SCOP.DeprivationIMDKey = PM.DeprivationIMDKey 
	OR SCOP.DeprivationNationalKey = PM.DeprivationNationalKey 

///////////////////////////

UPDATE 
	LCOP
SET 
AgeCode = RA.AgeInYears 
FROM 
dbo.LoadConsultationsOP LCOP 
INNER JOIN dbo.RefAge RA ON LCOP.AgeKey = RA.AgeKey 
WHERE 
	LCOP.AgeCode!=RA.AgeInYears

You can also probably run the last two updates as one statement rather than 2

UPDATE 
    dbo.LoadConsultationsOP
set
    HRGVersion=Case isSourceSUS When 0 then '3.5' When 1 then '4.0' End
where
    HRGVersion != Case isSourceSUS When 0 then '3.5' When 1 then '4.0' End

Or if you want to keep it as two add "AND HRGVersion!='3.5' | '4.0'" to the relevant where clause on each statement

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1379263

Mark Dalley

Posted Wednesday, October 31, 2012 6:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:57 AM
Points: 39, Visits: 788

Hi Jason

The master/model/MSDB/tempdb data and logs are all on C:. Total size of everything except tempdb is 41 MB, which is trivial.

The tempdb.ldf file is about 965 MB and the tempdb.mdf file is currently at 51,200 MB. The C: drive is 89.9 GB with current free space of 21.5 GB.

Yes I know it's can be a bad thing to fill up the system drive, so I have now capped the max size of the tempdb.mdf file to 60000 MB. Surely that ought to suffice!

I take your point about unnecessary updating though. As I am a bit nervous about about tweaking 3rd party code, what is the best way to rename/add an sp so I can backtrack if necessary using SSMS? Sorry to be so basic - I have never had to even consider doing this before.

NB I have taken a backup!

MarkD

Post #1379288

Jason-299789

Posted Wednesday, October 31, 2012 7:17 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803, Visits: 2,122

I didnt realise it was 3rd party code that you were looking at, in which case I would contact the 3rd party, rather than making a change, as it could invalidate any support contract you have.

As you have the message

"The transaction log for database 'tempdb' is full. To find out why the apace in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases."

Have you also got a restriction on the TempDB log file as this should grow to fill the remaining 21.5Gb of free space, if you have remove the restriction or set it to 10GB (10,000 mb) to see if that helps the query run through.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1379295

Lynn Pettis

Posted Wednesday, October 31, 2012 7:30 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 21,588, Visits: 27,384

Can you add additional disks to your server? I would highly recommend moving tempdb to its own drive, and preferably a large one just in case.

If this has been working fine until just recently, you may have reached a point where your data has exceeded what the system was originally designed to work with and definately needs some tuning by the vendor.

Adding disk is only a band aid here but is the first step I would take while getting the vendor to look at their code and hopefully modify it to work more effeciently.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1379300

ALZDBA

Posted Wednesday, October 31, 2012 7:45 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:25 AM
Points: 6,861, Visits: 8,045

you could also try to fetch SQLPlan data out of your sql engine, or post the estimated execution plan.

Using SqlServer Management Studio, you could highlight the actual code withing the sproc and then, using the menu, go for Query \ display estimated execution plan.

It will produce a graphical execution plan. Right click on it and you'll be able to save it to a file and attach it to your reply.

or you could use my powershell script to extract the sqlplan from the current cache and capture some execution statistics with it.
You can find the PoSh script at http://poshcode.org/3730
Just add an extra filter to the query

and p.name = ''yoursprocname''

( double single quotes !! )

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #1379310

Mark Dalley

Posted Wednesday, October 31, 2012 7:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 4:57 AM
Points: 39, Visits: 788

I can't get hold of our IT people at the minute so will cap the size of the tempdb log at 10 GB and re-run. Don't expect it to work, somehow, but it won't be a drive-full error at least.

The relevant person at the vendor isn't back until next week.

Is there anything in the way of traces or logs that would be sensible to put in place so we have more info if it again dies horribly?

Thanks

MarkD

Post #1379311

Jason-299789

Posted Wednesday, October 31, 2012 7:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 03, 2013 5:35 AM
Points: 803, Visits: 2,122

Mark Dalley (10/31/2012)

Was the Tempdb log previously capped?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1379319

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions