Handling MSSQL Insert degradation

Question

I have a SQL Server 2019 Express with no extended configuration aside from custom memory allocation which is as followed:

10GB Memory Usage Maximum

0 Minimum

0 Set for Dynamic Index Creation

10240KB Single Query Allocation

It is becoming painfully apparent that there is a degredation with consecutive simple insert queries, what used to take a few minutes to insert 15k Enteries (rounding up) when the table size was small. Thought brilliant, there is no need to worry about degredation for a long time. Well, scale forward. The Table is at (currently) 1,355,912 rows and has become painfully slow for the same set of inserts on this specific table & this grows once a day by ~15k rows which is being fed from custom applications (written in both node and C# which have been eliminated as a cause)

What used to take minutes has slowly become in excess of 20 minutes to finish this task.

The actual data that is being inserted is small, only a few integers and strings. With an auto index set to a RowID.

IndexName: (blank)

IndexID: 0

Level: 0

Pages: 13445

Rows: 1355912

Minimum Record Size: 65

Maximum Record Size: 102

Average Record Size: 77.8

Forwarded Records: 0

Extents: 1681

ExtentSwitches: 1680

Average FreeBytes: 48.167

Average Page Density: 99.4049048678033

Scan Density: 100

Best Count: 1681

Actual Count: 1681

LogicalFragmentation: 0

ExtendedFragmentation: 0.65437239738251

The index has a datatype of bigint, this is never referenced by any application.

What would be some of the solutions to handle performance degredation from simple insert queries?

For reference, I have another table which has only just started growing by the day & the inserts are much larger, we're talking in excess of 18k. Row count is still low, so it is running smooth when inserting this number of queries.

as a follow up for the request posted below, sp_spaceused nets the following results:

Rows: 1370356

Reserved: 114184 KB

data: 114112 KB

index size: 8KB

unused: 64 KB

With table structure:

[DBID] [bigint] IDENTITY(1,1) NOT NULL,
[Position] [int] NULL,
[Name] [varchar](max) NULL,
[Stat] [int] NULL,
[Lv] [int] NULL,
[Gu] [varchar](max) NULL,
[M] [varchar](max) NULL,
[Class] [varchar](max) NULL,
[Recorded] [date] NULL,
[Daily] [int] NULL,
[Territory] [varchar](max) NULL

Answer 1

You should add a clustered index. A table is either a heap ( & has an indexid of 0 ) Or it has a clustered index ( which always has an indexid of 1 ) When you insert to a heap there is a scan for freespace - probably this is what is slowing your workload down. If you could collect the wait statistics a better diagnosis could be given.