Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Previously well-behaved query is now causing tempdb to choke up Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 5:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 41, Visits: 936
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
Posted Wednesday, October 31, 2012 5:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:49 AM
Points: 70, Visits: 189
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
Posted Wednesday, October 31, 2012 5:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 41, Visits: 936
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
Posted Wednesday, October 31, 2012 5:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Posted Wednesday, October 31, 2012 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 41, Visits: 936
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
Posted Wednesday, October 31, 2012 7:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Posted Wednesday, October 31, 2012 7:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
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.



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
Posted Wednesday, October 31, 2012 7:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:38 AM
Points: 7,004, Visits: 8,448
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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1379310
Posted Wednesday, October 31, 2012 7:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 41, Visits: 936
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
Posted Wednesday, October 31, 2012 7:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Mark Dalley (10/31/2012)
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


Was the Tempdb log previously capped?


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1379319
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse