Что делать если у вас много баз данных, а нужно узнать активность только одной из них

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

1. Количество подключений к вашим БД
2. Занимаемое дисковое пространство
3. Объём оперативной памяти
4. Соберём активность файлов БД за определённый промежуток времени

Количество подключений к вашим БД

Можно воспользоваться master.dbo.sysprocesses

SELECT DB_NAME(p.dbid) db, COUNT(*) quantity 
FROM master.dbo.sysprocesses p 
WHERE p.spid > 50 
group by DB_NAME(p.dbid) 
ORDER BY 1

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

Занимаемое дисковое пространство

CREATE TABLE #sizingDB (dbname nvarchar(255), type_desc nvarchar(50), size_mb bigint)

INSERT INTO #sizingDB
exec sp_msforeachdb @command1 = 'use [?]; 
SELECT DB_NAME(),type_desc, SUM(size)*8/1024 as size FROM sys.database_files 
GROUP BY type_desc'

SELECT * FROM  #sizingDB
WHERE dbname NOT IN ('master','msdb','model')
ORDER BY dbname, type_desc DESC

DROP TABLE #sizingDB

Запрос возвращает по 2 строки на каждую БД. Одна строка отражает размер данных, а другая журнала транзакций.

Можно так же воспользоваться ещё одним способом, который вернёт по 1 строке на БД (сумма файлов данных и логов)

select
    db_name(dbid), 
    sum(cast(size as bigint)) * 8 / 1024 as SizeGB,
    sum(case when f.groupid = 0 then 0 else cast(size as bigint) end) * 8 / 1024 as DataSizeMb,
    sum(case when f.groupid != 0 then 0 else cast(size as bigint) end) * 8 / 1024 as LogSizeMb
from master.sys.sysaltfiles as f
group by db_name(dbid)
order by SizeGB desc

Объём оперативной памяти

WITH AggregateBufferPoolUsage
AS
(SELECT DB_NAME(database_id) AS [Database Name],
CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [CachedSize]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id))
SELECT ROW_NUMBER() OVER(ORDER BY CachedSize DESC) AS [Buffer Pool Rank], [Database Name], CachedSize AS [Cached Size (MB)],
		CAST(CachedSize / SUM(CachedSize) OVER() * 100.0 AS DECIMAL(5,2)) AS [Buffer Pool Percent]
FROM AggregateBufferPoolUsage
ORDER BY [Buffer Pool Rank];

Столбец Buffer Pool Percent отражает процентное использование памяти от общего объёма.

Активность файлов БД

SELECT DB_NAME(saf.dbid) AS [db],
	saf.name AS [name],
	vfs.BytesRead/1048576 AS [read],
	vfs.BytesWritten/1048576 AS [write]
	INTO #dbusage
FROM master..sysaltfiles AS saf
JOIN ::fn_virtualfilestats(NULL,NULL) AS vfs ON vfs.dbid = saf.dbid AND
												vfs.fileid = saf.fileid AND
												saf.dbid NOT IN (1,3,4)
WHERE  DB_NAME(saf.dbid) <> 'tempdb'
ORDER BY vfs.BytesRead/1048576 + BytesWritten/1048576 DESC

WAITFOR DELAY '00:01:00'

SELECT DB_NAME(saf.dbid) AS [db],
	saf.name AS [name],
	vfs.BytesRead/1048576 AS [read],
	vfs.BytesWritten/1048576 AS [write]
	INTO #dbusage2
FROM master..sysaltfiles AS saf
JOIN ::fn_virtualfilestats(NULL,NULL) AS vfs ON vfs.dbid = saf.dbid AND
												vfs.fileid = saf.fileid AND
												saf.dbid NOT IN (1,3,4)
WHERE  DB_NAME(saf.dbid) <> 'tempdb'
ORDER BY vfs.BytesRead/1048576 + BytesWritten/1048576 DESC


SELECT t.db,t.name,(t2.[read] - t.[read]) as tread,(t2.[write] - t.[write]) as [twrite]	
	FROM #dbusage t INNER JOIN #dbusage2 t2 on t.db= t2.db AND t.name=t2.name
		
DROP TABLE #dbusage
DROP TABLE #dbusage2

По-умолчанию скрипт будет собирать информацию 1 минуту, если вам нужен отчёт за более продолжительное время, то измените WAITFOR DELAY ’00:01:00′. Отчёт возвращает информацию по каждому файлу БД.

Заключение

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