Группа Microsoft Server Customer Advisory делится секретами повышения производительности
.
Влияние памяти
Иногда проблемы с производительностью прикладных задач проявляются следующим образом: пользователи могут сталкиваться с большим временем отклика приложений или медленной работой приложений. Тогда администраторы баз данных и разработчики начинают исследовать проблему, пытаясь найти причину этого явления. В остальных случаях внимательный администратор начинает искать причину, когда заметит необычное поведение системной характеристики, которую пользователи не могут видеть, вроде высокого процента использования центрального процессора или большого времени отклика от дисковой системы. Независимо от того, когда начинается исследование, в первую очередь необходимо выяснить, находится ли система под влиянием причин, связанных с памятью. Поскольку SQL Server разработан так, чтобы динамически управлять памятью, поиск решения может быть не таким простым, как может показаться. Например, в системе, в которой запущен SQL Server, процесс SQL Server (sqlservr.exe) обычно забирает большую часть памяти системы. Этот режим работы нормален, поскольку SQL Server спроектирован так, чтобы использовать как можно большее количество системной памяти до тех пор, пока никакие другие приложения не конкурируют за нее. Рост занятой SQL Server памяти не может быть ограничен параметром max server memory. Таким образом, объем памяти, который был распределен для процесса SQL Server, не поможет администратору понять, связана ли ошибка с памятью. О различиях использования памяти в 64-разрядных и 32-разрядных SQL Server рассказано во врезке "Управление памятью 64-разрядных и 32-разрядных систем". И хотя можно попробовать определить, связана ли проблема с памятью, посмотрев, не отведена ли память под процесс SQL Server на страницы, это не сработает, потому что SQL Server разработан так, чтобы избежать разбиения памяти на страницы.
Тогда как же обнаружить проблемы с памятью? Можно начать с просмотра счетчиков Performance Monitor (PerfMon): SQL Server:Memory Manager:Target Server Memory и SQL Server:Memory Manager:Total Server Memory. Счетчик Total Server Memory покажет, сколько памяти использует SQL Server. Если в течение некоторого времени Total Server Memory однозначно ниже, чем значение Target Server Memory, значит, в системе нет проблем с памятью. SQL Server не занимает больше памяти, чем он использует. Тем не менее, если величина Total Server Memory равна или превышает Target Server Memory, недостаток памяти может вызвать проблемы. Чтобы определить, где появляется недостаток памяти, нужно понять, каким способом SQL Server использует память. Соответствующие способы описаны в статье «Как работает память». Давайте посмотрим, чему можно научиться, если знать, как используется память в страничном кэше базы данных, кэше плана, рабочем пространстве запроса и блокировках.
Счетчики, связанные с объектом SQL Server:Buffer Manager PerfMon, информируют о работе памяти в страничном кэше базы данных. Сначала посмотрим на счетчик Database pages и сравним его со счетчиком Total pages. Если величина Database pages- большая часть от числа Total pages (более 50 процентов), знайте, что вы имеете дело с приложением, которому требуется перерабатывать большое количество данных. Два других счетчика, Buffer Cache Hit Ratio and Page life expectancy сообщат о том, насколько хорошо страничный кэш справляется с рабочей нагрузкой. Buffer Cache Hit Ratio покажет, какая часть страниц, запрашиваемая SQL Server, обслуживается из кэша и, соответственно, какая доля запросов на страницы идет в систему ввода/вывода. Нужно иметь в виду, что со значением Buffer Cache Hit Ratio в 99 % ввод/вывод в 10 раз больше чем при 99,9 %, и при 90 % ввод/вывод в 10 раз больше чем при 99 %. Счетчик Page life expectancy сообщает, как долго страница будет постоянно находиться в памяти в нынешнем состоянии. Низкое значение (обычно менее 300 секунд) означает, что система злоупотребляет буферным пулом. Таким образом, потенциально работа памяти может вызывать проблемы, приводящие к снижению производительности.
Если система имеет низкое значение Buffer Cache Hit Ratio или Page life expectancy, проверьте систему ввода/вывода, чтобы увидеть, как она справляется с рабочей нагрузкой. В частности, высокое значение (обычно более 10 мсек.) счетчика Avg. Disk sec/Read или Avg. Disk sec/Write PerfMon из объекта PhysicalDisk может указывать на то, что система работает с перегрузками. Будьте внимательны, просмотрите эти счетчики отдельно для каждого диска; если просматривать только средние числа, проблему с конкретным диском можно пропустить.
Если значения счетчиков Buffer Cache Hit Ratio и Page life expectancy малы, то можно для стимуляции производительности приложений добавить дополнительное количество памяти. Однако перед тем как проверять оперативную память, надо произвести небольшое исследование скрытых причин. Возможно, ваш буферный кэш перегружен, потому что SQL Server работает со слишком большим количеством таблиц одновременно, или использует сканирование индексов вместо поиска по индексу, чтобы ограничить обращение к определенным строкам. Сканирование, особенно больших таблиц или индексов, скорее всего, приведет к падению значения Buffer Cache Hit Ratio, потому что маловероятно, чтобы все страницы большого объекта оставались в кэше памяти. Сканирование может также вызвать понижение в значении Page life expectancy за счет выдавливания других страниц, постоянно находящихся в буферном кэше. Поиск по индексу уменьшает обращение к специфическому диапазону строк и таким образом вызывает меньшее количество просмотров страницы. Счетчик SQL Server:Access Methods:Full Scans/sec в PerfMon позволяет контролировать число полных сканирований за секунду, выполняемых SQL Server. Если пользователь решит, что для него слишком частое применение полного сканирования нежелательно, можно использовать SQL Server Profiler и, просматривая события в Showplan Statistics в категории Performance, найти то из предложений SQL Server, которое приводит к полному сканированию. Анализируя данные в Profiler, можно определить, требуется ли добавлять индекс к таблице и возможно ли переупорядочить столбцы существующего индекса, чтобы использовать поиск по индексу вместо сканирования индекса. О том, как использовать информацию SHOWPLAN, рассказано в статье Microsoft "HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later". (http://support.microsoft.com/default.aspx?scid=kb;en-us;243589).
Другой фактор, который мог бы повлиять на уменьшение использования буферного кэша - чрезмерная денормализация, которая ведет к избыточности данных и таким образом увеличивается общее число страниц в базе. Когда буферный кэш вынужден обслуживать большее количество страниц данных, он становится менее эффективным. Другая общая причина медленной работы буферного кэша касается программ, в которых модификации происходят на уровне формы. Эти программы показывают пользователям строки, столбцы в которых они могут модифицировать. В таких случаях приложение переписывает все столбцы в базе данных вместо обновления только тех, которые изменены пользователем. Этот вид приложений не только приводит к потере процессорного времени на сервере при обработке неизмененных полей, но и заставляет SQL Server обновлять индексы, чтобы отразить потенциально измененные значения полей. Это приводит к выполнению множества ненужных операций чтения и записи страниц.
Кэш плана
SQL Server требуется план запроса для выполнения предложения SQL, но создание плана запроса в ответ на предложение SQL (или компиляция) - это процесс, который потребляет много процессорного времени. SQL Server может кэшировать планы запроса в кэш плана и использовать его повторно. Это позволяет экономить ресурсы процессора, но из-за того, что память ограничена, SQL Server может не всегда использовать готовый план. И при некоторых особых обстоятельствах (о которых рассказано в статье "Query Recompilation in SQL Server 2000", http://msdn.microsoft.com/library/default.aspx?url=/library/en-usdnsql2k/html/sql_queryrecompilation.asp) SQL Server, может быть, придется отказаться от плана, который находится в кэше и предназначен для конкретного предложения SQL, и собрать новый план. Этот процесс называется перекомпиляцией. Счетчик SQL Server:Buffer Manager: Procedure Cache Pages соответствует общему количеству страниц в кэше плана. Если это число - существенная часть (обычно более 25 %) общего количества страниц в буферном пуле, пользовательское приложение задействует план. Счетчики объекта SQL Server: Cache Manager сообщат администратору, сколько планов каждого вида (например: AdHoc, Prepared, Procedure) кэшировано, и сколько страниц они занимают.
Рабочие нагрузки для OLTP-систем обычно включают в себя много подобных предложений, выполняемых с различными значениями для параметров типа customerID или orderID. По собственному опыту скажу, что проблемы, связанные с компиляцией, перекомпиляцией и эффективным использованием кэша плана - наиболее распространенный источник проблем, связанных с низкой производительностью в приложениях OLTP, выполняющихся на SQL Server.
Может ли кэш плана эффективно избегать ненужных компиляций? В статье «Как работает память» говорилось о том, что счетчик Cache Hit Ratio суммирует значения с момента запуска экземпляра, так что это плохой критерий текущей эффективности кэша плана. Однако взглянув на счетчики объекта SQL Server:SQL Statistics, можно получить довольно точную картину эффективности работы кэша плана. В частности, если SQL Compilations/sec - это большая доля (более 10 %) SQL Batch Requests/sec, и пользовательское приложение - это приложение OLTP, тогда приложение, вероятно, не использует кэш плана эффективно. Во врезке "Оптимизация через настройки параметров для запросов в планах повторного использования" объясняются некоторые общие причины, почему это могло иметь место, и показано, как улучшить повторное использование плана запроса.
Иногда повторное использование плана неуместно. Например, если размер набора результатов запроса (или промежуточных результатов) сильно зависит от параметров, описанных в предикате WHERE. В таких случаях стратегии обращений для различных операторов должны быть различными. Более подробную информацию о случаях, когда повторное использование плана неуместно, можно найти в "Query Recompilation in SQL Server 2000", http://msdn.microsoft.com/library/default.aspx?url=/library/en-usdnsql2k/html/sql_queryrecompilation.asp. Однако в целом повторное использование плана уменьшает нагрузку на процессор и может заметно улучшить производительность OLTP-систем.
Другой вариант, который может привести к плохой производительности при использовании кэша плана - создание отдельных объектов, таких как представления или хранимые процедуры, для каждого пользователя. Разработчики прикладных приложений иногда используют эту технологию, чтобы ограничить данные, к которым пользователи могут обращаться. Тем не менее, представления или хранимые процедуры для каждого пользователя становятся различными объектами, и не может быть никакого повторного использования плана даже при том, что все эти объекты подобны. Если в системе присутствуют сотни или тысячи пользователей, такая конструкция может излишне засорять память в кэше плана и приводить к наличию планов, которые, скорее всего, будут отвергнуты. Меньшее количество объектов требуется, если для управления данными каждого пользователя используются функции SUSER_SID () и SUSER_SNAME (). Эта перестройка дает лучший коэффициент использования кэша плана.
Недостаток памяти в любой части SQL Server может вызывать замещение кэшированных планов запроса и ненужных компиляций. Подсказка - внезапное снижение величины счетчика SQL Server:Buffer Manager:Page life expectancy. Вы можете следить за планами компиляций хранимых процедур, повторным использованием и замещением, задействуя SQL Server Profiler для поиска события кэша, см. Таблицу 1. Для конкретной хранимой процедуры событие SP:CacheRemove, следующее за событием SP:CacheMiss (которое сообщит, что SQL Server искал и не нашел план запроса), следующее в свою очередь за событием SP:CacheInsert (которое сообщает, что SQL Server создал новый план запроса в кэше плана ), обычно указывает на недостаток памяти. С другой стороны, когда происходит повторное использование плана и имеется некоторая нехватка памяти, можно будет увидеть большее количество событий SP:CacheHit и SP:ExecContextHit в Profiler.
Память рабочего пространства
Сложная система поддержки принятия решений или приложения для хранилища данных обычно требует памяти для рабочего пространства, результатом чего является высокое значение счетчика SQL Server:Memory Manager:Granted Workspace Memory (KB). В таких случаях важно контролировать длину очереди на предоставление памяти (как было описано в статье «Как работает память»), проверяя счетчик SQL Server:Memory Manager:Memory Grants Pending. Длинная очередь, особенно по сравнению со счетчиком SQL Server:Memory Manager:Memory Grants Outstanding, означает, что система перегружена слишком большим количеством запросов, интенсивно потребляющих память. Можно использовать Profiler, чтобы понять, какие запросы используют выделение памяти и почему. Поле Integer Data из трассировщика события 28 в Profiler показывает размер предоставленной памяти под оператор в килобайтах. Анализ событий в Showplan для оператора может подсказать, как обнаружить отсутствующий индекс для операций JOIN или SORT. Или анализ может показать, что запрос слишком общий и нуждается в указании имени пользователя или приложения, тогда требуется уточнить запрос для обработки меньшего количества строк.
SQL Server в значительной степени полагается на статистику при оценке количества рабочей памяти, которое потребуется для обработки запроса. Например, для объединения хешированием приблизительное число строк и размер строки первой таблицы для построения объединения (формирующей объединение) определяет, сколько памяти потребуется для присоединения. Проблемы возникают, если оценка неверна. Если оценка занижена, хэш-таблица не будет соответствовать размеру выделенной памяти, и строки будут скидываться на диск, замедляя выполнение запроса. События Hash Warning и Sort Warning из категории Errors and Warnings в Profiler укажут на такое переполнение. Если оценка слишком высока, SQL Server решит, что нужно выполнить запрос с большими допущениями, чем это требуется фактически, и запрос может находиться в очереди для предоставления памяти сверх необходимого. Пользователь может задействовать событие Show Plan Statistics, чтобы точно определить, насколько оцененное оптимизатором запросов количество строк начинает отличаться от фактического количества обработанных строк. В целом, статистика дает оптимизатору запросов больше шансов точно оценить размер. Иногда характер распределения данных или сложность запроса не позволяет точно предсказать результат. Пользователь в таких ситуациях должен использовать хинты объединения (типа HASH JOIN или LOOP JOIN) или хинты порядка объединения (типа FORCE ORDER).
Блокировка памяти
Способы применения памяти, которые мы до сих пор рассматривали - страничный кэш базы данных, кэш плана и рабочее пространство памяти - имеют большое значение для оптимизации производительности. Пользователям лучше, когда SQL Server предоставляет под эти нужды по возможности большее количество системной памяти. Блокировки, с другой стороны, означают дополнительные издержки для SQL Server, необходимые для гарантирования уровней изоляции транзакции, выбранной для пользовательского приложения. Следовательно, для обеспечения слаженной работы приложений пользователь должен осуществлять блокировки памяти на минимально необходимом уровне. Чрезмерное использование блокировок может подсказать пользователю, что его приложение применяет более высокий, чем требуется, уровень изоляции. Например, на уровнях изоляции REPEATABLE READ и SERIALIZABLE SQL Server держит до конца транзакции блокировку для каждой строки (или страницы), которую считывает предложение, что может составить в целом множество блокировок, если предложение пользователя читает большое количество строк.
В таких случаях, чтобы определить, можно ли удовлетвориться более низким уровнем изоляции, пользователь должен просмотреть модель приложения. Однако в некоторых случаях SQL Server захватывает и держит блокировки при чтении даже на уровне изоляции READ COMMITTED. Что может случиться, когда SQL Server использует более одного пути обращения к данным (например, через индекс или через «кучу») для конкретной таблицы в том же самом запросе. Например, если запрос имеет два предиката в условии WHERE той же таблицы (например, Customer.age > 35 и Customer.state = 'CA') и на каждый можно ответить, используя разные индексы (один по возрасту, другой - по местонахождению), SQL Server может задействовать оба индекса и объединить результаты операцией JOIN, чтобы получить требуемый результат. В таком случае SQL Server, чтобы гарантировать, что запрос получит последовательное изображение каждой строки, использовал бы блокировки на эти два индекса. Если такая блокировка неосуществима, можно попытаться переключиться на уровень READ UNCOMMITTED или, чтобы избежать блокировок, использовать в операторах хинты NOLOCK. Если схема пользовательского приложения препятствует применению NOLOCK, попробуйте изменить план запроса, изменяя индексы (например, добавить охватывающий индекс) или использовать хинты, чтобы исключить такие блокировки.
Создание пользовательской рабочей области памяти
Использование памяти SQL Server заметно влияет на производительность системы. Понимание того, как SQL Server использует память и как управлять этим процессом, поможет пользователю понять, сталкивается ли его система с нехваткой памяти и решить связанные с этим проблемы. Общаясь с клиентами, которые столкнулись со снижением производительности, связанным с памятью в SQL Server, специалисты группы SQL Server Customer Advisory Team всегда рекомендуют стараться устранять основные причины нехватки памяти, прежде чем пользователь примет решение увеличить память. Здесь уместно вспомнить аналогию с прохудившимся бензобаком в автомобиле. Несомненно, наличие большого бака поможет отсрочить остановку системы, но в конечном счете, пока хозяин не найдет и не устранит утечку, топливо из бака будет вытекать.
Управление памятью 64-разрядных и 32-разрядных систем
Важное различие между 64-разрядным и 32-разрядным SQL Server состоит в управлении памятью. 64-разрядный SQL Server может адресовать до 512 Гбайт памяти на Windows Server 2003. В 64-разрядном SQL Server можно обращаться ко всей этой памяти непосредственно через адресное пространство виртуальной памяти (в отличие от AWE, о котором я скажу позже). Таким образом, все компоненты SQL Server, которые используют память, включая страничный кэш базы данных, кэш плана, память рабочего пространства и блокировки, могут в 64-разрядных системах использовать дополнительную память. Напротив, виртуальная память SQL Server 2000 (32 разряда) ограничена пределом в 3 Гбайт, если используется ключ /3GB в файле boot.ini или 2Гбайт - без ключа. SQL Server 2000 (32 разряда) может обращаться к памяти емкостью до 32 Гбайт, но тогда нужно задействовать механизм Address Windowing Extensions (AWE), который используется, когда требуется обратиться к памяти, превышающей предел виртуальной памяти. AWE - это набор API-функций, который позволяет процессу отображать физическую память внутри или вне виртуального адресного пространства.
Важно обратить внимание на то, что SQL Server использует память AWE только в страничном кэше базы данных (то есть для данных и индексных страниц) и при этом может использовать физическую память вне виртуальной памяти процесса. Все другие способы использования памяти, включая кэш плана, память рабочего пространства запроса, блокировки и другие структуры типа пользовательских подключений, курсоров и пространств, используемых утилитами для резервирования и восстановления, ограничены виртуальным адресным пространством. Тем не менее, если пользовательское приложение для SQL Server сталкивается с нехваткой памяти в этих частях системы, добавление дополнительной память свыше 3 Гбайт на 32-разрядных системах может принести лишь небольшую выгоду. В таких случаях пользователь может подумать о миграции на 64-разрядную систему. Системы на 64-разрядах особенно хорошо показали себя в работе информационных хранилищ и в больших ERP-системах. Дополнительную информацию о потенциальных преимуществах SQL Server (64 разряда) можно найти в статье по адресу http://www.microsoft.com/sql/64bit/productinfo/SQL64bitAdvantages.asp. О том, как настроить память AWE с SQL Server, рассказано в статье, опубликованной по адресу http://support.microsoft.com/default.aspx?scid=kb;en-us;274750.
Оптимизация через настройки параметров запросов в планах повторного использования
Безусловно, более общая причина необходимости избегать компиляции - недостаток параметризации. Рассмотрим следующий SQL-оператор
SELECT C.cid, SUM(O.order_amount) FROM Customers C, Orders O WHERE C.cid = O.cid AND C.cid = 1234 GROUP BY C.cid
Этот оператор вычисляет общее число заказов для данного клиента, определяемое полем cid . В операторе SELECT это значение - встроенная константа 1234. Если данный оператор применяется в этом приложении часто, можно увидеть несколько подобных утверждений, отличающихся друг от друга только значением внедренной константы. Например, другое выражение может быть похожим на следующее:
SELECT C.cid, SUM(O.order_amount) FROM Customers C, Orders O WHERE C.cid = O.cid AND C.cid = 5678 GROUP BY C.cid
Эти два оператора получили бы преимущество от многократного использования одного и того же плана запроса, однако из-за того, что текст оператора различен, SQL Server не принимает во внимание, что операторы будут те же самые и повторно не использует план. В таком случае, если пользователь исследует поле мастер-таблицы .. syscacheobjects, он найдет много планов поля usecount со значением 1, указывающих на недостаток повторного использования плана.
Несколько методов позволяют создавать приложения таким образом, чтобы разрешить оптимизатору запросов повторное использование одного и того же плана. По определению, хранимые процедуры и их планы запросов используются многократно. Тогда если пользователь создает хранимую процедуру, которая задействует параметры вместо ввода переменных, SQL Server может повторно использовать план при работе той же хранимой процедуры. Для пиковой производительности пользовательское приложение должно вызвать хранимые процедуры, через применение запроса Remote Procedure Call (RPC). Запросы RPC пропускают большую часть разбора предложения и обработки параметров в SQL Server и работают быстрее, чем оператор T-SQL EXECUTE. Синтаксис RPC можно найти в SQL Server 2000 Books Online (BOL). Или, как показано в Листинге А, можно использовать процедуру sp_executesql, которая позволит SQL Server повторно применить тот же план двум операторам выполнением отделения констант от кода предложения. В качестве альтернативы можно улучшить повторное использование плана, применяя модель готовности/выполнения, обеспеченную обращением к данным через API типа ODBC или OLE DB. Описание соответствующих API для ODBC можно найти в статье «How to Prepare and Execute a Statement (ODBC)» по адресу http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_6_odbcht_66 0k.asp)и для OLE DB в MSDN OLE DB Programmers Reference по адресу http://msdn.microsoft.com/library/default.asp?url=/library/enus/oledb/htm/oledbicommandprepare__prepare.asp.
Листинг А. Код, использующий хранимую процедуру sp_executeSQL, что помогает повторно задействовать план запроса.
/* Use sp_executeSQL to create reusable execution plans for ad hoc SELECT
statements. The only difference between the SELECT statements is the value
assigned to the parameters you
pass to sp_executeSQL. */
DECLARE @joblevel ing, @jobid ing
DECLARE @SQLString nvarchar (500)
DECLARE @ParmDefinition nvarchar(500)
-- Build the SQL string once.
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @jlvl and job_id >= @jid'
---- Specify the parameter format once.
SET @ParmDefinition = N'@jlvl tinyint,@jid tinyint'
-- Execute the string with the first parameter values.
SELECT @joblevel = 35,@jobid = 10
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@jlvl=@joblevel,@jid=@jobid
-- Execute again with the new parameter values; SQL Server reuses the same plan.
SELECT @joblevel = 75, @jobid = 9
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@jlvl=@joblevel,@jid=@jobid
-- Look at execution plan usecounts. Usecounts > 1 indicates plan reuse.
SELECT bucketid,cacheobjtype,objtype,dbid,objid, usecounts, sql
FROM master..syscacheobjects
ORDER BY sql