SQL Server Performance

Join Precedence - SS7 vs SS2000

Discussion in 'Performance Tuning for DBAs' started by john, Feb 26, 2003.

  1. john New Member

    Dear Listers,

    Using two identical db's, ie. schema and data and size and file placement, a report running on SS7 completes approx. 20% faster than for the same report on the identical db on SS2000.

    The developers have noted that SS7 QEP uses Loop joins and SS2000 uses Merge joins. When a Loop join query hint is used in the SS2000 code, SS2000 performance outperforms SS7.

    What considerations can be taken into account to determine why, originally, SS2000 chose a sub-optimal QEP.

    Should I consider MAXDOP ?

    Are there any memory/time configurables available to permit the query optimizer to make a more informed decision ?

    How can I query SS buffers ? (Use undocumented dbcc's? )


  2. seasider New Member

    The usual one is statistics. Run a full update with fullscan on all the tables involved in the query to be sure. Also ensure tables and re-indexed. Are there any missing stats on one server that the optimniser needs? Look for red text against an operation on the execution plan.

    Is parallelism being used in the SQL2K query? I have seen instances in the past where parallelism causes this kind of behaviour. Try it with the MAXDOP option limiting it to 1 processor.

    If all of these don't work then you may have to use a query hint to force a Loop Join.
  3. thomas New Member

    I always switch parallelism off on my SMP servers - for the whole server. I have not seen any benefit from it, ever, quite the opposite in fact. I have seen parallel threads deadlock themselves - and Microsoft's response was "parallel query execution is not always guaranteed to work".

    Tom Pullen
    DBA, Oxfam GB
  4. bradmcgehee New Member

    Other than perhaps statistics, as has already been suggested, I don't know why the SQL Server 2000 Query Optimizer would make a less than wise decision, but this does happen, for whatever reasons. When I see this happen, I just use the appropriate hint and live with it. I also turn parallelism for SQL Server 7.0 because of trouble, but in SQL Server 2000, I have yet to run into any problems.

    Brad M. McGehee
  5. Argyle New Member

    Is the database on SQL 2000 in SQL7 or SQL2000 compability mode? Change to SQL2000 mode if possible.


Share This Page