August 15, 2003 at 1:51 pm
I have a query (3rd party app) that creates a #temp table that is then populated with an insert/select from a user db, then the #temp table is joined with 2 other tables from the user db to create a result set. The join for the result set runs very slow (20 secs). If I create the temp table in a user db as a user table, the join is fast (1 sec). If I create the temp table in tempdb as a user table, the join is slow (20 secs). I have loaded this user db to a different server that is at SP3 and the query runs fast no matter where the temp table is created (1 sec). The optimizer is creating different query plans on the SP4 server when selecting from the #temp table vs the temp table in user db. The SP3 server query plans are the same.
Has anyone ran across this performance issue with SP4 when selecting from #temp tables?
Any solutions would be greatly appreciated.
Thx
August 16, 2003 at 4:43 am
Can you test on another server at SP4 to possibly isolate whether it's indeed an SP4 problem or maybe a problem with your server's tempdb?
Cheers,
- Mark
Cheers,
- Mark
September 5, 2003 at 6:46 am
This issue has been resolved. The problem was that the db options "auto create statistics" and "auto update statistics" are set by default. On service packs prior to sp4, these options are ignored for tempdb. In sp4, these options, being set on tempdb, caused the optimizer to choose a bad query plan. I reset the options and the query ran as expected.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply