Optimiser Differences 1

[jarno]

Hi,



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

to:

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.





Thanks

 

[Crowley16]

could be the statistics becomming out of date so optimiser modifying the plan accordingly...

has there been a lot of new data going in or bulk imports or similar? Try doing an update statistics and indexdefrag...

remember that the order you do the joins is important, put more exclusive joins/tables at the start will improve performance...



--------------------
Procrastinate Now!

 

[jarno]

stats are updated every morning, indexes rebuilt once a week.

What I am struggling with is what is different between the two servers that will make the execution plan look so different to begin with and then when the join is moved make them identical.

The servers are both SQL Server 2000.

 

[ptheriault]

Sounds like a re-indexing issue. When was the last time you re-indexed your production database?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jarno]

Our usual weekly reindex is done with a variation on the sp by T.Pullen with the scan density threshold set to 85

http://www.sql-server-performance.com/tp_automatic_reindexing.asp

I am going to use the maintenance plan reindexing this weekend to see if it makes a difference to production.

 

[mrdenny]

Is this query something that is run often in production? If it is then the execution plan will be cached, while it may not be cached in development. Because of this the cached execution plan could have been valid before, but it's no longer valid.

sys.dm_exec_requests will tell you when SQL started using the cached plan.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jarno]

Reindex did not make a difference here.

Thank you mrdenny, this looks like a fruitful avenue to pursue.

I am using SQL Server 2000 so I will need to try and get the cached plan information some other way.

 

[jarno]

I went for the brute force route of using DBCC FLUSHPROCINDB(<db_id>) but to no avail.
This is not a stored procedure that I am running.

Anything else that I can try?

 

[mrdenny]

You can look at some cache information in SQL 2000 using the syscacheobjects system table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jarno]

Thank you mrdenny. This has highlighted the difference in the cached plans between test and production. The plans in question though are not in the cache on either machine, as far as I can tell. This may be why DBCC FLUSHPROCINDB made no difference.

Are there further factors influencing the plan that has been chosen on production apart from the statistics, (updated every night), and the indexes(updated on a weekly basis)?

 

[mrdenny]

Pretty much the only thing that should be effecting the plan is the stats.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog