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
INSERT
occurs, resulting in a lot of locking / contention issues, or your rows are really wide (very large data types and / or hundreds to thousands+ of columns wide) a few minutes to insert 15k records is unusual - it should be a few seconds or maybe a minute depending on how minimally provisioned your server is. So I think you have always had an issue going on, perhaps like what Stephen's answer suggests.EXEC sp_spaceused 'YourTableName'
) would be useful information to add to your post.spaceused