SQL Server Performance

Optimiser differences

Discussion in 'Performance Tuning for DBAs' started by jarno3000, Oct 12, 2007.

  1. jarno3000 New Member

    I have a production db and a restored copy of the db on two different servers.

    A query on production has degraded to the point of timeout(30 seconds).
    Running the same query on the restored copy produces an execution time of a couple of seconds.
    The Execution plans look slightly different.
    Moving a couple of joins around from:
    LEFT JOIN AD ON T.Ref = AD.Reference

    INNER JOIN SM ON T.Reference = SM.Reference

    INNER JOIN SM ON T.Reference = SM.Reference
    LEFT JOIN AD ON T.Ref = AD.Reference
    Results in the production execution time mirroring the restored copy execution time and the execution plans being the same.
    Any ideas as to what is happening here and how I can 'correct' the production system.

  2. Greg Larsen New Member

    Wonder if the restored DB's have different statistics causing a different execution plan to be generated.
  3. jarno3000 New Member

    The stats seem to be the same. The restored db is only a day or so old.
    The stats get updated on production every morning and the indexes are rebuilt once a week.
    Our usual weekly reindex is done with a variation on the sp by T.Pullen with the scan density threshold set to 85
    I am going to use the maintenance plan reindexing this weekend to see if it makes a difference to production.
  4. jarno3000 New Member

    Reindex did not make a difference. I have had a suggestion on another forum that it may be to do with a cached plan. I am going to have a look at this. For info I am using SQL server 2000.

  5. thomas New Member

    Is it a stored procedure? Try recompiling it if it is.
  6. jarno3000 New Member

    Unfortunately, this is not a stored procedure.
  7. ndinakar Member

    Also watch out for differences in data being retrieved. Moving around INNER JOINS doesnt affect results but if you move a LEFT JOIN the result could be different. As Greg mentioned, the stats could be different. This is the biggest problem I have noticed. Query plan goes bad in prod and works fine in dev environments.
  8. satya Moderator

    The execution on restored database will be quick as the plans are worked out afresh, similarly on the production ensure you haven't missed the index optimization in this case.
  9. jarno3000 New Member

    Plans are different between the two servers. I tried to use DBCC FLUSHPROCINDB(<db_id>) to update production but the other plan was still chosen.

Share This Page