MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
This has 4,938 rows, consuming 9.461 MB.
CREATE TABLE [dbo].[Integrations_Queue]( [QueueID] [bigint] IDENTITY(1,1) NOT NULL, [IntegrationID] [tinyint] NULL, [RecID] [bigint] NULL, [QueuedAt] [smalldatetime] NULL, [SubmittedAt] [smalldatetime] NULL, [IsError] [tinyint] NULL, [XMLData] [varchar](max) NULL, CONSTRAINT [PK_Integrations_Queue] PRIMARY KEY CLUSTERED ( [QueueID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
If I clear out most of the rows, delete the index, re-add the index, all is well until I get back up to a higher number of rows. If I simply rebuild the index, or even reorganize (even to the point that it has nearly 0% fragmentation), it will still take several minutes on an update query. And it's not even the index that's really the issue I don't think - I just issued a query a while ago after archiving 4,800 records into a different table - the following query took > 10 minutes to delete all 4,832 rows:
UPDATE Integrations_Queue SET SubmittedAt=GETUTCDATE(), IsError=0 WHERE QueueID=23923
DELETE FROM Integrations_Queue WHERE SubmittedAt IS NOT NULL AND IsError=0
SELECT * FROM Integrations_Queue WHERE QueueID=28723
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Linked Server Issue with SQL2012||3||33|
|length of the password hash sha1:64000 to set sql field property.||13||89|
|Enabled trace flag 4135 or 4199 - SQL SERVER||2||24|
|SQL server client app||3||33|