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

У меня есть две таблицы, в которые мы добавляем около 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]

План выполнения


sql sql-server query-optimization sql-optimization
person swit    schedule 28.06.2017    source источник
comment
версия sql-сервера?   -  person etsa    schedule 28.06.2017
comment
То, что вы ищете, это РАЗДЕЛЕНИЕ.   -  person Marek Vitek    schedule 28.06.2017
comment
@etsa: SQL Server 2012 с пакетом обновления 3 (SP3)   -  person swit    schedule 28.06.2017
comment
@MarekVitek: я изучал это несколько недель назад, но больше не занимался этой идеей. У вас есть хороший ресурс с дополнительной информацией?   -  person swit    schedule 28.06.2017
comment
Вы можете Google много информации в Интернете. Я прочитал документацию на MSDN, что тоже хорошо. Моя проблема заключалась в ограничении того, какой может быть функция разделения.   -  person Marek Vitek    schedule 28.06.2017
comment
Сначала убедитесь, что вы используете правильные индексы. Посмотрите на план выполнения, чтобы убедиться, что вы ищете таблицы, а не сканируете.   -  person cloudsafe    schedule 28.06.2017
comment
@cloudsafe Индексы кажутся правильными. Согласно плану выполнения поиск по индексу занимает около 80% стоимости.   -  person swit    schedule 28.06.2017
comment
@swift, не могли бы вы рассказать нам, как выглядят ваши индексы? Я хочу сказать, что они учат нас делать индексы как можно более избирательными, что подразумевает, что обычно индексы начинаются с метки времени, что является наихудшим вариантом для подобных запросов.   -  person Marek Vitek    schedule 28.06.2017
comment
Я также посоветовал бы удалить подсказку WITH (NOLOCK). Не для повышения производительности, а как лучшая практика. Кроме того, не могли бы вы добавить план выполнения?   -  person Evaldas Buinauskas    schedule 28.06.2017
comment
@EvaldasBuinauskas: я иногда попадал в тупик без подсказки. Есть ли другой способ их обойти?   -  person swit    schedule 28.06.2017
comment
Существуют и другие рекомендуемые способы устранения взаимоблокировок. Но это немного выходит за рамки этого вопроса.   -  person Evaldas Buinauskas    schedule 28.06.2017
comment
Хорошо, еще кое-что. Каково реальное состояние вашего запроса. И сколько записей он обычно возвращает?   -  person Marek Vitek    schedule 28.06.2017
comment
Remark LIKE '%' + 'btve' + '%' и AND (IsTemp = 0 OR IsTemp IS NULL ) - убийцы. Я бы посоветовал изменить структуру вашей таблицы, чтобы IsTemp был столбцом, не допускающим значение NULL, и заменить NULL на 0. Затем вы можете создать отфильтрованный индекс с условием IsTemp = 0.   -  person Evaldas Buinauskas    schedule 28.06.2017
comment
@MarekVitek: снова отредактировано. Условия в скобках задаются пользователем и могут меняться. Если установлено «ProcessElementName», он возвращает от 100 до 40 тыс. записей.   -  person swit    schedule 28.06.2017
comment
@EvaldasBuinauskas: я знаю, что LIKE неоптимален, но это условия фильтра, установленные конечным пользователем. В чем проблема с IsTemp?   -  person swit    schedule 28.06.2017
comment
Я бы поставил isTemp в качестве первого столбца в индексе. И, как упомянул Эвалдас, убийца здесь, вероятно, замечает что-то вроде этого. Возможно, поместите этот столбец в индекс, а не включайте его в столбцы. Я не вижу здесь другой возможности.   -  person Marek Vitek    schedule 28.06.2017
comment
OR условия обычно медленные. Имея столбец, не допускающий значения NULL, вы сможете изменить свой индекс с условием, которое я дал в предыдущем комментарии. Это уменьшит количество строк, которые вам нужно прочитать.   -  person Evaldas Buinauskas    schedule 28.06.2017
comment
Я бы переместил поле validFrom в первое или второе поле в индексе.   -  person Steve Ford    schedule 28.06.2017
comment
@SteveFord Перемещение validFrom обычно снижает вашу производительность. Это нормально для этого запроса. Но isTemp действительно может помочь. По моим оценкам, это может сократить время вдвое. Но это зависит от распределения данных в этом столбце.   -  person Marek Vitek    schedule 28.06.2017
comment
Я сейчас перестраиваю index. Спасибо за момент.   -  person swit    schedule 28.06.2017
comment
Что касается столбца «Примечания», я не уверен, было бы лучше иметь его до ValidFrom или после него. Так что вам, возможно, придется немного поэкспериментировать с ним.   -  person Marek Vitek    schedule 28.06.2017
comment
@MarekVitek: добавление IsTemp уже вдвое сократило время выполнения. Спасибо. Я не ожидал, что это окажет такое влияние на производительность. Сейчас я просматриваю все свои индексы и попытаюсь, возможно, добавить несколько столбцов и изменить порядок тех, которые у меня есть. Спасибо   -  person swit    schedule 28.06.2017

Ответы (3)


arrow_upward
1
arrow_downward

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

person cih y2k    schedule 28.06.2017

arrow_upward
1
arrow_downward

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

person Marek Vitek    schedule 28.06.2017
comment
@Swit должен будет подтвердить, есть ли у них Enterprise Edition или нет. - person cloudsafe; 28.06.2017
comment
У нас есть две базы данных с версией Developer Edition и одна с версией Enterprise. И чтобы было ясно, разделы должны быть статическими, и мы не можем использовать относительную дату, верно? - person swit; 28.06.2017
comment
Разделы доступны только в Enterprise Edition. - person cloudsafe; 28.06.2017

arrow_upward
1
arrow_downward

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

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);
person cloudsafe    schedule 28.06.2017