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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Настройка сервера
Даже наиболее дорогие аппаратные средства сервера не будут хорошо работать, если они соответствующим образом не настроены. Я сталкивался с большим числом проблем производительности, связанных с использованием оборудования, не совместимого с 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 server приложений. И подобно дизайну приложения, если вы что-то не продумаете в самом начале проектирования, потом это будет исправить практически невозможно (по крайней мере, очень тяжело). Здесь приведены некоторые базовые сведения, которые стоит держать в голове, когда вы проектируете базу данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В этом разделе мы ответим на вышеупомянутые вопросы. К сожалению, нет никакого совета, который пригодился бы во всех случаях. Подобно многому вещам при настройке производительности 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 сервер, а также утилиты, упомянутые в статье. Помните, настройка производительности SQL сервера - больше искусство, чем наука, и только через овладение базисом, и через практический опыт, вы можете стать экспертом по настройке производительности SQL серверных приложений.

PULS.LV Professional rating system