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 очень эффективен при фильтрации данных, в особенности с учетом того, что некластерные индексы поддерживают предикаты фильтрации.
Отрешиться от 1-го решения на все случаи жизни (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) эти соединения. Ликвидирование операции сжатия не приведет ни к повреждению данных, ни к другим дилеммам. Таковой подход позволит вам сохранить соглашение о поддержке, и в то же время, избежать утрат производительности вашего производственного сервера.

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.