Degraded Performance in 2017 vs 2008 R2 with inserts

  • Yeah, I'll install CU31 and see.

    I'll check the query store.

    The logs for sqliosim did have some throttling in them.  Is it my impression but was the MDX faster for one and the LDX faster for the other one.  It's kind of hard to read those things.  There were no errors.

  • Well, apparently I can't install CU31 as it's a lower version.

    I downloaded it from

    https://www.microsoft.com/en-us/download/details.aspx?id=56128

    And when I tried to run it, it gave me the following message:

    A SQL Server update with a higher version has already been installed on SQL Server instance MSSQLSERVER, so the current SQL Server update cannot be applied. The version of the SQL Server update that is already installed is GDR 14.0.2052.1(14.0.3465.0) with a KBKB5029375 and the current SQL Server update is 14.0.3456.2 with a KBKB5016884.

  • I installed CU31 and it's slightly faster - we're down to 18 seconds vs 9 so twice as slow.  Tempdb is faster though - have there been huge improvements in tempdb between 2008 and 2017?

  • yes tempdb has been subject to significant changes. majority were done with 2016 - see https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16

  • frederico_fonseca wrote:

    yes tempdb has been subject to significant changes. majority were done with 2016 - see https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16%5B/quote%5D

     

    Thanks, it's the only query where SQL 2017 beats our 2008 box by quite a margin.

    VMWare was purchased by Broadcom so we're trying to sort our account with them.  We are thinking of creating a new vm on the server that 2008 resides with 2017 or 2019 and testing there to see if it's a server configuration or vm issue.  Obviously, we'd like to follow best practices in setting it up.

    The other person with the same CPU using Hyper-V (instead of VMWare), 6 CPUs vs 4, and NVMEs vs SATA SSD got 600,000 TPMs in HammerDB and it ran within 3 refreshes.

    Obviously the NVME is going to give much better performance and 6 vs 4 CPUs will also increase performance possibly by up to 50% (200,000TPM).  We saw 200,000 on our 2008 box with 4 CPUs but that could be accounted by the NVME advantage.

     

  • So I realized that HammerDB is itself a hammer and you can't run it locally as SQL Server will have no resources.  It's a client-server test so I put it on a box with 24vCPUs and we have some interesting numbers:

    SQL Server 2008 

    Hammerdb Transaction Counter Log @ Fri May 17 17:12:22 EDT 2024

    +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

    0 MSSQLServer tpm @ Fri May 17 17:12:24 EDT 2024

    264090 MSSQLServer tpm @ Fri May 17 17:12:34 EDT 2024

    504540 MSSQLServer tpm @ Fri May 17 17:12:44 EDT 2024

    531588 MSSQLServer tpm @ Fri May 17 17:12:54 EDT 2024

    518958 MSSQLServer tpm @ Fri May 17 17:13:04 EDT 2024

    528552 MSSQLServer tpm @ Fri May 17 17:13:14 EDT 2024

    520980 MSSQLServer tpm @ Fri May 17 17:13:25 EDT 2024

    529506 MSSQLServer tpm @ Fri May 17 17:13:35 EDT 2024

    527010 MSSQLServer tpm @ Fri May 17 17:13:45 EDT 2024

    569220 MSSQLServer tpm @ Fri May 17 17:13:55 EDT 2024

    584190 MSSQLServer tpm @ Fri May 17 17:14:05 EDT 2024

    578850 MSSQLServer tpm @ Fri May 17 17:14:15 EDT 2024

    583446 MSSQLServer tpm @ Fri May 17 17:14:25 EDT 2024

     

    Near Metronomic performance between 500,000 and 580,000 - the server is on a different box.  On the same box, it runs a bit faster at 650,000.

    SQL Server 2017 (surprise)

    Hammerdb Transaction Counter Log @ Fri May 17 16:10:31 EDT 2024

    +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

    0 MSSQLServer tpm @ Fri May 17 16:10:32 EDT 2024

    839622 MSSQLServer tpm @ Fri May 17 16:10:43 EDT 2024

    938646 MSSQLServer tpm @ Fri May 17 16:10:53 EDT 2024

    981126 MSSQLServer tpm @ Fri May 17 16:11:03 EDT 2024

    930948 MSSQLServer tpm @ Fri May 17 16:11:13 EDT 2024

    928626 MSSQLServer tpm @ Fri May 17 16:11:23 EDT 2024

    950490 MSSQLServer tpm @ Fri May 17 16:11:33 EDT 2024

    935448 MSSQLServer tpm @ Fri May 17 16:11:43 EDT 2024

    924642 MSSQLServer tpm @ Fri May 17 16:11:54 EDT 2024

    943968 MSSQLServer tpm @ Fri May 17 16:12:04 EDT 2024

    864594 MSSQLServer tpm @ Fri May 17 16:12:14 EDT 2024

    819780 MSSQLServer tpm @ Fri May 17 16:12:24 EDT 2024

    745158 MSSQLServer tpm @ Fri May 17 16:12:34 EDT 2024

    660492 MSSQLServer tpm @ Fri May 17 16:12:44 EDT 2024

    545964 MSSQLServer tpm @ Fri May 17 16:12:55 EDT 2024

    582816 MSSQLServer tpm @ Fri May 17 16:13:05 EDT 2024

    586926 MSSQLServer tpm @ Fri May 17 16:13:15 EDT 2024

    592668 MSSQLServer tpm @ Fri May 17 16:13:25 EDT 2024

    394206 MSSQLServer tpm @ Fri May 17 16:13:35 EDT 2024

    623580 MSSQLServer tpm @ Fri May 17 16:13:45 EDT 2024

    554346 MSSQLServer tpm @ Fri May 17 16:13:56 EDT 2024

    574338 MSSQLServer tpm @ Fri May 17 16:14:06 EDT 2024

    598014 MSSQLServer tpm @ Fri May 17 16:14:16 EDT 2024

    584358 MSSQLServer tpm @ Fri May 17 16:14:26 EDT 2024

    404724 MSSQLServer tpm @ Fri May 17 16:14:36 EDT 2024

    410856 MSSQLServer tpm @ Fri May 17 16:14:46 EDT 2024

    393198 MSSQLServer tpm @ Fri May 17 16:14:57 EDT 2024

    464832 MSSQLServer tpm @ Fri May 17 16:15:07 EDT 2024

    549030 MSSQLServer tpm @ Fri May 17 16:15:17 EDT 2024

    448122 MSSQLServer tpm @ Fri May 17 16:15:27 EDT 2024

    438900 MSSQLServer tpm @ Fri May 17 16:15:37 EDT 2024

    409080 MSSQLServer tpm @ Fri May 17 16:15:47 EDT 2024

    371292 MSSQLServer tpm @ Fri May 17 16:15:57 EDT 2024

    506604 MSSQLServer tpm @ Fri May 17 16:16:08 EDT 2024

    501108 MSSQLServer tpm @ Fri May 17 16:16:18 EDT 2024

    466182 MSSQLServer tpm @ Fri May 17 16:16:28 EDT 2024

    Near 1 million at the start but can drop to 370,000.

    Here's a 2nd run where it does stay over 900,000 TPMs

    Hammerdb Transaction Counter Log @ Fri May 17 17:24:17 EDT 2024

    +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

    0 MSSQLServer tpm @ Fri May 17 17:24:19 EDT 2024

    671238 MSSQLServer tpm @ Fri May 17 17:24:29 EDT 2024

    951810 MSSQLServer tpm @ Fri May 17 17:24:39 EDT 2024

    957678 MSSQLServer tpm @ Fri May 17 17:24:49 EDT 2024

    942780 MSSQLServer tpm @ Fri May 17 17:24:59 EDT 2024

    930330 MSSQLServer tpm @ Fri May 17 17:25:09 EDT 2024

    944520 MSSQLServer tpm @ Fri May 17 17:25:20 EDT 2024

    938922 MSSQLServer tpm @ Fri May 17 17:25:30 EDT 2024

    931596 MSSQLServer tpm @ Fri May 17 17:25:40 EDT 2024

    954342 MSSQLServer tpm @ Fri May 17 17:25:50 EDT 2024

    915396 MSSQLServer tpm @ Fri May 17 17:26:00 EDT 2024

    958962 MSSQLServer tpm @ Fri May 17 17:26:10 EDT 2024

    965298 MSSQLServer tpm @ Fri May 17 17:26:20 EDT 2024

    971808 MSSQLServer tpm @ Fri May 17 17:26:31 EDT 2024

    938802 MSSQLServer tpm @ Fri May 17 17:26:41 EDT 2024

    955638 MSSQLServer tpm @ Fri May 17 17:26:51 EDT 2024

    961104 MSSQLServer tpm @ Fri May 17 17:27:01 EDT 2024

    927618 MSSQLServer tpm @ Fri May 17 17:27:11 EDT 2024

    945186 MSSQLServer tpm @ Fri May 17 17:27:21 EDT 2024

    949842 MSSQLServer tpm @ Fri May 17 17:27:32 EDT 2024

     

    HammerDB now shows 2017 to be 50% faster than 2008 which it is not unless tempdb is involved in which case it's faster.

    These are not bad numbers but our model that inserts millions of rows and spatial processes them is definitely a bit faster and more stable with 2008.  According to HammerDB, it should be running 50% faster (650,000 compared to 950,000).

    • This reply was modified 1 week, 4 days ago by  MichaelT.

Viewing 7 posts - 31 through 36 (of 36 total)

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