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

pdjpdj Member Posts: 643
edited 2009-10-16 in SQL Performance
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?
Regards
Peter

Comments

  • krikikriki Member, Moderator Posts: 9,094
    I would first try to do a rebuild index and see what happens.

    Indexhints should be used when all else fails.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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?
  • kinekine Member Posts: 12,562
    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? ;-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • pdjpdj Member Posts: 643
    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.
    Regards
    Peter
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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_GuzekSlawek_Guzek Member Posts: 1,690
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • DenSterDenSter Member Posts: 8,304
    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.
  • pdjpdj Member Posts: 643
    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:
    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?
    Regards
    Peter
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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_]
  • strykstryk Member Posts: 645
    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
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • pdjpdj Member Posts: 643
    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.
    Regards
    Peter
  • strykstryk Member Posts: 645
    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..
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • pdjpdj Member Posts: 643
    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:
    Regards
    Peter
Sign In or Register to comment.