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 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.
Minimum Record Size: 65
Maximum Record Size: 102
Average Record Size: 77.8
Forwarded Records: 0
Average FreeBytes: 48.167
Average Page Density: 99.4049048678033
Scan Density: 100
Best Count: 1681
Actual Count: 1681
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:
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