I'm using SqlBulkCopy to insert a lot of data (10000/second at peek) into a database. And when the table starts empty, each bulk insertion takes like 300 milliseconds. But after a day when the records count become more than 20 millions, the insertion time becomes 2.5 seconds per 10K records. And this time increases with the count of records.
I have only a simple table, with no indexes except an identity clustered primary key.
I thought that this behavior is an append-only behavior and won't be affected by the table size. Like throwing a pile of litter on yet a huge pile of litter. That shouldn't be affected by the size.
What do I miss here?
Update: Table's script created via SSMS is:
CREATE TABLE [pardis].[DeliveryReports]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [PardisId] [bigint] NOT NULL, [Status] [bigint] NOT NULL, [PartId] [bigint] NOT NULL, [ShortCode] [varchar](100) NOT NULL, [PhoneNumber] [varchar](100) NOT NULL, [DateTimeReceived] [datetime] NOT NULL, [PersianDateTimeReceived] [varchar](100) NOT NULL, [DateTimeSentToCp] [datetime] NULL, [PersianDateTimeSentToCp] [varchar](100) NULL, [DeliveringToCpResult] [nvarchar](max) NULL, [IsProcessing] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsProcessing_1] DEFAULT ((0)), [IsSentToCp] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsSentToCp] DEFAULT ((0)), CONSTRAINT [PK_DeliveryReports] 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] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Update 2: This is the execution plan for inserting 1 record via SSMS. As you can see, no hidden cost exists in inserting records in the table. Please note that this execution plan is after deleting the primary key index to prevent any index from affecting the performance.