October 17, 2006 at 3:38 am
I have got 2 identical databases A & B, out of which database A has got data & database B does not have any data. Whenever we are running the same Query or Stored Procedure in both the databases, it use to produce differenet query plans in different Databases.
Can anybody let me know what is the reason for the same & why is it happening? Also how to know which Query plan is correct ? To avoide this type of situation, what one should do?
Thankx in advance.
October 18, 2006 at 2:35 am
Both query plans are correct! The query plan takes into account the distribution of data within the table(s) used in a query, so the amount of data and also the statistical spread of that data will affect the best way to access it. For example, a query trying to get an individual row based on the key from a table containing say 10 rows. In this case it would be more expensive to use an index than it would be to perform a table scan. However, if the table had 10,000 rows, it would be far quicker to use an index. That's why you should always do your performance tuning with a realistic data load.
October 18, 2006 at 5:35 am
Hi Ian,
Thankx for the reply. But as I told you, both the databases A & B are having same tables & indexes. Will it still show different Query plans for the same queries or stored procedures for both the databases?
Thankx in advance.
October 18, 2006 at 5:39 am
Hi TK,
Yes, as I said, even for exactly the same databases, differences in the data in those identical tables can lead to different query plans. You said that one database doesn't have any data, the other does. That's the difference! Try checking the query execution plan on one table filled with data, which has some indexes defined, then delete the data and try again. There will probably be a difference.
October 18, 2006 at 7:16 am
If you check sysindexes in both DBs, you will see that they are not in fact the same -- the DB with no data will have fewer rows in sysindexes, assuming statistics and indexes are all refreshed.
The Optimizer uses information from sysindexes to arrive at the optimal query plan.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply