May 15, 2024 at 5:19 pm
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.
May 15, 2024 at 5:24 pm
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.
May 15, 2024 at 5:53 pm
you will likely need the CU+GDR version - https://support.microsoft.com/en-us/topic/kb5029376-description-of-the-security-update-for-sql-server-2017-cu31-october-10-2023-ce23ddf7-b79e-4ba7-ba9d-2679f23a1ad8
May 15, 2024 at 9:09 pm
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?
May 15, 2024 at 10:34 pm
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
May 16, 2024 at 2:35 pm
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.
May 17, 2024 at 9:42 pm
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).
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply