Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
Здравствуйте, дамы и господа!
Прошу помощи. В базу MS SQL пишутся данные из скада системы. Таблица заполняется до 100 Гб и запись данных прекращается.
Создал Job, который раз в сутки проверяет размер таблицы и при некотором размере начинает очистку таблицы. Проблема в том, что при выполнении задания запрос от скады встает в очередь и данные в таблицу не пишутся, что есть не хорошо. Можно выполнять задание таким образом, чтобы не прерывать запись в таблицу.
Таблица индексируется по дате. Удаляются записи за первые записанные сутки.
15 дек 16, 17:35    [20008541]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
Читайте про партиционирование.
15 дек 16, 17:38    [20008562]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4832
Князь_Тьмы,

https://www.cathrinewilhelmsen.net/2015/04/19/table-partitioning-in-sql-server-partition-switching/
https://www.toadworld.com/platforms/sql-server/w/wiki/9658.switching-partitions-example
15 дек 16, 19:14    [20008950]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
aleks2
Guest
Гавриленко Сергей Алексеевич
Читайте про партиционирование.

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

Тредстартеру: партиционирование, при наличии кластерного индекса по дате, нужно яко собаке пятая нога.

Просто удаляйте в цикле мелкими порциями
declare @rc int = 1;

while @rc > 0 begin

  delete top(4990) from t where дата < ...

  set @rc = @@rowcount;

end;
15 дек 16, 20:43    [20009136]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
aleks2
партиционирование, при наличии кластерного индекса по дате, нужно яко собаке пятая нога.
Просто удаляйте в цикле мелкими порциями
Блаженны те, кто не сталкивался с ghost-страницами с той стороны индекса, которую надо сканить через top.
15 дек 16, 23:35    [20009490]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Князь_Тьмы,

Как уже посоветовали выше, удаляйте порциями. Если хотите ещё более безболезнено удалять поставте with(paglock) при удалении порциями.
А секционирование при наличии кластерного индекса, по большей части, это элемент (удобство) обслуживания, или хранения.
Переключил секцию в другую таблицу (секцию) и работай с данными. Хочещь удали, хочешь выгрузи и очисть.
16 дек 16, 09:49    [20010033]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
aleks2
Эдак, скоро, для удаления пары записей партицию будем создавать.

Тредстартеру: партиционирование, при наличии кластерного индекса по дате, нужно яко собаке пятая нога.


Зря вы так. Ведь если подумать, секционирование удобно и выгодно использовать не только для запросов чтения - это даже малая часть того для чего их создовали. Ну хотя бы как минимум:
1. Удобство обслуживания таблицы (переносы данных, чистки, архивирование, ребилд, статистика).
2. Параллельная работа с одной таблицей.
3. Работа с небольшим отрезком данных (запросы, статистика, индексы все в рамках секции, а не большой таблицы).
и ещё куча примочек можно написать.
16 дек 16, 10:01    [20010085]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
Спасибо за ответы. Мелкими порциями не вариант. В сутки записывается порядка миллиона строк. В задании удаляю порциями по 100 тысяч строк. Создал два шага выполняющиеся циклически. Пока задание не отработает запрос на вставку данных стоит в очереди. Разве количество строк в запросе на удаление повлияет на другой запрос? Вариант с секциями пока мне нравится больше
16 дек 16, 10:02    [20010089]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
gds
Ведь если подумать, секционирование удобно и выгодно использовать не только для запросов чтения - это даже малая часть того для чего их создовали.
Ну, по сути они в первую очередь и нужны именно для удаления старых данных, и для эффективной пакетной вставки новых.

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

Может, тут можно обойтись цикликом, который будет ненавязчиво раз в день выполняться за 10 минут?
16 дек 16, 10:05    [20010100]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31868
Князь_Тьмы
В сутки записывается порядка миллиона строк.
Мда... Не 100 миллиардов, а всего миллион???
Князь_Тьмы
Разве количество строк в запросе на удаление повлияет на другой запрос?
Ну естественно, если запрос на удаление будет выполняться одну секунду, то другие запросы этого не будут замечать.
Я бы для удаление миллиона строк в день не стал бы городить секционирование.
Удаляйте в цикле по тыще строк, по 10 тысяч, и всё.
Только в нормальном цикле, а не "Создал два шага выполняющиеся циклически"
16 дек 16, 10:09    [20010123]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
gds
Князь_Тьмы,

Как уже посоветовали выше, удаляйте порциями. Если хотите ещё более безболезнено удалять поставте with(paglock) при удалении порциями.
А секционирование при наличии кластерного индекса, по большей части, это элемент (удобство) обслуживания, или хранения.
Переключил секцию в другую таблицу (секцию) и работай с данными. Хочещь удали, хочешь выгрузи и очисть.


Что мне даст paglock? По документации не понял. Эта опция освободит таблицу для инсерта?
16 дек 16, 10:09    [20010128]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Князь_Тьмы
Пока задание не отработает запрос на вставку данных стоит в очереди.


Удаляйте порциями по 10К с paglock - это не даст блокировкам эскалацию до уровня таблицы. и ваши данные буду удаляться и вставляться одновременно, если только вставка не происходит в дату удаления.

Князь_Тьмы
Разве количество строк в запросе на удаление повлияет на другой запрос?

кол-во сток влияет на выделение блокировок. Если строк слишком много, серверу проще наложить блокировку на таблицу.

Если есть возможность покажите план запроса на удаление.
16 дек 16, 10:12    [20010143]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
Только в нормальном цикле

Что Вы подразумеваете под нормальным циклом? Я не профессиональный программист и как SQL обрабатывает запросы пока слабо представляю, а бездумно ваять скрипты не хочу. Хочу понимать как они работают.
16 дек 16, 10:18    [20010174]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Князь_Тьмы,

автор
Таблица индексируется по дате.
это стоит понимать как кластерный индекс? какой скрипт на удаление? какой скрипт на вставку? вставка по нарастающей дате ?
16 дек 16, 10:19    [20010184]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Князь_Тьмы
Только в нормальном цикле

Не надо городить несколько заданий. создайте один запрос или джоб с запросом что то вроде этого
while (1=1) begin
	delete top (10000) d
	from MyTable with (paglock)
	where date<='20161201';
	if @@rowcount = 0
		break;
end

и выполняйте в единственном экземпляре.
16 дек 16, 10:21    [20010200]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
Индекс не кластерный.
Вставка BULK INSERT, подробно описать не могу т.к. вставка идет из программы и каким образом она вставляет я не знаю.
Да по нарастающей
16 дек 16, 10:23    [20010208]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Князь_Тьмы,

автор
Индекс не кластерный.
от беда, а люди то вам поверили. отключите эскалацию и страдайте чуть меньше
16 дек 16, 10:27    [20010223]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Князь_Тьмы
Индекс не кластерный.

А кластерный индекс есть? Не очень хорошо что по дате не кластерный. в этом случае очень спасло бы секционирование.
Уменьшите кол-во строк удаления хотя бы до 2000 - 4000 за раз. И используйте paglock.
16 дек 16, 10:28    [20010232]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
gds
Князь_Тьмы
Индекс не кластерный.

А кластерный индекс есть? Не очень хорошо что по дате не кластерный. в этом случае очень спасло бы секционирование.
Уменьшите кол-во строк удаления хотя бы до 2000 - 4000 за раз. И используйте paglock.

ой ой, да у него скорее всего скопом по индексам и до paglock доходит. И радости от него меньше глобально не станет, только дольше :) Гадать бесполезно, нужны планы и блокировки, хотя бы на удалении
16 дек 16, 10:30    [20010245]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
Скрипт на удаление

declare @date1 as date, @date2 as date, @size as float(20)
set @size = (select dbsize from GPA11.dbo.temp1)
set @date1 = (select top(1) timestamp_utc from GPA11.dbo.DATA_LOG order by timestamp_utc ASC)
set @date2 = (select data from GPA11.dbo.temp1)

if (@size>85 and @date2>@date1)
begin
delete from GPA11.dbo.DATA_LOG where timestamp_utc in (select top(100000) timestamp_utc from GPA11.dbo.DATA_LOG order by timestamp_utc ASC)
end
else begin
use msdb
EXEC dbo.sp_stop_job N'Очистка базы'
end
16 дек 16, 10:32    [20010255]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
Кластерный индекс есть. По другому столбцу
16 дек 16, 10:34    [20010266]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Князь_Тьмы
Member

Откуда:
Сообщений: 18
Это скрипт в первом шаге. Во втором аналогичный. Запускаются они циклически
16 дек 16, 10:36    [20010274]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Князь_Тьмы,

не понятно зачем вам 2 джоба... циклически...
есть WHILE, как вы там первые сутки определяете неясно,

ну и можно как то так
DELETE top(100000) from GPA11.dbo.DATA_LOG WHERE timestamp_utc < date2

но это не спасёт вас от блокировок читателей писателей
16 дек 16, 10:40    [20010316]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
А для delete без подзапроса, а с cte такой же план будет?
16 дек 16, 10:47    [20010355]     Ответить | Цитировать Сообщить модератору
 Re: Одновременное выполнение запросов  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Mike_za
А для delete без подзапроса, а с cte такой же план будет?

а cte по вашему это аки по щучьему велению, а не тот же подзапрос
16 дек 16, 10:48    [20010366]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить