Большое количество проблем, связанных с производительностью SQL Server на больших данных так или иначе упираются в IO. В нашем случае, когда речь идёт о действительно больших объёмах, это ощущается по полной программе. Для примера, маленькая дивелоперская база данных, используемая локально на моей машине, занимает сейчас чуть менее 14 гигабайт. Это так, считай ничего, локально фичи писать/отлаживать.

На моей рабочей машине установлено “всего” 12 гигабайт памяти, из которых SQL Server’у разрешено использовать 6. Поэтому, понятное дело, о полном кешировании базы в памяти речь идти не может. Да и, думаю, редко где такая возможность существует.

И вот тут мы упираемся в IO. Особенно на scan’ах.

Решать эту проблему можно двумя путями:

1) Грамотным построением SAN’а (Storage Access Network, всё имеющееся дисковое пространство), умным разбиением оного на LUN’ы (Logical Unit Number, логическая группа физических дисков), объединение LUN’ов в соответствующие задачам RAID’ы, распределением по ним файлов базы данных и т.д, что позволяет добиться отличных результатов за счёт параллельной работы дискового массива;

2) Настройкой и распределением самой базы данных SQL Server.

Сразу скажу, что 1 и 2 не исключают друг друга, наоборот, гармонично дополняют и всячески сопутсвуют.
Далее я буду говорить только о втором пункте, потому как первое – это работа администратора, в этом я не силён. Кроме того, часто бывает так, что конфигурацию “железа” особо выбирать не приходится (либо уже настроено на максимум), а производительность повысить надо.
В любом случае, не пользоваться возможностями SQL Server’а по оптимизации смысла нет.

Для оченки производительности именно в разрезе IO я перед каждым запросом делаю DBCC DROPCLEANBUFFERS, чтобы очистить clean buffers, которые в SQL Server являются кешем прочитанных с диска страниц в памяти.

Для начала – краткое и простое (без углублений) пояснение того, откуда и как вообще берётся нагрузка на диски.

SQL Server читает с диска данные так называемыми “страницами” по 8 килобайт. На каждой такой странице находится какое-то количество строк. Столько, сколько может уместиться на 8-килобайтовой странице. Соответственно, даже если требуется “выдать” всего одну строку, SQL Server прочитает с диска минимум страницу.
С другой стороны, чем больше строк “умещается” на страницу, тем меньшее количество раз SQL Server’у нужно обратиться к диску для получения результата.
Здесь мы приходим к важному:

• Размер имеет значение.

Часто работая с кодом, мы не придаём значения размеру типов переменных: long, int, byte – какая разница. В большинстве случаев это действительно так – разницы никакой. Но в случае с SQL Server всё иначе, здесь размер действительно имеет значение. Ибо, скажем, bigint занимает места в 2 раза больше, чем int, соответственно, данных с колонкой типа bigint на страницу поместится в два раза меньше, чем данных с колонкой типа int, что означает, что для чтения одного и того же набора данных SQL Server’у потребуется прочитать в 2 раза больше страниц. То есть – мы имеем в 2 раза большую нагрузку на диски (IO).
Так и с другими типами данных. Именно поэтому SQL Server и имеет всякие разные настройки точности типов, вроде datetime2(4) и т.д.
Это не значит, что надо бросаться переделывать все bigint на int, это значит, что в данном случае о размерности типов надо думать.

Говоря об IO, в общем-то, вся задача сводится к одному простому вопросу: как заставить SQL Server читать с диска меньше страниц (про “читать быстрее”, повторюсь, речь не идёт)?

Про “умещать на страницу больше данных” с помощью “правильного” выбора типов колонок я уже сказал, однако SQL Server (начиная с 2005 и в Enterprise версии, насколько я помню)  предлагает и ещё один вариант:

• Компрессия данных.

Существует два вида компрессии: Page (на уровне страниц) и Row (на уровне строк). Они друг от друга сильно отличаются.

Row-компрессия представляет собой “классический” вариант сжатия с помощью архивирования. Проще говоря, данные в строке сжимаются с помощью deflate (тут не уверен, кстати, может и не им), соответственно, в сжатом виде на 8-килобайтовую страницу “влезает” больше строк.

Page-компрессия представляет собой некий “справочник” в заголовке страницы, в котором записывается, что такое-то значение встречается в таком-то столбце столько-то раз. Соответственно, само повторяющееся значение сохраняется всего один раз вместо того, чтобы хранить его в каждой ячейке. Никакого архивирования при этом не происходит, но, опять же,  за счёт того, что не надо хранить повторяющиеся значения, на страницу умещается больше данных.

Забавно, но во всех случаях, которые я видел, Page-компрессия “сжимала” данные лучше, чем Row-компрессия. Хотя я, признаться, ожидал обратного. Коэфициент сжатия сильно зависит от самих данных, конечно. У меня получалось по-разному: бывало и 25-30%, а бывало и 70%…
Попробовать и оценить можно прямо из Management Studio – когда делаете компрессию там есть кнопочка “посчитать”. Или можно просто системной процедурой воспользоваться, названия не помню (дома я).

Уместить на страницу “да побольше, побольше” – это половина решения. Вторая половина заключается в том, что и как туда умещать.
Ну вот представьте, мы выбираем из таблицы Orders все записи, сделанные для компании VPupkin LTD. SQL Server читает, он читает постранично, он всегда так делает. На первой (подходящей, допустим сервер знает какие страницы надо читать и где есть интересующая нас информация) странице у нас одна нужная запись, на второй – ещё одна, на четвёртой – две и ещё одна на шестой. SQL Server вынужден прочитать с диска страницы 1,2,4 и 6. И совершенно не важно, что на каждой из этих страниц ещё по 196 записей, которые к нашему запросу отношения не имеют.
Отсюда вопрос: как бы сделать так, чтобы интересующие нас записи располагались “рядышком”, а не “расползались” по всему файлу данных?
И на это есть хороший ответ:

• Создание разделов (партиций)

По-моему эта штука доступна тоже только в Enterprise-версии, но я не уверен.
Смысл её состоит в том, что для какой-либо таблицы мы можем задать несколько разделов (партиций, я буду писать “партиций”, не привык я к “разделам”) и указать критерий, по которому SQL Server будет решать, в какую именно партицию попадёт запись.

То есть, буквально, мы просто говорим SQL Server’у: все Orders для компаний с ID от 1 до 100 – в эту партицию, а от 100-200 – вон в ту. Таким образом мы создаём некую физическую группировку данных, то есть, при наличии, скажем, 10 партиций заказы одной компании будут находиться в одной пратиции => “раскиданы” по 1/10 таблицы, а не по всей, => в 10 раз увеличивается плотность заказов одной компании => меньшее количество страниц нужно будет считать с диска.

К тому же SQL Server достаточно умён для того, чтобы понять: раз все записи для указанной компании находятся на одной партиции, то во все другие вообще смотреть не надо, в запросе учавствует только кусок таблицы. В 10 раз меньше данных.
Ну и существует синтаксис для того, чтобы явно обратиться к какой-то одной партиции и сделать запрос только к её данным, существует возможность для определения, в какой именно партиции лежат интересующие данные, если мы хотим обратиться к ней явно и т.д. и т.п.

При всём вышесказанном, таблица продолжает оставаться целой таблицей во всех её проявлениях, “извне” ничего не меняется, что просто замечательно.

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

Ещё одна замечательная открывающаяся возможность – это паралеллизм. SQL Server, выполняя запрос, в котором учавствует такая “разбитая” таблица, оптимизирует работу, выполняя запрос к каждой партиции параллельно. Это очень существенно уменьшает время выполнения запроса, особенно на scan’ах. Даже если файловые группы партиций находятся на одном физическом диске, видимо, за счёт “группировки” на страницах и этого самого паралеллизма.
В цифрах: у меня есть запрос, выполняющийся за 1 минуту 09 секунд. После разбивки таблицы (для теста, на глазок) всего на 5 партиций, находящихся на одном физическом диске (том же, другого у меня нет), время выполнения запроса сократилось до 40 секунд.

Максимальное число партиций – 1000 на одну таблицу. По словам человека из Microsoft – цифра взята “с потолка”, надо же было какое-то ограничение придумать :)

Теперь об “побочных эффектах”.

В смысле разбиения по партициям – никакого отрицательного побочного эффекта, в общем-то, и нет. Есть ещё несколько интересных возможностей, такие, как staging-таблицы, например, но я и не работал с этим, да и не по теме несколько.

В контексте компрессии overhead есть – это +3-4% CPU на упаковку/распаковку. Впрочем, как показывает опыт, SQL Server достаточно редко “ест” CPU так, что не найти “лишних” 4%, а уж если мы уткнулись в проблемы с IO – то уж CPU-то гарантировано свободен.
И ещё. К сожалению, ни один из видов компрессии не получится использовать на таблицах, в которых есть sparse columns.

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

Проблемы эти таковы:

1. При изменении объекта базы данных (процедуры, функции, триггера и т.д.) разработчик создаёт скрипт и помещает его в репозиторий. Всё бы хорошо, но несколько разработчиков могут написать свои скрипты (пофиксить что-то в процедуре, скажем), положить в репозиторий независимо.. И “победит” последний. Причём узнаётся об этом обычно в самый ответственный момент: на этапе тестирования (если не позже). Проявляется баг, разработчик недоумевает “я же пофиксил его, вот и скрипт есть!”. А то, что одни-двумя изменениями дальше идёт совершенно иная копия – как же это проверишь.
   
2. Вышеописанная проблема многократно усугубляется в случае использования веток (бранчей).
   Так, как в разных бранчах делаются разные куски функциональности, то, во-первых, достаточно сложно поддерживать “сквозную” систему нумерации. Либо при каждом слиянии бранчей нужно как-то “перенумеровывать” скрипты, но тоже никогда не скажешь, какой именно должен быть порядок в итоге.
   Кроме того – та же проблема: в одном из бранчей поменяли процедуру, в другом – тоже. Скрипты перенумеровали, залили в репозиторий – а не работает.
   При этом очень сложно сказать, какая же версия была “в оригинале” (базовая), какая являет собой изменение в одном бранче, какая – в другом. Это же всё разные файлы, да и их ещё поискать.

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

На замену пришёл новый Database Project для Visual Studio 2008: http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Его нужно скачать, установить – и вроде как все проблемы решены :)

Краткое описание того, что мы имеем, используя эту штуку.

Все объекты БД представлены в виде скриптов.

На этапе инициализации проекта ему нужно указать БД, из которой он “заскриптует” все элементы схемы: таблицы, представления, процедуры, очереди, индексы, ключи, роли.. всё-всё-всё, даже CLR-сборки, если таковые имеются.
В результате разработчик получает дерево, по своей структуре очень похожее на то, что мы привыкли видеть в Management Studio, но все элементы – в виде create-скриптов.

Проект “умеет” отслеживать ошибки на этапе “компиляции”.

Это очень удобно! Например, если мы попытаемся создать скрипт для индекса по несуществующей колонке, создать view для несуществущей таблицы или что-то ещё “этакое” (да просто допустим синтаксическую ошибку или опечатку), то мы увидим ошибку “компилятора” (так же, как мы видим в случае ошибок в коде). Понятное дело, что пока в проекте есть ошибки, изменения в БД применяться не будут.
Что характерно, проект разбирает скрипты и зависимости между ними самостоятельно, а не просто пытается выполнить SQL-код и перехватывает ошибки.
Так с предупрежденями.

Возможности рефакторинга.

Если честно – много не пробовал. Но оно там есть. Например, переименование таблицы или столбца. Просто кликаем правой кнопкой мыши на элементе в дереве, выбираем Refactor –> Rename – и готово. Будет переименовано везде.

Использование макро-переменных.

Скрипты – они, конечно, обычные SQL-скрипты, но в них можно (если захочется) использовать макро-переменные в виде $(DatabaseName). Значения этим переменным можно задать в конфигурации проекта, далее эти макросы будут автоматически подменены там, где они встречаются.
Зачем? Ну, я не знаю :) То же имя базы данных (или linked-сервера). Тот же логин пользователя. Словом, то, что может отличаться от среды к среде (у разработчиков – одно, в продакшн – другое).

Ещё много всякого…

…в виде определения изменений между проектом и базой данных, возможностями юнит-тестирования и т.д.

Как это работает.

Процесс примерно такой:

1. Мы говорим проекту “Deploy”.
2. Происходит Rebuild проекта. На этом этапе делаются все проверки на ошибки, предупреждения и т.д. В том случае, если ошибок нет, идём дальше.
3. Происходит сравнение схемы базы данных, в которую мы делаем deploy cо схемой, которая присутствует в нашем проекте в виде кучи create-скриптов. По результатам этого сравнения генерируется дельта-скрипт, который содержит уже всевозможные alter, delete, create и т.д.
   Надо сказать, что дельта эта генерируется очень качественно. Мы тестировали на достаточно сложных комбинациях – всё проходило “на ура”. Проблем не было ни разу.
4. Дельта-скрипт применяется в базу данных.

Кстати, применять дельта-скрипт вовсе не обязательно, можно “сказать” проекту, что этот скрипт нужно просто сгенерировать.
Вообще настроек у проекта достаточно много: ANSI-ключи, нужно ли удалять объект, если он пропал из схемы проекта, нужно ли делать бекап перед применением дельта-скрипта и т.д, проще посмотреть.

Да, это всё DDL.
Что касается DML: тут всё совсем примитивно просто: есть два раздела: PreDeployment Scripts и PostDeployment Scripts. Туда (обычно в Post-, разумеется) и предполагается складывать скрипты для DML. Единственное, за чем надо следить – так это за тем, чтобы скрипты проверяли, нужно ли делать то, что они хотят делать. Ибо все они будут исполняться каждый раз при обновлении БД из проекта. Но это, как и понятно, сделать очень и очень нетрудно и вообще best practices :)

Автоматическое развёртывание.

Поскольку всё делается через msbuild, все targets уже на месте, то нет проблем ни с каким билд-сервером, ни даже без него (msbuild можно и из командной строки запустить). Словом, всё точно так же, как и с любым “кодовым” проектом - “на ура” автоматизируется ночной билд-деплой, например.

Плюсы.

Для нас – очень существенные. Судите сами: каждый объект -  это просто текстовый sql-файл. Как и любой другой файл с кодом, он отлично “уживается” в системе контроля версий, если два или более разработчика правят один файл – они будут иметь нормальный “человеческий” конфликт при чекине (или при слиянии веток) на этом файле и будут иметь все возможности грамотно этот конфликт разрешить.

Кроме того, правка таблиц, например, становится гораздо более лёгким делом, так как не приходится задумываться об ALTER TABLE, проверке на наличие (дабы не свалиться с исключением при попытке добавить то, что почему-то уже существует) и т.д. Нужен новый столбец: просто дописал его в CREATE-скрипт для таблицы – и всё. Остальное проект сделает за тебя.

Минусы.

У нас база данных – большая. Поэтому сравнение идёт достаточно долго. Долго – это где-то от 5 до 10 минут.
Хотя тут тоже не всё понятно. Сравение идёт долго на UAT-сервере, а вот локально, на той же БД (backup/restore) – одна минута пятнадцать секунд на весь деплоймент. Так что тут нужно ещё смотреть, нет ли у нас каких-то инфраструктурных затыков.

Пока это единственный минус, который я нашёл. Хотя для нас он не очень-то и существенен, даже если с инфраструктурой не разбираться. Ну, будет ночью билд-деплой длиться не 15 минут, а 25. Ну и кому какая разница, все спят :)

Словом, будем теперь так жить.

Обещал про книжки: что посоветовал человек из Майкрософта.

Посоветовал он следующее:

Сказал, что еще есть такая хорошая книжка, но мы не найдем в ней ничего нового для себя:

Извиняюсь, забывал раньше.

P.S. На амазоне всё есть ;)

P.P.S. Книги на английском языке. Не знаю, может быть есть перевод на русский. И не надо мне спамить в комментах псевдо-вопросами с надеждой оставить ссылочку на очередные пилюли ;)

По поводу той базы данных, где таблица из тысячи колонок. Приходил полевой инженер по SQL Server из Майкрософт. Полтора дня работали с ним плотненько над этой проблемой. В результате наоптимизировали: та выборка, которая занимала 1 минуту 23 секунды сейчас выполняется за 800 милисекунд. Остальные, требовавшие раньше в районе секунды, делаются за 1-2 милисекунды.
Very impressed, что называется!

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

Ну и, конечно, на таком уровне разбираться в SQL Server – надо много поучиться. Человек просто смотрит на запрос – и говорит: “тут у тебя индекс работать не будет, как ни строй, потому, что при таком объеме выборки количество уровней будет где-то 4, а значит ему нужно просканировать столько-то страниц. В любом случае это быстрее, чем делать полный индекс скан”. Или “вот тут у нас join с ограничивающим условием, это условие только вредит, так как в этом случае сервер сформирует суперсет, исключит то, что там в условии, но потом вынужден будет вернуть данные для построения окончательной выборки и выбросить их снова, двойная работа”. Или “тут индекс, конечно, используется, но вот тут используется еще один, а значит ему надо делать два поиска по индексам, поэтому план будет неинтересным, построим другой индекс”, строит – и план становится действительно куда более приятным.

Посоветовал пару книжек почитать.
Рассказал, как работается в Майкрософт. Интересно. Прессинг, говорит, существенный, но не со стороны менеджеров, а со стороны самого себя. Потому, что работая “в поле” сталкиваешься с очень разнообразными задачами и приходится постоянно читать тонны документации, общей и внутренней. Со стороны руководства, говорит, в МС не принято никакого давления. Типа, допустим, я не смогу решить вашу проблему за два дня. Дам рекомендации, уйду. Со стороны руководства это будет означать, что для решения проблемы двух дней просто мало. Никто никогда не скажет: “эх ты”, не отругает за “невыполнение” и т.д.
И это еще больше увеличивает self-прессинг. Прихожу, говорит, иногда домой после рабочего дня со сложной проблемой – и еще часов до 10 вечера, а то и дольше, ковыряюсь, разбираюсь – как же так и что же не так.
Еще интереснее: поскольку “полевых” инженеров не хватает на всех желающих, они могут сами решать, к какому клиенту отправиться и сколько времени они могут потратить на этого клиента. Нет такого, чтобы пришел начальник и сказал: “завтра ты весь день там-то, послезавтра полдня тут и полдня в офисе, на понедельник я тебе позже скажу”.

Интересно. Хотя я читал, что в МС подобного рода само-прессинг – явление распространённое. Потом привыкается, говорят.

Завтра иду на ReMix Australia. Даже в качестве делегата :) Будем представлять достаточно большую линейку продуктов. Должно быть интересно – я даже не все видел :)

P.S. А книжки почитать надо.

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

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

В таблице этой примерно 1000 (одна тысяча) колонок. В колонках – данные, одна запись в таблице – одно сообщение со всеми-всеми своими атрибутами (коих может быть немерено). Вроде так же делает Sharepoint (не проверял).

Колонки, в целях экономии дискового пространства, почти все (кроме основных: ID, Name, etc) - sparse (это SQL Server 2008 у нас).

Вопрос:

Запросы типа SELECT [Id], [Name], [nVarchar1], [Integer1], [XmlData1], [XmlData3] FROM [Message].[Messages] WHERE TypeID = 3765

приводят к Table Scan. Возможно по причине sparse-колонок, я не уверен. Это происходит даже при наличии индекса на TypeId. Сервер предпочитает не использовать этот индекс. Если же его заставить, то время исполнения запроса только увеличивается.
Понятное дело, что это дело все неприемлемо медленно. Понятное дело, что всё это еще медленнее, если в WHERE появляется что-то более интересное.
Понятно, что при просмотре execution plan оно предлагает построить индекс по TypeId и включить в него все колонки, учавствующие в выборке. Но так, как их тысяча…
Нет, я даже попробовал. Ради интереса. Индекс (некластерный) строился час с лишним. Построился. На времени выборки это практически не сказалось, а вот время апдейта какого-нибудь nVarChar1 для одной строки выросло до 5 секунд! А было мгновенно. И должно быть быстро. Так что такие вот огромные индексы – тоже не панацея.

Может у кого-то есть какие-то идеи или опыт по работе с большими таблицами? Или на предмет как это можно реструктурировать, чтобы было поэффективнее, а?