May 29, 2008 at 1:08 pm
I'm testing a query in the same database. I have a table1 and table2 where table2 is a copy of table1 (same rows, etc).
The joins are identical in the SQL, statistics, indexes, and primary keys are also identical. When running the SQL query on table1 it runs very slowly, when running it against table2 the result set is returned within couple of seconds.
Indexes were rebuilt on both of these tables.
I don't understand why SQL is producing two different query plans. Any ideas? How can I force it to be more consistant and produce the same query plan for both queries?
May 29, 2008 at 4:32 pm
Double darn check that you really aren't missing an index. After that I would update your statistics on both tables.
update statistics tablea
go
update statistics tableb
go
Good luck,
Deb
😎
May 29, 2008 at 5:27 pm
Thanks for the tip. I already did. I'll look at it again tomorrow.
May 29, 2008 at 5:55 pm
Can you just check the fragmentation value for both the tables once?
Manu
May 30, 2008 at 6:12 am
Flush the procedure cache, dbcc freeproccache(), and then rerun the queries. I think MANU might have it. You inserted one table from the other. It's indexes are going to be distributed differently.
"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
May 30, 2008 at 2:05 pm
Have you tried defragmenting the table that takes longer to query?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply