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

Откуда: Москва
Сообщений: 4804
Господа, есть такая задача.

Известно, что очень крупная таблица X не обновляется в период времени Y. Вне периода Y таблица может обновляться.
Кластерный индекс таблицы X построен по дате.
Известно, что в период времени Y таблицу параллельно будут читать несколько десятков запросов, но каждый их них только данные за определённый период времени -- за месяц. Каждый запрос будет читать свой диапазон записей, который не пересекается с другими запросами. Характер запросов -- полное считывание всех данных за период. (SELECT * FROM X WHERE DT BETWEEN ....)
Таблицу Х можно партиционировать по месяцам.

Вопрос, как заставить эти запросы читать данные максимально быстро. Можно ли заставить запросы не ставить блокировки. Или уменьшить кол-во блокировок.

Один из способов добиться этого -- перевести таблицу в readonly, но есть одна проблема -- для переключения файловой группы в readonly и назад требуется переход в Single_user, чего я позволить не могу.

ALTER DATABASE [...] MODIFY FILEGROUP [READONLY] READWRITE

Msg 5070, Level 16, State 2, Line 4
Database state cannot be changed while other users are using the database '...'

Есть ли другие способы свести на нет блокировки и ускорить чтение при перечисленных допущениях. NOLOCK уже стоит, но он не отменяет всех блокировок.
15 май 15, 15:29    [17645978]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
a_voronin
NOLOCK уже стоит, но он не отменяет всех блокировок.
Ага не отменят. Будет висеть Sch-S на таблице. Что никак не влияет на производительность чтения.
Более того, на RC, если страница не содержит грязные данные, S-блокировки не накладываются. Так что можно и без NOLOCK обойтись.
В общем, проблема у вас не в блокировках.
15 май 15, 15:43    [17646106]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
invm
В общем, проблема у вас не в блокировках.


Есть такой факт. Что чтение по readonly в параллель происходит в 2 раза быстрее. Как вы его объясняете?

Собственное проблемы нет, вопрос лишь в скорости. Как её повысить. Я лишь хочу понять, можно ли заставить без readonly читать также быстро как по readonly. Чтение с TABLOCKXX тоже быстрое, но в этом случает не будет параллельности чтения.
15 май 15, 15:48    [17646136]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
a_voronin
Есть такой факт. Что чтение по readonly в параллель происходит в 2 раза быстрее. Как вы его объясняете?
Если коротко, то не верю.

Сообщение было отредактировано: 15 май 15, 16:06
15 май 15, 16:06    [17646274]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
a_voronin
Как вы его объясняете?
nolock, readonly, и tablock позволяют оптимизатору выбрать стратегию сканирования кластерного индекса в порядке распределения. Но только при сканировании таблицы. У вас же сканирование диапазона и оно будет в порядке индекса.
Так что не понятно, что там у вас за факт и что вы имеете в виду под "параллельностью чтения".
15 май 15, 16:10    [17646301]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
MSSQLBug
Guest
a_voronin
invm
В общем, проблема у вас не в блокировках.


Есть такой факт. Что чтение по readonly в параллель происходит в 2 раза быстрее. Как вы его объясняете?

Собственное проблемы нет, вопрос лишь в скорости. Как её повысить. Я лишь хочу понять, можно ли заставить без readonly читать также быстро как по readonly. Чтение с TABLOCKXX тоже быстрое, но в этом случает не будет параллельности чтения.

А пробовали без NOLOCK? А то бывают и вот такие штуки: http://www.queryprocessor.com/nolock-and-top-optimization/
15 май 15, 19:06    [17647226]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2676
a_voronin,

Партиции, конечно накладно с точки зрения трудозатрат, но весьма выгодно в описанной вами ситуации в разрезе:
1. Изоляции локов
2. Уменьшения глубины Б-дерева индексов
3. Потенциального распараллеливания запросов к view
16 май 15, 02:04    [17648148]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
a_voronin,

с чего ты решил, что блокировки так уж сильно влияют на скорость чтения данных из таблицы?
Если в это время таблица не меняется, то читателю вообще никто не будет мешать.

Короче, не майся дурью...
16 май 15, 07:39    [17648239]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
[quot a_voronin]
invm
В общем, проблема у вас не в блокировках.


Есть такой факт. Что чтение по readonly в параллель происходит в 2 раза быстрее. Как вы его объясняете?

Твоей неспособность адекватно поставить эксперимент и замерить время выполнения.


Собственное проблемы нет, вопрос лишь в скорости. Как её повысить.


повышается скорость чтения данных в бд двумя способами:
-- использованием индексов по критериям поиска.
-- кэшированием.

блокировки на скорость чтения не влияют, особенно в твоем случае.

no lock тоже лучше убирать, раз он не нужен.
16 май 15, 07:46    [17648242]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
какие блокировки пацанчик
Guest
a_voronin
Господа, есть такая задача.

Известно, что очень крупная таблица X не обновляется в период времени Y. Вне периода Y таблица может обновляться.
Кластерный индекс таблицы X построен по дате.
Известно, что в период времени Y таблицу параллельно будут читать несколько десятков запросов, но каждый их них только данные за определённый период времени -- за месяц. Каждый запрос будет читать свой диапазон записей, который не пересекается с другими запросами. Характер запросов -- полное считывание всех данных за период. (SELECT * FROM X WHERE DT BETWEEN ....)
Таблицу Х можно партиционировать по месяцам.

Вопрос, как заставить эти запросы читать данные максимально быстро. Можно ли заставить запросы не ставить блокировки. Или уменьшить кол-во блокировок.

Один из способов добиться этого -- перевести таблицу в readonly, но есть одна проблема -- для переключения файловой группы в readonly и назад требуется переход в Single_user, чего я позволить не могу.

ALTER DATABASE [...] MODIFY FILEGROUP [READONLY] READWRITE

Msg 5070, Level 16, State 2, Line 4
Database state cannot be changed while other users are using the database '...'

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

озвучено: есть микроскопическое количество неблокирующих друг друга запросов, которые по диапазонам чтения точно не пересекаются с обновляемыми диапазонами
задача при этом: какие хаки, приемы лоу-левел тюнинга можно применить к этим запросам, чтобы уменьшить количество блокировок, чтобы ускорить процесс чтения с диска/из кэша
16 май 15, 10:48    [17648370]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
Гавриленко Сергей Алексеевич
a_voronin
Есть такой факт. Что чтение по readonly в параллель происходит в 2 раза быстрее. Как вы его объясняете?
Если коротко, то не верю.
+1
a_voronin
Вопрос, как заставить эти запросы читать данные максимально быстро. Можно ли заставить запросы не ставить блокировки. Или уменьшить кол-во блокировок.

Один из способов добиться этого -- перевести таблицу в readonly
Непонятно, чем чтение в режиме readonly отличается от чтения в обычном режиме?
Если все коннекты только читают, то они друг другу не мешают.

Разве что в readonly уменьшаются накладные расходы на блокировки, но их можно уменьшить хинтом эскалации блокировки до уровня таблицы.
16 май 15, 11:05    [17648395]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Максимально быстро работают запросы, имеющие наименьшее количество чтений. Оптимизируйте запросы. Причем здесь блокировки.
16 май 15, 11:52    [17648467]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Владислав Колосов
Максимально быстро работают запросы, имеющие наименьшее количество чтений. Оптимизируйте запросы. Причем здесь блокировки.


И как можно оптимизировать прямолинейный скан по диапазону кластерного индекса?

SELECT * FROM X WHERE DT BETWEEN ...
16 май 15, 13:50    [17648778]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
alexeyvg
Разве что в readonly уменьшаются накладные расходы на блокировки, но их можно уменьшить хинтом эскалации блокировки до уровня таблицы.


С чего начали к тому и пришли. Нужно параллельно читать разные диапазоны кластерного индекса.
16 май 15, 13:51    [17648786]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
a_voronin
alexeyvg
Разве что в readonly уменьшаются накладные расходы на блокировки, но их можно уменьшить хинтом эскалации блокировки до уровня таблицы.


С чего начали к тому и пришли. Нужно параллельно читать разные диапазоны кластерного индекса.

понизить пороги для параллелизма, чтоб параллелизм параллелил?
17 май 15, 01:23    [17650588]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
К примеру таблица "t", столбец "a" int, идентити, кластерный индекс, столбец "b" к примеру варчар, для теста там будет одно и тоже (1, "1")(2,"2")... , инсертим туда 10 миллионов записей.

делаем запросы, смотрим время. у меня 1 и 3 секунды соответственно:

select max(b) from t 
where a between 1 and 9999999
option(maxdop 4)

Картинка с другого сайта.

select max(b) from t 
where a between 1 and 9999999
option(maxdop 1)


Картинка с другого сайта.
17 май 15, 01:52    [17650612]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
a_voronin
Владислав Колосов
Максимально быстро работают запросы, имеющие наименьшее количество чтений. Оптимизируйте запросы. Причем здесь блокировки.


И как можно оптимизировать прямолинейный скан по диапазону кластерного индекса?

SELECT * FROM X WHERE DT BETWEEN ...

ну, допустим, что никак, и что запросы и так уже имеют оптимальные планы.
Как из этого факта следует, что нужно убирать блокировки?
Почему ты думаешь, что блокировки тебе так мешают?
17 май 15, 07:12    [17650703]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
a_voronin
Есть такой факт. Что чтение по readonly в параллель происходит в 2 раза быстрее.

Предположим, что "в параллель" означает параллельную вычитку разных диапазонов в разных сессиях. Проверяем:
+ Подготовка
create database TestReads;
go

use TestReads;

create table dbo.t (id int identity primary key, v char(300));

insert into dbo.t
 (v)
 select top (4000000)
  'a'
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;
+ Тест
Открываем три соединения.
Выполняем в первом:
use TestReads;
exec sp_getapplock N'TestReads', 'Exclusive', 'Session';

declare @v char(300)
select @v = v from dbo.t;

Выполняем во втором:
use TestReads;
exec sp_getapplock N'TestReads', 'Shared', 'Session';

declare @v char(100)
set statistics time, io on;
select @v = v from dbo.t where id between 1 and 1000000;
set statistics time, io off;

exec sp_releaseapplock N'TestReads', 'Session';

Выполняем в третьем:
use TestReads;
exec sp_getapplock N'TestReads', 'Shared', 'Session';

declare @v char(100)
set statistics time, io on;
select @v = v from dbo.t where id between 1000001 and 2000000;
set statistics time, io off;

exec sp_releaseapplock N'TestReads', 'Session';

Выполняем в первом:
exec sp_releaseapplock N'TestReads', 'Session';

Во втором и третьем получаем результат:
Во втором
Table 't'. Scan count 1, logical reads 40152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 278 ms.

В третьем
Table 't'. Scan count 1, logical reads 40153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 271 ms.


Переводим БД в read_only, повторяем тест и получаем:
Во втором
Table 't'. Scan count 1, logical reads 40152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 288 ms.

В третьем
Table 't'. Scan count 1, logical reads 40153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 270 ms.

Итого: никаких "в 2 раза быстрее" не наблюдаем.

В качестве "подопытнго кролика" был:
Microsoft SQL Server 2014 - 12.0.2254.0 (X64) 
Jul 25 2014 18:52:51
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Теперь предположим, что "в параллель" означает план выполнения с параллелизмом.
Так вот, запрос вида
SELECT * FROM X WHERE DT BETWEEN ....
с кластерным индексом по DT, не может давать план с параллелизмом, ибо там параллелить нечего. Не поможет даже TF 8649.

Можно обмануть оптимизатор и получить таки план с параллелизмом. В терминах вышеприведенного теста это будет выглядеть так:
select * from dbo.t where id between 1 and 1000000 and v <> 'b' option (querytraceon 8649);
Но весь возможный выигрыш от параллельного выполнения будет нивелирован затратами на передачу результата клиентскому приложению.

Так что еще раз - не туда копаете.
17 май 15, 12:32    [17651068]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
invm
с кластерным индексом по DT, не может давать план с параллелизмом, ибо там параллелить нечего. Не поможет даже TF 8649.


Что насчёт партиций? Если считываем месяц, а партиции по дням, например. Даст это параллелизм?
19 май 15, 17:17    [17662968]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Makar4ik
a_voronin,

Партиции, конечно накладно с точки зрения трудозатрат, но весьма выгодно в описанной вами ситуации в разрезе:
1. Изоляции локов
Может я чего не понимаю, но читатели не блокируют друг-друга, что даст изоляция локов?
Makar4ik
2. Уменьшения глубины Б-дерева индексов
А это что прям таки даст большой выигрыш? При сканировании кластерного индекса?
Makar4ik
3. Потенциального распараллеливания запросов к view
Это вообще не понял. Какое view?
19 май 15, 20:11    [17663689]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
a_voronin
invm
с кластерным индексом по DT, не может давать план с параллелизмом, ибо там параллелить нечего. Не поможет даже TF 8649.


Что насчёт партиций? Если считываем месяц, а партиции по дням, например. Даст это параллелизм?
А что именно вы хотите параллелить? Чтение с диска что ли?
19 май 15, 20:27    [17663720]     Ответить | Цитировать Сообщить модератору
 Re: снижение уровня блокировок  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
a_voronin
Что насчёт партиций? Если считываем месяц, а партиции по дням, например. Даст это параллелизм?
Не даст. Нет никакого смысла в распараллеливании - данные уходят клиенту в любом случае последовательно.
19 май 15, 20:44    [17663759]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить