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

Question

У меня есть две таблицы, в которые мы добавляем около 100 тыс. И 1,5 млн. Новых строк каждый день. Это записи в журнале, и в более чем 99% случаев меня интересуют последние 3 рабочих дня при чтении.

Если я запускаю простой запрос, как

SELECT
0 as Id, ProcessElementName, Null as ModelPath, Status, Remark, ValidFrom, Application, JobID, JobName, CreateDate, CreatedBy, MessageType, Running, Manual, Environment, RunIdentifier, BatchJobGroup, BatchJob, IsTemp, TotalRows = COUNT(*) OVER() 
FROM dbo.pclTB_ProcessElementInfo WITH (NOLOCK)
WHERE
ValidFrom > '6/26/2017 12:00:00 AM'
AND ValidFrom <= '6/26/2017 11:59:59 PM'
AND (Environment in ('---')) AND
(
Remark LIKE '%' + 'btve' + '%'
AND Application = '---'
AND (IsTemp = 0 OR IsTemp IS NULL )
AND ProcessElementName = '---'
)
ORDER BY JobID ASC
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY

это может занять до 10 секунд. В других запросах есть несколько объединений, но большинство из них простые. Когда я обновляю статистику вручную, время выполнения уменьшается до 2 секунд, но я уверен, что еще есть возможности для улучшения (я знаю о флаге трассировки 2371).

Каков наилучший способ оптимизации таблицы (или запроса?) Для получения самых последних строк? Может быть, имеет смысл создать новую таблицу только с записями последних X дней?

Изменить: это индекс, используемый для запроса выше

CREATE NONCLUSTERED INDEX [IX_ProcessElementNameApplicationEnvironmentValidFrom] ON [dbo].[pclTB_ProcessElementInfo]
(
    [ProcessElementName] ASC,
    [Application] ASC,
    [Environment] ASC,
    [ValidFrom] ASC
)
INCLUDE (
    [Status],
    [Remark],
    [JobID],
    [JobName],
    [CreateDate],
    [CreatedBy],
    [MessageType],
    [Running],
    [Manual],
    [RunIdentifier],
    [BatchJobGroup],
    [BatchJob],
    [IsTemp]
    )
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

Answer 1

[1] Разделение - это одно решение (если у вас есть Enterprise Edition и SQL2005/2008[R2]/2012/2014/2016 pre-SP1) или SQL2016SP1 +.

[2] Другое решение - фильтрованные индексы. Я бы создал отфильтрованный индекс для каждого дня за последние 3 дня, таким образом:

CREATE NONCLUSTERED INDEX IXF_Table_2017_06_28_...
ON dbo.Table (SomeColumn1)
INCLUDE (Column2, Column3, ...)
WHERE Timestamp >= '2017-06-28' AND Timestamp < '2017-06-29'

CREATE NONCLUSTERED INDEX IXF_Table_2017_06_27_...
ON dbo.Table (SomeColumn1)
INCLUDE (Column2, Column3, ...)
WHERE Timestamp >= '2017-06-27' AND Timestamp < '2017-06-28'

CREATE NONCLUSTERED INDEX IXF_Table_2017_06_26_...
ON dbo.Table (SomeColumn1)
INCLUDE (Column2, Column3, ...)
WHERE Timestamp >= '2017-06-26' AND Timestamp < '2017-06-26'

Также убедитесь, что эти индексы включают все необходимые столбцы (см. Покрывающие индексы).

[3] Затем первоначальный запрос должен быть переписан так:

SELECT Column1 
FROM dbo.Table WITH (NOLOCK)
WHERE
-- Timestamp filters (these filters should match with filter predicates / `CREATE INDEX ... ON ... WHERE _`)
(
    (Timestamp >= '2017-06-27' AND Timestamp < '2017-06-28') 
    OR (Timestamp >= '2017-06-26' AND Timestamp < '2017-06-26')
)
-- End of Timestamp filters
AND some_conditions
ORDER BY Column1 ASC
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY

[4] Для всех приведенных выше операторов SQL SETдолжны быть использованы данные ( ссылка):

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

SET ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF

Answer 2

Когда вы вставляете данные в таблицу, вставляете в другую таблицу, в которой сохраняются записи за последний x день. И тогда вы можете автоматически удалять записи через определенное время с помощью хранимой процедуры. Как автоматически удалять записи на сервере sql через определенное время

Answer 3

У вас может быть ежедневная работа по воссозданию отфильтрованных индексов. Ваши существующие индексы могут быть продублированы с фильтрацией даты за последние три дня:

DECLARE @sql varchar(8000) = '

IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = ''IX_IndexName'')
    DROP INDEX IX_IndexNameON My_table ;

CREATE NONCLUSTERED INDEX IX_IndexNameON My_table (
    timestamp 
)
WHERE timestamp > ''' + CONVERT(varchar(25),DATEADD(d,-3,GETDATE()) ,121) + ''';';

EXEC (@sql);

Answer 4

Вы можете рассмотреть разбиение таблицы. Допустим, вы создадите раздел за последние 3 дня и для остальных данных. Затем вы обновите свой запрос, чтобы использовать только этот конкретный раздел.
У него есть некоторые ограничения, например, вы можете разбивать данные только на те данные, которые используете для кластерного индекса, но это может быть не так.
Вам не обязательно использовать разные файловые группы, как указано в ссылке выше. Вот еще одна ссылка, которая может вас заинтересовать. Это о том, как реализовать автоматическое скользящее окно в секционированной таблице на SQL Server 2005