0

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
8
  • Start by turning on Query Store to collect performance metrics for your queries. learn.microsoft.com/en-us/sql/relational-databases/performance/…. Oct 19, 2021 at 20:54
  • I have just enabled this, thank you
    – Daryl Gill
    Oct 19, 2021 at 21:27
  • FWIW, a few minutes to insert ~15k records (even in a table with over a million rows) is already a red flag to me. Unless you have an extremely busy server / table during when this 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.
    – J.D.
    Oct 19, 2021 at 22:16
  • 1
    Nonetheless, having your table definition with their data types, and perhaps the table size (which you can get from running EXEC sp_spaceused 'YourTableName') would be useful information to add to your post.
    – J.D.
    Oct 19, 2021 at 22:17
  • 1
    @J.D. I have updated my post with the table definition & The results from spaceused
    – Daryl Gill
    Oct 20, 2021 at 17:53

1 Answer 1

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.

1
  • I have just enabled the Query Store on the Databse. I'll leave it a couple of days to collect information & Update here. I'll look into a clustered index in the mean time, thank you for the suggestion
    – Daryl Gill
    Oct 19, 2021 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.