June 15, 2009 at 4:48 am
Hi All,
I was wondering if it is normal to have two replica copies of the same hardware and same schema objects placed on them, but when a stored procedure is called, they use two separate execution plans.
Is this behaviour normal and what should one look out for here for diagnosis.
June 15, 2009 at 5:13 am
It is possible. Few reasons that can cause this:
1) Different data in each server can cause different query plan.
2) Different session settings (this one can also cause different query plan on the same database for the same query).
3) Skewed data that different sets of parameters cause different query plans. For example suppose that you have Customer table. One of the columns is CountryID and 97% of the customers are from the U.S. The table has an index that is based on CountryID column. You have a stored procedure that gets CountryID as a parameter and returns the number of customer in this country. If on the first Server you ran the procedure for the first time with CountryID of U.S.A, you'll get a query plan that uses a table scan. If on the second server you ran this the procedure for the first time with CountryID of U.K, you'll get a query plan that uses index seek.
4) Different settings regarding updating statistics.
There are probably other reasons that I couldn't think about.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply