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

Откуда: Москва / Калуга
Сообщений: 35384
Блог
Коллеги,

Может быть кто-нибудь знает: почему время построении кластерного индекса с секционированием так отличается от времени построения простого кластерного индекса?

Отличие практически в 2 раза.

-- 22 секунды
CREATE CLUSTERED INDEX [idx1] ON [T]
(
...
)WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE)
ON [PRIMARY]


-- 41 секунда
CREATE CLUSTERED INDEX [idx1] ON [T]
(
...
)WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE)
ON ПоМесяцам(Дата)


Microsoft SQL Server 2012 (SP1) - 11.0.3339.0 (X64)
Jan 14 2013 19:02:10
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
28 фев 13, 12:25    [13993174]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по времени секционирования  [new]
Crimean
Member

Откуда:
Сообщений: 13147
спортивного интереса ради - а сколько ядер работает на построение индекса в обоих случаях?

у меня есть определенные наблюдения (пока не "мысли") что для ms sql есть "плохие" и "хорошие" индексы
сходные по объему / наполнению (и при общей таблице, безусловно), а время построения может отличаться в разы
разумеется, вопрос селективности данных, но, похоже, только для первого поля
а секционируя - мы фактически вводим то самое "первое поле" и - как результат - сервер начинает пилить процесс построения индекса не всеми доступными ядрами, а "в 1 лицо", медленно, уныло и печально
p.s.
почему-то это меня совершено не удивляет и - да - разумеется это "by design"
28 фев 13, 12:33    [13993214]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по времени секционирования  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35384
Блог
Crimean,

В примере наверху - 2х ядерный процессор, в случае простого кластерного индекса загрузка процессора 100%.
В случае построения секционированного кластерного индекса - загружается только 1 ядро.


Почему возник такой вопрос:
  • пробовал загружать данные в уже секционированную stage-таблицу, строить нужные индексы, а затем переключать секции в основную - получил медленную загрузку(по сравнению с пунктом ниже) с сортировкой по секциям в tempdb
  • тогда попробовал загружать в несекционированную таблицу, а затем построить секционированные индексы (то есть секционировать на втором этапе, т.к. мне все равно нужен кластерный секционированный индекс) - в этом случае получил выигрыш во времени загрузки примерно в 2 раза из-за исключения лишней сортировки данных при загрузке в таблицу
  • попробовал грузить секции по очереди и получил весьма и весьма существенный выигрыш во времени построения индексов
  • 28 фев 13, 13:09    [13993482]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 35384
    Блог
    MAXDOP нигде не указывал
    28 фев 13, 13:10    [13993488]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Критик,

    Можете дать репро-скрипты?

    Я не смог воспроизвести описанное вами. Правда, у меня 2008R2. Создал две тестовые таблицы со структурой (id int identity, Дата datetime not null, filler char(50)), ввёл в каждую по 10 млн. значений (по 230 Мб данных), затем у одной построил кластерный индекс на [primary], у второй — на ПоМесяцам(Дата). Время построения индексов было одинаковым (плюс-минус 10%), все четыре ядра бодро использовались. Перед каждым построением индекса выполнялся checkpoint+dropcleanbuffers.

    Кстати, у вас схема секционирования — all to primary, или другие файлгруппы тоже есть?
    28 фев 13, 13:39    [13993706]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 35384
    Блог
    Гость333,

    Все в PRIMARY.

    Репроскрипт:

    use tempdb
    go
    CREATE PARTITION FUNCTION [ПоМесяцам](smalldatetime) AS RANGE RIGHT FOR VALUES (N'20111001', N'20111101', N'20111201', N'20120101', N'20120201', N'20120301', N'20120401', N'20120501', N'20120601', N'20120701', N'20120801', N'20120901', N'20121001', N'20121101', N'20121201', N'20130101', N'20130201', N'20130301', N'20130401', N'20130501', N'20130601', N'20130701', N'20130801', N'20130901', N'20131001', N'20131101', N'20131201', N'20140101', N'20140201', N'20140301', N'20140401', N'20140501', N'20140601', N'20140701', N'20140801', N'20140901', N'20141001', N'20141101', N'20141201', N'20150101', N'20150201', N'20150301', N'20150401', N'20150501', N'20150601', N'20150701', N'20150801', N'20150901', N'20151001', N'20151101', N'20151201')
    CREATE PARTITION SCHEME [ПоМесяцам] AS PARTITION [ПоМесяцам]  TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
    
    
    CREATE TABLE [test](
    	[Дата] [smalldatetime]  NULL,
    	[A] [smallint]  NULL,
    	[B] [int]  NULL,
    	[C] [smallint]  NULL,
    	[D] [int]  NULL,
    	[E] [tinyint]  NULL,
    	[M1] [int]  NULL,
    	[M2] [int]  NULL
    ) ON [PRIMARY]
    
    ALTER TABLE [test] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
    go
    
    
    -- заполняем несколько секций, 8 млн записей
    insert into [test]
    select *, 2, checksum(newid())/10000000, checksum(newid())/10000000
      from (select top 100 dateadd(day,(ROW_NUMBER() over(ORDER BY t1.number)-1), '20110501' ) as [Дата]
              from master.dbo.spt_values as t1) as t1
      cross join (select top 50 (ROW_NUMBER() over(ORDER BY number)-1) as [A]
                    from master.dbo.spt_values) as t2
      cross join (select top 10 (ROW_NUMBER() over(ORDER BY number)-1) as [B]
                    from master.dbo.spt_values) as t3
      cross join (select top 4 (ROW_NUMBER() over(ORDER BY number)-1) as [C]
                    from master.dbo.spt_values) as t4
      cross join (select top 40 (ROW_NUMBER() over(ORDER BY number)-1) as [B]
                    from master.dbo.spt_values) as t5
    go
    
    CHECKPOINT
    DBCC DROPCLEANBUFFERS 
    go
    
    -------- начало теста 1: 52 сек, нет 100% загрузки всех ядер (у меня 2шт)--------
    CREATE CLUSTERED INDEX [idx1] ON [test]
    (
    	[Дата] ASC,
    	[A] ASC,
    	[B] ASC,
    	[C] ASC,
    	[D] ASC,
    	[M2] ASC,
    	[M1] ASC
    )WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE) ON ПоМесяцам(Дата)
    -------- конец теста 1: 51 сек, нет 100% загрузки всех ядер (у меня 2шт)--------
    
    go
    DROP INDEX [idx1] ON [test]
    
    
    ------------------------------------------
    go
    CHECKPOINT
    DBCC DROPCLEANBUFFERS 
    go
    
    -------- начало теста 2: 30 сек, есть 100% загрузка процессора--------
    CREATE CLUSTERED INDEX [idx1] ON [test]
    (
    	[Дата] ASC,
    	[A] ASC,
    	[B] ASC,
    	[C] ASC,
    	[D] ASC,
    	[M2] ASC,
    	[M1] ASC
    )WITH (SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE) ON [PRIMARY]
    -------- конец теста 2: 30 сек, есть 100% загрузка процессора--------
    go
    DROP INDEX [idx1] ON [test]
    
    28 фев 13, 14:24    [13994123]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 35384
    Блог
    Вобщем потестировал на нормальном сервере на 2008R2: при секционировании также используется только 1 ядро (есть фрагментарное использование других ядер, но в среднем только одно).

    Разница на 48 процессорном сервере для 16 млн тестовых данных составила:
  • почти 5 минут строился индекс с секционированием
  • 10 секунд без секционирования
  • 28 фев 13, 15:13    [13994534]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13147
    Критик,

    угу. это общая проблема пере/по-строения индексов. тут секционирование просто "послужило катализатором". я тоже со всего разбегу столкнулся с этим вопросом

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

    на грани оффтопа, но у меня большая таблица, "id int identity primary key clustered" и кучка индексов где первые поля отличаются распределением данных. полей от 3 до 5. так вот. размерность полей и число строк - одинаковы. а "хорошие" - строятся всеми ядрами. плохие - "в одно лицо"
    28 фев 13, 16:15    [13995042]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13147
    нашел свой репорт про это. 10.50.2806.0 (X64) 80 "потоков" (40 HT ядер) 128 GB озу
    одна табличка, 21 GB reserved для и 68 лямов записей. как говорил, Id int primary key clustered

    индекс Name (char80), Id (int), худшее распределение по Name = 522 одинаковых значения, дальше 71, 65 и т.д. то есть прекрасный индекс. сервер "берет" все ядра, индекс строится 16 секунд

    индекс Dummy (int), Id (int), никакое распределение данных. все строки заполнены одним значением. сервер использует только 1 ядро, 3.5 минуты молотим. то есть 210 секунд. разница в 13 (!) раз

    были и промежуточные варианты, их не пишу. вывод - распределение данных напрямую и не лучшим образом влияет на возможность использования всех доступных ядер для перестроения индексов
    1 мар 13, 11:40    [13998116]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Погонял предложенные скрипты. На самом деле ларчик просто открывался.

    При трассировке профайлером выяснилось, что при построении индекса на схеме секционирования всё-таки используется параллельный план запроса (см. картинку внизу).

    Но распределение данных не очень удачное, все тестовые данные попадают в одну секцию:
    select year(Дата) year, month(Дата) month, count(*) cnt
    from test
    group by year(Дата), month(Дата)
    order by year(Дата), month(Дата)
    
    select $partition.ПоМесяцам(Дата) SectNum, count(*) cnt
    from test
    group by $partition.ПоМесяцам(Дата)
    order by $partition.ПоМесяцам(Дата)
    

    year        month       cnt
    ----------- ----------- -----------
    2011 5 2480000
    2011 6 2400000
    2011 7 2480000
    2011 8 640000

    (4 row(s) affected)

    SectNum cnt
    ----------- -----------
    1 8000000

    (1 row(s) affected)

    Попробуем раскидать данные по разным секциям:
    CREATE PARTITION FUNCTION [ПоМесяцам_2](smalldatetime) AS RANGE RIGHT FOR VALUES (N'20110501', N'20110601', N'20110701', N'20110801')
    CREATE PARTITION SCHEME [ПоМесяцам_2] AS PARTITION [ПоМесяцам_2] ALL TO ([PRIMARY])
    

    Видим, что данные попадают в разные секции, хоть и не очень равномерно:
    select $partition.ПоМесяцам_2(Дата) SectNum, count(*) cnt
    from test
    group by $partition.ПоМесяцам_2(Дата)
    order by $partition.ПоМесяцам_2(Дата)
    

    SectNum     cnt
    ----------- -----------
    2 2480000
    3 2400000
    4 2480000
    5 640000

    (4 row(s) affected)

    Строим индекс на схеме "ПоМесяцам_2". Вуаля — используются 4 ядра (у кого они есть, конечно :-), время выполнения уменьшается (у меня уменьшилось примерно в 3 раза по сравнению со схемой "ПоМесяцам").

    Соль заключается в операторе Parallelism (Repartition Streams), который раскидывает данные по процессорам в зависимости от значения функции секционирования. Все записи с одинаковым номером секции попадают на один процессор.

    К сообщению приложен файл. Размер - 53Kb
    1 мар 13, 12:38    [13998456]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 35384
    Блог
    Гость333,

    Я вас не слишком понял )

    У меня была такая проблема:
  • имеется исходная несекционированная таблица
  • строим на ней несекционированный кластерный индекс - используются все ресурсы процессора
  • строим на ней же секционированный кластерный индекс - используется только 1 ядро

    У меня идет ETL-процесс, там перекачиваются данные в целевую таблицу. В целях упрощения кода я хотел качать все в несекционированную таблицу, затем в ней создать кластерный секционированный индекс и сделать переключение.

    А оказалось, что намного быстрее качать в цикле в несекционированную таблицу, создавать там несекционированный индекс и нужное CHECK-ограничение и делать переключение всей таблицы в целевую секцию.

    То есть вышло, что создание одного секционированного индекса занимает много больше времени, чем поочередное создание очень многих несекционированных индексов. Хотя, казалось бы, что и там и там сортировка данных, только в случае секционирования нужно поставить метку начала/конца секции.
  • 1 мар 13, 13:05    [13998636]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13147
    > оказалось, что намного быстрее
    > ...
    > делать переключение всей таблицы в целевую секцию

    да, конечно

    > Хотя, казалось бы, что и там и там сортировка данных, только в случае секционирования нужно

    тут - еще раз - разница от того, что "хорошее" распределение данных дает возможность использовать все процессора, плохое - не дает. если у вас 1 процессор (ядро) - вы не поймете разницу. но чем больше процессоров (ядер) тем разница будет больше
    1 мар 13, 13:12    [13998710]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Критик
    У меня была такая проблема:
  • имеется исходная несекционированная таблица
  • строим на ней несекционированный кластерный индекс - используются все ресурсы процессора
  • строим на ней же секционированный кластерный индекс - используется только 1 ядро

  • А какое было распределение данных по секциям? Всё попадало в одну секцию, как и в тестовом примере?

    Параллелизм при секционировании работает так: секции распределяются между процессорами — каждый процессор в некий момент времени обрабатывает только одну секцию. Несколько процессоров одновременно не будут обрабатывать одну секцию.

    При считывании очередной строки данных вычисляется значение функции секционирования. На основании этого значения определяется, какому процессору нужно отдать строку для обработки. В вашем примере все данные принадлежат одной секции — поэтому всех их обработал один процессор. Если данные будут принадлежать разным секциям (схема ПоМесяцам_2), то будут задействованы несколько процессоров.
    1 мар 13, 13:43    [13998946]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Я, например, зачастую наблюдал такое явление. Есть некая большая (скажем, около миллиарда записей) секционированная таблица, число секций = 12. Пишем тяжёлый запрос, где, кроме прочего, происходит фуллскан этой таблицы. В запросе ставим MAXDOP=8. Мониторим выполнение запроса через 'sp_who2 НомерПроцесса'. Сначала видим активность на 8 процессорах. Там постоянно увеличиваются CPU и IO, это длится X минут. Потом активные процессы начинают пропадать. Это значит, что процессор закончил обработку секции, а новой секции ему не досталось. Затем ещё примерно X минут мы видим активность на 4 процессорах — это отрабатывают оставшиеся 12-8 = 4 секции
    1 мар 13, 13:51    [13998986]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Упс, не дописал...

    Над всем этим висит координирующий процесс с минимальными значениями CPU и IO. По мере того, как пропадают активные процессы, они передают значения CPU и IO в этот координирующий процесс. Т.е. через X минут там будет 4*CPU и 4*IO, а через 2*X минут (когда всё отработает) там будет 12*CPU и 12*IO.
    1 мар 13, 13:53    [13999002]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 35384
    Блог
    Гость333
    А какое было распределение данных по секциям? Всё попадало в одну секцию, как и в тестовом примере?


    Изначально была таблица без секций, данные из которой должны были разделится на несколько секций. Я просто ожидал, что по длительности секционирование одной большой таблицы будет операцией одного порядка по сравнению с обработкой многих маленьких таблиц.
    1 мар 13, 13:58    [13999033]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13147
    Критик,

    я пытался "встрять" с мыслью что и для "обычных" индексов время перестроения определяется числом задействованных ядер, а это определяется распределением данных. а после секционирования все становится "еще интереснее"
    1 мар 13, 14:47    [13999336]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 35384
    Блог
    Crimean,

    Я это потестил на 2008R2, у меня получилось, что при построении индекса количество используемых ядер не больше количества уникальных значений первого поля.

    Репро-пример, если кому интересно:

    CREATE TABLE [test](
    	[Дата] [smalldatetime]  NULL
    ) ON [PRIMARY]
    
    ALTER TABLE [test] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
    go
    
    -- 8 уникальных значений
    insert into [test]
    select t1.Дата
      from (select top 8 dateadd(day,(ROW_NUMBER() over(ORDER BY t1.number)-1), '20110501' ) as [Дата]
              from master.dbo.spt_values as t1) as t1
      cross join (select top 2000 (ROW_NUMBER() over(ORDER BY number)-1) as [A]
                    from master.dbo.spt_values) as t2
      cross join (select top 4000 (ROW_NUMBER() over(ORDER BY number)-1) as [A]
                    from master.dbo.spt_values) as t3
    


    Весьма интересно. По идее, из этого следует, что не выгодно секционировать по точечным промежуткам (то есть вся секция i=5), рациональнее будет что-то типа i>=5 and i<10.

    А, например, месячная секция сможет утилизировать только 28-31 ядро.
    1 мар 13, 15:13    [13999511]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Гость333
    Member

    Откуда:
    Сообщений: 3683
    Критик
    А, например, месячная секция сможет утилизировать только 28-31 ядро.

    Я не очень понял... Вам удалось сделать так, чтобы при фулскане таблицы использовалось более одного ядра на секцию? Или 28-31 ядро будет использоваться, если ограничить запрос одной секцией?
    1 мар 13, 15:24    [13999598]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13147
    Гость333,

    судя по 28-31 речь идет про разбивку по дням месяца, коих реально 28-31 ну и по ядру на секцию
    то есть критерий секционирования - "день месяца", к реалу он за уши, а для примеров - самое оно

    а сама ситуация с индексацией на основании распределения - гадостная и тупиковая :(
    1 мар 13, 15:41    [13999700]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос по времени секционирования  [new]
    Критик
    Member

    Откуда: Москва / Калуга
    Сообщений: 35384
    Блог
    Гость333
    Я не очень понял... Вам удалось сделать так, чтобы при фулскане таблицы использовалось более одного ядра на секцию? Или 28-31 ядро будет использоваться, если ограничить запрос одной секцией?


    Я про регламентные операции над индексами, создание/перестройку.

    В примере чуть выше несекционированная таблица, однако при построении индекса используется ровно столько же ядер, сколько уникальных значений в столбце индекса. То есть, если у сервера более 31 ядра, то регламентные операции над индексами при секционировании по месяцам не будут полностью утилизировать процессорные ресурсы. Пока получается так.
    1 мар 13, 15:51    [13999789]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить