主页 / 问题 / 718324
Accepted
Serge Nazarenko
Asked:2020-09-14 18:32:07 +0000 UTC2020-09-14 18:32:07 +0000 UTC

如何确定上次执行数据库查询的时间是什么时候？

  • 772

有一个带有多个数据库的服务器。我想将未使用的转移到另一台服务器，如何了解数据库是否被使用以及上次查询处理是什么时候？

sql-server

4 个回答

  1. Best Answer

    我建议考虑以下选项：

    要了解数据库的使用，可以使用：

    1. 使用数据库中的索引
    2. 上次修改数据库对象是什么时候？
    3. 自启动以来特定数据库上有多少事务

    分析数据库活动：

    1. 分析特定数据库的查询计划
    2. 分析一段时间内数据库文件的活动

    让我们开始吧！

    查找数据库中索引的使用

    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 '%<MissingIndexes>%' 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

    PS当然有一个简单且100%的方法——关闭数据库，等到电话响起）

    • 9

  2. 如果您已获得迁移任务并且您有时间，则可以对 SELECT/UPDATE/INSERT/DELETE 操作运行数据库审计。因此，您可以准确跟踪哪些用户执行了这些操作，以便与他们协调基地的搬迁。

    • 2

  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('<dbname>');

    这是可能字段的描述

    • 1

  4. 您还可以使用此查询（一次所有数据库）：

    SELECT login_name, max(login_time) as last_logged_in, d.name as dbName
FROM sys.dm_exec_sessions s right outer join  sys.databases d on s.database_id = d.database_id 
where d.database_id > 4 -- т.е. кроме системных
GROUP BY s.login_name, d.name
order by dbNAme

    其中 login_name 和 last_logged_in 为空，表示它们从未被访问过。

    • 0

相关问题