December 10, 2009 at 9:35 am
Hi,
I have a problem with long running querys:
A kind of 'print server' use a big select with 15 tables/80 columns and fetch hundreds of rows one by one out of the database and process them (e.g create pdf, print, send mails, ...). This takes about 1 hour.
During that normal work is going on and it happens that a user updates a record in one of the tables, which is also in the result set of the print server. If the print server is going to process this record, it receives only null values. The next fetch is ok again.
Some more infos about the environment:
- SQL Server 2005 SP3
- Report server written in GUPTA and use odbc database connections
- isolation level ist read comitted (I don't need transaction processing)
Now my question:
How are the result set of a query 'protected', specially if updates are going on? I don't want to lock these rows.
Is it rather a environment/odbc problem, because the sql server preserves the result set?
markus
Here is the big select
SELECT CASE
WHEN MAHNHISTORY.ZUSEND = 'F' AND ISNULL( FIRMEN.PLZ, '' ) + ISNULL( FIRMEN.ORT, '') + ISNULL( FIRMEN.STR, '' ) != '' THEN FIRMEN.PLZ
WHEN MAHNHISTORY.ZUSEND = 'P' AND ISNULL( INTERESSENTEN.PLZ, '' ) + ISNULL( INTERESSENTEN.ORT, '') + ISNULL( INTERESSENTEN.STR, '' ) != '' THEN INTERESSENTEN.PLZ
WHEN MAHNHISTORY.ZUSEND = 'S' AND ISNULL( SONDERADR.PLZ, '' ) + ISNULL( SONDERADR.ORT, '') + ISNULL( SONDERADR.STR, '' ) != '' THEN SONDERADR.PLZ
WHEN ISNULL( FIRMEN.PLZ, '' ) + ISNULL( FIRMEN.ORT, '') + ISNULL( FIRMEN.STR, '' ) != '' THEN FIRMEN.PLZ
WHEN ISNULL( INTERESSENTEN.PLZ, '' ) + ISNULL( INTERESSENTEN.ORT, '') + ISNULL( INTERESSENTEN.STR, '' ) != '' THEN INTERESSENTEN.PLZ
WHEN ISNULL( SONDERADR.PLZ, '' ) + ISNULL( SONDERADR.ORT, '') + ISNULL( SONDERADR.STR, '' ) != '' THEN SONDERADR.PLZ
ELSE NULL END,
MAHNHISTORY.BUNR, MAHNHISTORY.KNR, MAHNHISTORY.JAHR, MAHNHISTORY.BEZ,
MAHNHISTORY.ERMBT1, MAHNHISTORY.ERMTX1, MAHNHISTORY.ERMBT2, MAHNHISTORY.ERMTX2,
MAHNHISTORY.ANMELD, MAHNHISTORY.FREQ,
MAHNHISTORY.ANMERK, MAHNHISTORY.WARTL, MAHNHISTORY.REIH, MAHNHISTORY.NACHR,
MAHNHISTORY.NACHDT, MAHNHISTORY.MAHNK, MAHNHISTORY.MAHNB1, MAHNHISTORY.MAHNB2, MAHNHISTORY.MAHNB3,
MAHNHISTORY.MAHND1, MAHNHISTORY.MAHND2, MAHNHISTORY.MAHND3,
MAHNHISTORY.AUSWEI, FIRMEN.TEL, INTERESSENTEN.GEBDAT, INTERESSENTEN.GEBORT,
INTERESSENTEN.GESCHL, INTERESSENTEN.TITEL, INTERESSENTEN.STR, INTERESSENTEN.PLZ, INTERESSENTEN.ORT,
INTERESSENTEN.TEL, INTERESSENTEN.FAX, INTERESSENTEN.NAME, INTERESSENTEN.VNAME,
INTERESSENTEN.BERUF, FIRMEN.BEZ, FIRMEN.STR, FIRMEN.PLZ, FIRMEN.FAX, FIRMEN.ORT, FIRMEN.BRANCHE,
VERANSTALTUNGEN.VANR, SELBSTINT.BEZ, INTERESSENTEN.BEM, INTERESSENTEN.SELBST,
MAHNHISTORY.TEILKN, MAHNHISTORY.TEILDT, MAHNHISTORY.ANZTEIL, MAHNHISTORY.RYTMUS, MAHNHISTORY.EINLK,
MAHNHISTORY.ANMT, MAHNHISTORY.EINLT, MAHNHISTORY.ANMKZ, MAHNHISTORY.KNRALT,
INTERESSENTEN.BEZUGP, MAHNHISTORY.ARTANM, INTERESSENTEN.EMAIL, INTERESSENTEN.TYP,
MAHNHISTORY.FREQTXT, INTERESSENTEN.TELFIRMA, INTERESSENTEN.FAXDURCHWAHL, INTERESSENTEN.MOBILTELEFON,
MAHNHISTORY.EXAUFTRAGSNR, INTERESSENTEN.SOZVERNR, BILDUNGSBAUSTEINE.MONITORTITEL,
MAHNHISTORY.TZANZTYP, isnull( MAHNHISTORY.TZANZBT, 0.00 ), MAHNHISTORY.STORNO,
MAHNHISTORY.TZRHYTTYP, MAHNHISTORY.TZSTARTTYP, MAHNHISTORY.TZSTARTDAT, MAHNHISTORY.ERSTETZDAT,
LANDCODE1.BEZ, LANDCODE2.BEZ,
VERANSTALTUNGEN.AMSVANR, VERANSTALTUNGEN.AMSKOSTENVOR, VERANSTALTUNGEN.AMSABRECH,
MITARBEITER.NAME, MITARBEITER.TELEFON, MITARBEITER.UNTERSCHRIFT, MITARBEITER.UNTERSCHRIFT2, MITARBEITER.EMAIL,
INTERESSENTEN.TITEL2,
SONDERADR.BEZ, SONDERADR.STR, SONDERADR.PLZ, SONDERADR.ORT, SONDERADR.LANDCODE, SONDERADR.SONDERADRBEZUGP,
MAHNHISTORY.ANMELDER, INTERESSENTEN.UID, BU_UMSATZ.UMSATZ, INTERESSENTEN.RABATTPROZ,
MAHNHISTORY.GUTSCHEINBT, MAHNHISTORY.GUTSCHEINTXT, KU_STATUS.BEZ, INTERESSENTEN.KATEGORIE,
MAHNHISTORY.ZUSENDPROFILNR, INTERESSENTEN.EXKOSTENSTELLE, MAHNHISTORY.BUNR_TEILRECHNUNG ,MAHNHISTORY.TNGEB, MAHNHISTORY.TEILBETRAG,
MAHNHISTORY.MAHNJOBFLAG, MAHNHISTORY.RECHBETRAG, MAHNHISTORY.MAHNNR,
MAHNHISTORY.BUTEILBT, MAHNHISTORY.BUANZBT, MAHNHISTORY.SCHULD , MAHNHISTORY.ZUSATZBLATT, MAHNHISTORY.ZUSENDPROFILDETAILVIEWNR , MAHNHISTORY.ZUSEND, MAHNHISTORY.ZUSENDEXTRA, ISNULL( ZUSENDPROFIL.ZUSEND, MAHNHISTORY.ZUSEND) , MAHNHISTORY.MAILPROFILNR, MAHNHISTORY.MAILPROFILNRALT , INTERESSENTEN.ABTEILUNG , INTERESSENTEN.EXPERSONALNR, INTERESSENTEN.EXKOSTENSTELLE , MAHNHISTORY.RESERVIERTBISDAT, INTERESSENTEN.LANGANREDE , INTERESSENTEN.ELLOGIN , MAHNHISTORY.ZUSATZTEXT
FROM MAHNHISTORY
inner join BU_UMSATZ on BU_UMSATZ.BUNR = MAHNHISTORY.BUNR
inner join VERANSTALTUNGEN on VERANSTALTUNGEN.VANR = MAHNHISTORY.VANR
inner join BILDUNGSBAUSTEINE on BILDUNGSBAUSTEINE.BBNR = VERANSTALTUNGEN.BBNR
inner join INTERESSENTEN on INTERESSENTEN.KUNR = MAHNHISTORY.KNR
left outer join FIRMEN on FIRMEN.FANR = MAHNHISTORY.FANR
left outer join LANDCODE LANDCODE2 on LANDCODE2.LANDCODE = FIRMEN.LANDCODE
left outer join LANDCODE LANDCODE1 on LANDCODE1.LANDCODE = INTERESSENTEN.LANDCODE
left outer join VA_ORTE on VA_ORTE.ORTNR = VERANSTALTUNGEN.ORTNR
left outer join SELBSTINT on SELBSTINT.SELBST = INTERESSENTEN.SELBST
left outer join MITARBEITER on MITARBEITER.KURZZEICH = VERANSTALTUNGEN.KURZZEICH
left outer join FIRMEN SONDERADR on SONDERADR.FANR = MAHNHISTORY.FANR_SONDER
left outer join KU_STATUS on KU_STATUS.KUSTATNR = INTERESSENTEN.KUSTATNR
left outer join ZUSENDPROFIL on ZUSENDPROFIL.ZUSENDPROFILNR = MAHNHISTORY.ZUSENDPROFILNR
WHERE INTERESSENTEN.STATUS != '$'
AND VERANSTALTUNGEN.STATUS != '$'
AND MAHNHISTORY.TSTAMP = convert( datetime, '2009-11-25 07:16:22.700', 121 )
AND MAHNHISTORY.MAHNTYPID = 'A'
AND MAHNHISTORY.MAHNDRUCK = 'J'
AND ((datediff( day, veranstaltungen.vondat, mahnhistory.anmeld ) < 0) or (datediff( day, veranstaltungen.vondat, mahnhistory.nachdt ) < 0 ))
and (datediff(day,veranstaltungen.EINLD, mahnhistory.anmeld) < 0 or veranstaltungen.EINK = 'N' or VERANSTALTUNGEN.TANG < VERANSTALTUNGEN.TMIN)
and ((Mahnhistory.TEILKN <> 'J') or (Mahnhistory.TEILKN is NULL))
ORDER BY 1, VERANSTALTUNGEN.VANR, INTERESSENTEN.NAME
December 10, 2009 at 9:51 am
Does anything perhaps insert mostly blank rows then come back and update them? Or do an update to null and then an update with the actual values? It's something I've seen before and, if they're not done within a transaction, it's possible for a query to read an intermediate value that it shouldn't
SQL locks rows for query integrity. While a query is reading rows there's a shared lock in place, other queries can read but nothing can update. While updates are occurring the rows are locked exclusively. Nothing else can read or write.
In the default isolation level shared locks are released as soon as the statement is complete, exclusive locks are released when the transaction commits
I'd suggest get Profiler out, see what the print server's actually doing and where the selects happen.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2009 at 10:29 am
thanks for answering.
Does anything perhaps insert mostly blank rows then come back and update them? Or do an update to null and then an update with the actual values? It's something I've seen before and, if they're not done within a transaction, it's possible for a query to read an intermediate value that it shouldn't
Good point, but definitly not.
The table 'mahnhistory' and their primary key is filled by only one process, no updates are done.
SQL locks rows for query integrity. While a query is reading rows there's a shared lock in place, other queries can read but nothing can update.
While updates are occurring the rows are locked exclusively. Nothing else can read or write.
In the default isolation level shared locks are released as soon as the statement is complete, exclusive locks are released when the transaction commits
What does '...shared locks are released as soon as the statement is complete...' mean exactly?
What happens, if a query is completed, but not fully sent to the client?
Will the sql server preserve the whole result set, until the client got all the data?
There
I'd suggest get Profiler out, see what the print server's actually doing and where the selects happen.
I'll work on that, but I wasn't able to reproduce it on my development machine and on the production machine it happens only 1 or 2 to times a month.
markus
December 10, 2009 at 10:53 am
wimark (12/10/2009)
What does '...shared locks are released as soon as the statement is complete...' mean exactly?
Locks are released when they are no longer needed, is once the row/page/table has been read. Basically, SQL has the row locked while it is reading it. Once it has completed the read and doesn't need that row any longer, the lock can safely be released.
I'm not sure 100% at what point it's released, but it is definitely after the reads are done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2009 at 10:30 am
We tried to nail down the problem and found out that this is caused by a clustered index!???
One of the joined table in the select has a clustered index on a column. If this column is updated _after_ the select is fired, but before all rows are fetched, the result set of the odbc connection includes empty rows for the affected records!
An update on other columns cause no problem.
Is this really the intended behavier of SQL2005 (SQL Server 2000 doesn't have this issue)?
As Workaround I dropped the clustered index.
markus
December 15, 2009 at 10:55 am
Are you doing the select WITH NOLOCK or are you explicitly setting the READ UNCOMMITTED isolation level?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2009 at 12:48 pm
GilaMonster (12/15/2009)
Are you doing the select WITH NOLOCK or are you explicitly setting the READ UNCOMMITTED isolation level?
No, I'm using default READ COMMITTED and autocommit true.
The select is not part of a transaction nor do I want to lock the records during a the slow printer output.
During the 'run' of the select some locks are shown:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
132 11 0 0 DB S GRANT
132 11 373576369 0 TAB IS GRANT
132 11 0 0 MD 4(1:0:0) Sch-S GRANT
132 11 2082106458 5 PAG 1:643289 IS GRANT
132 11 373576369 2 KEY (2c0033f9a523) S GRANT
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
132 11 0 0 DB S GRANT
132 11 0 0 DB [PLANGUIDE] S GRANT
132 11 0 0 MD 4(1:0:0) Sch-S GRANT
132 11 0 0 MD 4(6:0:0) Sch-S GRANT
132 11 0 0 MD 1(236943a5:0:0) Sch-S GRANT
132 11 0 0 MD 1(236943a5:2:0) Sch-S GRANT
... but after the first row is select there are no more locks, even if not all rows are fetched!
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
132 11 0 0 DB S GRANT
Remember: I can't reproduce this behavier on a SQL Server 2000
markus
December 15, 2009 at 1:08 pm
wimark (12/15/2009)
The select is not part of a transaction nor do I want to lock the records during a the slow printer output.
Unless you're using read uncommitted, you are locking the rows. A select will lock the rows with a shared lock for the duration of the read.
Are you 100% sure there's no switch to read uncommitted or a WITH NOLOCK hint (or a plan guide that's forcing a nolock hint)? There should be more locks than what you have listed, unless that select retrieved a single row.
Based on the locks shown, there is a plan guide being used.
... but after the first row is select there are no more locks, even if not all rows are fetched!
The locks are only held until the read from the table has finished. Once that's finished, the locks on the table can be released as the query results are per-session (and not affected by any changes that occur to the table afterwards). There's no need to hold locks, reducing concurrency, while spooling query results to the client.
I think you may have to call MS customer support on this. It's going to be near-impossible to identify a cause without seeing all of the code, lots of traces and similar data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 15, 2009 at 1:10 pm
Hang one, just noticed what you said in the first post. "fetch hundreds of rows one by one"
Are you indeed retrieving one row, then doing something with it, then the next row, etc? If so how? Client cide cursor? Server side cursor? Single row selects?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 3:46 am
Thank you for the hint, that pointed me to the right direction:
API cursor are used: sp_cursoropen, sp_cursorprepexec, sp_cursorfetch
and sp_cursorprepexec is called with
@ccopt =] concurrency_options OUTPUT = 0x0001 .. Read-only!!
(Don't know, why this isn't documented by Microsoft,
http://jtds.sourceforge.net/apiCursors.html)
That's the reason, why locks are immediatly released!
Anyway, that doesn't help me much, because don't know how to influence this parameter in gupta ...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply