September 10, 2009 at 5:59 pm
Hi
I have a two databases on the same SQL 2005 instance.
I created a table, call it 'Table1' on both databases with the same data.
When I look at the execution plan when I run it on 'Database1' it is diffenrent to the execution plan
when I run it on 'Database2'.
The query also runs much longer over 'Database2'
Why is this??
Any help is appreciated.
Thanks
September 11, 2009 at 12:56 am
I suppose indexes are the same and statistics are up to date...
I think some more details are required here to understand what goes on. Please take a look at this article and post some more information. I'll be glad to help you.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
-- Gianluca Sartori
September 11, 2009 at 3:50 am
Hi
I tried to see what the difference was between the two tables and after a while I realised that 'Table1' on 'Database1' was populated through SSIS and 'Table1' on 'Database2' was just populated with an INSERT INTO statement is Management studio.
The data and table definitions are exactly the same BUT the table that is populated through SSIS has a different execution plan and the SELECT query also runs much faster on this table.
I then truncated the table in 'Database2' and populated it with SSIS. After this the exection plan was the same as it is for the table on 'Database1' and the SELECT query also runs much quicker.
September 11, 2009 at 4:08 am
It may simply have to do with the order of the data. If one or the other inserts in the same order as the clustered index, there will be no chance of page splits.
Try rebuilding the indexes on both tables and see if they then take the same time to execute because the source of data makes no difference.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2009 at 7:37 am
Does the SSIS package do anything besides insert the data? Maybe the statistics are getting updated as well. If not, maybe they need to be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply