По материалам статьи Joseph Sack Troubleshooting SQL Server with the Sysperfinfo Table

Когда есть возможность выбора между использованием инструментальных средств GUI или Transact-SQL, автор предпочитает выбирать последнее. Эта статья кратко описывает несколько запросов, которые помогают разрешать проблемы узких мест памяти, которые обычно обнаруживаются с помощью System Monitor (Performance Monitor). System Monitor полезен для отслеживания тенденций в течении какого-то времени (использование журналирования счётчиков), однако иногда автор предпочитает анализировать снимки текущего состояния экземпляра SQL Server. Используя Query Analyzer, Вы можете использовать или интегрировать в свой код представленные ниже запросы, которые автор представил в виде детализированных скриптов Transact-SQL.

Адресное пространство памяти SQL Server 2000 состоит из пула памяти и пула исполняемого кода. Пула исполняемого кода содержит объекты памяти, такие как загружаемые для распределенных запросов OLE DB Provider DLL, DLL - расширенных хранимых процедур, а также исполняемые движком SQL Server файлы и сетевые библиотеки.

Пул памяти содержит различные системные таблицы структур данных; буферный кэш (куда считываются страницы данных), кэш процедур (содержащий планы исполнения Transact-SQL запросов), кэш журналов (каждый transaction log для каждой базы данных имеет свой собственный кэш буферных страниц) и информацию о контексте подключений. Пул памяти - наиболее прожорливый потребитель ОЗУ для типичных случаев применения SQL Server.

Вообще говоря, автор предпочитает идентифицировать большинство проблем с узкими местами в использовании памяти путём анализа ошибок, которые появляются в журнале SQL сервера. Например, пользователь может запустить инструкцию с огромным предложением IN. В таком случае, может появится сообщение об ошибке "Failed to reserve contiguous memory of Size=XXXX". Когда автор видит эту ошибку, он выполняет в Query Analyzer несколько запросов, чтобы точно определить источник снижения или повышения производительности.

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

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

SELECT  cntr_value/1024 as 'MBs used'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Memory Manager' and
   counter_name = 'Total Server Memory (KB)'

Для именованного экземпляра, используется следующий код вместо предыдущего, где InstanceName - вторая часть вашего имени именованного экземпляра, например: ServerNAME\InstanceName:

SELECT  cntr_value/1024 as 'MBs used'
from master.dbo.sysperfinfo
where object_name = 'MSSQL$InstanceName:Memory Manager' and
   counter_name = 'Total Server Memory (KB)'

Этот запрос возвращает размер в Мегабайтах используемой SQL Server памяти. Конечно, это число может изменяться во времени. Использование System Monitor может оказаться необходимым, чтобы отследить тенденции в использовании памяти, которые можно сохранять в журнале (не рассматривающемся в этой статье).

При анализе размера используемой сервером памяти, может возникнуть вопрос: "Почему размер используемой SQL Server памяти меньше доступного максимума?". Максимизация использования памяти потребует дополнительного анализа. Меньшее чем максимальное использование памяти может быть вызвано тем, что экземпляр SQL Server работает на компьютере совместно с другими прикладными программами (что не рекомендуется). Тогда SQL Server не сможет достичь потенциально возможного максимума, т.к. будет конкурировать за ресурсы сервера.

Следующий запрос используется для возврата размера буферного кэша, кэша процедур и свободных страниц в Мбайтах для экземпляра по умолчанию. Для именованного экземпляра, не забудьте заменять 'SQLServer:Buffer' на 'MSSQL$InstanceName:Buffer Manager'.

SELECT  'Procedure
Cache Allocated',
     CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
   counter_name = 'Procedure cache pages'
UNION
SELECT  'Buffer Cache database pages',
     CONVERT(int,((CONVERT(numeric(10,2),cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
   counter_name = 'Database pages'
UNION
SELECT  'Free pages',
     CONVERT(int,((CONVERT(numeric(10,2), cntr_value)
* 8192)/1024)/1024)
as 'MBs'
from master.dbo.sysperfinfo
where object_name = 'SQLServer:Buffer Manager' and
   counter_name = 'Free pages'

Относительно результатов, возвращённых этим запросом, обратите внимание на очень высокие или низкие значения. Например, ошибка "contiguous memory" появляется, когда буферный кэш большой, а кэше процедур маленький (маленький для вашего запроса). Иногда инструкции или другие запросы пользователей могут создать ситуацию, когда кэш процедуры не может расшириться из-за того, что буферный кэш полностью использован.

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

[В начало]