Комментарии 21
Эм… А если у нас 20 ядер на каждой ноде — нам тоже выставлять max degree в 1?
Я просто очень часто встречаю данную рекомендацию, но реально её пришлось трогать только в системе, которая написана не очень понимающими механизм работы ms sql людьми, и потому мощно генерирующую некешируемые ad-hoc запросы… Кстати, на той системе всего 8 ядер, но 1 — это не то, что стоит советовать. Стенд из 20 клиентов-роботов генерировал нагрузку, а мы подбирали подходящую величину. Так вот: разумным компромисом между временем выполнения и нагрузкой на ядра хоста оказалась цифра 3.
Я просто очень часто встречаю данную рекомендацию, но реально её пришлось трогать только в системе, которая написана не очень понимающими механизм работы ms sql людьми, и потому мощно генерирующую некешируемые ad-hoc запросы… Кстати, на той системе всего 8 ядер, но 1 — это не то, что стоит советовать. Стенд из 20 клиентов-роботов генерировал нагрузку, а мы подбирали подходящую величину. Так вот: разумным компромисом между временем выполнения и нагрузкой на ядра хоста оказалась цифра 3.
Благодарю за Ваш описанный пример
Да, мы тоже проводили ряд исследований, и в статье написано когда лучше отключать параллелизм, а когда нет (это общая рекомендация, которая не говорит всегда выключать параллелизм, а только в определенных случаях). Тесты мы проводили на серверах, у которых свыше 12 ядер, но на которых стоят экземпляры MS SQL Server 2012-2014 Standart (т е больше 4-х ядер все равно не будет использовать). Но смысл был в том, что при исследовании было выявлено, что параллелизм может породить неэффективный план при хотя бы одном из 3-х условий, которые и описаны в статье. Однако, прошу заметить, что в статье не писалось о том, что всегда нужно запрещать параллелизм. В свое время обратил внимание на то, что при установке системы 1С этот параметр выставляется у скуля в 1, т е почему-то разработчики 1С считают, что в большинстве случаев лучше запрещать параллелизм. Но на самом деле это не всегда так.
Да, мы тоже проводили ряд исследований, и в статье написано когда лучше отключать параллелизм, а когда нет (это общая рекомендация, которая не говорит всегда выключать параллелизм, а только в определенных случаях). Тесты мы проводили на серверах, у которых свыше 12 ядер, но на которых стоят экземпляры MS SQL Server 2012-2014 Standart (т е больше 4-х ядер все равно не будет использовать). Но смысл был в том, что при исследовании было выявлено, что параллелизм может породить неэффективный план при хотя бы одном из 3-х условий, которые и описаны в статье. Однако, прошу заметить, что в статье не писалось о том, что всегда нужно запрещать параллелизм. В свое время обратил внимание на то, что при установке системы 1С этот параметр выставляется у скуля в 1, т е почему-то разработчики 1С считают, что в большинстве случаев лучше запрещать параллелизм. Но на самом деле это не всегда так.
но на которых стоят экземпляры MS SQL Server 2012-2014 Standart (т е больше 4-х ядер все равно не будет использовать)
А можете раскрыть этот момент подробнее? То есть для этой редакции нету смысла использовать CPU с кол-вом ядер более 4-х?
Увы да-Standart более 4-х не использует
Вообще-то для редакции Standart до 16 ядер SQL Server 2008-2014 видит. Для SQL Server 2016 этой же редакции увеличили до 24.
… еще не вижу рекомендаций о горизонтальном масштабировании: как правило, 50-80% нагрузки — это чтение данных, при этом совершенно не обязательно они должны быть актуальны до милисекунды. А с ms sql 2012 появился совершенно прекрасный high avalaibility кластер, в котором существую замечательные read-only реплики. Перенос, например, генерации аналитических отчётов на данные реплики позволяет очень существенно разгрузить основную ноду.
Спасибо за дополнение
Также прошу Вас раскрыть данную тематику или привести ссылки на источники
Также прошу Вас раскрыть данную тематику или привести ссылки на источники
Собственно, источник сокровенного знания, как обычно, в MSDN
Вкратце: в SQL Server 2008 был новый механизм переноса данных на другие сервера — кроме trnsaction log shipping и репликации был добавлен mirroring. Для старта необходим один full backup БД + минимум один transaction log backup. На принимающей стороне данные бекапы восстанавливаются с опцией NORECOVERY. Что происходит в после включения mirroring: все транзакции, происходящие после LSN последнего transation log бекапа по сети едут на принимающий сервер. БД при этом остается в состоянии Recovery.
Существуют две модели переноса транзакций: синхронная и асинхронная. При синхронном переносе транзакций на сервере-источнике любая транзакция не считается законченой, пока сервер-приёмник не подтвердил её получание. При этом гарантируется целостность данных на обеих серверах, однако длительность каждой транзакции значительно увеличивается — кроме собственно времени вставки в БД к длительности добавляет латентность сетевого подключения, причем в две стороны. И, естественно, длительность собственно коммита транзакции на сервер-приёмник.
В случае асинхронного переноса транзакций длительность транзакций на сервере-источнике практически не увеличивается, но данные на сервере-приёмнике могут отставать на несколько миллисекунд. Кроме того, в случае аварии сервера-источника возможны потери нескольких последних транзакций на сервере-приёмнике. Для использования БД на сервере-приёмнике необходимо было перевести БД из состояния RECOVERY в консистентное состояние, однако после данной процедуры зеркалирование по понятным причинам останавливалось.
Начиная с SQL Server 2012 данная технология была развита в Always On Availability Groups. Для работы always on группы необходимо стандартными средствами ОС создать кластер, в который необходимо включить оба (три, четыре) сервера MS Sql. Это сделано для того, чтобы при аварии одного из серверов обеспечить доступность данных в любом случае. Кроме того, теперь сервер-приёмник называется secondary replica, а для каждого сервера в группе можно настроить поддерживаемые им виды подключений — нормальный, read-only (применим на вторичных репликах) и вообще запретить клиентам к ним подключаться. Также у always on группы возможно понятия listener. При создании listener-а группы клиентам не надо подключаться к конкретному серверу, а надо подключаться к доменному имени listener-а. Почему: подключение к адресу listener-а приводит к автоматическому подключению клиента к работоспособной реплике — за этим следит кластерная служба. Кроме того, если софт не очень древний, то в момент подключения можно указать, что клиент собирается данные только читать, и тогда он будет подключен к read-only реплике. Временные таблицы на этих репликах безусловно создаются (tempdb не реплицируется), потому 90% хранимых процедур работают совершенно корректно без дополнительных ухищрений и на read-only репликах. Клиенты, которые изменяют данные (или не указывают свой тип подключения — read only или read-write), автоматически подключаются к первичной реплике.
Тонкостей в настройке достаточно много. Например, для того, чтобы failover происходил автоматически, мы обязаны использовать синхронный коммит транзакций. Возможность ручного failover существует при любом коммите, но управлять им можно только с первичной реплики. В случае выхода из строя именно первичной реплики, в случае ручного failover придётся переносить кластерную службу на другой узел с помощью оснастки windows failover clustering, однако больших проблем это не вызывает, и это просто надо знать.
В общем технология удобная, у нас в production работает уже два года, проблемы испытывали только в самом начале: оказалось что always on group достаточно чувствительна к быстрому разрешению имён серверов. Например, если отправить в перезагрузку по очереди два контроллера домена, то кластер обязательно развалится (правда, потом починится сам). В итоге, не добившись от наших коллег бесперебойной работы контроллеров домена, нам пришлось вручную заполнить файлик hosts на каждом из серверов кластера. Не могу сказать, что это соответствует best practices, но работа кластера стала стабильной.
Короче, извините, не мог, а на полноценную статью сейчас времени нет. Потому настоятельно рекомендую ознакомиться с данной темой на MSDN, построить макет, удивиться тому, насколько это удобно и попробовать ввести в production.
Вкратце: в SQL Server 2008 был новый механизм переноса данных на другие сервера — кроме trnsaction log shipping и репликации был добавлен mirroring. Для старта необходим один full backup БД + минимум один transaction log backup. На принимающей стороне данные бекапы восстанавливаются с опцией NORECOVERY. Что происходит в после включения mirroring: все транзакции, происходящие после LSN последнего transation log бекапа по сети едут на принимающий сервер. БД при этом остается в состоянии Recovery.
Существуют две модели переноса транзакций: синхронная и асинхронная. При синхронном переносе транзакций на сервере-источнике любая транзакция не считается законченой, пока сервер-приёмник не подтвердил её получание. При этом гарантируется целостность данных на обеих серверах, однако длительность каждой транзакции значительно увеличивается — кроме собственно времени вставки в БД к длительности добавляет латентность сетевого подключения, причем в две стороны. И, естественно, длительность собственно коммита транзакции на сервер-приёмник.
В случае асинхронного переноса транзакций длительность транзакций на сервере-источнике практически не увеличивается, но данные на сервере-приёмнике могут отставать на несколько миллисекунд. Кроме того, в случае аварии сервера-источника возможны потери нескольких последних транзакций на сервере-приёмнике. Для использования БД на сервере-приёмнике необходимо было перевести БД из состояния RECOVERY в консистентное состояние, однако после данной процедуры зеркалирование по понятным причинам останавливалось.
Начиная с SQL Server 2012 данная технология была развита в Always On Availability Groups. Для работы always on группы необходимо стандартными средствами ОС создать кластер, в который необходимо включить оба (три, четыре) сервера MS Sql. Это сделано для того, чтобы при аварии одного из серверов обеспечить доступность данных в любом случае. Кроме того, теперь сервер-приёмник называется secondary replica, а для каждого сервера в группе можно настроить поддерживаемые им виды подключений — нормальный, read-only (применим на вторичных репликах) и вообще запретить клиентам к ним подключаться. Также у always on группы возможно понятия listener. При создании listener-а группы клиентам не надо подключаться к конкретному серверу, а надо подключаться к доменному имени listener-а. Почему: подключение к адресу listener-а приводит к автоматическому подключению клиента к работоспособной реплике — за этим следит кластерная служба. Кроме того, если софт не очень древний, то в момент подключения можно указать, что клиент собирается данные только читать, и тогда он будет подключен к read-only реплике. Временные таблицы на этих репликах безусловно создаются (tempdb не реплицируется), потому 90% хранимых процедур работают совершенно корректно без дополнительных ухищрений и на read-only репликах. Клиенты, которые изменяют данные (или не указывают свой тип подключения — read only или read-write), автоматически подключаются к первичной реплике.
Тонкостей в настройке достаточно много. Например, для того, чтобы failover происходил автоматически, мы обязаны использовать синхронный коммит транзакций. Возможность ручного failover существует при любом коммите, но управлять им можно только с первичной реплики. В случае выхода из строя именно первичной реплики, в случае ручного failover придётся переносить кластерную службу на другой узел с помощью оснастки windows failover clustering, однако больших проблем это не вызывает, и это просто надо знать.
В общем технология удобная, у нас в production работает уже два года, проблемы испытывали только в самом начале: оказалось что always on group достаточно чувствительна к быстрому разрешению имён серверов. Например, если отправить в перезагрузку по очереди два контроллера домена, то кластер обязательно развалится (правда, потом починится сам). В итоге, не добившись от наших коллег бесперебойной работы контроллеров домена, нам пришлось вручную заполнить файлик hosts на каждом из серверов кластера. Не могу сказать, что это соответствует best practices, но работа кластера стала стабильной.
Короче, извините, не мог, а на полноценную статью сейчас времени нет. Потому настоятельно рекомендую ознакомиться с данной темой на MSDN, построить макет, удивиться тому, насколько это удобно и попробовать ввести в production.
Ну блин, ну да, дом надо строить, но почему обязательно топором? Програмисты постоянно пробуют новые инструменты и технологии, почему многие админы застряли на 15-летнем инструменте и делают вид что после этого ничего не было создано? Автор 5 раз дает ссылку «Настройка почтовых уведомлений в MS SQL Server». Ну не проще вместо того чтобы мучаться с этим устаревшим мусором потратить 5 минут на поиск современных инструментов которые и бэкап сделают и автоматом куда надо восстановят и письма пошлют после бэкапа или если SQL упадет, да и Health Check сделают? Да часто и бесплатно. Есть несколько софтин и сервисов, сами ищите что вам подходит, но главное не сидите на месте думая что после MS ничего лучше не придумано.
Надо отдать должное автору — он в основном говорит о том что нужно делать, а не как. По этому поводу можно придраться только к некоторой книжной абсолютности требований. Есть вещи критические (вроде бэкапа и отслеживания производительности) и вещи которые на практике часто можно и нужно (поскольку время не резиновое) игнорировать. И вот этой иерархии мне в статье не хватает. Сам бы написал, да критиковать проще :)
Надо отдать должное автору — он в основном говорит о том что нужно делать, а не как. По этому поводу можно придраться только к некоторой книжной абсолютности требований. Есть вещи критические (вроде бэкапа и отслеживания производительности) и вещи которые на практике часто можно и нужно (поскольку время не резиновое) игнорировать. И вот этой иерархии мне в статье не хватает. Сам бы написал, да критиковать проще :)
Ну блин, ну да, дом надо строить, но почему обязательно топором? Програмисты постоянно пробуют новые инструменты и технологии, почему многие админы застряли на 15-летнем инструменте и делают вид что после этого ничего не было создано?
Потому что настроить стандартными средствами резервное копирование с проверкой, а также восстановление резервной копии и уведомление почтой, а также дефрагментацию индексов-это задача несложная для людей, на достаточном уровне знающих T-SQL и администрирование в MS SQL Server (достаточно уровня Middle).
Посложнее вопрос стоит в построении системы, которая бы показывала быстродействие базы данных.
Да, есть софтины и бесплатные, даже у самого скуля есть механизмы для этого, но понимает ли потребитель как именно работает эта утилита? От куда такие цифры? Мы наблюдали за системными представлениями, и можем смело утверждать, что в большинстве случаев приходится писать свою систему, подходящие по своим нуждам, а не систему уникальную для всех и собирающую лишнюю информацию+те, кто использует сторонние средства для регламентных работ, которые лучше написать самому или своей командой, зависят от производителей сторонних утилит (и опять же нужно хорошо понимать как эти утилиты работают). Другое дело обстоит с синхронизацией схем и самих данных. Написать собственную утилиту-уйдет много времени. Поэтому пока используем dbForge. Но опять же при первой возможности напишем свою утилиту.
Сторонние утилиты можно использовать для разработок, но для регламентных работ. Лучше взвесить-что можно самим написать и это будет работать оптимально для Ваших нужд, а не унифицировано для всего, а что лучше с помощью сторонних утилит. Пункты 1,2,5 регламентных работ можно полностью покрыть своими силами без сторонних утилит, а вот пункты 3,4,6,7 скорее всего в симбиозе с готовыми решениями.
Также, чтобы не быть голословным, прошу привести ссылки на бесплатные и хорошие по Вашему мнению ПО, которое позволяет работать с резервными копиями, восстанавливать их и отправлять на почту уведомления.
Есть вещи критические (вроде бэкапа и отслеживания производительности) и вещи которые на практике часто можно и нужно (поскольку время не резиновое) игнорировать. И вот этой иерархии мне в статье не хватает.
Да, согласен, тогда регламентные работы можно расположить по следующему принципу-в порядке убывания по значимости:
1) Плановое создание резервных копий с последующей проверкой без восстановления
2) Плановое восстановление ранее созданных резервных копий с целью полной проверки их работоспособности
3) Плановая оптимизация производительности системы
4) Плановая проверка работы необходимых служб
5) Анализ носителей информации, на которых расположены системные и все необходимые базы данных
6) Плановая проверка целостности данных
7) Плановая проверка корректности данных
Сам бы написал, да критиковать проще :)
Так пишите-делитесь информацией-с удовольствием почитаю)
Да часто и бесплатно.
Например какие?
— но плохо подойдут поля типа uniqueidentifier, т к последние будут приводить к постоянным перестройкам B-дерева
На вставке я бы сказал разница не существенная, но геморой при использовании инкремента на распределенных базах с identity с лихвой перекрывает копеейчную экономию.
43 sec
48 sec
На вставке я бы сказал разница не существенная, но геморой при использовании инкремента на распределенных базах с identity с лихвой перекрывает копеейчную экономию.
create table TestTable (
id int identity(1,1) not null primary key clustered,
sequence int not null,
data char(250) not null default '');
go
declare @count int;
set @count = 0;
while @count < 500000 begin
insert TestTable (sequence)
values (@count);
set @count = @count + 1;
end;
go
43 sec
create table TestTable2 (
id uniqueidentifier default newid() not null primary key clustered,
sequence int not null,
data char(250) not null default '');
go
declare @count int;
set @count = 0;
while @count < 500000 begin
insert TestTable2 (sequence)
values (@count);
set @count = @count + 1;
end;
go
48 sec
Подобные тесты я видел от ведущих разработчиков, которые почему-то на тестах пытаются понять плохо или хорошо использовать uniqueidentifier в качестве кластерного индекса. Это неправильный подход, т к можно поставить SSD-диски, увеличить оперативную память и будет все летать, НО! Погрузитесь в алгоритмы и типы данных и вспомните как работает B-дерево и только тогда станет понятно и без тестов, что uniqueidentifier-плохой кандидат для кластерного индекса. Но почему-то большинство выбирает тесты. А они невсегда покажут проблему.
Также не стоит забывать, что любой тест МОЖЕТ выявить проблему, а может и не выявить. Важно понимать базовые принципы. И порой этого достаточно и без тестов. Тесты нужны лишь для сравнения скоростей или для выявления проблем в сложнопонимаемой системе. Тесты также выявляют порой другую проблему (напр, мало ОЗУ и прочее), а не то, что на самом деле тестируем. Поэтому в данном случае тестирование не является лучшим решением для исследования кластерного индекса.
— А они невсегда покажут проблему.
Ну дак докажите что проблема существует.
Если выбрать sequential guid — скорость получется выше даже по сравнению с identity. Внутри sequential guid также растет монотонно. Мне кажется у вас упрощенное представление что такое b+ tree и как строится индекс и поиск внутри этого дерева.
Разработчики учитывают специфику этого типа данных.
41 sec.
Ну дак докажите что проблема существует.
Если выбрать sequential guid — скорость получется выше даже по сравнению с identity. Внутри sequential guid также растет монотонно. Мне кажется у вас упрощенное представление что такое b+ tree и как строится индекс и поиск внутри этого дерева.
Разработчики учитывают специфику этого типа данных.
create table TestTable3 (
id uniqueidentifier default newsequentialid() not null primary key clustered,
sequence int not null,
data char(250) not null default '');
go
declare @count int;
set @count = 0;
while @count < 500000 begin
insert TestTable3 (sequence)
values (@count);
set @count = @count + 1;
end;
go
41 sec.
На самом деле скорость везде примерно одинакова и зависит от свободного места в файле (будет ли AutoGrowth), включен ли IFI, скорости дисковой подсистемы (не забываем про WRITELOG) и тд.
Если все эти факторы учесть, то вот мой тест:
Скорость выполнения в миллисекундах:
Ожидания:
И самое интересное (так в чем же различие):
А то, что в случае использования NEWID() у нас будут чаще происходить операции разбиения страниц, соответственно и фрагментация будет выше (оттого больше логических чтений при работе с этой таблицей).
Кроме того, в этот пост еще хорош для ознакомления Первичный ключ – GUID или автоинкремент?
Если короче, то в общем поддерживаю точку зрения BalinTomsk
Если все эти факторы учесть, то вот мой тест:
SET NOCOUNT ON
IF OBJECT_ID('t1', 'U') IS NOT NULL
DROP TABLE t1
GO
CREATE TABLE t1 (
id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED
, d CHAR(250) NOT NULL DEFAULT ''
)
GO
DECLARE @count INT = 0
, @dt DATETIME = GETDATE()
WHILE @count < 500000 BEGIN
INSERT t1 DEFAULT VALUES
SET @count += 1
END
SELECT DATEDIFF(MILLISECOND, @dt, GETDATE()) -- 23 second
GO
CHECKPOINT
IF OBJECT_ID('t2', 'U') IS NOT NULL
DROP TABLE t2
GO
CREATE TABLE t2 (
id UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL PRIMARY KEY CLUSTERED
, d CHAR(250) NOT NULL DEFAULT ''
)
GO
DECLARE @count INT = 0
, @dt DATETIME = GETDATE()
WHILE @count < 500000 BEGIN
INSERT t2 DEFAULT VALUES
SET @count += 1
END
SELECT DATEDIFF(MILLISECOND, @dt, GETDATE()) -- 23 second
GO
CHECKPOINT
IF OBJECT_ID('t3', 'U') IS NOT NULL
DROP TABLE t3
GO
CREATE TABLE t3 (
id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL PRIMARY KEY CLUSTERED
, d CHAR(250) NOT NULL DEFAULT ''
)
GO
DECLARE @count INT = 0
, @dt DATETIME = GETDATE()
WHILE @count < 500000 BEGIN
INSERT t3 DEFAULT VALUES
SET @count += 1
END
SELECT DATEDIFF(MILLISECOND, @dt, GETDATE()) -- 23 second
GO
CHECKPOINT
GO
SELECT t.[name]
, size
, s.avg_fragmentation_in_percent
, s.avg_page_space_used_in_percent
FROM sys.objects t
JOIN (
SELECT p.[object_id]
, size = SUM(a.total_pages) * 8. / 1024
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
GROUP BY p.[object_id]
) i ON t.[object_id] = i.[object_id]
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.[object_id], NULL, NULL, 'DETAILED') s
WHERE t.is_ms_shipped = 0
AND i.[object_id] > 255
AND t.[type] = 'U'
AND s.index_level = 0
Скорость выполнения в миллисекундах:
-------- -----------
t1 24080
t2 24256
t3 22280
Ожидания:
name wait_type wait_time wait_resource wait_signal
-------- ----------------------- ----------- ---------------- ------------
t1 WRITELOG 8.8480 6.7930 2.0550
t2 WRITELOG 8.9310 6.9120 2.0190
t3 WRITELOG 8.2180 6.2340 1.9840
И самое интересное (так в чем же различие):
name size avg_fragmentation_in_percent avg_page_space_used_in_percent
-------- ------------- ---------------------------- ------------------------------
t1 130.757812 0,365992680146397 97,4529651593773
t2 197.320312 99,0648999243962 67,572658759575
t3 135.632812 0,666975988864401 98,5015196441809
А то, что в случае использования NEWID() у нас будут чаще происходить операции разбиения страниц, соответственно и фрагментация будет выше (оттого больше логических чтений при работе с этой таблицей).
Кроме того, в этот пост еще хорош для ознакомления Первичный ключ – GUID или автоинкремент?
Если короче, то в общем поддерживаю точку зрения BalinTomsk
Для тестов нужно брать не маленькие, а большие таблицы, где более чем 1 млн строк, т к чем меньше записей в таблице, тем больше прощается как в самих запросах, так и в проектировании этой таблицы.
А вообще лучше взять таблицу, где скажем 10 млрд записей и протестировать Вашими тестами.
А вообще лучше взять таблицу, где скажем 10 млрд записей и протестировать Вашими тестами.
А то, что в случае использования NEWID() у нас будут чаще происходить операции разбиения страниц, соответственно и фрагментация будет выше (оттого больше логических чтений при работе с этой таблицей).
В любом случае фрагментация будет зависеть от порядка назначения ключа. Тесты лучше делать на больших данных
Кроме того, в этот пост еще хорош для ознакомления Первичный ключ – GUID или автоинкремент?
В этой статье опять же рассматривали таблицу небольшую.
Чтобы увидеть как работает B-дерево, нужно много данных.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Регламентные работы с базой данных информационной системы 24x7 в MS SQL Server