Комментарии (21)

1. 

   kolu4iy

   22.02.2017 23:32

   #10086170

   Эм… А если у нас 20 ядер на каждой ноде — нам тоже выставлять max degree в 1?
   Я просто очень часто встречаю данную рекомендацию, но реально её пришлось трогать только в системе, которая написана не очень понимающими механизм работы ms sql людьми, и потому мощно генерирующую некешируемые ad-hoc запросы… Кстати, на той системе всего 8 ядер, но 1 — это не то, что стоит советовать. Стенд из 20 клиентов-роботов генерировал нагрузку, а мы подбирали подходящую величину. Так вот: разумным компромисом между временем выполнения и нагрузкой на ядра хоста оказалась цифра 3.

   
   

   1. 

      jobgemws

      23.02.2017 10:51

      #10086618

      Благодарю за Ваш описанный пример
      Да, мы тоже проводили ряд исследований, и в статье написано когда лучше отключать параллелизм, а когда нет (это общая рекомендация, которая не говорит всегда выключать параллелизм, а только в определенных случаях). Тесты мы проводили на серверах, у которых свыше 12 ядер, но на которых стоят экземпляры MS SQL Server 2012-2014 Standart (т е больше 4-х ядер все равно не будет использовать). Но смысл был в том, что при исследовании было выявлено, что параллелизм может породить неэффективный план при хотя бы одном из 3-х условий, которые и описаны в статье. Однако, прошу заметить, что в статье не писалось о том, что всегда нужно запрещать параллелизм. В свое время обратил внимание на то, что при установке системы 1С этот параметр выставляется у скуля в 1, т е почему-то разработчики 1С считают, что в большинстве случаев лучше запрещать параллелизм. Но на самом деле это не всегда так.

      
      

      1. 

         Shaz

         23.02.2017 14:52

         #10086940

         но на которых стоят экземпляры MS SQL Server 2012-2014 Standart (т е больше 4-х ядер все равно не будет использовать)

         
         
         А можете раскрыть этот момент подробнее? То есть для этой редакции нету смысла использовать CPU с кол-вом ядер более 4-х?

         
         

         1. 

            jobgemws

            23.02.2017 15:19

            #10086976

            Увы да-Standart более 4-х не использует

            
            

            1. 

               AlanDenton

               23.02.2017 19:00

               #10087212

               +1

               Вообще-то для редакции Standart до 16 ядер SQL Server 2008-2014 видит. Для SQL Server 2016 этой же редакции увеличили до 24.

               
               

               1. 

                  jobgemws

                  23.02.2017 20:10

                  #10087268

                  Спасибо за замечание, да имелось в виду не более 4-х процессоров и не более 16 ядер

1. 

   kolu4iy

   22.02.2017 23:35

   #10086176

   +1

   … еще не вижу рекомендаций о горизонтальном масштабировании: как правило, 50-80% нагрузки — это чтение данных, при этом совершенно не обязательно они должны быть актуальны до милисекунды. А с ms sql 2012 появился совершенно прекрасный high avalaibility кластер, в котором существую замечательные read-only реплики. Перенос, например, генерации аналитических отчётов на данные реплики позволяет очень существенно разгрузить основную ноду.

   
   

   1. 

      jobgemws

      23.02.2017 10:53

      #10086626

      Спасибо за дополнение
      Также прошу Вас раскрыть данную тематику или привести ссылки на источники

      
      

      1. 

         kolu4iy

         23.02.2017 13:13

         #10086800

         +1

         Собственно, источник сокровенного знания, как обычно, в 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.

         
         

         1. 

            jobgemws

            23.02.2017 16:24

            #10087052

            Спасибо за подробное описание
            Да, зеркалирование используется у нас, правда пока только в одном месте

1. 

   pranas

   23.02.2017 01:49

   #10086316

   Ну блин, ну да, дом надо строить, но почему обязательно топором? Програмисты постоянно пробуют новые инструменты и технологии, почему многие админы застряли на 15-летнем инструменте и делают вид что после этого ничего не было создано? Автор 5 раз дает ссылку «Настройка почтовых уведомлений в MS SQL Server». Ну не проще вместо того чтобы мучаться с этим устаревшим мусором потратить 5 минут на поиск современных инструментов которые и бэкап сделают и автоматом куда надо восстановят и письма пошлют после бэкапа или если SQL упадет, да и Health Check сделают? Да часто и бесплатно. Есть несколько софтин и сервисов, сами ищите что вам подходит, но главное не сидите на месте думая что после MS ничего лучше не придумано.
   Надо отдать должное автору — он в основном говорит о том что нужно делать, а не как. По этому поводу можно придраться только к некоторой книжной абсолютности требований. Есть вещи критические (вроде бэкапа и отслеживания производительности) и вещи которые на практике часто можно и нужно (поскольку время не резиновое) игнорировать. И вот этой иерархии мне в статье не хватает. Сам бы написал, да критиковать проще :)

   
   

   1. 

      jobgemws

      23.02.2017 11:06

      #10086656

      Ну блин, ну да, дом надо строить, но почему обязательно топором? Програмисты постоянно пробуют новые инструменты и технологии, почему многие админы застряли на 15-летнем инструменте и делают вид что после этого ничего не было создано?

      
      Потому что настроить стандартными средствами резервное копирование с проверкой, а также восстановление резервной копии и уведомление почтой, а также дефрагментацию индексов-это задача несложная для людей, на достаточном уровне знающих T-SQL и администрирование в MS SQL Server (достаточно уровня Middle).
      Посложнее вопрос стоит в построении системы, которая бы показывала быстродействие базы данных.
      Да, есть софтины и бесплатные, даже у самого скуля есть механизмы для этого, но понимает ли потребитель как именно работает эта утилита? От куда такие цифры? Мы наблюдали за системными представлениями, и можем смело утверждать, что в большинстве случаев приходится писать свою систему, подходящие по своим нуждам, а не систему уникальную для всех и собирающую лишнюю информацию+те, кто использует сторонние средства для регламентных работ, которые лучше написать самому или своей командой, зависят от производителей сторонних утилит (и опять же нужно хорошо понимать как эти утилиты работают). Другое дело обстоит с синхронизацией схем и самих данных. Написать собственную утилиту-уйдет много времени. Поэтому пока используем dbForge. Но опять же при первой возможности напишем свою утилиту.
      Сторонние утилиты можно использовать для разработок, но для регламентных работ. Лучше взвесить-что можно самим написать и это будет работать оптимально для Ваших нужд, а не унифицировано для всего, а что лучше с помощью сторонних утилит. Пункты 1,2,5 регламентных работ можно полностью покрыть своими силами без сторонних утилит, а вот пункты 3,4,6,7 скорее всего в симбиозе с готовыми решениями.
      Также, чтобы не быть голословным, прошу привести ссылки на бесплатные и хорошие по Вашему мнению ПО, которое позволяет работать с резервными копиями, восстанавливать их и отправлять на почту уведомления.
      

      Есть вещи критические (вроде бэкапа и отслеживания производительности) и вещи которые на практике часто можно и нужно (поскольку время не резиновое) игнорировать. И вот этой иерархии мне в статье не хватает.

      
      Да, согласен, тогда регламентные работы можно расположить по следующему принципу-в порядке убывания по значимости:
      1) Плановое создание резервных копий с последующей проверкой без восстановления
      2) Плановое восстановление ранее созданных резервных копий с целью полной проверки их работоспособности
      3) Плановая оптимизация производительности системы
      4) Плановая проверка работы необходимых служб
      5) Анализ носителей информации, на которых расположены системные и все необходимые базы данных
      6) Плановая проверка целостности данных
      7) Плановая проверка корректности данных
      

      Сам бы написал, да критиковать проще :)

      
      Так пишите-делитесь информацией-с удовольствием почитаю)

1. 

   Sleuthhound

   23.02.2017 09:11

   #10086520

   Да часто и бесплатно.

   
   

   Например какие?

1. 

   BalinTomsk

   23.02.2017 20:10

   #10087270

   +1

   — но плохо подойдут поля типа uniqueidentifier, т к последние будут приводить к постоянным перестройкам B-дерева
   
   На вставке я бы сказал разница не существенная, но геморой при использовании инкремента на распределенных базах с 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
   

   
   

   1. 

      jobgemws

      23.02.2017 20:20

      #10087278

      Подобные тесты я видел от ведущих разработчиков, которые почему-то на тестах пытаются понять плохо или хорошо использовать uniqueidentifier в качестве кластерного индекса. Это неправильный подход, т к можно поставить SSD-диски, увеличить оперативную память и будет все летать, НО! Погрузитесь в алгоритмы и типы данных и вспомните как работает B-дерево и только тогда станет понятно и без тестов, что uniqueidentifier-плохой кандидат для кластерного индекса. Но почему-то большинство выбирает тесты. А они невсегда покажут проблему.

      
      

      1. 

         jobgemws

         23.02.2017 20:24

         #10087280

         Также не стоит забывать, что любой тест МОЖЕТ выявить проблему, а может и не выявить. Важно понимать базовые принципы. И порой этого достаточно и без тестов. Тесты нужны лишь для сравнения скоростей или для выявления проблем в сложнопонимаемой системе. Тесты также выявляют порой другую проблему (напр, мало ОЗУ и прочее), а не то, что на самом деле тестируем. Поэтому в данном случае тестирование не является лучшим решением для исследования кластерного индекса.

         
         

         1. 

            BalinTomsk

            23.02.2017 21:53

            #10087330

            +1

            — А они невсегда покажут проблему.
            
            Ну дак докажите что проблема существует.
            
            Если выбрать 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.
            
            

            
            

            1. 

               AlanDenton

               24.02.2017 08:05

               #10087558

               +1

               На самом деле скорость везде примерно одинакова и зависит от свободного места в файле (будет ли AutoGrowth), включен ли IFI, скорости дисковой подсистемы (не забываем про WRITELOG) и тд.
               
               Если все эти факторы учесть, то вот мой тест:
               
               

               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. 

                  jobgemws

                  24.02.2017 09:45

                  #10087614

                  Для тестов нужно брать не маленькие, а большие таблицы, где более чем 1 млн строк, т к чем меньше записей в таблице, тем больше прощается как в самих запросах, так и в проектировании этой таблицы.
                  А вообще лучше взять таблицу, где скажем 10 млрд записей и протестировать Вашими тестами.

               
               

               1. 

                  jobgemws

                  24.02.2017 10:24

                  #10087636

                  А то, что в случае использования NEWID() у нас будут чаще происходить операции разбиения страниц, соответственно и фрагментация будет выше (оттого больше логических чтений при работе с этой таблицей).

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

               
               

               1. 

                  jobgemws

                  24.02.2017 10:26

                  #10087638

                  Кроме того, в этот пост еще хорош для ознакомления Первичный ключ – GUID или автоинкремент?

                  
                  В этой статье опять же рассматривали таблицу небольшую.
                  Чтобы увидеть как работает B-дерево, нужно много данных.