Inserting 100K rows Performance - Baseline Performance

Question

Inserting 100K rows Performance - Baseline Performance

MichaelT

Old Hand

Points: 386
More actions
May 8, 2024 at 3:08 pm

#4419797
We're trying to understand how quick new versions of SQL server can be. Obviously server specs come into play. Can anyone run this simple test and share the output (ms).
Create simple Table
CREATE TABLE [dbo].[Data](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Comment] [varchar](50) NOT NULL,
[CreateDate] [datetime] NOT NULL,
CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Data] ADD CONSTRAINT [DF_Data_CreateDate] DEFAULT (getdate()) FOR [CreateDate]

Insert 100k Rows
SET NOCOUNT ON
DECLARE @StartDate DATETIME2
SET @StartDate = CURRENT_TIMESTAMP
DECLARE @CreateDate DATETIME = GETDATE()
DECLARE @index INT = 1
WHILE @index <= 100000
BEGIN
INSERT INTO Data (
Comment,
CreateDate
)
VALUES (
'Testing insert operations',
@CreateDate
)

SET @index +=1
END
SELECT DATEDIFF(ms, @StartDate, CURRENT_TIMESTAMP)
Please post the results along with SQL Server Version and some basic specs (CPUs and RAM).
We've seen crazy variances between 4 boxes.
- This topic was modified 7 months, 2 weeks ago by MichaelT.

Viewing 12 posts - 1 through 11 (of 11 total)

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

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

this is a waste of time in my opinion - see history behind this request at https://www.sqlservercentral.com/forums/topic/degraded-performance-in-2017-vs-2008-r2-with-inserts

I can run this code in multiple servers, with different specs and results will vary greatly - and it won't really tell me if the server is setup correctly or if there are issues with the sql patch (which on my case would be the same in all sql versions (2016 to 2019) that I have)

and for that volume I would guess a good number of them would take around 5-7 seconds to do it

if this is how your system is inserting data (e.g. 1 row at the time) you likely should revisit it and change it so it does it in bulk.

MichaelT Old Hand Points: 386 More actions · Answer 2

I think it would be interesting to see performance differentials to know what is possible. Also we may see performance differences between versions. The script is simple. If anything, I would recommend running just to see if your system is indeed quick.

As I said, I tested it on 4 - I'm too lazy to start my decommissioned 5th SQL Server (2014) one and the results are crazy.

5-7 is not bad. Is that what you got?

I have 8, 21, 75, and 80.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

I think that maybe the HammerDB code you ran in the other thread is a better tell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MichaelT Old Hand Points: 386 More actions · Answer 4

Yeah, that machine's results are all over the map with HammerDB and very low. It ranges from 740-35,000 TPS. 50x difference...

But my other test results (not HammerDB) were metronomic in terms of consistency including the script that pegs the CPUs at 100%. HammerDB is the first tool to show such variance on a single machine.

This is quite the conundrum.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

Are you planning on basing your software purchasing decisions on the results? If not, why are you doing this?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

frederico_fonseca SSCoach Points: 16167 More actions · Answer 6

Phil Parkin wrote:

Are you planning on basing your software purchasing decisions on the results? If not, why are you doing this?

see the other threads from the OP - using a sql 2017 on new box, without the latest CU's, and with a very high probability of misconfigured VM and storage and performance is significantly worst than a corresponding SQL 2008 VM.

MichaelT Old Hand Points: 386 More actions · Answer 7

Phil Parkin wrote:

Are you planning on basing your software purchasing decisions on the results? If not, why are you doing this?

Trying to get a sense of reasonable performance.

For instance, we just tested a m5d-xlarge AWS instance with SQL Server 2019 and it posted 93 seconds. A bit slower than 8 seconds for the Sql Server 2008 and 21 seconds for the SQL server 2017. But in line with our AWS results.

We also spun a RDS service with 10,000 IOPS (we couldn't select 64,000) and it posted 32 seconds.

I'm just trying to understand if the performance is reasonable. Federico is right that there is an issue and obviously that's why I'm here. If our SQL 2017 processed the results out-of-the-box in the same time as the SQL 2008 or lower (let's use a range of 4-8 seconds), I would have moved on.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 8

one one of your other posts you mentioned that your VM's were Vmware - AWS and AZURE are a totally different kettle of fish and settings for those are way different - not only the machine type affect performance but disk type and its settings have a HUGE impact on performance - as well whether you have a single disk or volumes (and on these the underlying disk type will also impact performance)

MichaelT Old Hand Points: 386 More actions · Answer 9

frederico_fonseca wrote:

Phil Parkin wrote:
Are you planning on basing your software purchasing decisions on the results? If not, why are you doing this?
see the other threads from the OP - using a sql 2017 on new box, without the latest CU's, and with a very high probability of misconfigured VM and storage and performance is significantly worst than a corresponding SQL 2008 VM.

Well, it's the latest GDR 🙂

There's benefit to this because I honestly don't know how quick SQL server should be in a simple operation and I suspect the vast majority of SQL server implementations are probably compromised unless they are perfectly configured out of the box.

This would give them a baseline of performance.

Perhaps, you have already done a poll of performance and you can point me to that. If I were a novice, I would have accepted the performance of the SQL Server 2017 box but I've worked on 2000, 2005, 2008, 2014, 2016, 2017 over 30 years so I know what's reasonable in terms of performance and can even tell if a database should struggle in a query just by running it without even seeing the execution plan.

I had 3 other boxes so I could compare and I have a HammerDB tool of my own to test against since our model does what HammerDB does only against massive tables. HammerDB just helped us figure out a bit more.

For instance, in my HammerDB tool we got 8,000 rows per second with 24 parallel operations followed by spatial queries (insanely optimized) on the SQL Server 2008 box. That's 480,000 rows per minute in 24 transactions (no DTC) - not equivalent to 480,000 TPMs but not shabby especially since every row is processed over the network and not batched to reduce network trips.

Our SQL Server 2017 box posts 6,000 rows per seconds which is 360,000 rows per minute. So it's on average 33% slower but it gets complicated because SQL Server compatibility mode can make a difference (we tested it with every compatibility between 2008 and 2014 for the CE estimator introduced in 2014) and that box is capable of matching the other box in 2008 compatibility mode and being almost as stable.

As you can tell, we've done a lot of thorough testing which is why I finally boiled it down to the most simple operation that can be performed.

Inserting a few rows directly in SSMS in the simplest flat table without any rows in it to start and giving it enough time to exceed to go over 1 second of processing so you can tell the difference.

MichaelT Old Hand Points: 386 More actions · Answer 10

frederico_fonseca wrote:

one one of your other posts you mentioned that your VM's were Vmware - AWS and AZURE are a totally different kettle of fish and settings for those are way different - not only the machine type affect performance but disk type and its settings have a HUGE impact on performance - as well whether you have a single disk or volumes (and on these the underlying disk type will also impact performance)

Agreed but as I pointed out here, the performance of a fairly decent AWS instance (a few thousand dollars a year in operating costs and much more without a SQL Server license) yields 10x slower results than a VMware 2008 R2 instance that might not be properly configured and is probably sharing resourcs with 7 other vms.

That being said, we've used AWS server for development purposes for over a decade and they have been acceptable for the most part.

So if anyone is using AWS or Azure for production, please check the results and run your scripts and share your results.

I suspect with cloud being so ubiquitous now and with results that are 10x slower, I can't help but wonder how many companies are running versions that are slower than our 2008 box with 4vCPUs. You'd have to build a SQL Server instance that's 10x faster to match our 2008 box 😉

frederico_fonseca SSCoach Points: 16167 More actions · Answer 11

the issue with trying this at a DB level is that you are not proving anything - if the OS/VM/Storage is badly configured then ALL the IO is going to be slow - does NOT mean that the SQL server is slow.

this is why I gave you the scripts for SQLIOSIM as this tests are done outside the SQL instance - but do simulate a load as if it was SQL.

still waiting for you to get back with the results on both servers.