August 30, 2007 at 3:47 pm
Guys,
I have the following crazy query written by someone :
select platebarcode = a.platebarcode+case
when c.sectionid is null
then '' else '-'+convert(varchar,c.sectionid) end,
a.ABIName, a.threshold,a.XDXanalyzerstatus,a.experimenter,
convert(varchar,a.experimentdate,112) expdate,
convert(varchar,a.experimentdate,120) experimentdate, exprobotdeck,
h.comment as expcomments, a.manufacturer,
convert(varchar,a.manufacturerdate,120) mfrdate, mfrrobotname, mfrrobotdeck,
f.qcstatus, conds = Lims.dbo.getPlateConditions(a.platebarcode,'C'),
condsvalues = Lims.dbo.getPlateConditions(a.platebarcode,'V'),
e.projectname, e.encrypt, e.qcproject, e.qcquadproject,
f.rnaused, f.odratio,
b.samplename encryptedid,
c.shortdesc platedesign, c.versionid,
a.experimentdate,
age=datediff(year,dateofbirth,getdate()),
d.encounterid sampleid, d.encounterid,
d.phaseid, d.classification, d.nomination, d.donclass,d.dongrade, d.prevgrade,
d.nextgrade, d.dayssincetx, d.daysfollowed, d.biopsygrade,
rankrej = case left(d.rankrej2fw,1) when '1' then 'Yes' Else 'No' end,
d.pcwmean, death = case left(d.deathfw,1) when '1' then 'Yes' Else 'No' end,
d.hospfw, d.hosprv, d.medfw, d.medrv, d.infectionfw, d.infectionrv, d.complications,
hgbdata =d.hgb+d.hgbunits, wbcdata=d.wbccount+d.wbcunits, d.sex, d.race,
d.indication, d.status, d.magrades,
d.maquiltyA, d.maquiltyB, d.macomments, d.begrades, d.bequiltyA, d.bequiltyB,
d.becomments, d.bigrades, d.biquiltyA, d.biquiltyB, d.bicomments, d.cgrades,
d.cquiltyA, d.cquiltyB, d.ccomments,
rejth = case left(d.RejThfw,1) when '1' then 'Yes' Else 'No' end,
d.RejThfw, d.rejthrv, d.cyclosporinADose, d.mycophenolateDose, d.sirolimusdose,
d.steroidsdose, d.tacrolimusdose
from Lims..tblTaqManPlateList a
inner join Lims..tblTaqManData b on a.platebarcode = b.platebarcode
inner join Lims..tblTaqManPlatemap c on a.versionid = c.versionid and b.wellname = c.wellname
left outer join Lims..Analysis d on d.encryptedid = b.samplename
inner join Lims..Project e on a.project = e.projectid
inner join Lims..tblTaqManPlateDesign g on c.designid = g.designid
left outer join Lims..tblTaqManSampleQC f on a.platebarcode = f.platebarcode
and isnull(c.sectionid,0) = isnull(f.sectionid,0)
left outer join Lims..assayPreparationItem h on a.platebarcode = h.assayplate
and isnull(c.sectionid,0) = isnull(h.section,0)
left outer join processbatch i on h.processbatchid = i.processid
where nullif(a.platebarcode,'') is not null
and nullif(b.samplename,'') is not null
and patindex('%,'+convert(varchar,c.wellname)+',%',g.sectionwells) <> 0
and isnull(i.currentStatus,'Finished') = 'Finished'
and a.xdxanalyzerstatus is not null and e.display = 1
and isnull(f.available,1) <> 0
order by b.samplename, expdate, e.description, platebarcode
It makes tempdb database to jump from 500 MB to 4 GB in a single run. The initial size of the database is 1GB. It happens in SQL Server 2005 SP2 only (we are moving from 2000 to 2005 and SQL Server 2000 has no such problem).
I've looked at sys.databases to make sure that options ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are set to OFF.
Any idea what makes tempdb to behave in a such strange way. I've already asked developers to redesign the query...
Thank you, Mike.
August 30, 2007 at 4:14 pm
Couple small items
a) remove the interesting case statements with a.* and test tempdb size
b) is nullif(expr1, '') = null more efficient then expr1 = ''?
August 30, 2007 at 4:27 pm
Hi Daryl,
Thanks for the quick reply, but what you do mean by "a.*". There is no wildcard search in this query. Do you mean anything start with the alias a??
August 30, 2007 at 5:02 pm
Daryl,
removing all case stmts doesn't impact tempdb growth....
August 30, 2007 at 6:08 pm
What's the query plan look like for that thing? Check in both 2000 and 2005.
August 31, 2007 at 1:33 am
Hi
The 'PATINDEX, convert and the use of the % wildcard will lead to a table scan. (check execution plan). I guess that the execution plan also shows that a (table) spool in tempdb.
So, I agree, we need the query plan to optimize this query.
JP
August 31, 2007 at 6:13 am
In the where clase you have scalar functions like nullif(b.samplename,'') is not null and also in your joins with the isnull(c.sectionid,0) = isnull(f.sectionid,0). These force table scans because SQL has to resolve the function on every row to do the join or check the criteria. These eliminate the use of indexes.
This is particularly bad in the joins because to join the two tables together, the query engine is going to need to collect all of this data and then join it together. Since your server has some limit of the amount of memory it can use, the query optimizer is going to tell it to generate tables in tempdb for working with the information. This is where you get all of the growth.
See if you can get the correct result set by changing your joins into something like:
left outer join Lims..tblTaqManSampleQC f on a.platebarcode = f.platebarcode
and ((c.sectionid = f.sectionid) OR (c.sectionid IS NULL AND f.sectionid IS NULL))
This less-compact criteria will allow indexes on the tables to be used and allow the optimization engine filter records it does not need before doing the join.
Do the same thing with the WHERE clause:
a.platebarcode <> '' AND a.platebarcode IS NOT NULL
August 31, 2007 at 11:09 am
Guys,
all your comments are valuable. Optimizer shows a full table scan on all tables and we're working on reducing number of joins and transforming them as you suggested. Microsoft has redesigned tempdb architecture in SQL Server 2005. The above bad written query didn't cause problems in SQL Server 2000.
Thank you again, Mike.
September 4, 2007 at 9:01 am
What happens if you get rid of the order by? As your using many calculated fields, I would think the entire result may need to be tempdb'd to do the sort . Not sure why this would be different in 2000 vs 2005 unless you have different memory management settings?
September 4, 2007 at 11:31 am
Hi,
Unfortunately removing ORDER BY clause ( 4 columns are involved) doesn’t help to prevent enormous tempdb growth…Technique suggested by Michael Earl (replacing stmts like isnull (c.sectionid,0) = isnull(f.sectionid,0) and nullif brought query to a normal state ( the same performance as it was in SQL Server 2000). Microsoft claims that it has improved optimizer and redesigned tempdb to make it better and more competitive, but it looks really bad that new algorithms deteriorate performance of some queries (in our case most important parts of the commercial application). Now I understand why so many have a lot of fears converting to 2005…
Mike.
September 4, 2007 at 11:47 am
Here is my thought on the why...I don't know if this is what the engineers at MS were thinking, so take this how you may.
In MSSQL 2000 (and 7), a lot of operations were done in memory using TempDB for storage only as necessary. This made it very difficult to manage memory on large-scale multi-user systems.
Since then, disk space has gotten very cheap and much faster. Even using a SAN has gotten very fast. It is my guess that they have switched from heavy memory operations to use of TempDB to reduce the overall memory consumption of individual processes.
Nobody has really asked if the query you are having the growth problem with was faster in SQL 2000, but even if the performance is simply comperable, you will probably find that the overall resource use is less on SQL 2005.
I think that MS has just made the assumption that nobody cares if TempDB grows pretty large and they are using it to give the database engine more memory where it is really important. If it does not hurt performance to put something on disk and it relieves memory, I think they have decided to use the disk space and allow more important processing to use the memory.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply