Есть сервер с несколькими БД. Хочется перенести неиспользуемые на другой сервер, как понять используются ли БД и когда была обработка запросов последний раз?
Ответ
Предлагаю рассмотреть следующие варианты:
Для понимания использования БД можно воспользоваться:
Использование индексов в БД
Когда последний раз модифицировались объекты БД
Сколько было транзакций по конкретной БД с момента запуска
Для анализа активности БД:
Проанализировать планы запросов по конкретной БД
Проанализировать активность файлов БД за определённый промежуток времени
Приступим!
Поиск использования индексов в БД
SQL Server хранит статистику по обращениям к индексам на чтение и запись, в частности из представления SYS.DM_DB_INDEX_USAGE_STATS мы можем получить время последнего обращения (LAST_USER_SEEK,LAST_USER_SCAN,LAST_USER_LOOKUP) и обновления (LAST_USER_UPDATE) индекса. Перейдите в нужную БД и выполните:
SELECT
T.NAME
,USER_SEEKS
,USER_SCANS
,USER_LOOKUPS
,USER_UPDATES
,LAST_USER_SEEK
,LAST_USER_SCAN
,LAST_USER_LOOKUP
,LAST_USER_UPDATE
,modify_date
FROM
SYS.DM_DB_INDEX_USAGE_STATS I JOIN
SYS.TABLES T ON (T.OBJECT_ID = I.OBJECT_ID)
WHERE DATABASE_ID = DB_ID()
ORDER BY LAST_USER_UPDATE DESC
GO
Дата модификаций объектов
Чтобы получить дату модификации объектов в БД, необходимо перейти в нужную БД, выполнить и обратиться внимание на поле modify_date:
SELECT * FROM sys.objects ORDER BY modify_date DESC
Анализ активности по количеству выполненных транзакциях в БД
Представление sys.dm_os_performance_counters позволяем посмотреть всевозможные счётчики производительности SQL Server (сбрасывается после рестарта), один из них поможет с нашим вопросом, это счётчик Transactions/sec. Выполните следующий скрипт и вы получите информацию по всем БД:
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Transactions/sec%'
GO
Поиск планов запросов по конретной БД
Мы можем, использую ряд представлений, понять какие планы с какими БД работают. Это очень удобно, так как это позволит понять какие запросы выполняются с нашими БД (информация сбрасывается после рестарта и при вытеснении планов из кэша). Обязательно укажите название вашей БД тут WHERE pl.query_plan LIKE '%MyDb%'. Обратите внимание, что разбор планов запросов это сложная операция, поэтому запрос может выполняться долго, не выполняйте следующий запрос если ваш сервер испытывает трудности:
SELECT SUBSTRING(tx.[text],
(qs.statement_start_offset / 2) + 1,
(CASE WHEN qs.statement_end_offset =-1 THEN DATALENGTH(tx.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)
/ 2 + 1) AS QueryText,
case when pl.query_plan LIKE '%
Анализ активности файлов БД за промежуток времени
Кроме всего прочего мы можем отследить активность файлов БД за определённый промежуток времени. По-умолчанию скрипт настроен на сбор информации с момента запуска на 1 минуту, чтобы это изменить следует поправить вот тут WAITFOR DELAY '00:01:00'; . Информация собирается для всех БД:
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats1')
DROP TABLE [##SQLskillsStats1];
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats2')
DROP TABLE [##SQLskillsStats2];
GO
SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],
[num_of_writes], [io_stall_write_ms], [io_stall],
[num_of_bytes_read], [num_of_bytes_written], [file_handle]
INTO ##SQLskillsStats1
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
GO
WAITFOR DELAY '00:01:00';
GO
SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],
[num_of_writes], [io_stall_write_ms], [io_stall],
[num_of_bytes_read], [num_of_bytes_written], [file_handle]
INTO ##SQLskillsStats2
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
GO
WITH [DiffLatencies] AS
(SELECT
[ts2].[database_id],
[ts2].[file_id],
[ts2].[num_of_reads],
[ts2].[io_stall_read_ms],
[ts2].[num_of_writes],
[ts2].[io_stall_write_ms],
[ts2].[io_stall],
[ts2].[num_of_bytes_read],
[ts2].[num_of_bytes_written]
FROM [##SQLskillsStats2] AS [ts2]
LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
ON [ts2].[file_handle] = [ts1].[file_handle]
WHERE [ts1].[file_handle] IS NULL
UNION
SELECT
[ts2].[database_id],
[ts2].[file_id],
[ts2].[num_of_reads] - [ts1].[num_of_reads] AS [num_of_reads],
[ts2].[io_stall_read_ms] - [ts1].[io_stall_read_ms] AS [io_stall_read_ms],
[ts2].[num_of_writes] - [ts1].[num_of_writes] AS [num_of_writes],
[ts2].[io_stall_write_ms] - [ts1].[io_stall_write_ms] AS [io_stall_write_ms],
[ts2].[io_stall] - [ts1].[io_stall] AS [io_stall],
[ts2].[num_of_bytes_read] - [ts1].[num_of_bytes_read] AS [num_of_bytes_read],
[ts2].[num_of_bytes_written] - [ts1].[num_of_bytes_written] AS [num_of_bytes_written]
FROM [##SQLskillsStats2] AS [ts2]
LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
ON [ts2].[file_handle] = [ts1].[file_handle]
WHERE [ts1].[file_handle] IS NOT NULL)
SELECT
DB_NAME ([vfs].[database_id]) AS [DB],
LEFT ([mf].[physical_name], 2) AS [Drive],
[mf].[type_desc],
[num_of_reads] AS [Reads],
[num_of_writes] AS [Writes],
[ReadLatency(ms)] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency(ms)] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[mf].[physical_name]
FROM [DiffLatencies] AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
ORDER BY [WriteLatency(ms)] DESC;
GO
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats1')
DROP TABLE [##SQLskillsStats1];
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'##SQLskillsStats2')
DROP TABLE [##SQLskillsStats2];
GO
P.S. Есть, конечно, простой и 100% способ - отключить БД и ждать пока зазвонит телефон)
Комментариев нет:
Отправить комментарий