Re: MIN() + MAX() Deadlock ?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: avnrao (avn_at_newsgroups.com)
Date: 01/13/05 

Date: Thu, 13 Jan 2005 11:03:41 +0530

The only difference between the statements is with (nolock). can you check
if the second statement is trying to use parellelism in its query plan.
can you set Max Degree of Parallelism to 1 for the query and run it. 

-- 
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"eval" <eval@eval.com> wrote in message
news:ed89SdP#EHA.2568@TK2MSFTNGP11.phx.gbl...
> Hi guys
>
>
> The only difference between the following 2 queries...
>
>
> Select min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> Where UnitID = '1720200022285010001407'
>
>
> Select  min(GpsDate) minTime, max(GpsDate) maxTime From TblAvlTrans
> with (nolock)  Where UnitID = '1720200022285010001419'
>
>
> ...is that the first one takes only 36 seconds in a 21 million table
> database, but the second one takes forever (last time I tried, the query
>   took 3 minutes and had to stop it because of blocking)
>
> Note that both queries are exactly the same (except for the where
> clause), and both records have more or less the same amount of records
> (about 100,000)
>
> The funny thing is that, if I get the MIN() first, and then the MAX()
> for the same where clause:
>
> Select  min(GpsDate) minTime From TblAvlTrans  with (nolock)  Where
> UnitID = '1720200022285010001419'
>
> Select  max(GpsDate) maxTime From TblAvlTrans  with (nolock)  Where
> UnitID = '1720200022285010001419'
>
> I get the value. However, it does not work in combination.
>
> Also note that the problem is only with UnitId =
> '1720200022285010001419' (The others work just fine)
>
> I checked the nulls (no nulls at all), the indices (dropped and
> re-created them with fill factor of 90%, althhough it is under heavy
> "insert" stress), and there seems to be no reason for this deadlock
>
> Any clues
>
>
> Eval

Relevant Pages

  • Re: MIN() + MAX() Deadlock ?
    ... Actually one of the statements has a NOLOCK and the other does not. ... maxmaxTime From TblAvlTrans Where ... > Select minminTime From TblAvlTrans with Where UnitID ...
    (microsoft.public.sqlserver.programming)
  • Re: Lookup Evils
    ... Lets say you add an Autonumber field to your Units table (call it UnitID) ... a query window and join them by the Measurement unit field (ie not the ID ... You can add a 'lookup' table more than once to the relationship window ...
    (microsoft.public.access.tablesdbdesign)
  • Display the latest date in series
    ... so I have a query that looks like: ... SELECT UnitID, Maxas LatestReportTime ... >a variety of amounts that are entered on a weekly basis. ... >entered on my report. ...
    (microsoft.public.access.queries)
  • A query with too many joins...
    ... I have a query that makes tempdb as large as 20GB and exceed the disk ... join EXTENDED_ACC_SET eac (nolock) on ... These and other tables used in such queries are ...
    (microsoft.public.sqlserver)
  • Re: SQL tuning
    ... Does SQL Server optimizer use indexes to perfom the query? ... It might be that otpimizer uses a 'bad' execution plan ... > FROM dbo.QuarterlyRunPortfolio qrp (NOLOCK) INNER JOIN ...
    (microsoft.public.sqlserver.programming)