Execution plan SQL 2008

Question

Post reply

Execution plan SQL 2008

dtipser

SSC-Addicted

Points: 446
More actions
April 23, 2010 at 7:44 am

#386773

Hi,
I have two databases on SQL Server 2008, one DEV and other QA with the same structure and very similar data inside. I run the same stored procedure in both databases and I get two different Execution Plan. In one, wich is the slower, the Query Optimizer did a lot of Clustered Index Scan and propose creation of an index that allready exist (only include some columns), in the other, the faster one, the Optimizer did only Clusters Index Seek and no needs for new indexes.
Why this behavior for identic statement and allmost same data (the databases are on same physical server and same drive)? Could be because of physical fragmentation or can be update statistics issue?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

statistics.

the statistics are different between the two databases.

even if the two db's started out as identical, the way the data is selected/updated can change the statistics, and THAT is what the optimizer uses to determine the fastest way to get the data.

you can try updating your statistics, adn see if it choses a better execution plan after that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dtipser SSC-Addicted Points: 446 More actions · Answer 2

Thanks. I run Update Statistics on both databases and I have to say that I was surprised by the fact that after running again the statements, the statement that was faster is proposing now the creation of an index (like the slower one) and the execution time increased (I saw more index scan than seek). The slower one remained as before.

For me it's a strange behavior for Optimizer. Any remarks?

Lowell SSC Guru Points: 323518 More actions · Answer 3

well the optimizer uses the stats to determine the fastest way to get the data; if the statistics imply that the selectivity of a record is very close to 1 record per key, it would chose and index seek; if the selectivity was higher, it expects multiple rows for a specific value (all the 'Smith' records for example) so it determines it needs to do a scan;

In your case, out of date stats can make it think it can use a Seek, but when it implements it, the seek doesn't work so well;

here i get fuzzy, as i don't know what happens when a bad execution plan occurs; I have always just assumed the system throws it's hands in the air and tries to use the same SEEK execution plan for every row in the table,then merges the data based on the WHERE criteria, but i really don't know(yet)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Stefan_G SSCertifiable Points: 6609 More actions · Answer 4

dtipser (4/23/2010)
Hi,
I have two databases on SQL Server 2008, one DEV and other QA with the same structure and very similar data inside. I run the same stored procedure in both databases and I get two different Execution Plan. In one, wich is the slower, the Query Optimizer did a lot of Clustered Index Scan and propose creation of an index that allready exist (only include some columns)

Have you tried creating the index the query optimizer is recommending ? It will likely make your query much faster.

dtipser SSC-Addicted Points: 446 More actions · Answer 5

Hi,

Yes, I created the index, but after that is asking to create another one. For sure the statement run a litle faster but it still doing some scans in place of seek.

Lowell SSC Guru Points: 323518 More actions · Answer 6

dtipser (4/26/2010)
Hi,
Yes, I created the index, but after that is asking to create another one. For sure the statement run a litle faster but it still doing some scans in place of seek.

if you can post the .sqlplan file fo the execution plan, or at least the query itself, we might be able to offer some suggestions;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dtipser SSC-Addicted Points: 446 More actions · Answer 7

Hi,

I run today same statements in both databases and surprise, the Optimizer shows almost identical Execution Plan (for one statement is asking to create a new index, but it's not a big issue).

And running time is identical too (faster result).

So, I think that recreate statistics on both databases fix the discrepancy between Execution Plan (I'm not sure at 100%). What I found strange is that even after I did this it took some times to get same result in Execution Plan (as faster query).

Thank for your advices ,it was greatly apreciated.