От Oracle к SQL Server: пресекая великий разлом

После многих лет поиска неполадок в базах данных Oracle, работы над повышением их эффективности и обучения других людей всему этому меня неожиданно попросили сделать презентация о том, насколько SQL Server 2008 пригоден в качестве "решения масштаба предприятия". Это была интересная задача. У меня не было опыта работы с этим продуктом, и совсем немного времени на подготовку. После того как я скачал и установил 180-дневную пробную версию SQL Server Enterprise Edition вместе с "Books Online", у меня остался 21 час на то, чтобы выяснить, может ли SQL Server 2008 дать мне все, что мне нужно от СУБД.

Я пережил эту презентацию, и аудитории, похоже, даже понравился результат, хотя мне и пришлось оставить некоторые вопросы открытыми. Кроме того, сама по себе попытка узнать максимально много об SQL Server за минимальное время оказалась весьма интересным делом, и я решил описать ее в короткой серии статей. Результат – это, в сущности, "путевые заметки об открытии SQL Server", но с точки зрения кого-то, понимающего основные требования к корпоративной РСУБД.

Эта серия статей, в конечном счете, должна описать ключевые требования к корпоративной РСУБД (в порядке важности):

Сперва, однако, мне пришлось обратиться к основам: как создать БД, таблицу, индекс, и как после этого заполнить таблицу тестовыми данными. Говоря шире, я должен был прочувствовать РСУБД SQL Server в целом, поэтому сперва я обращусь к производительности.

Setting Targets

Опыт работы с Oracle подсказывает мне, какого рода вещи нужно искать в СУБД, даже если я не знаю точно, где их искать в SQL Server, или как именно они выглядят. Мне удалось составить список ключевых терминов, отыскать их в документации и, в большинстве случаев, очень быстро найти концепции и объяснения. Поскольку я учился и экспериментировал, во многих случаях (отмеченных в тексте) я "помечал" вопрос, который впоследствие требуется разобрать подробнее.

Технологии SQL Server, конечно, несколько отличаются от Oracle, и некоторые детали различаются по самым разным причинам, но на фундаментальном уровне на производительность любой СУБД влияют только три аспекта работы с данными:

Если вы разделяете этот упрощенный, но весьма точный взгляд, то вы понимаете необходимость в инструментах, которые позволяют видеть влияние текущей активности в БД (пункта 2 и 3 из приведенного выше перечисления), и в инструментах, позволяющих видеть, как выполняются SQL-выражения, почему они исполняются именно так, и как их можно было бы исполнить эффективнее (пункт 1).

Первоначально я буду заниматься только отслеживанием индивидуальных SQL-выражений. Таким образом, я буду искать способы увидеть, как данные физически расположены в БД, какие есть структуры, позволяющие сделать доступ к данным более эффективным, какую статистику использует оптимизатор, чтобы выбрать план выполнения, как увидеть план выполнения и работу, которую он выполняет, и как можно отменить план, выбранный оптимизатором в случае необходимости.

В терминах Oracle, я буду искать эквивалент запроса к словарю данных в поисках информации о сегментах данных, экстентах и блоках; я постараюсь найти heap-таблицы, кластерные индексы и хэш-кластеры, таблицы, организованные по индексу, разделенные таблицы, индексы и материализованные представления; я также хочу исследовать в SQL Server эквиваленты статистики объектов, планов исполнения, хинтов и динамических представлений производительности (v$-таблиц).

Но сперва мне нужно выяснить, как создать БД, таблицу, какие-нибудь данные и индекс.

Начинаем

После настройки старого ноута (2 CPU, 2GB RAM) с Windows XP Pro, скачивания и распаковки пробной версии и "Books Online", я обнаружил, что установка ПО – это простая операция, сводящаяся к нескольким щелчкам мышью и занимающая всего несколько минут.

Я устанавливал все из-под учетной записи " administrator" и запускал все тесты с администраторскими правами. Конечно, такая стратегия не имеет смысла при серьезной разработке, хотя я слыхал, что считается, что для администратора SQL Server имеет смысл быть системным администратором, поскольку ОС и СУБД тесно связаны.

Когда я начал разбираться с безопасностью и аудитом, я перезагрузился и начал заново с соответствующим набором привилегий. Но на этот момент я просто хотел разобраться в системе с максимальным уровнем привилегий, а "install and run as administrator" было быстрым и простым временным решением.

Экземпляры, базы данных, схемы...

Когда все заработало, мне потребовалось выяснить, как что делается. Поиск в Google немедленно выдал мне серию полезных видео от Microsoft, SQL Server 2008 for Oracle DBA. Однако я быстро понял, что у меня нет времени двигаться со скоростью лектора, и я обратился к "Books Online" и ввел в поисковой строке "Create Database" (в кавычках).

Седьмым пунктом в результатах поиска стояло: "Как создать БД (SQL Server Management Studio)", а одиннадцатым – "Создание БД (Database Engine)". И то, и другое выглядело многообещающе. Тот же поиск выдал мне руководства по созданию схем БД и учетных записей пользователей (что несколько удивляет, потому что в Oracle схема – это синоним пользователя).

Пробираясь через ссылки на "Создание БД", я вскоре узнал немного о том, что SQL Server имеет в виду под "экземпляром", и как экземпляр управляет несколькими БД (в отличие от Oracle, где один экземпляр управляет одной БД), которые состоят из файлов и "групп файлов". Я также нашел имена нескольких словарных структур, которые помогли мне найти информацию о БД, файлах и схемах.

Я также обнаружил утилиту sqlcmd и пару опций командной строки, включая :setvar. Это значит, что мне не придется делать все из GUI (Настоящий Администратор Oracle не использует GUI!). По мере разбирательств, однако, выяснилось, что sqlcmd не предназначена для использования в качестве "стоящего" средства отчетов, как Oracle's SQL*Plus; ее трудно использовать и у нее нет возможностей типа "прерывания" или "вычислений" для значений столбцов, у нее нет возможности форматировать отдельные колонки данных, и мне потратилось потратить некоторое время, прежде чем я решил, что в основном нужно форматировать все в SQL, а не с помощью утилиты. Тем не менее, она делает все, что мне нужно, выполняет аудит моих экспериментов и упрощает мне отладку и выполнение повторяемых тестов, хотя и выводит результаты не так аккуратно, как мне бы хотелось.

Через пару часов я написал свой первый скрипт для SQL Server 2008, столкнулся с парой сюрпризов и понял несколько ключевых различий между SQL Server 2008 и Oracle:

:setvar sqlcmdheaders            40     -- "page size"
:setvar sqlcmdcolwidth          132     -- line width
:setvar sqlcmdmaxfixedtypewidth  32     -- max column width (fixed length)
:setvar sqlcmdmaxvartypewidth    32     -- max column width (varying length)
 
use master
create database testdata;
go
 
use testdata
go
 
select    name 
from      sys.databases;
 
select    name, physical_name
from      sys.master_files;
 
select    name, schema_id
from      sys.schemas;
go
 
use master
drop database testdata;
go
 
exit

Я, возможно, был слегка расточителен в использовании "go"; им отмечается конец пакета команд, которые должен выполнить SQL Server, и, честно говоря, у меня есть желание писать его фактически после каждого оператора. Это способ подражать инструменту Oracle SQL*Plus, где каждый SQL-оператор заканчивается точкой с запятой, после чего выполняется.

В любом случае вот результат запуска моего скрипта из sqlcmd:

C:\ss2008>sqlcmd
1> :r test1.sql
Changed database context to 'master'.
Changed database context to 'testdata'.
name
--------------------------------
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
testdata
 
(7 rows affected)
name                             physical_name
-------------------------------- --------------------------------
master                           C:\Program Files\Microsoft SQL S
mastlog                          C:\Program Files\Microsoft SQL S
tempdev                          C:\Program Files\Microsoft SQL S
templog                          C:\Program Files\Microsoft SQL S
modeldev                         C:\Program Files\Microsoft SQL S
modellog                         C:\Program Files\Microsoft SQL S
MSDBData                         C:\Program Files\Microsoft SQL S
MSDBLog                          C:\Program Files\Microsoft SQL S
ReportServer                     C:\Program Files\Microsoft SQL S
ReportServer_log                 C:\Program Files\Microsoft SQL S
ReportServerTempDB               C:\Program Files\Microsoft SQL S
ReportServerTempDB_log           C:\Program Files\Microsoft SQL S
testdata                         C:\Program Files\Microsoft SQL S
testdata_log                     C:\Program Files\Microsoft SQL S
 
(14 rows affected)
name                             schema_id  
-------------------------------- -----------
dbo                                        1
guest                                      2
INFORMATION_SCHEMA                         3
sys                                        4
db_owner                               16384
db_accessadmin                         16385
db_securityadmin                       16386
db_ddladmin                            16387
db_backupoperator                      16389
db_datareader                          16390
db_datawriter                          16391
db_denydatareader                      16392
db_denydatawriter                      16393
 
(13 rows affected)
Changed database context to 'master'.
 
C:\ss2008>

Заметьте, что выводимое physical_name обрезано, и из-за этого нельзя увидеть реальные имена различных файлов. Как уже говорилось, sqlcmd очень трудно использовать, если вы привыкли к командам форматирования и представления, имеющимся в SQL*Plus, и мне еще предстоит выяснить, как получить смесь длинных и коротких текстовых колонок в выводе скрипта.

Так что же мне удалось выяснить к этому моменту?

Экземпляры и базы данных

Файловые группы и файлы журналов

Схемы

Создание тестовых данных

Вот пример того, как можно сгенерировать довольно типичный набор тестовых данных в Oracle:

execute dbms_random.seed(0)
drop table t1;
 
create table t1
as
with generator as (
    select            rownum            id
    from        dual
    connect by
                rownum <= 1000
)
select
    rownum                                          id,
    trunc((rownum - 1) / 1000)                clustered,
    mod(rownum - 1,1000)                      scattered,
    trunc(dbms_random.value(0,1000))          randomised,
    substr(dbms_random.string('U',12),1,12)   random_string,
    lpad(rownum,10)                           vc_small,
    rpad('x',10,'x')                          vc_padding
from
    generator   g1,
    generator   g2
;
 
alter table t1 add constraint t1_pk primary key(id);

Это чрезвычайно просто и очень быстро, генерирование и индексация миллиона строк занимает около минуты. Если заменить 1000 в моем скрипте на 5, чтобы код генерировал только 25 строк, данные будут выглядеть так (кроме колонки padding с ее строкой из 100 "х").

        ID  CLUSTERED  SCATTERED RANDOMISED RANDOM_STRIN VC_SMALL
---------- ---------- ---------- ---------- ------------ ----------
         1          0          0          0 VGFJBLYYVLHM          1
         2          0          1          3 VMIEYETSDPDA          2
         3          0          2          1 YGSAWZADCKDC          3
         4          0          3          1 VJJITCRIDNRY          4
         5          0          4          1 CBEWPEQUMVYV          5
         6          1          0          2 JXXXREOHMNVU          6
         7          1          1          4 GOBWQUBBWQHS          7
         8          1          2          0 PXERZSFRAJNR          8
         9          1          3          4 ELGXALKRBHON          9
        10          1          4          4 UMWBBMVWVNAS         10
        11          2          0          0 WELXKFIXRCMH         11
        12          2          1          1 IZTDUYDVUHDW         12
        13          2          2          1 IEWWPCBMJVWJ         13
        14          2          3          0 GPIBUFBUQPUY         14
        15          2          4          1 VAITJUCGCYZT         15
        16          3          0          2 VCESDZTDAXBW         16
        17          3          1          0 UMAHORWYVLGL         17
        18          3          2          0 YHDZEEEYYFUJ         18
        19          3          3          3 VWKRCCFKBRXZ         19
        20          3          4          4 VEVHDDAHZXHF         20
        21          4          0          4 ZBXEFWDZJZBB         21
        22          4          1          4 ICZXEZEZLIBY         22
        23          4          2          2 THAASKTZGKGF         23
        24          4          3          3 GYXWADKCBEGD         24
        25          4          4          4 SNXYOQLFXWKC         25

К сожалению, есть как минимум восемь причин, по которым мой код, генерирующий данные, не будет работать в SQL Server, и даже если я заменю SQL на что-нибудь работающее, он не будет делать то же, что в Oracle (по причинам, которые я раскрою ниже). Очевидно, исследования Oracle придется отложить до тех пор, пока я не смогу решить, как перевести этот код Oracle в код SQL Server. Так что давайте посмотрим на код и на некоторые из проблем его переноса на SQL Server.

Кода должен состоит создавать простую таблицу с одним миллионом строк "случайных" числовых и строковых данных. Давайте посмотрим на раздел скрипта CREATE TABLE, где мы используем общее табличное выражение (common table expression, CTE), механизм, в Oracle обычно называемый "вынесением подзапроса" (subquery factoring). В этом CTE мы генерируем небольшой набор данных (используя пару специфичных для Oracle возможностей), который затем будет "развернут" в большой набор данных.

В последующем выражении SELECT мы раскрываем этот начальный маленький набор данных в намного больший. Хотя я и использовал для его генерирования внутреннюю функцию Oracle, столбец id представляет уникальный идентификатор для каждой записи. Общий эффект, если просмотреть записи в порядке id, состоит в том, что разреженные, кластеризованные и рандомизированные столбцы будут показывать повторяющиеся вхождения значений от 0 до 999, но с чрезвычайно различающимися шаблонами распределения данных:

Функций mod() и trunc() нет в SQL Server, но первая же ссылка в результатах поиска функций в Books Online приводит на полный список страниц, посвященных различного типа функциям SQL Server. Чило функций показалось мне несколько меньшим, чем в Oracle. Однако арифметические функции включают floor() и ceiling(), что дает нам хорошую стартовую точку, и имеется оператор modulo (оператор %), которого я не нашел, пока не догадался поискать "арифметические операторы" в Books Online.

Столбец random_string будет содержать строку из 12 символов в верхнем регистре. (вызов dbms_random.seed(0) в коде обеспечивает, что тот же "случайный" набор данных порождается при каждом запуске скрипта). Столбец vc_small является 10-символьным представлением id, дополненным пробелами слева, и, наконец, столбец vc_padding – это всего лишь 100 раз повторенная буква "х", которую я использую для забивки записей до приемлемого размера.

В выражении from наш CTE-генератор вновь появляется в декартовом (или перекрестном) соединении, поскольку мы начинаем с небольшого набора данных и затем разворачиваем его в большой набор. Oracle, скорее всего, будет работать с JOIN как с соединением слиянием (merge join), а SQL Server, скорее всего, сообщит о вложенном цикле.

После создания данных я определил первичный ключ на столбце id, что неявно сгенерирует уникальный индекс для столбца и добавит ограничение NOT NULL. Несмотря на то, что синтаксис одинаков для Oracle и SQL Server, эффект будет разным, поскольку в SQL Server индекс по умолчанию кластеризован, что означает (в терминах Oracle), чт он будет перестраивать таблицу в нечто очень похожее на "индексированную таблицу ".

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

use testdata
go
 
begin
    if exists (
          select      name 
          from        sys.objects 
          where       name = 'contracts' 
          and         type_desc = 'USER_TABLE'
          and         schema_name(schema_id) = 'dbo'
    )
    begin
          drop table contracts
    end
end
go
 
create table contracts (
    id          int         not null 
          constraint con_pk primary key nonclustered,
    id_office   int         not null,
    description varchar(60)
)
 
create index con_off on contracts(id_office)
go
    
insert into contracts 
    (id, id_office, description)
values
    (1,1,'Contract 1 office 1'),
    (2,1,'Contract 2 office 1'),
    (3,2,'Contract 3 office 2')
;
go
 
commit;
go
 
select * from contracts;
go

Я сомневаюсь, что у кого-то из мира Oracle будут проблемы с пониманием этого кода, хотя этой формы if exists не существует в SQL или PL/SQL, а процедурная часть скрипта должна была бы быть создана как "анонимный PL/SQL-блок".

Я написал проверку существования для схемы dbo, проверяющую мое предыдущее утверждение, что dbo – это схема, используемая по умолчанию, если вы не подключаете или не указываете каким-либо иным образом схему при создании объекта. Есть несколько популярных способов написания кода, исключающего ошибку для "drop table"; но единственная причина того, что я использовал один из них – дать пример смешивания чистого SQL с процедурным кодом в SQL Server.

Функция schema_name() – интересная деталь SQL Server, и кажется, как и многие функции, связанные с использованием словаря данных, эффективно заменяет join. Вопрос к себе: изменяет ли использование функции план исполнения или она как-то расширяет SQL соединением с представлением sys.schema, что было бы более "чистым", и, возможно, более эффективным способом написания SQL?

Есть еще пара деталей, незнакомых пользователям Oracle. В частности, достойно упоминания использование ключевого слова nonclustered в определениях ограничений: по умолчанию первичные ключи в SQL Server кластеризованы, что в терминах Oracle приводит к "индексированной таблице". Мой выбор в данном случае – создание простой таблицы с отдельным индексом – даже несмотря на то, что это кажетс близким к ереси поведением в мире SQL Server.

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

Последним сюрпризом на этом первом шаге моего пути стало открытие эффекта команды commit; она было не только избыточна, она еще и порождала ошибку:

По умолчанию моя SQL Server БД не выполняется в настоящее время в "транзакционном, с непротиворечивым чтением" режиме. Я выполнил вызов, чтобы вставить данные, и он фиксирован и виден в момент завершения команды. Я не могу откатить изменения, а все остальные могут немедленно видеть данные. В краткосрочном плане, пока я ищу способы сгенерировать тестовые данные, это не проблема; но эту область мне нужно исследовать особо тщательно, если я хочу достойным образом сравнить Oracle и SQL Server. Согласованность чтения и изоляция транзакций очень важны, и когда-то мне придется исследовать все уровни изоляции, предлагаемый SQL Server, чтобы выяснить, как их лучше использовать.

Итог

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

Пока что я узнал, как использовать руководства, создать БД, выбрать немного информации из словаря данных и создать небольшое количество данных. Моя следующая цель – найти эффективный способ быстрой генерации больших объемов данных, чтобы иметь возможность начать исследовать различные структуры, используемые для хранения данных, и понять, какие возможности у меня есть для оптимизации хранения и выборки данных.

Стратегии генерирования данных

Хорошая, масштабируемая производительность БД во многом зависит от размещения данных в правильном месте, возможности эффективного доступа к ним и устранения ненужных накладных расходов. В конце предыдущего раздела я поставил перед собой задачу быстро создать большой набор данных, чтобы начать исследовать стратегии размещения данных в SQL Server 2008, чтобы узнать его слабости и преимущества.

Все, что я сперва хотел – эмулировать работу SQL-выражения, которое я часто использую в Oracle, для генерирования нескольких миллионов строк данных, и этот раздел – история моих странствий по SQL Server в поисках подходящего, пусть не совершенного, способа сгенерировать данные, которые мне нужны.

Следующий раздел показывает различные испробованные мной подходы. Если не сказано другого, код исполняется с помощью SQLCMD.

Стратегия 1: строка за строкой

Я начал со стратегии, которая, по моему мнению, была очень плохой идеей (в Oracle это точно так): создать пустую таблицу и использовать программный цикл для вставки одной строки за раз. Это простой способ получить счетчик, который мне нужен для моих игр с mod() и trunc().

Я был несколько удивлен тем, что единственной конструкцией цикла в T-SQL оказалось WHILE...END. У меня были также небольшие проблемы с поисками оператора присвоения; сперва казалось, что единственным вариантом был SELECT в переменные, но в итоге я нашел оператор SET. После нескольких тестов оба варианта выглядели одинаково в смысле производительности, так что возможно, за кулисами это на самом деле один и тот же код.

Обрезанный до минимума Листинг 2 показывает, что у меня получилось.

Листинг 2. Подход строка-за-строкой.

CREATE TABLE big_table
    (
      id INT ,
      clustered_data INT ,
      scattered_data INT ,
      vc_small VARCHAR(10) ,
      vc_padding VARCHAR(100)
    ) ;
go
 
DECLARE @div INT = 50
DECLARE @mod INT = 200
DECLARE @loop INT = 1
DECLARE @limit INT = @div * @mod
 
BEGIN
    WHILE @loop <= @limit 
        BEGIN
            INSERT  INTO big_table
                    ( id ,
                      clustered_data ,
                      scattered_data ,
                      vc_small ,
                      vc_padding
                 )
            VALUES  ( @loop ,
                      FLOOR(( @loop - 1 ) / @div) , -- trunc()
                      ( @loop - 1 ) % @mod ,        -- mod()
                      STR(@loop, 10, 0) ,           -- to_char()
                      REPLICATE('x', 100)           -- rpad()
                 )
 
            SET @loop = @loop + 1
        END
END
Go

Использованные мной функции – ближайшие аналоги функций Oracle, указанных в комментариях, но они не вполне идентичны. Я создал несколько переменных вместо фиксирования констант в скрипте; в конце концов я надеюсь создать шаблон, который я смогу легко настраивать для генерирования различных объемов и образцов.

В данном случае я ограничился 50 отдельными значениями, по 200 строк каждого, чтобы получить всего 10000 строк. Производительность была ожидаемо гнусной: исполнение кода заняло 19.5 секунд. Независимо от используемой РСУБД, деление большой работы за множество маленьких кусочков почти наверняка очень плохая идея.

Интересно, что приведенный выше код страдает от накладных расходов на обработку обмена сообщениями для каждой отдельной вставки в цикле. Читая документацию, я наткнулся на опцию NOCOUNT, и добавив команду SET NOCOUNT ON в начало Листинга 2, уменьшил время выполнения до 5.5 секунд. Это все еще медленно, конечно, но позволяет понять, сколько мелких деталей нужно изучить прежде, чем иметь право критиковать продукт!

Стратегия 2: Sys.objects, CTE и row_number

Представление sys.all_objects всегда содержит достаточное число строк, так почему бы не использовать его, выполняя перекрестное объединение (cross join) с ним самим, для генерирования большого количества данных? Чтобы код выглядел проще, можно поместить управляющий запрос в CTE до выполнения перекрестного объединения. Мне нужны порядковые номера, для этого я могу воспользоваться в CTE аналитической функцией row_number(), а затем использовать немного арифметики в перекрестном объединении.

Листинг 3 показывает реализацию этого подхода. Он нормально работает и генерирует 1000000 записей примерно за 13.5 секунд, отнимая почти 100% CPU. Определение то же, что и в листинге 2.

Листинг 3. Перекрестное объединение sys.all_objects в CTE

DECLARE @div INT = 50 ;
DECLARE @mod INT = 20000 ;
DECLARE @limit INT = @div * @mod ;
DECLARE @driver INT = 1000 ;
 
WITH    generator
          AS ( SELECT TOP ( @driver )
                        row_number() OVER ( ORDER BY schema_id ) id
               FROM     sys.all_objects
             )
    INSERT  INTO big_table
            SELECT TOP ( @limit )
                    @driver * ( g1.id - 1 ) + g2.id ,
                    FLOOR(( @driver * ( g1.id - 1 ) + g2.id - 1 ) / @div) ,
                    ( @driver * ( g1.id - 1 ) + g2.id - 1 ) % @mod ,
                    STR(@driver * ( g1.id - 1 ) + g2.id, 10, 0) ,
                    REPLICATE('x', 100)
            FROM    generator g1
                    CROSS JOIN generator g2
    OPTION  ( FORCE ORDER ) ;

Однако в этом коде есть пара вещей, которые мне не нравятся. Во-первых, он зависит от объекта SYS, и возможно, я его могу сейчас видеть только потому, что работаю как привилегированный пользователь. Потенциальной проблемой может быть и то, что в представлении может меньше строк, чем мне нужно для тестов большого размера; в моей тестовой БД менее 2000 строк.

Еще одна вещь, от которой я не в восторге, - это хинт FORCE ORDER, который я включил, чтобы быть уверенным, что оптимизатор объединит две копии генератора в правильном порядке, чтобы моя арифметика выдавала верные результаты. На самом деле я не знаю, как оптимизатор собирается работать с CTE; план выполнения, случается, выбирает способ, которым мне сейчас нужен, но это вопрос удачи, и может измениться, если мне понадобится меньше или больше строк.

Чтобы было яснее, мне нравится идея удалить повторяющийся арифметический кусок, включающий два id, введя inline-представление. Я покажу это в следующем примере.

Замечание по "insert...with...select ..." вместо "with...insert...select..."

Моей первой попыткой использования СТЕ для создания данных кончилась неудачей ("Msg 102, Level 15, State 1, Server HPBASE, Line 2, Incorrect syntax near 'generator'"). Я использовал шаблон "insert...with...select ..." из Oracle, где СТЕ – часть выражения Select вместо "with...insert...select...".

Стратегия 3: рекурсивное СТЕ

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

Как насчет рекурсивного СТЕ (возможность, которой не было в Oracle до версии 11g)? которое вовсе не обращается ни к каким объектам?

Листинг 4. Подход с рекурсивным СТЕ

DECLARE @div INT = 50 ;
DECLARE @mod INT = 20000 ;
DECLARE @limit INT = @div * @mod ;
DECLARE @driver INT = 1000 ;
 
WITH    generator
          AS ( SELECT   1 AS id
               UNION ALL
               SELECT   id + 1
               FROM     generator
               WHERE    id < @driver
             )
    INSERT  INTO big_table
            SELECT  id ,
                    FLOOR(( id - 1 ) / @div) ,
                    ( id - 1 ) % @mod ,
                    STR(id, 10, 0) ,
                    REPLICATE('x', 100)
            FROM    ( SELECT TOP ( @limit )
                                @driver * ( g1.id - 1 ) + g2.id id
                      FROM      generator g1
                                CROSS JOIN generator g2
                    ) iv
    OPTION  ( MAXRECURSION 0, FORCE ORDER ) ;

Это решение работает довольно хорошо, генерируя миллион строк примерно за 13 секунд - пока что лучшее время. Однако у меня было много фальстартов, прежде чем я пришел к этому решению, и на самом деле я на некоторое время вообще отказался от этой идеи, потому что не мог заставить ее работать. На паре первых очень мелких тестов код, казалось, работал, но когда я, наконец, задал значение @driver в 1000, чтобы сгенерировать мой миллион строк, я получил сообщение об ошибке:

На тот момент я не рассматривал возможность того, что уровень рекурсии задается программно с помощью хинта, и только через несколько недель после первой попытки узнал об этом из беседы с SQL Server-экспертами. Уровню рекурсии можно задать любое значение до 32,767, после которого идет ноль, означающий "нет ограничений".

Уровень рекурсии был не единственной моей проблемой с этим запросом: при первой попытке использования inline-представления я получил следующую ошибку:

Когда я удалил выражение option(), ошибка изменилась на:

Я провел 15 минут, тупо глядя в текст, прежде чем заметил, что я не дал псевдонима inline-представлению. Это не требуется в Oracle, и даже в некоторых случаях вызывает сообщения об ошибках. Кажется, что всегда больше всего времени при работе в незнакомой среде отнимают самые мелкие мелочи!

Замечание по поддержке ANSI join в SQL Server vs. Oracle

Синтаксис SQL Server для ANSI join гораздо лучше, чем в Oracle. Для меня было неприятным сюрпризом, когда я впервые начал писать ANSI-код и обнаружил, что такие вещи, как псевдонимы для inline join и столбцов, допустимые в SQL Server, просто не работают в Oracle.

Прежде чем перейти к следующей стратегии, вы можете поинтересоваться, почему листинг 4 (и исходный код для Oracle) использует маленькое рекурсивное СТЕ с перекрестным объединением вместо простого использования большого СТЕ, как в листинге 5 (который избавился от хинта FORCE ORDER).

Листинг 5. Более простой, но куда более медленный подход с СТЕ

WITH    generator
          AS ( SELECT   1 AS id
               UNION ALL
               SELECT   id + 1
               FROM     generator
               WHERE    id < @driver    -- set to something BIG
             )
    INSERT  INTO big_table
            SELECT  id ,
                    FLOOR(( id - 1 ) / @div) ,
                    ( id - 1 ) % @mod ,
                    STR(id, 10, 0) ,
                    REPLICATE('x', 100)
            FROM    generator g1
    OPTION  ( MAXRECURSION 0 ) ;

Ответ в том, что рекурсия в большинстве сред кодирования, и я просто предположил, что так же, вероятно, будет и в SQL Server 2008. В примере для Oracle основная стоимость генерирования 1000000 строк скакнула с 7 до 11 секунд, когда я переключился с маленьких объединений на одно использование СТЕ, а пример для SQL Server – с 13 секунд до 48 секунд

Стратегия 4: Функция, возвращающая табличное значение

Временно оставив подход с рекурсивным СТЕ, я подумал, что лучше попробовать другую "свободную от данных" стратегию, а именно функцию, возвращающую табличное значение (Table-Valued Function или TVF, в Oracle называется конвейерной функцией). Я начну с определения табличной функции, показанного в листинге 6, а затем приведу обоснование.

Листинг 6. Подход с использованием TVF

CREATE FUNCTION table_function ( @i_in AS INTEGER )
RETURNS @table_result TABLE ( id INT )
AS 
    BEGIN
        DECLARE @loop INT = 1 ;
        WHILE @loop <= @i_in 
            BEGIN
                INSERT  INTO @table_result
                VALUES  ( @loop ) ;
                SET @loop = @loop + 1 ;
            END ;
        RETURN
    END
Go

Возвращаемый тип TVF – это таблица, как и следует из ее названия, и я на самом деле объявил переменную табличного типа и затем написал простой цикл, вставляющий строки в эту таблицу (в одном из вариантов этого теста я использовал механизм рекурсивных СТЕ для генерирования набора результатов для табличной функции). Имя таблицы – @table_result, символ @ указывает, что это временная таблица.

Один из комментариев к этой статье указывал, что есть три разных типа временных таблиц: #table_name для локальной области видимости, ##table_name для глобальной области видимости и @table_name для внутренней локальной области видимости. Мне пришлось проделать немало работы, проверяя, как они работают, чего они стоят и тот ли тип таблицы я использовал.

Чтобы использовать эту функцию, мне нужно просто исправить определение СТЕ, как показано в листинге 7.

Листинг 7. Использование TVF с CTE-генератором

WITH    generator
          AS ( SELECT   id
               FROM     table_function(@driver)
             )
    INSERT  INTO big_table
            SELECT  id ,
                    FLOOR(( id - 1 ) / @div) ,
                    ( id - 1 ) % @mod ,
                    STR(id, 10, 0) ,
                    REPLICATE('x', 100)
            FROM    ( SELECT TOP ( @limit )
                                @driver * ( g1.id - 1 ) + g2.id id
                      FROM      generator g1
                                CROSS JOIN generator g2
                    ) iv
    OPTION  ( FORCE ORDER ) ;

Исполнение кода занимает 15 секунд, что на 2 секунды больше, чем рекурсивное СТЕ. Я предполагаю, что эта разница в принципе несущественна, но, возможно, 2 секунды – это влияние временной таблицы, или обращений к памяти, выполняемых для табличной функции. Если бы все это делалось не для быстрого и убогого генератора данных, мне пришлось бы выяснять, почему разница столь велика. Пока же я просто занес эту аномалию в свой список задач.

Опять же достойно быстрой проверки влияние замены перекрестного объединения на один вызов табличной функции для 1000000 строк: ответ аналогичен примеру с рекурсивным СТЕ; время исполнения изменилось с 15 секунд на 48 секунд. И снова мы видим, что создание большого объекта в памяти может быть очень нагружать процессор, тогда как SQL-подход, реально работающий в непрерывном потоке, гораздо более эффективен.

Стратегия 5: использование временной таблицы с СТЕ

К этому моменту я понял две вещи. Первое, мне реально необходимо было знать, как SQL Server генерирует данные, так что исследования планов исполнения для генерации данных и их чтения стали очень важны. Второе, я понял, что думаю слишком в стиле Oracle.

Мне нужно было попробовать подумать над моей проблемой в натуральном "SQL Server-стиле", и возможно, есть еще масса вещей в стиле SQL Server которые мне стоило сделать к этому моменту (я уверен, вы мне скажете, каких!).

Может, если бы я бросил возиться с рекурсивными СТЕ и табличными функциями, я мог бы сделать что-то более эффективное. Код в листинге 8 выглядит очень похоже на листинг 4, но в нем есть важный промежуточный шаг, который, как я думал, может многое изменить.

Листинг 8. Использование временной таблицы с СТЕ

WITH    generator
          AS ( SELECT   1 AS id
               UNION ALL
               SELECT   id + 1
               FROM     generator
               WHERE    id < @driver
             )
    SELECT  *
    INTO    #generator
    FROM    generator
OPTION  ( MAXRECURSION 0 ) ;
 
INSERT  INTO big_table
        SELECT  id ,
                FLOOR(( id - 1 ) / @div) ,
                ( id - 1 ) % @mod ,
                STR(id, 10, 0) ,
                REPLICATE('x', 100)
        FROM    ( SELECT TOP ( @limit )
                            @driver * ( g1.id - 1 ) + g2.id id
                  FROM      #generator g1
                            CROSS JOIN #generator g2
                ) iv
OPTION  ( FORCE ORDER ) ;
 
go

Разница в том, что я создал временную таблицу для моего набора данных. Одна из мантр энтузиастов Oracle говорит "вам не нужны временные таблицы" (это, конечно, не так, но обычно мы их прячем за СТЕ и неподключаемых представлениях). Я заметил, что в SQL Server они, наоборот, используются широко и вполне открыто. С этой временной таблицей (я думал!) я могу быть уверенным, что я не буду перегенерировать рекурсивное СТЕ 1000 раз, или вызывать 1000 раз табличную функцию. Так что если какая-то из моих стратегий и должна была работать заметно быстрее других, то я бы поставил именно на эту. Я ошибался. На самом деле эта версия кода исполнялась несколько дольше остальных. Я, конечно, понятия не имею, почему.

Заключение

После нескольких часов валяния дурака я нашел способ сгенерировать полезные объемы данных. Я не нашел столь же быстрого механизма как в Oracle, но затрачиваемое время примерно того же порядка, а когда дело дошло до добавления ограничения PRIMARY KEY и других индексов, преимущество во времени сместилось в другую сторону.

Здесь интересно, что в Oracle я добавлял ограничение PRIMARY KEY одной командой, а в SQL Server мне пришлось сперва ввести ограничение NOT NULL. Составляет ли это существенную разницу? Когда вы знаете, в какую сторону смотреть (а я знаю, когда работаю с Oracle) вы можете увидеть множество интересных деталей. На самом деле, за сценой, добавление ограничения PRIMARY KEY в Oracle требует двух проходов по таблице, один – для добавления ограничения NOT NULL, а второй – для создания индекса. Так что над БД выполняется тот же объем работы, что и в SQL Server, но Oracle прячет два действия за одной командой.

Из разных испробованных мной механизмов я собираюсь использовать в будущем рекурсивное СТЕ из стратегии 3, просто потому что это наиболее независимый вариант из рассмотренных.

Мне по-прежнему нужно отыскать пристойный способ генерирования псевдослучайных данных, и мне интересно, какой метод объединения выбрал SQL Server для перекрестного объединения, использованного в моих непроцедурных примерах, так что подходит время пристально посмотреть на планы исполнения.

Закончу двумя пунктами о сходстве и различии таблиц с миллионом строк, созданных SQL Server и Oracle:

В следующей паре статей я более подробно рассмотрю, где хранятся мои данные, и как SQL Server обращается к ним – поскольку без такой информации нельзя создать эффективно работающую базу данных.

Страницы и экстенты

В Oracle мы говорим о сегментах данных. Простой объект соответствует одному сегменту данных, каждый раздел разделенного объекта (или подраздел композитного разделенного объекта) – это отдельный сегмент данных; сегмент, в свою очередь, состоит из коллекции экстентов; экстент же состоит из непрерывного набора блоков (страниц) в файле.

Я начал свое исследование с аналогичных структур данных в SQL Server. В SQL Server нет такой вещи, как сегмент, но поиск слова "extents" в Books Online выдает то, что нужно. Первая ссылка ведет на статью Understanding Pages and Extents (http://msdn.microsoft.com/en-us/library/ms190969.aspx), которая оказалась отличной отправной точкой для понимания того, как SQL Server занимает пространство и хранит данные.

Я не буду повторять все детали моих странствий по руководствам, а подведу основные итоги (с комментариями по Oracle курсивом):

Пара мыслей, вытекающих из этого рассмотрения: есть "единица размещения" на уровне операционной системы Windows, по умолчанию на моем маленьком лаптопе это 4КВ. Очевидно, есть смысл в том, чтобы размер этой единицы размещения совпадал с размером страницы БД. На самом деле, поскольку, как я заметил, что SQL Server по возможности выполняет опережающее чтение всего экстента, есть причина создавать единицы размещения в 16KB, 32KB или 64KB (если это возможно), чтобы границы страниц БД совпадали с границами единиц размещения ОС (с другой стороны, возможно, больший размер экстента приведет к операциям "чтение/заполнение/запись", если SQL Server захочет записать один блок в 8KB в 32-килобатный экстент).

Хранение данных в кучах и в В-деревьях

Двигаясь от экстента к объекту, всего в двух щелчках мыши от Table and Index Organization (http://msdn.microsoft.com/en-us/library/ms189051.aspx), мы найдем, что:

После этого краткого объяснения пришла пора для некоторых технических исследований. Насколько хорошо могу я отследить, куда попадают данные? В предыдущей статье я сделал несколько комментариев о различиях в объеме места, занимаемого Oracle и SQL Server для хранения одних и тех же данных; Для миллиона одних и тех же записей куча в Oracle занимала на 8% больше страниц, а некластерный индекс первичного ключа – на 7% меньше. Поскольку размещение данных – это ключ к производительности, я собираюсь попробовать выяснить, что они делают по-разному.

Начнем со скрипта создания данных, показанного в листинге 1 (причины того, что этот скрипт именно таков, объяснялись в предыдущей статье). Скрипт основан на шаблоне, который я использую для генерирования всевозможных объемов и моделей данных, но в данном случае создается только 5000 записей в heap-таблице с некластерным неуникальным индексом.

Листинг 1. Создание test_table и заполнение ее данными

CREATE TABLE test_table
    (
      id INT ,
      random_data INT ,
      update_date DATE ,
      vc_small VARCHAR(10) ,
      vc_padding VARCHAR(100)
    ) ;
go
 
CREATE INDEX bt_i_rand ON test_table(random_data) ;
go
 
DECLARE @div INT = 50 ;
DECLARE @mod INT = 100 ;
DECLARE @limit INT = @div * @mod ;
DECLARE @driver INT = 1000 ;
 
WITH    generator
          AS ( SELECT   1 AS id
               UNION ALL
               SELECT   id + 1
               FROM     generator
               WHERE    id < @driver
             )
    INSERT  INTO test_table
            SELECT  id ,
                    ABS(xx % @mod) ,
                    NULL ,
                    NULL ,
                    REPLICATE('x', 100)
            FROM    ( SELECT TOP ( @limit )
                                @driver * ( g1.id - 1 ) + g2.id id ,
                                CAST(NEWID() AS VARBINARY) xx
                      FROM      generator g1
                                CROSS JOIN generator g2
                    ) iv
    OPTION  ( MAXRECURSION 0, FORCE ORDER ) ;

В процессе исследований выделения места на диске в SQL Server я обнаружил скрипт, сообщающий о пространстве, занимаемом таблицей (и ее индексами), с помощью запроса к представлению sys.allocation_units. Я приспособил этот запрос для своих нужд, как показано в листинге 2 (STR-команды здесь просто чтобы сделать вывод в SQLCMD аккуратнее).

Листинг 2. Скрипт, сообщающий о месте, занимаемом test_table

SELECT  SUBSTRING(tab.name, 1, 16) table_name ,
        tab.object_id object_id ,
        prt.index_id index_id ,
        SUBSTRING(alu.type_desc, 1, 12) alloc_type ,
        alu.data_space_id ,
        STR(alu.total_pages, 8, 0) tot_pages ,
        STR(alu.used_pages, 8, 0) used_pages ,
        STR(alu.data_pages, 8, 0) data_pages
FROM    sys.schemas sch
        INNER JOIN sys.tables tab ON tab.schema_id = sch.schema_id
        INNER JOIN sys.partitions prt ON prt.object_id = tab.object_id
        INNER JOIN sys.allocation_units alu
                                  ON alu.container_id = prt.partition_id
WHERE   sch.name = 'DBO'
ORDER BY tab.name ,
        prt.partition_id ,
        prt.index_id ,
        alu.allocation_unit_id
go

После создания новой базы данных (с названием testdata), и создания одной (heap) таблицы и индекса, показанных в листинге 1, результат запроса был следующим:

table_name    object_id   index_id   alloc_type   data_space_id  tot_pages  used_pages  data_pages
---------------- ----------- ----------- ------------ ------------- --------- ---------- ---------
test_table    2105058535  0          IN_ROW_DATA  1              81         80          79
test_table    2105058535  2          IN_ROW_DATA  1              25         19          17

index_id для heap-таблицы равен нулю, а для индекса – 2. Если бы я создавал кластерный индекс, в результате была бы только одна строка с index_id, равным 1.

Heap-таблица занимает 81 страницу, из которых 79 заняты данными, а индекс – 25 страниц, из которых данными заняты 17.

Выше я намекнул, что, кажется, в heap-таблицах нет никакого эквивалента для "коэффициента заполнения", который можно указать для индексов (в Oracle параметр PCTFREE, применимый к таблицам и индексам, это практически то же самое, что и "100 –коэффициент заполнения"). К сожалению, никто намека не понял, таким образом, пришло время на собственном опыте выяснять, насколько хорошо заполняются блоки таблицы, что означает формирование дампа реальных страниц...

Исследование блоков таблицы с использование DBCC IND и DBCC PAGE

Мне необходимо было найти способ перечисления страниц таблицы, а затем вывести их. Это непросто, но я обратил внимание, что всякий раз, когда в SQL Server нужно сделать что-нибудь тонкое, надо использовать DBCC-команду. Так что я попробовал поискать в Google "DBCC PAGE", и вскоре нашел набор интересных статей Поля Рэндалла, первая из которых дала мне синтаксис команды DBCC PAGE, после чего я быстро нашел команду DBCC IND.

Я начал с исследований heap-таблицы с помощью вызова DBCC IND, показанного в листинге 3.

Листинг 3. Вызов DBCC IND

Вопреки тому, что предполагается из "ind", этот вызов выдает список блоков таблицы:

PageFID   PagePID     IAMFID   IAMPID    ObjectID    IndexID   PartitionNumber  PartitionID
------- ----------- ------ ----------- ----------- ----------- --------------- -----------------
1         154         NULL     NULL      2105058535  0         1               72057594038779904
1         153         1        154       2105058535  0         1               72057594038779904
1         157         1        154       2105058535  0         1               72057594038779904
1         158         1        154       2105058535  0         1               72057594038779904
 
iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
-------------------- -------- ---------- ----------- ----------- ----------- -----------
In-row data          10       NULL       0           0           0           0
In-row data          1        0          0           0           0           0
In-row data          1        0          0           0           0           0
In-row data          1        0          0           0           0           0

Здесь говорится, что файл 1, блок 153 – это первый блок данных в таблице. Давайте выгрузим его, как показано в листинге 4, и посмотрим, что в нем лежит. Заметьте, что в SSMS вам сперва потребуется включить флаг трассировки (3604), используя DBCC TRACEON (3604).

Листинг 4. Выгрузка страницы 153

Дамп уровня 3 дает полный символьный дамп, из которого я извлек несколько строк:

PAGE: (1:153)
 
...{page header was here}
 
Slot 0 Offset 0x60 Length 124
 
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 124      
 
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
 
id = 1                               
 
Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4
 
random_data = 32                     
 
Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0
 
update_date = [NULL]                 
 
Slot 0 Column 4 Offset 0x0 Length 0 Length (physical) 0
 
vc_small = [NULL]                    
 
Slot 0 Column 5 Offset 0x18 Length 100 Length (physical) 100
 
vc_padding = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 
Slot 1 Offset 0xdc Length 124
 
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 124   
 ... 
Slot 63 Offset 0x1ee4 Length 124
 
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 124

Блок содержал 64 записи (слоты с 0 по 63), по 124 байта каждая, всего 7936 байт. Добавьте пару байт на каждый указатель на "Offsets" в таблице, и получится 8,064. Добавьте заголовок блока (который, как я думаю, равен 96 байтам), и в блоке останется всего 32 байта свободного пространства.

Это довольно плохие новости, если вы надеетесь изменять какие-либо данные в таблице. Если нет "коэффициента заполнения" для heap-таблиц, это используемое по умолчанию стопроцентное заполнение дает каждому пользователю SQL Server вескую причину не использовать ничего, кроме кластерных индексов, везде, кроме некоторых таблиц, которым не потребуется никаких обновлений.

Чтобы продемонстрировать проблему обновлений, давайте выполним следующее простое обновление test_table, показанное в листинге 5.

Листинг 5. Изменение таблицы test_table

Вот как выглядит слот 3 (в числе прочих) после выполнения обновления и повторного исполнения команды вывода дампа 3 уровня.

Slot 3 Offset 0x1f2 Length 9
 
Record Type = FORWARDING_STUB        Record Attributes =                  Record Size = 9
 
Memory Dump @0x4176C1F2
 
00000000:   040e0100 00010008 00†††††††††††††††††........                
Forwarding to  =  file 1 page 270 slot 8

Слоты 0,1 и 2 показывают vc_small = xxxxxxxxxx, но между ними три записи заняли 30 из 32 байт, которые оставались свободными, так что в блоке нет места для обновления слота 3, и запись копируется в другой блок, не оставляя за собой ничего, кроме указателя. Пространство, освободившееся после этой "миграции записи", позволило выполнить еще несколько обновлений блока, прежде чем он опять заполнился, и в конце концов в блоке появилось 5 заглушек-указателей.

У дизайнера баз данных Oracle, если нет особой нужды использовать кластерные индексы и группировать данные по шаблонам, отличающимся от порядка, отражающего естественный порядок поступления данных, heap-таблица является очевидной структурой таблицы. Однако когда вы реализуете heap-таблицу в SQL Server, любые изменения данных будут отрицательно влиять на производительность из-за перемещения записей в другие блоки. Это, вероятно, одна из причин видимого энтузиазма по поводу создания кластерного индекса для колонки id, так как это сохраняет данные в порядке их поступления, но позволяет оставить в каждом блоке место для обновлений, указав подходящий коэффициент заполнения.

Что насчет некластерных индексов для heap-таблиц? Как они справляются с перемещением записей? Вот первые несколько строк результата использования команды DBCC IND для нашего индекса:

PageFID PagePID IAMFID IAMPID ObjectID   IndexID PartitionNumber PartitionID
------- ------- ------ ------ ---------- ------- --------------- -----------------
1       156     NULL   NULL   2105058535 2       1               72057594038845440
1       155     1      156    2105058535 2       1               72057594038845440
1       172     1      156    2105058535 2       1               72057594038845440
1       173     1      156    2105058535 2       1               72057594038845440
 
iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
-------------- -------- ---------- ----------- ----------- ----------- -----------
In-row data    10       NULL       0           0           0           0
In-row data    2        0          1           248         0           0
In-row data    2        1          0           0           0           0
In-row data    2        0          1           215         1           210

Из PagePID можно заметить, что индекс и таблица начинаются с использования одного и того же экстента (таблица использует блоки 153, 154, 157, 158 и 159, а индекс использует блоки 155 и 156).

Блок 156 – это IAM-страница (PageType = 10) для индекса, а блок 172 – это корневой блок (IndexLevel = 1, так что это не дочерний блок). Давайте сделаем дамп блока 155, первого дочернего блока, и посмотрим на вхождения индекса:

FileId      PageId    Row  Level random_data (key) HEAP RID (key) KeyHashValue   
------ ----------- ------ ------ ----------------- -------------- ---------------
     1         155      0      0                 0 0x020100000100 (03003c52c4d8)  
     1         155      1      0                 0 0x050100000100 (0600e2cd409d)  
...
     1         155    143      0                 3 0x0D0100000100 (1100bb6857c6)  
     1         155    144      0                 3 0x990000000100 (9d00b21bdb69)  
     1         155    145      0                 3 0x9F0000000100 (a300191bb3a4)  
...
     1         155    307      0                 6 0xBE0000000100 (c50036fde6b2)  
     1         155    308      0                 6 0xBF0000000100 (c600ed093b03)  
 
(309 rows affected)

Я показал начало, конец и исправление из середины списка вхождений дочернего блока. Вы видите, что каждое вхождение состоит из значения ключа (моя колонка random_data) и HEAP RID (идентификатор записи), который, по-видимому, является адресом блока для ключа. Поскольку HEAP RID также помечен как "key", я предполагаю, что он присоединяется к реальному значению ключа как средство для упорядочивания появления дубликатов; RID точно выглядят сортированными внутри ключа. Интересно, что порядок байтов в HEAP RID означает, что он может, в принципе, представлять большую степень случайного ввода/вывода, чем это необходимо – все выглядит так, будто записи с одним и тем же ключом в смежных блоках могут появиться в несмежных позициях в индексе.

Я распечатал кусок индекса, показывающий несколько вхождений random_data со значением 3, поскольку он показывает одну из записей из блока таблицы, дамп которого я делал: HEAP RID = 0x990000000100 соответствует файлу 1, блоку 153 (похоже, что первые 4 байта – это id блока, а последние 2 – id файла).

Однако есть проблема: слот 47 блока таблицы – это слот, который содержит значение 3, но я не могу найти ссылку на слот 47 где-либо в дампе дочернего блока. Значит ли это, что поиск по индексу имеет точность блока таблицы, а дальше SQL Server просматривает целый блок таблицы в поисках нужного ключа? Не похоже на то, особенно если подумать о возможных последствиях этого при поиске перемещенных записей.

К счастью, при переключении на уровень дампа 2 (сырой дамп деталей записи) можно найти в файле следующее:

Slot 144, Offset 0x60, Length 16, DumpStyle BYTE
Record Type = INDEX_RECORD     Record Attributes =  NULL_BITMAP     Record Size = 16
Memory Dump @0x4146C060
00000000:   16030000 00990000 0001002f 00020000 †.

Обратите внимание на "2f"; после перевода в десятичную систему это будет число 47, нужный нам идентификатор слота. Можно также заметить, что эта версия дампа не включает ничего, выглядящего как KeyHashValue. Как и в Oracle, некоторые файлы дампов сообщают информацию, которая выводится в момент создания дампа, а кое-какая реальная информация в дампе отсутствует.

Если отследить связь листа индекса с записью таблицы, понадобится всего несколько минут, чтобы проверить, что если запись перемещена из heap-таблицы, вхождение индекса не обновляется; оно указывает на заглушку-указатель, а не на новое расположение записи (Oracle использует ту же стратегию).

Использования одной структуры за раз, вероятно, достаточно. Кластерными индексами (уникальными и неуникальными) я займусь в следующий раз.

Выводы

Если я не пропустил чего-нибудь очевидного, основной вывод об SQL Server и heap-таблицах состоит в том, что нельзя указать эквивалент коэффициента заполнения для heap-таблицы. Это значит, что обновления heap-таблицы, возможно, приведут к неразумному количеству перемещений записей, что может привести к неразумным потерям производительности при обращении к данным, поскольку в поисках записей придется переходить по ссылкам. Отсутствия коэффициента заполнения (самого по себе) достаточно, чтобы сделать использование heap-таблиц в SQL Server нежелательным.

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

Технология Клиент-Сервер 2010'3

От Oracle к SQL Server: пресекая великий разлом

Автор: Джонатан Льюис (Jonathan Lewis)
Источник: simpletalk.com

Опубликовано: 05.07.2011
Версия текста: 1.1

Setting Targets

Начинаем

Экземпляры, базы данных, схемы...

Экземпляры и базы данных

Файловые группы и файлы журналов

Схемы

Создание тестовых данных

Итог

Стратегии генерирования данных

Стратегия 1: строка за строкой

Листинг 2. Подход строка-за-строкой.

Стратегия 2: Sys.objects, CTE и row_number

Листинг 3. Перекрестное объединение sys.all_objects в CTE

Стратегия 3: рекурсивное СТЕ

Листинг 4. Подход с рекурсивным СТЕ

Листинг 5. Более простой, но куда более медленный подход с СТЕ

Стратегия 4: Функция, возвращающая табличное значение

Листинг 6. Подход с использованием TVF

Листинг 7. Использование TVF с CTE-генератором

Стратегия 5: использование временной таблицы с СТЕ

Листинг 8. Использование временной таблицы с СТЕ

Заключение

Страницы и экстенты

Хранение данных в кучах и в В-деревьях

Листинг 1. Создание test_table и заполнение ее данными

Листинг 2. Скрипт, сообщающий о месте, занимаемом test_table

Исследование блоков таблицы с использование DBCC IND и DBCC PAGE

Листинг 3. Вызов DBCC IND

Листинг 4. Выгрузка страницы 153

Листинг 5. Изменение таблицы test_table

Выводы

Технология Клиент-Сервер 2010'3

От Oracle к SQL Server: пресекая великий разлом

Автор: Джонатан Льюис (Jonathan Lewis)Источник: simpletalk.com

Опубликовано: 05.07.2011Версия текста: 1.1

Setting Targets

Начинаем

Экземпляры, базы данных, схемы...

Экземпляры и базы данных

Файловые группы и файлы журналов

Схемы

Создание тестовых данных

Итог

Стратегии генерирования данных

Стратегия 1: строка за строкой

Листинг 2. Подход строка-за-строкой.

Стратегия 2: Sys.objects, CTE и row_number

Листинг 3. Перекрестное объединение sys.all_objects в CTE

Стратегия 3: рекурсивное СТЕ

Листинг 4. Подход с рекурсивным СТЕ

Листинг 5. Более простой, но куда более медленный подход с СТЕ

Стратегия 4: Функция, возвращающая табличное значение

Листинг 6. Подход с использованием TVF

Листинг 7. Использование TVF с CTE-генератором

Стратегия 5: использование временной таблицы с СТЕ

Листинг 8. Использование временной таблицы с СТЕ

Заключение

Страницы и экстенты

Хранение данных в кучах и в В-деревьях

Листинг 1. Создание test_table и заполнение ее данными

Листинг 2. Скрипт, сообщающий о месте, занимаемом test_table

Исследование блоков таблицы с использование DBCC IND и DBCC PAGE

Листинг 3. Вызов DBCC IND

Листинг 4. Выгрузка страницы 153

Листинг 5. Изменение таблицы test_table

Выводы

Автор: Джонатан Льюис (Jonathan Lewis)
Источник: simpletalk.com

Опубликовано: 05.07.2011
Версия текста: 1.1