SQL в вопросах и ответах: В борьбе за производительность

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

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

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

• Обработка: при считывании данных с SQL Server приложение должно избегать обработки данных по одной записи за раз. Такую обработку часто называют RBAR (row-by-agonizing-row, «запись, мучительная пауза, еще запись»). Всякий раз, когда SQL Server направляет данные приложению, один из его потоков дожидается подтверждения доставки данных от приложения. Обработка в стиле RBAR приводит появлению у SQL Server большого количества ожиданий ASYNC_NETWORK_IO. Приложение должно кэшировать входные данные локально и быстро уведомлять SQL Server, что оно получило данные.
• Фильтрация: приложение должно избегать локальной фильтрации данных перед их использованием и показом. Гораздо эффективнее передать предикат фильтрации на SQL Server, который возвратит в приложение минимальное количество данных. SQL Server очень эффективен при фильтрации данных, особенно с учетом того, что некластерные индексы поддерживают предикаты фильтрации.
• Отказаться от одного решения на все случаи жизни (One Size Fits All, OSFA): сведите к минимуму количество считываемых столбцов, ограничившись только необходимыми. Кроме того, разработчикам следует избегать попыток создать одно представление данных на все случаи жизни. Использование SELECT со списком столбцов вместо SELECT * позволит сократить количество обрабатываемых и возвращаемых данных. К тому же при меньшем количестве запрашиваемых столбцов SQL Server, возможно, найдет более эффективные способы получения этих данных, что повысит производительность.
• Сортировка: если возвращаемые данные не нужно сортировать с помощью ORDER BY, не указывайте ORDER BY, поскольку это может запустить операцию сортировки. Операции сортировки часто бывают дорогостоящими, поскольку приводят к созданию большого количества вспомогательных данных сортировки в tempdb.
• Запросы «на всякий случай»: откладывайте операции SELECT до момента, когда они действительно понадобятся. Если приложение выполняет операцию SELECT просто на всякий случай, когда пользователь переходит на кнопку, то может оказаться, что данные запрашиваются зря. Нужно дождаться, когда кнопку, в самом деле, нажмут, и только тогда выполнить SELECT, убрав обработку в случае, когда кнопка не нажата.
• Подумайте о кэшировании: если вы запрашиваете одни и те же данные снова и снова, кэшируйте их локально и выполняйте новый SELECT только при изменении данных. Это идеальный подход, когда данные меняются нечасто или когда вам не нужны самые свежие данные.

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

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

Зеркало, зеркало…

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

Ответ Зеркалирование баз данных стало чрезвычайно популярным после своего успешного появления в SQL Server 2005 SP1. Однако на клиентских системах часто возникает одна проблема. Она связана с предположением, что раз уж вы реализовали зеркалирование баз данных, можно, ничем не рискуя, забыть о нем и рассчитывать, что оно идеально отработает при возникновении сбоя — защищенная база данных на зеркальном сервере перейдет в онлайновый режим без потерь данных и с минимальным простоем.

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

• Размер очереди SEND показывает, сколько данных журнала транзакций сгенерировано на основном сервере, но еще не передано на зеркальный сервер. Если оно отлично от нуля, значит, данные при зеркалировании не синхронизированы, и автоматическое восстановление после сбоя невозможно. Кроме того, размер очереди SEND показывает объем данных, которые потеряются при аварии основной базы данных. Вы должны следить за этим показателем и контролировать, что размер очереди SEND при зеркалировании баз данных не превышает максимально допустимый с точки зрения требований SLA (Service Level Agreement, соглашение об уровне сервиса) или RPO (Recovery Point Objective, целевая точка восстановления) относительно потерь данных.
• Размер очереди REDO показывает, сколько данных из журнала транзакций существует на зеркальной базе данных, но еще не воспроизведено в ней. Вспомните, что записи журнала должны записываться на диск, хранящий журнал транзакций зеркальной базы данных, но не должны сразу же воспроизводиться. На зеркальном диске постоянно идет процесс воспроизведения. Если в решении с зеркалированием произойдет сбой, вы не сможете обратиться к зеркальной базе данных до тех пор, пока все записи журнала транзакций в очереди REDO не будут воспроизведены в зеркальной базе данных. Это, в конечном счете, означает, что восстановление после сбоя требует времени. Чем больше очередь REDO, тем дольше будет длиться восстановление после сбоя. Вспомним, что в Enterprise Edition поддерживается быстрое восстановление и база данных становится доступной после завершения фазы восстановления REDO, но до начала фазы UNDO. Вы должны наблюдать за этим показателем и контролировать, что размер очереди REDO при зеркалировании не превышает максимально допустимый с точки зрения требований SLA или RTO (Recovery Time Objective, директивное время восстановления) относительно времени простоя при сбое.

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

Вы можете наблюдать за очередями SEND и REDO, установив уведомления с помощью Database Mirroring Monitor в SQL Server Management Studio. Также аы можете наблюдать за ними напрямую, используя perfmon-счетчики объекта Database Mirroring — Log Send Queue KB и Redo Queue KB.

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

Нужно ли сжатие?

Вопрос Один из наших поставщиков приложений требует, чтобы мы регулярно запускали операцию DBCC (database consistency checking, проверка целостности базы данных) SHRINKDATABASE для баз данных приложения и tempdb. Поставщик настаивает, что это необходимо для обеспечения приемлемой производительности. Что вы нам посоветуете?

Ответ Вопросы такого рода поступают довольно часто. Поставщики приложений могут не позволять вам отказаться от регулярных операций сжатия (shrink), поскольку полагают, что это «необходимо для производительности». Сжатие баз данных приводит к фрагментации индексов и требует много ресурсов процессора и ввода-вывода. Также при нем генерируется много данных в журнале транзакций. Все это может привести к проблемам при зеркалировании баз данных, использовании AlwaysOn Availability Groups, репликации и во всех остальных ситуациях, связанных с доставкой журнальных записей. Однако бывают обстоятельства, при которых необходимы единовременные операции сжатия.

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

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

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

• Индексы базы данных уже фрагментированы, так что от сжатия хуже не будет.
• Раньше никто не жаловался на производительность, а почему вы жалуетесь?
• Мы должны регулярно выполнять сжатие, поскольку наши операции приводят к значительному увеличению базы данных и надо вернуть дисковое пространство пользователям.
• Мы должны сжимать tempdb, поскольку выполняемые нами операции приводят к ее постоянному росту.

Ни один из этих доводов не является убедительной причиной для регулярного сжатия баз данных. В статье базы знаний KB 307487 утверждается, что сжатие tempdb во время работы пользователей может привести к повреждению tempdb. А в техническом документе «Working with Tempdb in SQL Server 2005» (применимом ко всем версиям) говорится: «Сжатие файлов — не рекомендуемый подход».

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

К сожалению, нет возможности отказаться от операций сжатия, если это требование поставщика. Отказ от них приведет к аннуляции соглашения о поддержке. Лучшее, что можно сделать, — написать задание SQL Server Agent, которое будет выполняться каждые 15 секунд, искать соединения, выполняющие сжатие баз данных, и уничтожать (kill) эти соединения. Уничтожение операции сжатия не приведет ни к повреждению данных, ни к другим проблемам. Такой подход позволит вам сохранить соглашение о поддержке, и в то же время, избежать потерь производительности вашего производственного сервера.