Как определить когда последний раз выполнялся запрос к БД?

#sql_server

Есть сервер с несколькими БД. Хочется перенести неиспользуемые на другой сервер,
как понять используются ли БД и когда была обработка запросов последний раз?
    

Ответы

Ответ 1

Предлагаю рассмотреть следующие варианты:

Для понимания использования БД можно воспользоваться:


Использование индексов в БД
Когда последний раз модифицировались объекты БД
Сколько было транзакций по конкретной БД с момента запуска


Для анализа активности БД:


Проанализировать планы запросов по конкретной БД
Проанализировать активность файлов БД за определённый промежуток времени


Приступим!

Поиск использования индексов в БД

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 '%%' then 1 else 0 end as [Missing
Indexes?],
    qs.execution_count,
  qs.total_worker_time/execution_count AS avg_cpu_time,
  qs.total_worker_time AS total_cpu_time,
  qs.total_logical_reads/execution_count AS avg_logical_reads,
  qs.total_logical_reads,
  qs.creation_time AS [plan creation time],
  qs.last_execution_time [last execution time],
  CAST(pl.query_plan AS XML) AS sqlplan
FROM    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset,
qs.statement_end_offset) AS pl
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS tx
WHERE pl.query_plan LIKE '%MyDb%'
ORDER BY execution_count DESC OPTION (RECOMPILE);
GO


Анализ активности файлов БД за промежуток времени

Кроме всего прочего мы можем отследить активность файлов БД за определённый промежуток
времени. По-умолчанию скрипт настроен на сбор информации с момента запуска на 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% способ - отключить БД и ждать пока зазвонит телефон)


Ответ 2

Если вам дали задачу на миграцию, и у вас есть время, то можно запустить аудит базы
данных на операции SELECT/UPDATE/INSERT/DELETE. 
Так вы можете отследить, какие именно пользователи проводят данные операции, чтобы
согласовать с ними переезд баз.


Ответ 3

Даты последних обращений пользователей:

select  max(last_user_update),
        max(last_user_seek),
        max(last_user_scan),
        max(last_user_lookup)
from sys.dm_db_index_usage_stats where database_id = db_id('');


Здесь описание возможных полей