none
SQL Server 2016 slower than SQL Server 2008 RRS feed

  • Question

  • I have two databases with the same data, one on an SQL Server 2008 R2 instance, and one on an SQL Server 2016 instance. Both SQL Server instances are on the same machine, and the database files are on the same disk. The SQL Server 2016 has Legacy Cardinality Estimation = ON. Both instances and databases have Max DOP=1. With the SQLQueryStress tool, I run a stored procedure 100 times on the SQL Server 2008 R2 instance and then on the SQL Server 2016 instance. The total time on the SQL Server 2008 R2 is 1,83 sec whereas the total time on the SQL Server 2016 is 2,16 sec. The execution plans are exactly the same, why is SQL Server 2016 slower?

    The Results in the SQLQueryStress:

    The execution plan of the stored procedure on the SQL Server 2008 R2 database:


    The execution plan of the stored procedure on the SQL Server 2016 database:




    • Edited by pappasa Thursday, March 22, 2018 8:17 AM
    Thursday, March 22, 2018 8:14 AM

All replies

  • Just for curiosity: Do you really think, that 0,3s/100 is a problem?

    Thursday, March 22, 2018 9:01 AM
  • It is 18% slower. Our system executes millions of queries each day. If all my queries are 18% slower, this can be a problem. In addition, if I buy a new product, I expect it to be better than the old one.
    Thursday, March 22, 2018 9:06 AM
  • What if you remove a index hint? Running tests you turned off the another instance?

    What if you set Legacy Cardinality Estimation = OFF.?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, March 22, 2018 9:12 AM
    Answerer
  • Moreover, it is possible that the optimizer would have chosen a paralle plan on SQL Server 2016  to speed up the query , can you test it ?

    Use option (recompile, querytraceon 8649) to force parallel plan


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 22, 2018 9:20 AM
    Answerer
  • The difference could be from anywhere as that is almost a second. Hover the mouse over the select(on extreme left side) in two plans can you see any difference ?. Also I do not see this as much difference

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, March 22, 2018 9:22 AM
    Moderator
  • Well, what I wanted to express only 100 runs giving 0.3s is not a significant change.

    E.g. your storing both databases on the same disk. Thus this alone can already explain the difference when you started with clean buffers.

    Another problem: memory. As both instances are on the same machine, do they both have the same amount of max. memory and is this limited, so that both machines have this limit at the same time? Cause memory pressure could also be a problem.

    Both queries use the same plan, the same amount of reads. I would make more tests. At least running with clean buffers and free proc cache.

    And "better" does not imply "faster". Especially as technology has changed. On what compat levels do you run your databases in this test? Did you a index rebuild and update statistics before testing?

    btw, I would start by using the actual SSMS..

    Thursday, March 22, 2018 9:37 AM
  • That test does not run long enough to determine anything about performance.  You are likely simply running into a problem with the resolution of the timer, not an actual problem.

    If you are going to attempt these kinds of tests, you need a controlled environment with nothing else running but your test, and it needs to run for at least 1 minute and you need to run it multiple times and average the time.

    For example, on a VM environment, I was able to get +-18% difference simply by running a ~5 minute test cycle 10 times.

    Thursday, March 22, 2018 11:31 AM
    Moderator
  • 1. As stated above, SQL Server 2016 is 18% slower. On a system running all day, with millions of queries, that has an impact.

    2. Yes both databases are on the same disk, but I run the tests one after the other. Therefore, the two SQL Servers compete not for disk I/O.

    3. I tested other stored procedures too, they all seem slightly slower on SQL Server 2016. Yes, I clean buffers and free proc cache before every test.

    4. Better means for us that we can process more transactions in a day. I do not see, how I can achieve that with an 18% slower system.

    5. The SQL Server 2008 R2 database is on compatibility level 100 and the SQL Server 2016 database is on compatibility level 130. I ran DBCC DBREINDEX for all tables, this should update statistics too.

    5. I use the new SSMS. I opened the SQL plans in the old SSMS just to make the screenshot.

    Thursday, March 22, 2018 11:35 AM
  • 1. As I stated, your tests is a simple one. Its results are not significant.

    2. When they have to read the same amount of pages, one instance is faster, cause its database is on the outside of the disk. Thus higher read rates from the disk.

    3. see 1.

    4. see 1.

    Thursday, March 22, 2018 11:50 AM
  • Without the index hint, and without Legacy Cardinality Estimation = ON, the query runs even slower.
    Thursday, March 22, 2018 12:08 PM
  • How did you create the SQL 2016 database? If you didn't use backup/restore, try reorganizing the indexes.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, March 22, 2018 12:53 PM
    Moderator
  • I did a backup/restore, and changed the compatibility level to 130.
    Thursday, March 22, 2018 1:38 PM
  • 2. When they have to read the same amount of pages, one instance is faster, cause its database is on the outside of the disk. Thus higher read rates from the disk.

    That would be relevant if pappasa works in a museum with spinning disks.

    I would agree with the sentiment that this test is too small to be significant. There can be several factors that affects performance, of which not all are related to the SQL Server version. Performance testing is more difficult than it looks at first sight.

    When it comes to the execution plans, I note that there are two table-valued functions in the plan. There could be plan differences with these.

    Thursday, March 22, 2018 10:18 PM
  • do both instances of sql servers have same patching level for Spectre / Meltdown?

    Check if both servers are patched at least till the build mentioned in below link

    https://support.microsoft.com/en-us/help/4073225/guidance-protect-sql-server-against-spectre-meltdown

    Performance difference between patched/not patched server may be 5%-30%.

    Thursday, March 22, 2018 11:18 PM
  • It could of course be that the code path in 2016 is slightly longer than in 2008. I.e., 2008 being "closer to the metal". But we don't have enough evidence for that conclusion yet.

    I would do at least 10 times more executions to start with.

    Also, I would try to eliminate physical I/O (instead of the other way around). I..e, make sure that you fit the data you work against in case and warm up the cache by a simple SELECT *, or similar.

    Also, make sure you do the tests in both orders. 2008 first. And then 2016 first. Also do this with both the other other (non tested) instance running and not running.

    I would also try with a lower compatibility level. I know you turned on the legacy CE, but you still have optimizer fixes which are enabled by default in compat level 2016. 

    In essence, try to change a bunch (one at a time, of course) environmental attributes and see if we can find something else than the "new code in 2016" that makes the difference.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, March 23, 2018 8:15 AM
    Moderator
  • We installed the patch SQLServer2016-KB4057119-x64.exe on SQL Server 2016, there was a slight improvement, but still the difference remains at 15% worse than SQL Server 2008 R2.
    Friday, March 23, 2018 10:16 AM
  • The execution plan of the SQL Server 2008 R2 is here.

    The execution plan of the SQL Server 2016 is here.

    Friday, March 23, 2018 11:32 AM
  • The plans do indeed seem to be identical, both in estimated and actual values, although I only made samples by looking at the popups.

    I think it would be beneficial to collect wait stats and maybe also performance counters when the test is running. It could be that as someone suggested that the code path in SQL 2016 is longer, and therefore SQL 2016 is slower in this particular test. But the differences could also be due to external factors that not related to the SQL version. Performance testing is by no means a simple task, and you can easily jump to incorrect conclusion.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, March 25, 2018 5:20 PM
  • What do you mean by code path? Is it possible to see it?
    Monday, March 26, 2018 10:14 AM
  • You are running 2 instances on the same server. Did you dedicate CPU and RAM to each instance?  Did you stop the other instance when running your tests?

    Monday, March 26, 2018 11:10 AM
    Moderator
  • I mentioned code path. It is the SQL Server code, as compiled by the compiler that compiles the high-level language code that SQL Server is written in (perhaps C++). 

    I guess you *could* debug SQL Server (the symbols are out there) and see what machine instructions are executes in the end. But that would be a huge undertaking, and is probably not doable for us mere mortals.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, March 26, 2018 8:16 PM
    Moderator
  • Pappasa,

    We are experiencing the same exact issues you are with SQL 2016 being about 50% slower than SQL 2008 R2. Did you ever come to any conclusions on your issue?

    Friday, July 20, 2018 4:27 PM
  • 1. Noticing any temp space contention? Is temp sized and configured same for both instance? Check this link https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my/

    2. Are you seeing any specific wait type while running the query in 2016?

    Thanks,

    -SreejitG

    Friday, July 20, 2018 7:10 PM
  • Pappasa,

    We are experiencing the same exact issues you are with SQL 2016 being about 50% slower than SQL 2008 R2. Did you ever come to any conclusions on your issue?


    If you see such a problem and want help with it start a new thread, and include full details of how you arrived at this conclusion. It's useless to piggyback on an old thread.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 20, 2018 9:27 PM
  • No, unfortunately, I did not find out.
    Monday, July 23, 2018 7:22 AM
  • for sure SQL Server 2016 is faster then 2008.

    i have few question
    did you  rebuild indexes after migration. index usage and stats are diffrent with 2008 and 2016
    did you check the IOPS of you OLD SQL Server and New SQL Server


    Ramesh Babu Vavilla MCTS,MSBI

    Monday, July 23, 2018 2:18 PM
  • I have run the TPC-H queries at scale-factor 1, 10 and 100 since SQL Server 2000 to present, on hardware from Xeon 5160 to Xeon E5 v4. There has not been a significant negative change in any of 22 TPC-H queries. Caveat, TPC-H does not use functions!

    However, I did notice a sharp drop in performance after the Intel Meltdown and Spectre patches were app for transactional queries (involving few rows, execution time typically less than 20ms). There was no change in the TPC-H queries at SF 100, all execution CPU times > 8sec, elapsed time > 0.7sec each. I do not know if the patches took effect at the OS or SQL Server level. If OS, then both your tests would be affected. If at the SQL Server level, then?

    Another possibility can occur if your system has 2 or more processors (sockets). Should the circumstance arise that in one case the executing thread is access local node memory, and in the other case remote memory, then expect about 40% performance difference.


    jchang

    Monday, July 23, 2018 10:35 PM