SQL Server 2005
I need to be able to continuously process about 350M records in a 900M record table. The query I'm using to select the records to process becomes badly fragmented as I process and I have a need to stop the processing to rebuild the index. Pseudo data model & query ...
/**************************************/
CREATE TABLE [Table]
(
[PrimaryKeyId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[ForeignKeyId] [INT] NOT NULL,
/* more columns ... */
[DataType] [CHAR](1) NOT NULL,
[DataStatus] [DATETIME] NULL,
[ProcessDate] [DATETIME] NOT NULL,
[ProcessThreadId] VARCHAR (100) NULL
);
CREATE NONCLUSTERED INDEX [Idx] ON [Table]
(
[DataType],
[DataStatus],
[ProcessDate],
[ProcessThreadId]
);
/**************************************/
/**************************************/
WITH cte AS (
SELECT TOP (@BatchSize) [PrimaryKeyId], [ProcessThreadId]
FROM [Table] WITH ( ROWLOCK, UPDLOCK, READPAST )
WHERE [DataType] = 'X'
AND [DataStatus] IS NULL
AND [ProcessDate] < DATEADD(m, -2, GETDATE()) -- older than 2 months
AND [ProcessThreadId] IS NULL
)
UPDATE cte
SET [ProcessThreadId] = @ProcessThreadId;
SELECT * FROM [Table] WITH ( NOLOCK )
WHERE [ProcessThreadId] = @ProcessThreadId;
/**************************************/
Data content...
While the column [DataType] is typed as a CHAR(1), about 35% of all the records equal 'X' with the remainder equaling 'A'.
Of only the records where [DataType] equals 'X', about 10% will have a NOT NULL [DataStatus] value.
The [ProcessDate] and [ProcessThreadId] columns will be updated for every record processed.
The [DataType] column is updated ('X' is changed to 'A') about 10% of the time.
The [DataStatus] column is updated less than 1% of the time.
For now my solution is to select the primary key of all the records to process into a separate processing table. I delete the keys as I process them so that as the index fragments I'm dealing with less records.
However, this doesn't fit the workflow I want to have so that these data are processed continuously, without manual intervention and significant downtime. I do anticipate downtime on a quarterly basis for housekeeping chores. But now, without the separate processing table, I can not get through processing even half of the data set without the fragmentation becoming so bad as to necessitate stopping and rebuilding the index.
Any recommendations for indexing or a different data model? Is there a pattern I need to research?
I have full control of the data model and the process software so nothing is off the table.