Главная » Microsoft SQL Server, Базы данных » Теория оптимизации и SQL Server

0

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

Первое, что можно сказать, — не стоит применять все известные стратегии, потому что многие из них имеют внутренние и обойденные вниманием зависимости. Например, использование индексации для повышения скорости извлечения данных увеличит время выполнения транзакций, что связано с конкуренцией. Итак, мы видим непосредственную связь между индексацией и конкуренцией. Можно найти гораздо больше пересечений. Одни стратегии повышения производительности можно применять непосредственно, а эффективность других напрямую зависит от того, были ли перед этим применены другие.

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

Модель схемы

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

Чтобы создать эффективную схему, следуйте следующим советам:

?               избегайте чрезмерной сложности;

?               тщательно выбирайте ключи;

?               следите за необязательными данными;

?               используйте некоторый уровень абстракции.

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

Рис. 1.3. Теория оптимизации гласит, что каждая стратегия зависит от другой и активизируется ею

Запросы

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

При принятии решения относительно использования последовательного кода или пакетных запросов ознакомьтесь с табл. 1.4 — она послужит хорошим руководством к действию.

Таблица 1.4. Методы программирования

Задача

Лучшее решение

Сложная бизнес-логика

Запросы, подзапросы, СТЕ

Динамическое генерирование DDL Курсоры

Перестройка списка

Переменные или курсор

Перекрестная таблица

Запрос с предложением pivot или case

Прохождение по иерархии

Пользовательская функция или СТЕ

Накопительные суммы

Курсор

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

Индексация

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

Дополнительная Создание кластеризованных, некластеризованных и прочих индексов детально информация описано в главе 50.

Конкуренция

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

Конкуренцию можно сравнить с источником воды. Если люди, вальяжно прогуливаясь, ожидают своей очереди наполнить стаканчики, ситуация стабильна. Установка параметра nolock равносильна призыву: “Все бросайтесь к источнику!” Естественно, в этом случае ситуация выйдет из-под контроля и давка неизбежна. В такой ситуации лучше последовательно наливать воду в стаканчики, при этом сокращая время своего пребывания у источника. Аналогичная ситуация и в базах данных: лучше ограничиться небольшими запросами или сократить время выполнения транзакций. А для создания эффективных транзакций нужно разработать эффективную схему, использовать пакетный код и индексы.

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

Расширенная масштабируемость

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

?               изоляцией мгновенных снимков;

?               разделением таблиц;

?               индексированными представлениями;

?               брокером служб.

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

Теория оптимизации, в которой раскрыты зависимости между разными технологиями оптимизации, является революционной концепцией. Самую свежую информацию о теории оптимизации, а также мои презентации решений повышения производительности вы найдете на сайте www. SQLServer Bible. com.

Резюме

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

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии