SQL 2005 & 2008, same data & SELECT -> Diff. Execution Plan

pdj

I’m currently optimizing a solution which is used by different customers on both Native and SQL2005 and SQL2008. My focus so far has only been to ensure that Native and SQL both performed fine, but now I just encountered a difference between SQL2005 and SQL2008. I have two databases with the same data and same statistics and same collation settings; only difference is the SQL version. But the execution plans are different, so I guess SQL2008 is trying to be smarter than SQL2005. This should be a good thing, but in my case it picks a worse index for a specific SELECT. I’m quite sure I can adjust the indexes to fit with SQL2008, but that might destroy some of the execution plans for SQL2005. I’m currently considering to start using IndexHints to ensure both versions uses the same index, but I would like to avoid it.

Has anybody else encountered this problem and has any recommendations?

kriki

I would first try to do a rebuild index and see what happens.

Indexhints should be used when all else fails.

Marije_Brummel

I have not seen this issue before, but then again, most of our customers do not have comparable databases.

Any chance of getting more information?

kine

I assume that databases have different statistics and of course I assume different weights for different parameters when the planning engine is selecting execution plan which can lead to different results in some cases.

But, why it matter if all is working quickly? Or you want to say that some execution plan is too slow? ;-)

pdj

The data IS the same. It all started by a customer complaining about the speed in our solution in their SQL2008 test server (they are currently using Native). I examined the server, and found several bad “statements” which made me take a good-old NAV backup (fbk-files) of their 40gb SQL database. The purpose was to optimize our vertical based on the 2 week old live data from this customer. I then restored the DB into my SQL2005 to continue optimization. Here I noticed that some statements results in different execution plans, even though both data and objects are 100% identical. And yes, the SQL2008 is creating execution plan that are very slow. But all optimizations are also made for SQL2005, as I assumed they created the same plans.

I’m currently installing a new SQL2008 on a local server to ensure I can reproduce the problem on both rather clean servers.

Marije_Brummel

Restoring a fbk file in a new database does not nessesarily mean having the same statistics, especialy when auto create statistics and update statistics is turned on.

You'll have to dive into the statistics (DBCC SHOWSTATISTICS) to see if they exactly match. You can also see in the real execution plan how SQL made the decision. Are all these exactly the same?

Slawek_Guzek

pdj wrote:

...But the execution plans are different, so I guess SQL2008 is trying to be smarter than SQL2005...

Why are you surprised ? SQL is evolving, and Microsoft guys tries to make it better from version to version. The most important part of SQL server having the biggest performance impact is Query Optimizer, and ways it generates query plans..

As Mark mentioned - the same data does not necessarily mean the same statistics. And this is the statistics and NOT the data what decides how execution plan will look like. I would add that even the same statistics does not warrant the same execution plans in different SQL versions.

If you want to compare/tune THE SAME database for performance, detach database from client server, make a copy of database files, and attach copied files to your server. It database is to big to copy it that way read How to generate a script of the necessary database metadata to create a statistics-only database in SQL Server 2005 and in SQL Server 2008 article to see how to duplicate database with statistics only for performance troubleshooting.

As for recommendation - in my opinion enforcing SQL to pick the same (fixed) index may not be good strategy in long term. Try rather to start enforcing recompilation of queries (add WITH RECOMPILE hint), if of course you have not many concurrent users and some free CPU power to waste on each query recompilation. Then try to analyze (and eventually compare) generated query plans.

Regards,
Slawek

DenSter

Slawek Guzek wrote:

I would add that even the same statistics does not warrant the same execution plans in different SQL versions.

Even on the same database, with the same query, you get different execution plans at different times, or at the same time for different users.

pdj

The mystery seems to be solved!
I did an Index Rebuild of the SQL2008 database, and now it picks the same index as my SQL2005.
And I only agree about the comments with Index Hints. That’s why I asked for your help :-)
Thanks for your help, everybody.

Just a few final questions:

Mark Brummel wrote:

You can also see in the real execution plan how SQL made the decision.

How do I do that?

DenSter wrote:

Even on the same database, with the same query, you get different execution plans at different times, or at the same time for different users.

How can that be?

Marije_Brummel

When running query from SSMS you can activate the 'Include Actual Executionplan' option. Then when you click on the executionplan tab then you can hover over the different parts and see the properties.

http://www.sql-server-performance.com/t ... is_p1.aspx

You can change the query to force an index and then analyse the differences.

select * from [CRONUS Nederland BV$Cust_ Ledger Entry] WITH (INDEX ("$2"))
where [Document Type] = 3 order by [Document No_]

stryk

Hi all,

just my two cents:

Once we ran a benchmark comparing SQL 2005 (STD) and SQL 2008 (STD) - same hardware, same database, same configuration etc..
We testes a crucial business processes.
Result: at average, all tested processed performed 20% faster on SQL 2008!

This, the new engine HAS improved! Some MS SQL PM once told me, that the new engine was improved to increase the performance of most queries by generating better QEP, but he admitted that few queries might perform slower, too ...

I have several customers who upgraded from SQL 2005 to 2008 and the throughout experience is an improvement of overall performance! Once in a while there are indeed few queries which perform worse than before, but so far we could fix all of that by either adding optimized indexes or changing the sorting in NAV.

Best regards,
Jörg

pdj

Mark: Yes I know. However I never execute the troublesome SQL statements I find. I simply declare the dynamic cursor and use the “Display Estimated Execution Plan” function. This is the best and easiest way I have found to reproduce the calls NAV makes. I.e. like this:

declare
@P1 int,@P2 int,@P3 varchar(20)
declare C CURSOR DYNAMIC FOR
    SELECT  * 
    FROM "MyDatabase"."dbo"."MyCompany$Registered Whse_ Activity Line" WITH (READUNCOMMITTED)   
    WHERE (("Activity Type"=@P1)) AND (("Source Type"=@P2)) AND (("Source No_"=@P3)) 
    ORDER BY "Activity Type","Source Type","Source No_","Registering Date","No_","Line No_"

However; I miss the ability to see how many Reads and Writes it actually has with a given set of parameters, but I have to rely on the results from the statistics in sys.dm_exec_query_stats etc. Do you have an T-SQL example of a Dynamic Cursor from NAV including the actual execution?

Stryk: I know SQL2008 is often a bit faster, but I still expect my optimizations would be ok for both 2005 and 2008. I still expect that different data is more likely to cause different Execution Plans than different SQL versions.

stryk

You can enable the STATISTICS IO either in SSMS (Extras - Options - Query Execution - SQL Server - Advanced) or via TSQL:
SET STATISTICS IO ON
The the "Message" Tab of the Result-Output will show the "Reads" etc..

pdj

Stryk: Thanks, but my problem is not to get the statistics from a "normal" SELECT. I need to know how I use the declared cursor C which I make in the sample T-SQL. (See above)
I expect I need to assign the variables some values and then "execute" the cursor, but I don't know the syntax :oops: