I have several SQL Server 2008 databases - all are in the 1GB to 3GB range. All run beautifully smooth in performance, except for update queries on two tables (very similar), in two different databases.
Here's an example of the issue.
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This has 4,938 rows, consuming 9.461 MB.
Once this table gets in the realm of anywhere from about 4,800 rows to 7,500 rows, update queries against the QueueID primary key take like 4 minutes. Example query that has horrible performance:
SET SubmittedAt=GETUTCDATE(), IsError=0
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:
DELETE FROM Integrations_Queue WHERE SubmittedAt IS NOT NULL AND IsError=0
Again, the rest of these two databases run beautifully - many tables with as many as 1,000,000 rows, stored procedures joining way too many tables, it doesn't appear to be an issue with the databases themselves, the drives they reside on, or a lack of memory - no query in this database takes more than just a second, maybe two at the worst, except for this update query on this one table.
I'm at a total loss as to why this one table, in each of two different databases, is having this issue? Any thoughts?
This is SQL Server 2008 Workgroup Edition, running on Windows Server 2008 R2 Standard 64-bit. 12 GB ram (I know WGE maxes out at 4GB), dual hex core processors. It's not like it's a P2! haha
Thanks so much for any advice you may be able to offer!
EDIT: cleared out all rows except for what I NEEDED in this table - 106 rows, 0.180 MB - the above update query is now running a 15 minutes, and still counting. I have no index on this table at this particular moment, but 15 minutes to scan 106 rows?!?!
EDIT 2: A select statement for this same row (referencing the same row as the update query is updating) -
SELECT * FROM Integrations_Queue WHERE QueueID=28723
takes 0 seconds, according to query analyzer.
It's not an issue with select statements, just an issue with update and delete. And inserts work great, as well.