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

Если вы хотите получить представление о планах и их статистике использования в SQL Server, существуют различные подходы в зависимости от версии SQL Server, которую вы используете.

SQL Server 2000 и более ранние версии

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

USE Master;
GO
SELECT UseCounts, RefCounts, CacheObjtype, ObjType, DB_NAME(dbid) as DatabaseName, SQL
FROM syscacheobjects
ORDER BY dbid, usecounts DESC, objtype;
GO

SQL Server 2005 и более поздние версии

Начиная с SQL Server 2005, были введены динамические представления управления (DMV), которые предоставляют более полную информацию о кэше планов. Чтобы получить содержимое кэша планов вместе со статистикой их использования в SQL Server 2005 и выше, вы можете использовать следующий скрипт DMV:

USE Master;
GO
SELECT UseCounts, RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
ORDER BY dbid, usecounts DESC;
GO

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

Очистка кэша планов

Во время тестирования или устранения неполадок может потребоваться очистить кэш планов. SQL Server предоставляет две команды для этой цели:

DBCC FREEPROCCACHE;
GO

Эта команда очищает весь кэш планов в SQL Server.

DBCC FLUSHPROCINDB (<dbid>);
GO

Эта команда очищает кэш планов для конкретной базы данных. Замените <dbid> на идентификатор базы данных, для которой вы хотите очистить кэш планов. Идентификатор базы данных можно найти с помощью следующей команды:

SELECT dbid FROM sysdatabases WHERE name = '<DBName>';

Важно отметить, что отсоединение базы данных, обновление базы данных до уровня совместимости 90 или выше, выполнение некоторых команд ALTER DATABASE или удаление базы данных также приведет к очистке всего кэша планов и потребует генерации новых планов.

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