Большое количество проблем, связанных с производительностью 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.

Купил термометр, читаю гарантию:

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

Как в том анекдоте: гарантия пожизненная, вещь “умерла” – значит гарантия кончилась.

P.S. Для тех, кто не читает по-английски и невнимательных: в англоязычной версии обещается пожизненная гарантия, в русскоязычной – двухгодовая.

Намедни тестируя приложение на большом количестве данных наткнулся на кусок кода, смысл которого был прост и привычен: выбрать все элементы коллекции А, идентификаторы которых присутствуют в коллекции Б.

Код выглядел примерно так:

//items is a List<MyItem>
//itemIds is a List<long>

var resultItems = items.Where(x => itemIds.Contains(x.Id));

Можно было делать join, но он в итоге всё равно в нечно подобное выражается, поэтому разницы нет.
Время выполнения этой штуки – 6.7 секунды. Да, там полно элементов, что-то десятки тысяч, но всё же – почти 7 секунд – это неприемлемо долго.

Оптимизировалось всё это дело введением одной строчки кода:

var hashedIds = new HashSet<long>(itemIds); //и дальше вместо itemIds используем hashedIds

В общем-то оно и понятно, что хэш в подобных вещах быстрее.

Время выполнения вот этой строчки (создание хэш-таблицы) – порядка 100 милисекунд.
Время выполнения выборки – порядка 40 милисекунд.

Итого – 150 милисекунд вместо 6.7 секунд. Неплохо :)

P.S. Поковыряйтесь в профайлером – много интересного найдёте. Я нашёл :)

Я уже затрагивал тему пляжей и купаний в одном из предыдущих постингов, когда рассказывал о том, как попал в рип, но это было ещё на территории Нового Южного Уэльса (NSW), так что не считается.

Практически сразу после прибытия в Редклиф мы посетили местный пляж с, как нам было сказано, учебно-тренировочными волнами. Волны там действительно небольшие, где-то в районе полуметра всего, но детям на бодибордах кататься в самый раз.
Пляж оказался большим, приятным и тёплым настолько, чтобы было не холодно, но освежало. Что для меня лично очень большой плюс, ибо в менее благоприятных условиях я могу ну очень долго заходить в воду.

Посмотрев, как мы потренировались и поучились, ребята решили отвезти нас на Sunshine Coast. Это по-русски будет, наверное, Побережье Сияющего Солнца. Ещё, если проявить фантазию и чувство юмора, то можно перевести как Весёлое или Радостное побережье.

Да, в наши дни всё действительно радужно. Но вот прошлое и у этого светлого клочка земли с кучей километров прекрасных пляжей было не слишком весёлым.
Я уже упоминал о том, что на территории нынешнего Квинсленда коротали свои дни, если так можно выразиться, дважды ссыльные. Так вот и они убегали. И прятались на территории нынешнего Sunchine Coast’а. Жили там с аборигенами, лишь бы свои не нашли.
Некоторые из баталий так называемых “Чёрных Войн” – разборок между белыми и аборигенами – тоже происходили там же. Все знают, кто и как победил.
Ну, а с середины 1960-х там и началось то, что мы имеем там сейчас – пляжно-курортная направленность.

Волны на Sunshine Coast’е уже не были учебно-тренировочными и неплохо возили нас на себе, когда удавалось их оседлать. Поначалу не слишком получалось, потом стало получаться лучше. Из воды выходил всего пару раз – передохнуть, ибо оказалось, что в основном силы тратятся на то, чтобы после того, как “приехал” к берегу, обратно зайти в воду, борясь с волнами.

Следующим пляжным этапом для нас стал Surfers Paradise (сёрферс пэрэдайз, “рай сёрферов”), центральное и самое культовое место одного из самых известных курортов Австралии Gold Coast (золотое побережье).

История этого места не столь драмматична: раньше там жил немецкий фермер и выращивал сахарный тросник. Там так принято – выращивать сахарный тросник. Его там полно, даже больше, чем банановых ферм, коих с годами становится всё меньше. А ещё, говорят, если ехать поездом на юг, от Брисбена до Кернса, то сахарный тросник – это всё, что будет видно из окон на протяжении всей дороги. А это полторы тысячи километров с гаком.
Так вот. Фермер этот даже не был заключённым (да, и нормальные люди в Австралию тоже приезжали!).
Однако, фермерство фермертвом, а немец достаточно быстро понял, что к чему, продал ферму и построил отель. Так и повелось – там теперь полно отелей.

Из названия можно понять, что волны там отличные. Высокие, метра два точно есть. И сёрферы, действительно, висят вдали, много.
И флажки – где можно плавать и спасатели спасут, если что. И там значки ещё, знаете, такие: в зоне флажков на сёрфах не кататься. Ну, чтобы людей не задавить, видимо. Назначение синих и красных флажков до поры оставалось загадкой…

Так вот, посмотрели мы на эти знаки – и решили, что с бодибордами, наверное, тоже нельзя. Ушли в сторону от флажков, катаемся себе, волны ловим.
Выносит меня волной на берег, я беру доску, плыву от берега… Смотрю – Ленка и Алексей в воде головой к берегу на досках  месте анвисят, руками перебирают… Здорово, думаю, волну ловить научились, как специалисты висят! Ну, я на доску – и тоже туда, подальше. Тоже, стало быть, волну ловить. Пока доплыл, поворачиваюсь, смотрю – у берега стоит спасатель, а Ленка нам кричит: “Выходите! Спасатель сказал, что здесь рип!”. И резво так ловит волну и выезжает на берег, на самый песок.

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

Вылезли, спасатель сказал, что поскольку у нас нет ласт, в этом месте купаться не стоит – из сильного рипа выплыть тяжело. Дальше купались уже в пределах флажков, с бодибордами там, оказывается, можно. И там, оказывается, была табличка, на которой всё подробно и доступно написано: “сильный рип за флажками, не заходите глубоко, очень нестабильные условия, следите за детьми.”

И там как-то сильно легче даже, без течения-то! Ну, в зоне флажков.
На берег выходил за всё время два раза – попить воды из бутылки – и обратно. То есть, считай, что и не выходил. Купались доооолго. Часа 4, наверное, может 5.

А потом пошли фотографировать Gold Coast с самого высокого жилого здания – там сверху круговая смотровая площадка (первая фотография сделана днём, но уж очень понравилось и захотелось забраться туда ночью).

Оставшееся небольшое время уделили прогулке по туристическим улицам Surfers Paradise…

P.S. Раз уж зашёл разговор о песках и побережье, расскажу забавную историю, которую мне недавно рассказали. Про австраллийский песок.

Однажды, какое-то количество лет назад, один английский инженер в азии строил что-то масштабное, я не помню что, да это и не важно. И нужно ему было вести строительство в воде. И тогда он решил, что в качестве базиса можно насыпать песка (в принципе решение, как оказалось, верное), пустыня-то рядом. Затратили кучу денег и сил, привезли из пустыни песка, высыпали его… И тут оказалось, что песок этот пустынный – он “не такой”, он просто размылся и растворился в воде.

Ну, раз имеющийся песок не подходит, то нужно же где-то взять подходящего. Подходящий нашёлся на южном побережье Австралии. Местный песок, как говорят, compacts in the water, то есть, становится плотным и оседает на дно.
Закупили тогда песка, перевезли его. Затратили 9 миллиардов денег, если мне память не изменяет.

И тут понеслось. Песок стал популярным, его стали закупать.

Правительство тогда, чтобы сохранить побережье, ввело жёсткий запрет на экспорт австраллийского песка. И теперь его вывозить нельзя.

Но это только присказка. Сказка – вот она.

Однажды в Бразилии проводился чемпионат по пляжному волейболу. И организаторам и спортсменам не нравился местный песок. Тогда они взяли песка в Австралии. Тут, в Сиднее, на Бондае. Отвезли его в Бразилию, провели чемпионат… И выслали обратно.
Потому, что купить австраллийский песок нельзя, а взять в рент – можно.
Вот, вернули песок обратно на Бондай.

По-моему – весёлая история.

Не смотря на то, что на территорию австраллийского штата Квинсленд (Queensland) ещё десятки тысяч лет назад открыли всякие островитяне, а потом, гораздо позже, туда добирались французские, голлансдкие, португальские мореплаватели, всем известно, что Квинсленд открыл Джеймс Кук в 1770-м году.

Тогда, в 1770-м, Джеймс, сидя у себя в Дарлинг Харбор (в Сиднее), подумал, что неплохо было бы записать на своё имя ещё что-нибудь, и поплыл на север. Вот он плыл, плыл, и приплыл… нет, не в Брисбен, ибо Брисбен стоит на достаточном удалении от побережья и туда он приплыть не мог. Он приплыл в Редклиф, это теперь минут 30 на машине до Брисбена.

То, что “открыл” Кук – это, я полагаю, в те времена была достаточно суровая земля, но англичане быстро сообразили, как её использовать. И применив уже имеющиеся у них навыки, стали ссылать туда заключённых. Но, поскольку земля-то суровая, то не просто заключённых, а рецидивистов, которые умудрились провиниться уже будучи в ссылке в Новом Южном Уэльсе. Такая вот получилась двухэтапная система. Проштрафился в Англии – тебя ссылают в Новый Южрый Уэльс, проштрафился и там – дальше на север, в коллонию “Моретон Бэй”, так раньше это называлось.

В 1859-м году королева Виктория официально отделила эти земли от Нового Южного Уэльса, новая территория получила название в её честь – Квинсленд (Queensland, земля королевы).

По следам Кука мы в первым делом приехали в Редклиф. Ну ещё потому, что там живут добрые люди, которые любезно согласились принять нас и организовали отличную культурно-развлекательную программу, за что им огромное спасибо :)

Юг Квинсленда – та местность, где мы были – это субтропики, поэтому природа и растительность существенно отличается от того, что есть у нас в Сиднее и тем более от того, что мы видели в Виктории на пути в Мельбурн. В Квинсленде всё как-то живее, чтоли. Разнообразнее.

Взять, например, дождевые леса. Здесь у нас тоже есть, да, но в Квинсленде это как-то по-настоящему. Сказано – дождевые леса. Приезжаешь – и вот тебе лес, вот тебе дождь. Всё по-честному. Очень красиво.

Или вот, горы. Если долго ехать по дороге вверх на гору (удивляясь, что вдоль этой дороги на этой горе кто-то живёт – что они там делают?!), то можно приехать к Лучшей Из Площадок Для Обзора (она так и называется – The Best Of All Lookout).
Мы ехали туда уже после захода солнца, в темноте и в тумане. Хотя, может это и не туман был, а низкие облака.

Противотуманных фар у нас нет, так что видно было плохо, хорошо ещё, что успели разглядеть перебегающего дорогу маленького кенгуру. Ехали мы, ехали, и уже решили, что где-то что-то проскочили, потому как не видно же ничего. А тут как раз смотрим – какое-то место, где можно поставить машину. Решили – встанем и позвоним ребятам, которые тоже уже сзади давно не едут, чтобы направили куда надо. Только встали, как они подъехали. Оказывается – это самое оно и есть, а дорога дальше не идёт и надо идти 350 метров пешком через лес.
И вот представьте – темно, к тому же туман, верхушка горы, лес и узкая тропинка. Хорошо, что я лампу взял ту, которая для палатки покупалась.

Зрелище, на этой площадке, конечно, неповторимое. Высоко-высоко, на самой верхушке горы мы стоим на площадке и смотрим вдаль… А там – ничего нет. Совсем. Потому, как темно и толи туман, толи облаком накрыло. Вспоминается фильм “Лангольеры”. Вот именно так же – просто сзади тёмный лес и гора, а впереди – ничего, белая молочная пустота. И я стою с этой лампочкой на этой площадке, в темноте, и остальные – кто с буттербродом, кто с чашкой чая из термоса… Незабываемая картинка.
А говорят, что когда нет этого молочного тумана или облака, то можно увидеть соседние горы, дорогу внизу, склоны, покрытые лесами, воду и светящуюся полоску Голд Коста вдали. Но я рад, что увидел именно этот туман и это облако.

Ещё в южном Квинсленде живут уникальные светящиеся червяки. Которые, по давней австраллийской традиции, на самом деле никакие не червяки, как коала – не медведь а вобмат – не свинья. Но мы не будем придираться, мы не местные. Glow Worms. Worms – так worms, чего нам.

Червяков этих можно увидеть только ночью (а фотографировать и вовсе нельзя, чтобы не пугать их подсветкой автофокуса) и светятся они только когда голодные. Своим свечением они приманивают насекомых, которыми и питаются. Съесть они могут только мелких насекомых, таких, как например комары, а крупные твари им не по зубам, да ещё и навердить могут – попортят светилку. Поэтому червяки договорились с пауками. Пауки на деревьях плетут свои сети вокруг колоний светящихся червяков, в эти сети и попадает добыча покрупнее комара.
Так же, как живущие здесь же светящиеся грибы договорились со змеями – те ловят и жрут всех, кто может навредить этим грибам…

Столлман побил Балмера с его “developers, developers, developers”. Думается, навсегда. Это просто слов нет. У него даже вид как у того мужика из Аум-Сенрикё.