October 31, 2012 at 5:10 am
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
October 31, 2012 at 5:13 am
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
October 31, 2012 at 5:46 am
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
October 31, 2012 at 5:49 am
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
October 31, 2012 at 6:58 am
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
October 31, 2012 at 7:17 am
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
October 31, 2012 at 7:30 am
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.
October 31, 2012 at 7:45 am
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
October 31, 2012 at 7:47 am
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
October 31, 2012 at 7:54 am
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
October 31, 2012 at 8:03 am
How many rows are returned by the SELECT version of the first update in your script?
SELECT 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
You might also want to change the update slightly;
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
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 31, 2012 at 8:43 am
Was the Tempdb log previously capped?
No, it wasn't, but it is now (10 GB).
@ChrisM@Work
Number of recs returned by your SELECT version of the query is unknown but large...it has now been running for 28 minutes, no 34 minutes; and is still going!!
What does SELECT PatientKey = P.PatientKey, AgeKey = PM.AgeKey ... mean - in a SELECT query that is? are the assignments just ignored?
Hmm, just looked at the PatienMonths table - it has 47303750 records and occupies 2,469.805 MB data space and 816.430 MB index space!
MarkD
October 31, 2012 at 9:20 am
There’s your problem. You’re only updating a maximum of a few tens of thousands of rows in the target table, but the source – now rejigged as a SELECT for this purpose, is returning millions. The SELECT shouldn’t return more than one row per target row. In some RDBMS it would throw an error, and SQL Server is heading in the right direction with MERGE, which won’t allow more than one source row per target row.
Flag it up with the vendor as a lemon, it’s cost you time and money and justifies an immediate rewrite.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 31, 2012 at 9:27 am
What does SELECT PatientKey = P.PatientKey, AgeKey = PM.AgeKey ... mean - in a SELECT query that is? are the assignments just ignored?
Its practiacally the same as doing : Select P.PaitentKey AS PatientKey,.......
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 31, 2012 at 9:29 am
ChrisM@Work (10/31/2012)
There’s your problem. You’re only updating a maximum of a few tens of thousands of rows in the target table, but the source – now rejigged as a SELECT for this purpose, is returning millions. The SELECT shouldn’t return more than one row per target row. In some RDBMS it would throw an error, and SQL Server is heading in the right direction with MERGE, which won’t allow more than one source row per target row.Flag it up with the vendor as a lemon, it’s cost you time and money and justifies an immediate rewrite.
+1, The supplier should have someone on hand to deal with this, and I wouldnt accept that the 'relevant' person is on holiday as an excuse either.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply