Оптимизировать таблицу базы данных SQL для многократного чтения и записи

Question

Я разрабатываю функцию, которая будет использоваться в качестве монитора прогресса процессов.

Я буду охватывать 40-50 потоков, на завершение которых может потребоваться несколько минут или даже часов, и они обновят его статус до данных.

Из веб-приложения я создам механизм опроса, который будет считывать состояние процессов, используя одно чтение каждые 0,5 секунды.

Мне нужно оптимизировать таблицу для нескольких записей в секунду и одного чтения в 0,5 секунды. Мне все равно, если я прочитал грязное состояние, так как это просто для мониторинга процесса, это не так критично.

Это таблица, которую я использую

CREATE TABLE [cmn].[ProcessProgress]
(
    [id] [bigint] NOT NULL,
    [status] [smallint] NOT NULL,
    [step] [int] NOT NULL,
    [max_step] [int] NOT NULL,

    CONSTRAINT [PK_ProcessProgress] 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]
) 
GO

И выберите с (NOLOCK) Я полагаю, я должен использовать

SELECT * FROM [cmn].[ProcessProgress] (NOLOCK)

Нужно ли использовать транзакцию со специальным ISOLATION LEVEL или же (nolock) достаточно? Или же (nolock) заставит думать хуже?

Можете ли вы предложить наиболее оптимальное решение этой проблемы?

Answer 1

Просто установите параметр READ_COMMITTED_SNAPSHOT в вашей базе данных, и читатели и писатели никогда не будут конфликтовать. Вместо этого они будут использовать Row Versioning:

alter database current set read_committed_snapshot on

В дополнение к повышению параллелизма и масштабируемости вашего приложения за счет устранения блокировки между читателями и авторами, оно устраняет множество взаимоблокировок и устраняет стимул для грязного чтения.

Answer 2

Вот пример (надеюсь, id не сделал слишком много ошибок). Тестовый код внизу - это то, что будет выполняться каждым потоком.

if object_id('ProcessProgress') is not null
    drop table ProcessProgress
    Go

CREATE TABLE [ProcessProgress]
(
    [id] [bigint] NOT NULL IDENTITY(1,1), --added identity to shorten sample dev
    [status] [smallint] NOT NULL, --1 -ready,2-inprogress, 3-complete
    [step] [int] NOT NULL,
    [max_step] [int] NOT NULL,

    CONSTRAINT [PK_ProcessProgress] 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]
) 
GO
SET NOCOUNT ON
INSERT INTO [ProcessProgress]
    (status, step, max_step)
VALUES
    (1, 1, 1)
GO 1000

Go
IF OBJECT_ID('StartWork') is not null drop proc StartWork
GO
CREATE PROC StartWork
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    BEGIN TRAN
        ;WITH TODO
        AS (
            SELECT TOP 1 Id, [status] from ProcessProgress WITH (ROWLOCK, READPAST) WHERE [status] = 1 --ready
        )
        UPDATE TODO 
            SET [status] = 2 --InProgress
        OUTPUT inserted.id 

    COMMIT
END
GO

IF OBJECT_ID('FinishWork') is not null drop proc FinishWork
GO
CREATE PROC FinishWork
    @id int
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRAN

    Update  ProcessProgress
        SET [Status] = 3 --finished
    WHERE
        id = @id

    COMMIT
END
GO


/*tester*/
declare @idout table (id int)
insert into @idout exec StartWork

declare @idin int = (Select top 1 id from @idout)
exec FinishWork @idin