(Возврат на основную страницу)

Содержание номера за Декабрь 2008 год

SQL Server

Декабрь 2008
№ 12 (96)

Editorial

Дмитрий Артемов

О репликации

Programming

По материалам статьи Майкла Р. Хотека

Планирование репликации с использованием identity

Александр Гладченко

Резервное копирование в репликации SQL Server

Марселло де Баррос и Кентон Гайдуолл

Запрос BINARY_CHECKSUM лучше подходит для больших выборок

DB Design & Warehousing

Ашиш Кумар Мехта

Фиксация DML изменений с помощью системы отслеживания измененных данных в версии SQL Server 2008

Other

Брэд МакГи

Как использовать системный монитор (System Monitor) для определения узких мест SQL Server

Питер Фогель

Никогда не блокируйте записи в вебприложениях! Никогда! Это вредно!

Кевин С. Гофф

Чертова дюжина: 13 советов для работы с SQL Server 2008 и SSRS 2008. Часть 1

О репликации

Здравствуйте, уважаемые читатели!

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

Дело в том, что большой авторский коллектив с портала sql.ru готовил сборник статей для книги, посвященной репликации в SQL Server. По независящим от них причинам, издать книгу в наше тяжелое (с экономической точки зрения) время не удалось, и переведенный комплект предложили нам. Естественно, отказываться от такой прекрасной подборки качественно переведенных статей было бы глупо, вот мы и не отказались.

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

Статьи охватывают все аспекты механизма репликации, реализованные в SQL Server 2000/2005, и мы уверены, будут очень полезны для разработчиков и администраторов БД, интересующихся в силу служебных обязанностей или просто для расширения кругозора, этой важной, но мало описанной темой.

Планирование репликации с использованием identity

Автор статьи очень часто встречает вопросы о том, что использование поля со свойством identity порождает проблемы в репликации. Но он убежден, что колонка Identity вполне может быть использована в репликации, просто необходимо выполнить некоторое предварительное планирование, наряду с установкой редко используемой опции. Эта статья описывает хороший метод, который автор использовал несколько лет и который позволил ему успешно использовать колонки identity.

Резервное копирование в репликации SQL Server

В этой статье рассматриваются требования к резервному копированию баз данных, задействованных в репликации моментальных снимков, репликации транзакций или репликации слиянием. Эти требования зависят от роли, которую сервер исполняет в репликации, и от места, где в топологии репликации необходимо обеспечить восстановление тиражируемых данных. Чтобы восстановить участвующие в репликации данные, необходимо регулярно резервировать издателей, дистрибуторов и подписчиков. Статья основана на материалах электронной документации, поставляемой в дистрибутиве сервера баз данных
SQL Server 2000 Books Online.

Запрос BINARY_CHECKSUM лучше подходит для больших выборок

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

Фиксация DML-изменений с помощью системы отслеживания измененных данных в версии SQL Server 2008

В версии SQL Server 2008 появилась новая возможность, которая называется «отслеживание измененных данных»  (Change Data Capture,
CDC). Система CDC обеспечивает фиксацию выполняемых применительно к таблице SQL Server DDL- и DML-операций и хранение внесенных в данные изменений в отдельной реляционной таблице SQL Server. В этой статье мы выясним, как система CDC может помочь администратору проследить за изменениями, вносимыми в таблицы SQL Server в результате выполнения DML-операций. По умолчанию на уровне базы данных система CDC отключена. Член серверной роли sysadmin должен включить отслеживание измененных данных для конкретной базы данных. Как только система CDC получает доступ к базе данных, любой член предопределенной роли базы данных dbo может включить отслеживание измененных данных для таблиц из этой БД.

Общие сведения о системе отслеживания измененных данных

После того как использование системы CDC разрешено на уровне базы данных, на следующем шаге необходимо будет разрешить использование системы CDC для той конкретной таблицы, изменения данных в которой требуется отслеживать. Система CDC «собирает» измененные данные из файла журнала транзакций БД и помещает информацию об изменениях в соответствующую таблицу изменений, которая создается в процессе установки и конфигурирования этой системы.

Связь между исходной таблицей и таблицей изменений — это связь типа «один­к­одному». Вы можете иметь до двух таблиц изменений для каждой конкретной исходной таблицы. Поскольку системе CDC для того, чтобы она работала, необходимо постоянно «считывать» данные из журнала транзакций, обязательно должен функционировать агент SQL Server Agent (SQL Server Agent).

Система отслеживания внесенных изменений помогает администраторам баз данных получить информацию обо всех изменениях, внесенных в конкретную пользовательскую таблицу с помощью операторов языка манипулирования данными (Data Manuplation Language).

Как использовать системный монитор (System Monitor) для определения узких мест SQL Server

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

В данной статье мы начнем нашу первую «исследовательскую» работу. Мы будем использовать системный монитор (также называемый монитором производительности) для определения потенциальных узких мест аппаратных средств. С помощью мониторинга нескольких ключевых счетчиков в течение 24 часов, вы узнаете все основные узкие места оборудования вашего SQL Server. Обладая этими знаниями, нам будет легче определить потенциальные проблемы производительности.

В идеале вы должны использовать системный монитор для создания журнала основных счетчиков за 24­часовой период. Вам нужно выбрать «типичный» 24­часовой период, чтобы выбрать, когда создавать журнал системного монитора. Например, выбрать типичный будний день, а не выходные или праздник.

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

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

Как интерпретировать показания ключевых счетчиков системного монитора

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

Память: Страниц/сек (Pages/sec)

Этот счетчик измеряет количество страниц, прочитанных с диска или записанных на диск за одну секунду. Чем больше это значение, тем больше накладных расходов на ввод/вывод испытывает ваш сервер, что, в свою очередь, может уменьшить производительность SQL­сервера. Наша цель — попытаться свести дисковую подкачку к минимуму, а не устранить ее.

Предположим, что SQL Server — основное приложение, запущенное на вашем сервере, тогда данный показатель в идеале должен быть от 0 до 20. Скорее всего, вы будете наблюдать и пики, гораздо выше 20, что совершенно нормально. Учитывая эти пики, среднее значение Страниц/сек должно быть меньше 20.

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

В большинстве случаев на серверах, обслуживающих SQL Server и имеющих адекватный объем ОЗУ, значения дисковой подкачки в среднем меньше 20. Адекватным может считаться такой объем ОЗУ, чтобы коэффициент попадания в буферный кэш (Buffer Hit Cache Ratio) (описанный более детально ниже) был не менее 99%.

Если на вашем SQL Server коэффициент попадания в буферный кэш составляет 99% или больше в течение 24 часов, но средние значения дисковой подкачки выше 20 в течение того же периода, это может означать, что на физическом сервере выполняются и другие приложения, кроме SQL Server. В этом случае, в идеале, вы должны удалить эти приложения, позволив SQL­серверу оставаться основным приложением на физическом сервере.

Если ваш SQL Server не выполняет других приложений и дисковая подкачка в среднем превышает 20 Страниц/сек на протяжении 24 часов, это может означать, что вы изменили настройки памяти SQL­сервера. SQL Server обычно должен быть настроен таким образом, чтобы была установлена опция «Динамически распределять память SQL Server» (Dynamically configure SQL Server memory), а опция «Максимальная память» (Maximum Memory) была установлена на самый высокий уровень. Для оптимальной производительности SQL Server должен иметь возможность занимать столько ОЗУ, сколько ему потребуется, без какой­либо конкуренции с другими приложениями. Эта рекомендация подразумевает, что вы не используете AWE­память. Если же вы используете AWE­память, то вам необходимо установить вручную количество памяти, используемое SQL­сервером.

Память: Доступно байт

Другой способ проверить, достаточно ли вашему SQL­серверу физической памяти — это проверить счетчик «Доступно байт» (Available Bytes). Это значение должно быть более 5 МБ. Если это не так, то ваш сервер нуждается в ОЗУ. На сервере, предназначенном для SQL Server, SQL Server старается удерживать от 5­10 МБ свободной физической памяти. Остальная физическая память используется операционной системой и SQL­сервером. Когда объем доступной памяти приближается к 5МБ или ниже, вероятнее всего SQL Server испытает спад производительности из­за недостатка памяти. Когда это происходит, вам необходимо увеличить объем ОЗУ на сервере, уменьшить загрузку на сервер или изменить настройки памяти SQL­сервера соответствующим образом.

Физический диск: % Активности диска (% Disk Time)

Этот счетчик измеряет, насколько загружен дисковый массив (не логический раздел или отдельный диск в массиве). Он предоставляет хорошую относительную оценку загруженности ваших дисковых массивов.

Счетчик % активности диска приблизительно должен быть меньше 55%. Если счетчик превышает 55% в течение длительного промежутка времени (более 10 минут за 24­часовой период наблюдений), то узким местом вашего SQL­сервера может быть система ввода/вывода. Если вы наблюдаете такое поведение только изредка в течение 24­часового периода наблюдений, то не стоит слишком волноваться об этом, но если это происходит часто (скажем, несколько раз в час), то я бы начал искать пути увеличения производительности системы ввода/вывода сервера или уменьшения нагрузки на сервер. Вот некоторые способы увеличения производительности дискового ввода/вывода: добавление дисков в массив (если это возможно), установка более быстрых дисков, добавление буферной кэш­памяти на плату контроллера (если возможно), использование другой версии RAID или использование более производительного контроллера.

Замечание Если ваш SQL Server запушен на SAN, значения дисковых счетчиков могут быть неточными. Используйте ПО, предоставляемое производителем SAN для сбора этой информации.

Физический диск: Средняя длина очереди диска (Avg. Disk Queue Length)

Кроме счетчика % активности диска, вас заинтересует также счетчик «Средняя длина очереди диска». Если он превышает продолжительный период времени (более 10 минут за 24­часовой период наблюдений) для каждого диска в массиве, то, вероятно, этот массив является узким местом производительности. Как и для счетчика % активности диска, если это происходит изредка, не стоит слишком волноваться, но если это случается часто, тогда я бы начал искать способы увеличения производительности дискового ввода/вывода, как описано ранее.

Вы должны вычислить это значение, т. к. системный монитор не знает, сколько физических дисков в вашем массиве. Например, если у вас массив из шести физических дисков и средняя длина очереди диска для этого массива равна 10, тогда средняя длина очереди диска для каждого диска этого массива равна 1,66 (10/6=1,66), что ниже максимального рекомендуемого значения 2.

Используйте счетчики «% Активности диска» и «Средняя длина очереди диска» совместно для определения, испытывает ли ваш сервер недостаток производительности системы дискового ввода/вывода. Например, если вы наблюдаете много временных периодов, когда % активности диска более 55% и когда средняя длина очереди диска более 2, то вы можете быть уверены: узкое место вашего сервера — система дискового ввода/вывода.

Процессор: % Загруженности процессора

Счетчик % загруженности процессора доступен для каждого экземпляра процессора и измеряет загруженность каждого отдельного процессора. Такой же счетчик доступен для всех процессоров в общем. Это ключевой счетчик для слежения за загруженностью ЦП. Если значение % загруженности процессора превышает 80% в течение продолжительного периода времени (более 10 минут за 24­часовой период наблюдения), то, вероятно, узким местом является процессор.

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

Система: Длина очереди процессора (Processor Queue Length)

Наряду со счетчиком % загруженности процессора, вы можете захотеть отследить и счетчик «Длина очереди процессора». Если он превышает 2 на каждый процессор в течение продолжительного периода времени (более 10 минут за 24­часовой период наблюдений), то, вероятно, узким местом вашего сервера является ЦП. Например, если на вашем сервере установлено четыре процессора, длина очереди процессора не должна превышать в сумме 8 на весь сервер.

Если длина очереди процессора регулярно превышает рекомендуемый максимум, но загрузка процессора не так высока, как должна быть в этом случае, то следует уменьшить значение параметра SQL­сервера «максимум рабочих потоков» (max worker threads). Возможная причина того, что длина очереди процессора так высока, это чрезмерное количество рабочих потоков, ждущих своей очереди. Уменьшение количества «максимума рабочих потоков» позволит вам добиться большей преимуществ от организации пула потоков.

Используйте счетчики «Длина очереди процессора» и «% загруженности процессора» совместно, чтобы определить, является ли узким местом вашего сервера процессор. Если оба показателя превышают свои рекомендуемые значения в течение продолжительных периодов времени, вы можете быть уверены: узкое место системы — ЦП.

Буфер SQL-сервера: Коэффициент попадания в буферный кэш
(Buffer Cache Hit Ratio)

Этот счетчик показывает, как часто SQL Server обращается в буфер вместо обращения к жесткому диску, для получения данных. В OLTP­приложениях этот показатель должен превышать 90%, а в идеале быть выше 99%. Если ваш коэффициент попадания в буферный кэш менее 90%, то вам необходимо пойти и купить больше ОЗУ прямо сейчас. Если коэффициент колеблется от 90% до 99%, то вам необходимо серьезно задуматься о покупке дополнительной памяти, что приблизит вас к 99% и сделает ваш SQL Server более производительным. Следует помнить, что значение счетчика «Коэффициент попадания в буферный кэш» — это усредненное значение с момента старта экземпляра SQL­сервера. Из­за этого у вас может возникнуть желание использовать значение данного счетчика, взятое из инструментальной панели производительности (Performance Dashboard), которая описана в другой статье. Этот счетчик отражает текущую, а не усредненную активность.

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

SQL Server General: Количество пользовательских подключений
(User Connections)

В виду того, что количество пользователей, использующих SQL Server, влияет на его производительность, вы, вероятно, захотите отслеживать счетчик «Количество пользовательских подключений» объекта «Общая статистика SQL Server». Он показывает количество пользовательских подключений (не количество пользователей, подключенных к SQL­серверу).

Если этот счетчик превышает 255, то, возможно, вы захотите установить значение параметра SQL­сервера «Максимум рабочих потоков» (Maximum Worker Threads) выше стандартного значения в 255. Если количество подключений превышает количество доступных рабочих потоков, SQL Server начинает использовать рабочие потоки совместно, что наносит удар по производительности. Параметр «Максимум рабочих потоков» должен быть выше максимального количества пользовательских подключений, которого когда­либо может достигнуть ваш сервер.

Что дальше?

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

Табл. 1. Контрольная таблица аудита производительности

Никогда не блокируйте записи в веб-приложениях! Никогда! Это вредно!

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

Мои заказчики часто переживают по поводу возможного возникновения конфликтной ситуации на уровне данных. Они представляют себе примерно такой сценарий:

•    В 9:00 Сью извлекает запись о покупателе из таблицы Customer и вносит изменения в поле CustomerName.

•    В 9:01 эту же самую запись извлекает Лу и вносит изменения в поле CustomerCredit.

•    В 9:02 Сью нажимает кнопку подтверждения и обновляет запись.

•    В 9:03 кнопку подтверждения нажимает Лу и тоже обновляет запись.

На этой стадии функционирование объекта Data­Sources ASP.NET создает проблему, поскольку обновлению подвергаются все поля записи. Из­за такого функционального поведения, предусмотренного по умолчанию, в результате обновления, которое выполняет Лу в 9:03, изменения, внесенные Сью в поле CustomerName и сохраненные в 9:02, перезаписываются исходными данными, которые Лу извлек в 9:01. Это плохо. Мои заказчики обычно предполагают, что мы решим эту проблему, если «заблокируем записи в тот момент, когда их кто­то извлекает». Но это еще хуже.

Дело не в том, что в объектной модели ADO.NET отсутствуют какие­либо встроенные команды, обеспечивающие поддержку блокировки записей: вы не должны блокировать записи в базе данных, особенно в приложении ASP.NET.

Чертова дюжина: 13 советов для работы с SQL Server 2008 и SSRS 2008. Часть 1

Основной выпуск программного обеспечения содержит либо много незначительных дополнений, либо немногие, но важные, дополнения. Два последних выпуска SQL Server напоминают мне рождественские праздники в двух вариантах: версия SQL 2005 была похожа на Рождество с получением множества подарков, а версия SQL Server 2008 сродни дарам не столь многочисленным, но ценным. Те из вас, кто просил Санту предоставить возможность выполнять операции INSERT, UPDATE и DELETE в одном SQL-предложении или мечтал о возможности определить несколько предложений GROUP BY в одном SQL-запросе, будут просто счастливы. Если вам хотелось иметь иерархический тип данных, вы испытаете неистовый восторг. Кроме того, выпуск SQL Server 2008 включает новую, полностью переработанную версию служб SQL Server Reporting Services (SSRS): с совершенно новым интерфейсом,
с новыми возможностями создания графиков и (приготовьтесь аплодировать) полностью независимую от служб Internet Information Services (IIS). Итак, несмотря на то, что на улице температура растет, здесь, в рубрике «Чертова дюжина», наступило Рождество, и мы готовы «развернуть» тринадцать главных подарков, которые фирма Microsoft приготовила
в SQL Server и SSRS 2008.

Признания приверженца данных

Когда фирма Microsoft реализует новые возможности в языке или в библиотеке .NET Framework, я повторяю свой малый жизненный цикл. Прямо­таки слышу, как Элтон Джон поет «Can you feel the code tonight?» («Разве ты не во власти программирования в этот вечер?»). Я снова и снова вдумываюсь в смысл предложенной конструкции, беседую об этом с другими, а потом пробую применить новую возможность в нескольких прототипах, прежде чем начать пользоваться ею в условиях производства.

Признаюсь: я был просто потрясен, когда прочитал о реализованных в SQL Server новых возможностях, касающихся хранения данных, их извлечения с помощью запросов и визуализации. Меня в немалой степени впечатлили языковые возможности, которые были предложены в версии SQL Server 2005, и я точно так же «загорелся» от того, что фирма Microsoft сделала в версии SQL Server 2008. Новые дополнения свидетельствуют, что разработчики фирмы не только прислушиваются к просьбам о включении в язык новых операторов; они также заботятся о том, как обеспечить их безболезненное внедрение в процесс разработки.

Что у нас в меню?

Давайте перейдем прямо к меню. (Или по аналогии с Рождеством, давайте развернем подарки, лежащие под елкой!) В этой статье из рубрики «Чертова дюжина» будут предложены советы по использованию новой функциональности, реализованной в версии SQL Server 2008: восемь советов затрагивают функциональность, предназначенную разработчикам, и пять советов относятся к новым возможностям служб Reporting Services. Вот эти советы:

•    Кое­что из «чулка с подарками» (чувствуете, с каким нетерпением я жду рождественских праздников?), что имеет отношение к языку T­SQL Server 2008: конструкторы для вставки строк (Insert Row Constructors), операторы присваивания (Assignment Operators) и инициализаторы переменных (Variable Initializers).

•    Новые добавочные предложения GROUP BY, позволяющие разработчикам обеспечить группировку данных одного результирующего набора по нескольким различным признакам.

•    Новое ключевое слово MERGE, которое позволяет разработчикам выполнить операции INSERT, UPDATE и DELETE, сформулировав для этого одно­единственное SQL­предложение.

•    Новый системный тип данных Hierarchy ID.

•    Новый тип данных Table для передачи «плоских» (Table­like) структур в качестве параметров.

•    Новые типы данных Date и Time. (Да, можно иметь столбец, объявленный как Date, в котором хранится только дата!)

•    Другие разнообразные улучшения, внесенные в версию SQL Server 2008.

•    Новая архитектура служб SSRS 2008.

•    Изучение новой среды разработки для служб SSRS 2008.

•    Новая область данных Tablix служб SSRS (области данных table плюс matrix, плюс еще кое­что!).

•    Новые возможности для построения диаграмм в службах SSRS 2008 (предоставлены фирмой Dundas).

•    Новые возможности экспорта и доставки.

•    Более тесная интеграция с SharePoint.

==========================================================================

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

(Возврат на основную страницу)