Настройка производительности SQL СЕРВЕРА для разработчиков приложений на SQL Server

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

Если вы очень долго были администратором базы данных или разработчиком SQL Server приложений, тогда вы вероятно натыкались на некоторые медленные SQL Server приложения. И, когда это случается, люди начинают винить разные вещи в проблемах производительности - сеть, аппаратные средства сервера, SQL сервер, пользователи, база данных. И это продолжается снова и снова, но, к сожалению, это не исправит медленные приложения. Лекарством для большинства медленных SQL Server приложений является профилактика, которая включает в себя тщательный анализ потребностей пользователей, продуманный дизайн, оптимальное кодирование и соответствующее выполнение. Для любого приложения, основанного на SQL server или любого другого, универсальность и производительность должны быть учтены с самого начала. Как только приложение выпущено в эксплуатацию, очень трудно решить большинство проблем масштабируемости и производительности.

Из этой статьи вы узнаете основы дизайна, кодирования и исполнения масштабируемых и производительных SQL Server приложений. Естественно, в статье не рассматриваются все возможные аспекты, т.к. это заняло бы целую книгу. Статья сфокусирована на изучении необходимого минимума, который вы должны знать, чтобы создавать масштабируемые и производительные SQL Server приложения. Вот что вы узнаете:

• Что каждый разработчик и администратор баз данных должен знать о настройке производительности SQL сервера
• Как оптимизировать аппаратные средства вашего сервера
• Как оптимизировать параметры настройки конфигурации SQL сервера
• Как оптимизировать дизайн вашего приложения
• Как оптимизировать структуру вашей базы данных
• Как оптимизировать ваш прикладной код для SQL сервера
• Как оптимизировать ваш Transact-SQL код
• Как построить индексы для оптимальной производительности базы данных
• Как получить выигрыш от использования SQL Server Performance Tuning Tools

По крайней мере, если вы воспользуетесь советами и информацией из этой статьи, вы найдете, что настойка SQL server приложений - не такая страшная и загадочная тайна, как могло показаться сначала. Так что приступим к работе.

Что каждый разработчик должен знать о настройке производительности SQL сервера

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

Настройка производительности - не технология

Настройка производительности SQL сервера - больше искусство, чем наука (технология). Я уверен, что вам это не понравится, но это факт. Мне жаль, что я не могу рассказать точно и по шагам, как заставит ваши приложения работать гибко и производительно. Проблема в том, что, как вы вероятно уже знаете, что современное приложение зависит от очень большого числа факторов. К сожалению, как бы вы не пытались, вы не сможете иметь полный контроль над вашим приложением и средой, в которой оно выполняется. Вот некоторые (не все) из факторов, влияющих на производительность ваших приложений:

• SQL server(сам сервер);
• Параметры настройки SQL server'а;
• Код Transact-SQL приложения;
• Код non-Transact-SQL приложения;
• Структура базы данных;
• Операционная система (сервер и клиент);
• Межпрограммные (middleware) средства (Microsoft Transaction Server, Microsoft Messaging Server);
• Аппаратные средства (сервер и клиент);
• Сетевые аппаратные средства и полоса пропускания (LAN и WAN);
• Число клиентов;
• Тип деятельности клиентов;
• Тип и количество данных, хранимых на SQL сервере;
• Является - ли приложение OLTP или OLAP приложением.

Хотя и невозможно контролировать все факторы, влияющие на гибкость и производительность, вы должны контролировать все, что в ваших силах. 

Тестирование в течение всех стадий развития вашего приложения

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

При проведении тестов, всегда подходите с научной точки зрения - тестируя только один параметр одновременно. Например, если вы подозреваете, что вы должны добавить индекс к таблице, чтобы увеличить производительность, но вы точно не знаете, какой именно, или какой тип индекса является наилучшим, экспериментируйте только с одним изменением одновременно, проверяя каждое изменение индивидуально, чтобы видеть, производит ли это результаты, которые вы ожидаете. Если вы изменяете более чем один параметр одновременно, вы не будете знать, которые изменения работают, а какие нет. Это правило действует для любых тестов, будь то добавление индексов, изменение конфигурации SQL сервера или проверка различных аппаратных конфигураций.
Всегда тестируйте в реальных условиях. Это означает использование "реальных" данных, проверка наибольших ожидаемых наборов данных (data sets), и использование аппаратных средств, подобных тем, которые будут использоваться, когда приложение будет запущено в работу. Если вы не будете этого делать, вы можете быть удивлены, что, в то время как ваше приложение работает хорошо для 10 активных пользователей в течение тестов, тогда как 500 пользователей создают большие проблемы. 

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

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

Настройка производительности SQL сервера - этому можно научиться
Навыкам настройки производительности SQL сервера нельзя овладеть, прочитав пособие за ночь. Практический опыт гораздо ценнее книжного знания в этой области. Но чтобы воспользоваться преимуществом опыта, который вы получите через какое-то время, также важно знать базовые технологии, которые влияют на ваше приложение.
Например, вы должны знать язык программирования, на котором написано ваше приложение, должны уметь проектировать базы данных и приложения, должны знать язык Transact-SQL, должны понимать, как SQL сервер хранит и индексирует данные, как работает железо сервера и сеть. Чем лучше вы владеете технологиями, лежащими в основе вашего приложения, тем проще вам будет находить и решать проблемы производительности. Учитесь всему, чему сможете.

Как оптимизировать аппаратные средства вашего сервера

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

Причина этого - то, что наиболее медленные приложения являются медленными из-за плохой структуры (дизайна), а не из-за слабых аппаратных средств. Часто аппаратные средства вызывают претензии, потому что проблемы производительности могут не проявлятьсебя до момента выпуска приложения. И так как дизайн приложения уже не может быть изменен, единственная вещь, которую вы можете сделать, это усилить железо, на котором будет выполняться приложение. Аппаратные средства могут помочь, но полностью решить проблемы с их помощью нельзя, и это причина, по которой к аппаратным средствам часто высказываются претензии. Иногда слабость железа может влиять на производительность, но чаще всего - нет.
Чтобы оградить ваше приложение от проблем производительности, связанных с аппаратными средствами (которые могут возникнуть, если hardware неправильно сконфигурировано и настроено), давайте бегло просмотрим типичные вопросы, возникающие при выборе и настройке аппаратного обеспечения.

• Выбор аппаратных средств
  Выбор оптимальных аппаратных средств для вашего SQL server приложения зависит от разнообразных факторов, например, размера базы данных, числа пользователей, режима использования базы данных (OLTP или OLAP), и других. Хотя и не существует точного рецепта для выбора аппаратного обеспечения сервера, вы можете получить определенное представление о нем, проводя тесты в самом начале разработки приложения. Ах, снова эти тесты! Да. Хотя многие опытные администраторы баз данных могут дать вам неплохую смету требуемого оборудования, только с помощью тестов вы сможете узнать, какое аппаратное обеспечение будет отвечать нуждам вашего приложения.
  
  Когда дело касается аппаратного обеспечения сервера, имейте в виду следующие вещи:
• Центральный процессор: Всегда приобретайте сервер с возможностью увеличения числа центральных процессоров. Например, если тесты говорят, что одного CPU вполне хватит, купите сервер с возможностью расширения хотя бы до двух центральных процессоров, даже если вы используете только один. Тот же самый принцип действует и для серверов с четырьмя и более процессорами. Всегда оставляйте возможность для роста.
• Память: Это, вероятно, наиболее значительная часть аппаратных средств, которые затрагивают производительность SQL сервера. В идеале, вся ваша база данных должна входить в оперативную память. К сожалению, это не всегда возможно. По крайней мере, постарайтесь, чтобы в ОЗУ входила самая большая таблица вашей БД, и если вы можете позволить себе это, купите столько памяти, сколько ваш сервер сможет поддерживать. Часто это 2GB или больше. НИКОГДА НЕ БЫВАЕТ СЛИШКОМ МНОГО ПАМЯТИ.
• Подсистема ввода - вывода: После оперативной памяти, подсистема ввода - вывода - наиболее важная часть аппаратных средств, которая затрагивает производительность SQL сервера. Минимальное решение - это аппаратный RAID-контроллер. И еще - лучше купить много дисков меньшего объема, чем несколько дисков большего объема. Чем больше дисков будет в системе, тем быстрее будут происходить операции ввода-вывода
• Сетевое соединение: Поставьте на сервер по крайней мере одну 100Mbs сетевую плату, которая будет связана с распределителем (switch). В идеале, поставьте две такие карты, и подключите их к распределителю (может быть, это HUB?), чтобы обмен происходил в дуплексном режиме. 

Настройка сервера
Даже наиболее дорогие аппаратные средства сервера не будут хорошо работать, если они соответствующим образом не настроены. Я сталкивался с большим числом проблем производительности, связанных с использованием оборудования, не совместимого с Windows NT. Некоторые типы таких проблем очень тяжело отследить и устранить. В идеале, удостоверьтесь, что все аппаратные средства (и сама NT) правильно установлены и сконфигурированы опытным техником. После этого проверьте, как будет работать ваше приложение в лабораторных условиях, прежде чем оно будет использоваться в реальной работе.

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

Для лучшей производительности, SQL Server должен быть единственным приложением, которое работает на сервере, исключая различные сервисные утилиты. Не экономьте несколько $USD, устанавливая на тот же сервер IIS или MTS. Мало того, что это снижает производительность SQL сервера, это еще усложняет настройку сервера и мешает решению возникающих проблем. 

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

SQL сервер обладает возможностью самонастройки. Что это значит? Это значит, что SQL сервер наблюдает за тем, что на нем выполняется, и автоматически делает внутренние корректировки, которые позволяют выполнять текущие задачи с максимальной производительностью на имеющемся оборудовании.

Когда вы выполняете различные тесты на SQL сервере, имейте в виду, что может потребоваться некоторое время, в течение которого сервер будет подстраиваться под ваши запросы. Другими словами, производительность, которую вы имеете в начале тестирования и производительность, которой достигает сервер через несколько часов работы с типичными задачами, может сильно отличаться. Проводите тесты после того, как сервер уже настроился на ваши запросы.
Есть 36 опций в конфигурации SQL server'а, которые могут быть изменены с помощью Enterprise Manager'а или хранимыми процедурами sp_configure (еще бы знать, что это означает - прим. переводчика :). Я не рекомендую производить какие-либо настройки SQL сервера, если у вас нет опыта его настройки. Как новичок, вы можете сделать какое-либо изменение, которое может ухудшить производительность, потому что, когда вы изменяете какую либо опцию, вы "жестко программируете" ее. SQL сервер может сам изменить эту установку "на лету", исходя из текущей нагрузки. Но когда вы "жестко программируете" опцию, вы частично уменьшаете способность SQL сервера к самонастройке.

Когда вы чувствуете, что изменение какой-либо опции или нескольких может улучшить производительность SQL сервера в ваших конкретных условиях, действуйте постепенно и осторожно. Прежде, чем вы сделаете какие-либо изменения в настройках, сначала исследуйте производительность SQL сервера при типичной загрузке с помощью Performance Monitor'а (будет обсуждаться далее). После этого делайте не больше одного изменения за один раз. Никогда не делайте больше одного изменения одновременно, так как, если вы сделаете это, вы не будете знать, какие изменения (если вообще какие - нибудь) повлияли на производительность.

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

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

Как оптимизировать дизайн вашего приложения
Если вы используете n-связный (n-tier) дизайн для вашего приложения, (а кто его не использует для крупномасштабных приложений в наше время?), SQL сервер - только одна часть большого приложения. При этом дизайн приложения больше влияет на производительность, чем сам SQL сервер (как, возможно, вы думали). К сожалению, SQL сервер обычно получает большее количество претензий из-за низкой производительности, даже если причиной является структура (дизайн) приложения. Что я хочу сделать, так это внести несколько предложений, которые могут помочь вам в вашем прикладном дизайне, избавляя SQL сервер от обвинений в низкой производительности. Давайте начнем.

Одним из первых шагов, которые вы должны решить при проектировании n-tier приложения, должен быть выбор логического и физического дизайна. Из этих двух видов дизайна приложения, физический дизайн - то место, где делается большинство ошибок, ведущих к потерям производительности. Так происходит потому, что теория, основанная на логическом дизайне, должна быть реализована в реальном мире. У вас появляется множество возможностей для выбора. И многие из этих путей не ведут к гибкости или высокой производительности. Например, вы хотите осуществить физическую двухзвенную реализацию (two-tier implementation) с толстыми клиентами, или с толстым сервером, или трехзвенное приложение, Internet приложение, или что-то еще? Как только вы решаете этот вопрос, тогда вы должны спросить себя, какой язык программирования будет использоваться, какой браузер, будете ли вы использовать Microsoft Transaction Server (MTS), и/или использовать Microsoft Message Queue Server (MSMQ), и т.д. и т.п.

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

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

• Для обработки данных используйте хранимые SQL процедуры. Избегайте действий над данными в презентационной и бизнес - логике.
• Не храните данные (из базы данных) в бизнес - логике. Храните данные в базе данных
• Не создавайте сложные и/или глубокие объектные иерархии. Создание и использование сложных классов или большого количества объектов, используемых для моделирования сложных правил или ситуаций поглощает много ресурсов и снижает производительность и гибкость приложения (прим переводчика: по-моему, объектные иерархии как раз предназначены для повышения гибкости приложения). Так происходит из-за того, что выделение и освобождение памяти при работе с объектами является ресурсоемким процессом.
• Пересмотрите проект так, чтобы использовать кеширование соединения с БД и кеширование объектов с помощью Microsoft Transaction Server (MTS). MTS кеширует и соединение с БД, и объекты, что сильно повышает общую производительность и масштабируемость приложения.
• Если ваше приложение использует SQL запросы, которые по своей природе выполняются долго, постарайтесь сделать так, чтобы ваше приложение выполняло такие запросы в асинхронном режиме. Запросы не должны дожидаться окончания окончания предыдущих запросов, чтобы начать выполняться самим. Один из способов обеспечить это в вашем n - звенном приложении - использование Microsoft Message Queue Server (MSMQ).

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

Как оптимизировать дизайн вашей базы данных

Подобно дизайну (структуре) приложения, структура базы данных очень сильно влияет на производительность и масштабируемость ваших SQL server приложений. И подобно дизайну приложения, если вы что-то не продумаете в самом начале проектирования, потом это будет исправить практически невозможно (по крайней мере, очень тяжело). Здесь приведены некоторые базовые сведения, которые стоит держать в голове, когда вы проектируете базу данных.

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

Одно из первых решений, которые вы должны будете принять - будет ли база данных использоваться для OLTP или OLAP приложений. Обратите внимание, что я сказал "или". Одна из самых больших ошибок, которые вы можете сделать при проектировании базы данных, состоит в том, чтобы попробовать совместить потребности обоих OLTP и OLAP типов приложения. Эти два типа приложений взаимоисключающие, если вы заинтересованы в конечной производительности проекта.

OLTP базы данных вообще сильно нормализуются, что помогает уменьшить количество данных, которые должны быть сохранены. Чем меньшее количество данных вы сохраняете, тем меньшее количество операций ввода - вывода будет выполнять SQL сервер, тем быстрее будет доступ к БД. Транзакции также делаются как можно более короткими, чтобы избежать блокировок. И в заключение, индексирование БД минимизируется, чтобы ускорить выполнение INSERT'ов, UPDATE'ов и операций удаления.
OLAP базы данных, с другой стороны, являются сильно денормализованными. Кроме того, транзакции не используются, т.к. база данных используется только для чтения, и блокировки просто невозможны. И конечно, очень сильно используется индексация, чтобы различные типы запросов выполнялись с максимальной скоростью.

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

• Когда вы выполняете процесс нормализации при проектировании ваших OLTP баз данных, ваша начальная цель - полностью нормализовать БД согласно трем общим принципам нормализации.
• Следующий шаг - некоторое предварительное тестирование, особенно если вы предвидите, что нужно будет объединять (JOIN) четыре или более таблиц одновременно. Убедитесь, что тестирование выполнялось на типовых реальных данных.
• Если тестирование дало приемлемые результаты, тогда вы можете не беспокоиться об объединения четырех и более таблиц в одном запросе. Но результаты тестирования не удовлетворительны, тогда Вам придется сделать выборочную денормализацию используемых таблиц, чтобы уменьшить число объединений используемых в запросе, и улучшить производительность.
• Гораздо проще захватить проблему в ранней стадии проекта базы данных, чем тогда, когда приложение уже готово. Денормализация таблиц в поздней стадии выполнения проекта практически невозможна. Одно предупреждение. Не делайти денормализации таблиц без исчерпывающего тестирования. Очень трудно вывести логически, как денормализация повлияет на производительность. 

Как оптимизировать ваш прикладной код для SQL сервера

В некоторой точке процесса развития проекта вы будете должны начать делать код работы вашего приложения с SQL сервером. К этому времени, проекты приложения и базы данных должны уже быть закончены и протестированы на предмет производительности, используя быстрые методы макетирования (rapid prototyping).

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

Так как я не знаю, каком языком программирования вы будете спользоваться, я далее предполагаю, что вы будете использовать объектную модель Microsoft ADO (Active Data Objects), для обращений к SQL серверу из приложения. Примеры, которые я использую, должны работать для большинства Visual Basic и ASP разработчиков. Так что давайте рассмотрим в деталях некоторые методы, которые помогут вам достичь высокой эффективности при доступе к данным SQL сервера. 

Используйте OLE DB, для обращений к SQL серверу
Вы можете обращаться к данным SQL сервера, используя ODBC или OLE DB. Какой конкретно метод вы используете, зависит от того, как вы определяете строку подключения (connection string) при использовании ADO, когда соединяетесь с SQL сервером. Для лучшей производительности всегда выбирайте OLE DB. Режим OLE DB является "родным" для SQL сервера, и это наиболее эффективный способ обратиться к любым данным SQL сервера.

Точно так же, при создании подключения ADO к SQL серверу, вы можете использовать DSN в строке подключения, или вы можете использовать DSN-less подключение. Для оптимальной производительности, используйте DSN-less соединение. При его использовании OLE DB драйвер не ищет connection string в реестре клиентской машины, на которой выполняется приложение, что дает небольшой выигрыш при установлении соединения. 

Инкапсулируйте ваш DML (Data Manipulation Language) в хранимых процедурах
ADO предоставляет Вам три различных способа для выполнения операций SELECT, INSERT, UPDATE, или DELETE над данными в базе данных SQL сервера. вы можете использовать методы ADO, вы можете использовать динамический SQL, или вы можете использовать хранимые процедуры. Давайте вкратце рассмотрим каждый из них.

Самый простой способ управлять данными от вашего приложения состоит в том, чтобы использовать различные методы ADO, типа rs.AddNew, rs.Update, или rs.Delete. Эти методы достаточно просты для понимания и использования, но они дают сильный проигрыш по производительности. Методы ADO часто создают медленные курсоры и генерируют большое количество сетевого трафика. Если ваше приложение очень маленькое, вы никогда заметите разницы. Но если ваше приложение оперирует большими объемами данных, производительность сильно упадет.

Другой способ управления данными SQL сервера с использованием ADO, состоит в том, чтобы использовать динамический SQL (также иногда упоминаемый как специальные запросы). Здесь вы посылаете запросы от ADO к SQL серверу в виде строк кода на Transact-SQL. Использование динамического SQL - вообще намного быстрее чем использование методов ADO, хотя это и не самый производительный метод. Когда SQL сервер получает динамический SQL от вашего ADO-based приложения, он компилирует код Transact-SQL, создает план выполнения запроса и только потом выполняет запрос. Компилирование кода и генерация плана выполнения занимает некоторое время . Но как только код Transact-SQL был откомпилирован и для него был создан план выполнения, этот план может использоваться многократно для похожих запросов, что экономит время на компиляцию и генерацию планов.

Для оптимальной производительности вы можете использовать ADO c хранимыми на сервере процедурами для всех манипуляций данными в вашем приложении. У хранимых процедур много преимуществ, например, они прекомпилируются и оптимизируются, так что этот шаг не повторяется каждый раз при вызове этих процедур. Когда хранимая процедура вызывается в первый раз, для нее создается и сохраняется в памяти SQL сервера план выполнения запроса, который используется многократно, что сохраняет большое количество времени. Другая выгода сохраненных процедур - то, что они помогают уменьшать сетевой трафик и задержки. Когда код вашего приложения ADO вызывает хранимую процедуру SQL сервера, он делает единственный сетевой запрос. Вся требуемая обработка данных выполняется на SQL сервере, где она выполняется наиболее эффективно, и, если потребуется, процедура возвратит результаты вашему приложению. Это сильно уменьшает сетевой трафик и увеличивает гибкость и производительность.

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

Когда вы используете ADO, чтобы выполнить хранимые процедуры на SQL сервере, у вас есть 2 способа сделать это. Вы можете использовать ADO, чтобы вызвать метод Refresh коллекции параметров (Parameters collection), чтобы сэкономить немного кода. ADO должна знать, какие параметры используются к сохраненной процедуре, и метод Refresh может сделать запрос к хранимой процедуре на SQL сервере, чтобы выяснить параметры. Но, как вы могли и ожидать, это производит дополнительный сетевой трафик и потери времени. И, хотя это и требует немного больше кодирования, более эффективный путь вызова хранимой процедуры это задание параметров явно в вашем коде. Это устраняет дополнительное потери времени, вызванные методом Refresh и ускоряет ваше приложение. 

Инкапсулируйте код ADO в COM компонентах
Как часть создания масштабируемых и оптимизированных n-звенных приложений, поместите код ADO, который обращается к данным SQL сервера в компоненты COM. Это будет работать и для Visual basic, и для для web - приложения на ASP. Это дает Вам все обычные преимущества компонентов COM, например, кеширование объекта с помощью MTS. Для ASP приложений это обеспечивает большую скорость, потому что код ADO в объектах COM уже откомпилирован, в отличие от кода ADO, размещенного в ASP страницах. Вопрос, как вы реализуете код обработки данных в COM объектах, должен рассматриваться в начальной стадии разработки дизайна приложения.

При проектировании COM объектов, старайтесь делать их не имеющими состояния (stateless), не используйте свойства (property). Вместо этого используйте методы, чтобы выполнять связанные с данными задачи. Это особенно критично, если вы используете MTS, так как любые объекты, которые сохраняют состояние, могут значительно снижать гибкость MTS, снижая общую производительность.

Для оптимальной производительности COM объекты должны быть откомпилированы как DLL в-процессе (DLL in-process), что требуется, если они должны работать под MTS. Вы должны всегда использовать раннее связывание при ссылке на COM объекты, и создавать их явно.

Как оптимизировать ваш код Transact-SQL

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

Выберите правильные типы данных
Этот раздел можно отнести к дизайну базы данных, но я решил обсудить его здесь, т.к. Transact-SQL используется для создания физических таблиц, которые были спроектированы на ранних стадиях разработки БД.
Выбор соответствующих типов данных влияет на то, как быстро SQL сервер может выполнять SELECT, INSERT, UPDATE и DELETE по отношению к данным, и выбор наиболее оптимального типа данных не всегда очевиден. Вот некоторые предложения, которые должны помочь вам при создании физических таблиц SQL сервера, чтобы достигнуть оптимальной производительности.

• Всегда выбирайте самый маленький тип для данных, которые вы должны хранить в столбце. Например, если все, что вы собираетесь сохранять в столбце - это числа от 1 до 10, тогда вам больше подходит тип данных TINYINT, чем INT. То же самое подходит для CHAR и VARCHAR типов данных. Не определяйте большее количество символов для символьных столбцов, чем действительно нужно. Это позволит SQL серверу сохранять большее количество строк в его страницах индексов и данных, сокращая количество ввода - вывода, необходимого, чтобы читать их. Также, это уменьшает количество данных, перемещенных от сервера до клиента, что сокращает сетевой траффик и задержки.
• Если текстовые данные в столбце имеют разную длину, используйте тип данных VARCHAR вместо CHAR. Хотя VARCHAR требует немного больше времени для обработки, объем данных уменьшится из-за использования VARCHAR, что снизит количество операций ввода-вывода и, соответственно, повысит общую производительность приложения.
• Не используйте NVARCHAR или NCHAR типы данных, если вы не должны сохранить 16-разрядные символьные данные (UNICODE). Они требуют в два раза больше места, чем CHAR и VARCHAR, что повышает расходы времени на ввод-вывод.
• Если вы должны хранить большие строки данных и их длина меньше чем 8,000 символов, используют тип данных VARCHAR вместо TEXT. Текстовые поля требуют больше ресурсов для обработки и снижают производительность.
• Если у вас есть столбец, который предназначен только для хранения чисел, используйте числовой тип данных, например INT, вместо CHAR или VARCHAR. Числовые данные занимают меньше места при хранении тех же самых чисел, чем строковые. Это помогает уменьшать размер столбцов, и может увеличить производительность, когда идет поиск по столбцу (WHERE) или выполняется JOIN к другому столбцу. 

Используйте триггеры c осторожностью
Триггеры являются мощным инструментом Transact-SQL, но так как они выполняются каждый раз при изменении таблицы (INSERT, UPDATE, DELETE, в зависимости от того, для чего триггер создан), они могут производить большие задержки. Вот некоторые советы относительно того, как оптимизировать производительность триггеров. 

• Старайтесь сводить код ваших триггеров к минимуму для уменьшения потерь. Чем большее кода, который выполняется в триггере, тем медленнее будет выполняться каждый INSERT, UPDATE, DELETE.
• Не используйте триггеры, для выполнения задач, которые могут быть выполнены более эффективными методами. Например, не используйте триггеры, чтобы поддерживать целостность БД (enforce referential integrity), если имеется встроенный в SQL сервер механизм для выполнения тех же задач. Тот же принцип работает когда вы выбираете между использованием триггеров или ограничением CHECK, чтобы предписать правила или значения по умолчанию. Предпочтительнее использовать CHECK, т.к. он быстрее, чем триггеры при выполнении той же самой задачи.
• Пытайтесь избегать триггеров, откатывающих базу данных из-за большой потери производительности. Вместо того, чтобы триггер находил ошибку и откатывал транзакцию, пытайтесь поймать ошибку до того, как она дойдет до триггера (если это возможно в вашем коде). Раннее обнаружение ошибки (до срабатывания триггера) потребляет меньше ресурсов, чем откат транзакций триггером. 

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

• Не возвращайте клиенту большее количество столбцов или строк, чем действительно необходимо. Это только увеличивает дисковый ввод - вывод на сервере, а также сетевой трафик, что ухудшает производительность. В инструкциях SELECT, не используйте *, чтобы возвратить строки, всегда точно определяйте в вашем SELECT'е, какие столбцы должны быть возвращены. В большинстве случаев, убедитесь, что вы включили условие WHERE для уменьшения количества строк клиенту. Клиент должен получать не больше строк, чем необходимо для решения текущей задачи.
• Если ваше приложение позволяет пользователям выполнять запросы, но вы не можете отсечь лишние сотни и тысячи возвращаемых строк, используйте оператор TOP внутри инструкции SELECT. Таким способом вы можете ограничить число строк, возвращаемых клиенту, даже если пользователь не ввел никаких критериев для ограничения поиска.

Избегайте использования курсоров
Transact-SQL так разработан, что работатает лучше всего на рекордсетах, а не на единичных записях. Это то место, где входят в игру курсоры. Они позволяют Вам обрабатывать индивидуальные записи. Единственная проблема с индивидуальной обработкой записи состоит в том, что такая обработка медленная. В идеале, для производительных SQL server приложений, курсоров нужно избегать.
Если вы должны исполнить операции с таблицей построчно (строка за строкой), попробуйте найти другой метод выполнить задачу. Некоторые варианты - выполнение построчных операций на клиенте или используя временные (tempdb) таблицы в сервере, или используя коррелированый подзапрос.
К сожалению, это не всегда возможно, и вы должны использовать курсор. Если вы уверены, что вам нужно использовать курсоры в ваших приложениях, тогда рассмотрите следующие рекомендации.

• SQL сервер предлагает Вам несколько различных типов курсоров, каждый с различными характеристиками производительности. Всегда выбирайте курсор с наименьшим количеством издержек, у которого есть особенности, необходимые для выполнения вашей задачи. Наиболее эффективный курсор, который вы можете выбрать - быстрый forward-only курсор.
• При использовании серверного курсора, старайтесь использовать как можно меньший рекордсет. Для этого выбирайте только те столбцы и строки, которые необходимы клиенту для решения его текущей задачи. Чем меньше курсор (неважно, какого типа), тем меньше он использует ресурсов, тем выше будет производительность.
• Когда вы закончили использовать курсор не только ЗАКРОЙТЕ (CLOSE) его, но и ОСВОБОДИТЕ (DEALLOCATE). Освобождение требуется, чтобы освободить ресурсы SQL сервера, используемые курсором. Если вы только ЗАКРЫВАЕТЕ курсор, все блокировки перестанут действовать, но ресурсы сервера не освободятся. Если вы не ОСВОБОДИТЕ ваши курсоры, ресурсы, используемые курсором останутся распределенными, уменьшая производительность вашего сервера до тех пор, пока курсоры не будут ОСВОБОЖДЕНЫ.

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

• Если вы имеете две или более таблиц, которые часто объединяются вместе, тогда столбцы, используемые для объединений должны иметь соответствующий индекс. Если столбцы, используемые для объединений не компактны по своей природе (имеют большой размер), тогда рассмотрите добавление компактных суррогатных ключей к таблицам. Объединение в этом случае должно производиться по суррогатным ключам, чтобы уменьшить ввод - вывод при объединении таблиц, что увеличит общую производительность. Вы узнаете несколько больше об индексировании из следующего раздела этой статьи.
• Для лучшей производительности, столбцы, используемые в объединениях должны иметь одинаковые типы данных. И если возможно, это должны быть числовые типы данных, вместо символьных типов.
• Избегайте объединять таблицы по столбцам с малым числом уникальных значений. Если столбцы, используемые при объединениях, имеют мало уникальных значений, то SQL сервер будет просматривать всю таблицу, даже если по данному столбцу существует индекс. Для наилучшей производительности объединение таблиц должно производится по столбцам с уникальными индексами.
• Если вы должны регулярно объединять четыре или более таблиц, для получения recordset'а, попробуйте денормализовать таблицы так, чтобы число таблиц, участвующих в объединении уменьшилось. Часто, при добавлении одного или двух столбцов из одной таблицы в другую, объединения могут быть уменьшены.

Инкапсулируйте ваш код в хранимых процедурах
Фактически, весь код Transact-SQL, используемый в приложениях, должен находиться в хранимых процедурах, а не запускаться в виде динамического SQL или скриптов. Это не только уменьшает сетевой трафик (передается только CALL или EXECUTE), но и ускоряет выполнение самого кода Transact-SQL, т.к. код в хранимых процедурах является прекомпилированным. Вот - пара вещей, которые стоит имет в виду при создании хранимых процедур для достижения оптимальной производительности.

Когда хранимая процедура выполняется в первый раз (и у нее не определена опция WITH RECOMPILE), она оптимизируется, для нее создается план выполнения запроса, который кешируется SQL сервером. Если та же самая хранимая процедура вызывается снова, она будет использовать кешированный план выполнения запроса, что экономит время и увеличивает производительность. Это может быть как хорошо, так и плохо. Если запрос в хранимой процедуре не изменяется каждый раз, тогда это - хорошая вещь. Но если запрос динамический (WHERE существенно изменяется от одного выполнения хранимой процедуры к другому), тогда это - плохая вещь, поскольку запрос не будет оптимизирован, и производительность запроса может пострадать.
Если вы знаете, что ваш запрос будет меняться каждый при выполнении хранимой процедуры, добавьте опцию WITH RECOMPILE при создании хранимой процедуры. Это заставит сохраненную процедуру каждый раз перекомпилироваться, что гарантирует оптимизацию запроса при каждом выполнении.

Всегда включайте в ваши хранимые процедуры инструкцию "SET NOCOUNT ON". Если вы не включите эту инструкцию, тогда каждый раз при выполнении запроса SQL сервер пошлет ответ клиенту, указывающему число строк, на которые воздействует запрос. Вряд ли клиент нуждается в этой информации. Использование этих инструкций уменьшает трафик между сервером и клиентом.

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

Чтобы избежать таких ситуаций, пытайтесь разрабатывать ваше приложение с учетом следующих рекомендаций:

• Всегда получайте доступ к объектам в одном и том же порядке.
• Не допускайте пользовательского ввода во время транзакций. Получите все необходимые данные перед началом транзакции.
• Старайтесь делать транзакции короткими и заключайте их в один пакет (batch)
• Старайтесь использовать максимально низкий уровень изоляции для пользовательского соединения, которое работает с транзакцией.

Как выбирать индексы для оптимальной производительности БД

Выбор индексов является тайной для многих администраторов баз данных и разработчиков. Конечно, мы знаем что они делают, и как они увеличивают производительность. Проблема состоит в выборе идеального типа индекса (кластерный/некластерный), числа столбцов, которое нужно индексировать (нужны ли мне индексы по нескольким столбцам?), и какие столбцы должны быть проиндексированы.

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

Бывает ли слишком много индексов?
Да. Некоторые люди думают, что все, что надо сделать - это проиндексировать все возможные столбцы. Это ошибочный путь. Индекс может ускорить доступ к данным, но может и замедлить, если он неправильно выбран. Проблема с лишними индексами состоит в том, что SQL сервер должен изменять их при любых изменениях таблицы (INSERT, UPDATE, DELETE). Если индексов один или два, то они не вносят больших потерь производительности, но если их четыре, пять или более, то SQL сервер оказывается перегружен работой с таблицами. В идеале вы должны иметь как можно меньше индексов. Очень часто требуется определенный баланс, чтобы выбрать оптимальное число индексов.

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

Проблему выбора оптимальных индексов часто трудно решить для приложений OLTP, потому что в них очень часто изменяется база данных (INSERT, UPDATE, DELETE). Вам одновременно нужно и наличие хорошей системы индексирования, для того чтобы быстро выполнялись SELECT'ы и UPDATE'ы, а с другой стороны не нужны потери производительности из-за наличия большого числа индексов. Но если вы разрабатываете OLAP приложение, то вы можете добавлять столько индексов, сколько потребуется, т.к. OLAP приложения работают только на чтение из БД, и вас не должно волновать медленное изменение базы данных (INSERT, UPDATE, DELETE). От того, как работает ваше приложение, будет зависеть ваша стратегия индексирования.

Другая проблема при выборе индексов состоит в том, что Оптимизатор Запроса может не использовать ваши индексы. В этом случае индексы являются бесполезным балластом и должны быть удалены. Но как может получится, что Оптимизатор запроса не использует индексы, если они присутствуют в таблице? Вообще это слишком обширный вопрос для рассмотрения в данной статье, но можно сказать, что иногда SQL серверу проще (и быстрее) просканировать (перебрать) таблицу, чем использовать индекс. Это может случиться из-за того, что таблица маленькая (в ней мало записей) или из-за того, что в столбце имеется меньше, чем 95% уникальных записей. Как узнать, будет ли SQL сервер использовать индексы, которые вы создаете? Мы ответим на этот вопрос немного позже, когда будем рассматривать использование SQL Server Query Analyzer.

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

• Первичный ключ не всегда должен быть кластерным индексом. Если вы создаете первичный ключ, тогда SQL сервер автоматически делает первичный ключ кластерным индексом. Первичный ключ должен быть кластерным индексом, только если он отвечает одной из нижеследующих рекомендаций.
• Кластерные индексы идеальны для запросов, где есть выбор по диапазону или вы нуждаетесь в сортированных результатах. Так происходит потому, что данные в кластерном индексе физически отсортированы по какому-то столбцу. Запросы, получающие выгоду от кластерных индексов, обычно включают в себя операторы BETWEEN, <, >, GROUP BY, ORDER BY, и агрегативные операторы типа MAX, MIN, и COUNT.
• Кластерные индексы хороши для запросов, которые ищут запись с уникальным значением (типа номера служащего) и когда вы должны вернуть большую часть данных из записи или всю запись. Так происходит потому, что запрос покрывается индексом.
• Кластерные индексы хороши для запросов, которые обращаются к столбцам с ограниченным числом значений, например столбцы, содержащие данные о странах или штатах. Но если данные столбца мало отличаются, например, значения типа "да/нет", "мужчина/женщина", то такие столбцы вообще не должны индексироваться.
• Кластерные индексы хороши для запросов, которые используют операторы GROUP BY или JOIN.
• Кластерные индексы хороши для запросов, которые возвращают много записей, потому что данные находятся в индексе, и нет необходимости искать их где-то еще.
• Избегайте помещать кластерный индекс в столбцы, в которых содержатся постоянно возрастающие величины, например, даты, подверженные частым вставкам в таблицу (INSERT). Так как данные в кластерном индексе должны быть отсортированы, кластерный индекс на инкрементирующемся столбце вынуждает новые данные быть вставленным в ту же самую страницу в таблице, что создает "горячую зону в таблице" и приводит к большому объему дискового ввода-вывода. Постарайтесь найти другой столбец, который мог бы стать кластерным индексом.

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

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

• Некластерные индексы лучше подходят для запросов, которые возвращают немного записей (включая только одну запись) и где индекс имеет хорошую селективность (более чем 95 %).
• Если столбец в таблице не содержит по крайней мере 95% уникальных значений, тогда очень вероятно, что Оптимизатор Запроса SQL сервера не будет использовать некластерный индекс, основанный на этом столбце. Поэтому не добавляйте некластерные индексы к столбцам, которые не имеют хотя бы 95% уникальных записей. Например, столбец с "Да" или "Нет" не имеет 95% уникальных записей.
• Постарайтесь сделать ваши индексы как можно меньшего размера (особенно для многостолбцовых индексов). Это уменьшает размер индекса и уменьшает число чтений, необходимых, чтобы прочитать индекс, что увеличивает производительность.
• Если возможно, создавайте индексы на столбцах, которые имеют целочисленные значения вместо символов. Целочисленные значения имеют меньше потерь производительности, чем символьные значения.
• Если вы знаете, что ваше приложение будет выполнять один и тот же запрос много раз на той же самой таблице, рассмотрите создание покрывающего индекса на таблице. Покрывающий индекс включает все столбцы, упомянутые в запросе. Из-за этого индекс содержит все данные, которые вы ищете, и SQL сервер не должен искать фактические данные в таблице, что сокращает логический и/или физический ввод - вывод. С другой стороны, если индекс становится слишком большим (слишком много столбцов), это может увеличить объем ввода - вывода и ухудшить производительность.
• Индекс полезен для запроса только в том случае, если оператор WHERE запроса соответствует столбцу (столбцам), которые являются крайними левыми в индексе. Так, если вы создаете составной индекс, типа "City, State", тогда запрос " WHERE City = 'Хьюстон' " будет использовать индекс, но запрос " WHERE State = 'TX' " не будет использовать индекс.

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

Что дальше?

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