<legend id='r0FgB'><style id='r0FgB'><dir id='r0FgB'><q id='r0FgB'></q></dir></style></legend>

<tfoot id='r0FgB'></tfoot>
    • <bdo id='r0FgB'></bdo><ul id='r0FgB'></ul>

    <small id='r0FgB'></small><noframes id='r0FgB'>

    <i id='r0FgB'><tr id='r0FgB'><dt id='r0FgB'><q id='r0FgB'><span id='r0FgB'><b id='r0FgB'><form id='r0FgB'><ins id='r0FgB'></ins><ul id='r0FgB'></ul><sub id='r0FgB'></sub></form><legend id='r0FgB'></legend><bdo id='r0FgB'><pre id='r0FgB'><center id='r0FgB'></center></pre></bdo></b><th id='r0FgB'></th></span></q></dt></tr></i><div id='r0FgB'><tfoot id='r0FgB'></tfoot><dl id='r0FgB'><fieldset id='r0FgB'></fieldset></dl></div>

      1. 如何判断数据库表是否正在被访问?想要像“选择触发器"这样的东西

        时间:2023-11-28
        • <tfoot id='2mAdj'></tfoot>
            <tbody id='2mAdj'></tbody>

            <small id='2mAdj'></small><noframes id='2mAdj'>

              <bdo id='2mAdj'></bdo><ul id='2mAdj'></ul>
                <legend id='2mAdj'><style id='2mAdj'><dir id='2mAdj'><q id='2mAdj'></q></dir></style></legend>

                  <i id='2mAdj'><tr id='2mAdj'><dt id='2mAdj'><q id='2mAdj'><span id='2mAdj'><b id='2mAdj'><form id='2mAdj'><ins id='2mAdj'></ins><ul id='2mAdj'></ul><sub id='2mAdj'></sub></form><legend id='2mAdj'></legend><bdo id='2mAdj'><pre id='2mAdj'><center id='2mAdj'></center></pre></bdo></b><th id='2mAdj'></th></span></q></dt></tr></i><div id='2mAdj'><tfoot id='2mAdj'></tfoot><dl id='2mAdj'><fieldset id='2mAdj'></fieldset></dl></div>
                  本文介绍了如何判断数据库表是否正在被访问?想要像“选择触发器"这样的东西的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我有一个包含数百个表的非常大的数据库,经过多次产品升级后,我确信其中一半不再使用.如何判断是否正在主动选择表?我不能只使用 Profiler - 我不仅要观察几天以上,而且还有数千个存储过程,而且 Profiler 不会将 SP 调用转换为表访问调用.

                  I have a very large database with hundreds of tables, and after many, many product upgrades, I'm sure half of them aren't being used anymore. How can I tell if a table is is actively being selected from? I can't just use Profiler - not only do I want to watch for more than a few days, but there are thousands of stored procedures as well, and profiler won't translate the SP calls into table access calls.

                  我唯一能想到的就是在感兴趣的表上创建一个聚簇索引,然后监控sys.dm_db_index_usage_stats看是否有对聚簇索引的seek或scans,表示已加载表中的数据.但是,在每个表上添加聚集索引是一个坏主意(出于多种原因),因为这实际上并不可行.

                  The only thing I can think of is to create a clustered index on the tables of interest, and then monitor the sys.dm_db_index_usage_stats to see if there are any seeks or scans on the clustered index, meaning that data from the table was loaded. However, adding a clustered index on every table is a bad idea (for any number of reasons), as isn't really feasible.

                  我还有其他选择吗?我一直想要像SELECT 触发器"这样的功能,但可能还有其他原因导致 SQL Server 也没有该功能.

                  Are there other options I have? I've always wanted a feature like a "SELECT trigger", but there are probably other reasons why SQL Server doesn't have that feature either.

                  解决方案:

                  感谢 Remus,为我指明了正确的方向.使用这些列,我创建了以下 SELECT,这正是我想要的.

                  Thanks, Remus, for pointing me in the right direction. Using those columns, I've created the following SELECT, which does exactly what I want.

                    WITH LastActivity (ObjectID, LastAction) AS 
                    (
                         SELECT object_id AS TableName,
                                last_user_seek as LastAction
                           FROM sys.dm_db_index_usage_stats u
                          WHERE database_id = db_id(db_name())
                          UNION 
                         SELECT object_id AS TableName,
                                last_user_scan as LastAction
                           FROM sys.dm_db_index_usage_stats u
                          WHERE database_id = db_id(db_name())
                          UNION
                         SELECT object_id AS TableName,
                                last_user_lookup as LastAction
                           FROM sys.dm_db_index_usage_stats u
                          WHERE database_id = db_id(db_name())
                    )
                    SELECT OBJECT_NAME(so.object_id) AS TableName,
                           MAX(la.LastAction) as LastSelect
                      FROM sys.objects so
                      LEFT
                      JOIN LastActivity la
                        on so.object_id = la.ObjectID
                     WHERE so.type = 'U'
                       AND so.object_id > 100
                  GROUP BY OBJECT_NAME(so.object_id)
                  ORDER BY OBJECT_NAME(so.object_id)
                  

                  推荐答案

                  查看 sys.dm_db_index_usage_stats.last_user_xxx 列将包含上次从用户请求访问表的时间.此表会在服务器重启后重置其跟踪,因此您必须让它运行一段时间,然后才能依赖其数据.

                  Look in sys.dm_db_index_usage_stats. The columns last_user_xxx will contain the last time the table was accessed from user requests. This table resets its tracking after a server restart, so you must leave it running for a while before relying on its data.

                  这篇关于如何判断数据库表是否正在被访问?想要像“选择触发器"这样的东西的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:将信用卡信息存储到数据库中的最佳实践 下一篇:包含 8000 万条记录的表并添加索引需要超过 18 小时(或永远)!怎么办?

                  相关文章

                  <tfoot id='2tCGQ'></tfoot>

                      <bdo id='2tCGQ'></bdo><ul id='2tCGQ'></ul>

                    1. <legend id='2tCGQ'><style id='2tCGQ'><dir id='2tCGQ'><q id='2tCGQ'></q></dir></style></legend>
                    2. <small id='2tCGQ'></small><noframes id='2tCGQ'>

                    3. <i id='2tCGQ'><tr id='2tCGQ'><dt id='2tCGQ'><q id='2tCGQ'><span id='2tCGQ'><b id='2tCGQ'><form id='2tCGQ'><ins id='2tCGQ'></ins><ul id='2tCGQ'></ul><sub id='2tCGQ'></sub></form><legend id='2tCGQ'></legend><bdo id='2tCGQ'><pre id='2tCGQ'><center id='2tCGQ'></center></pre></bdo></b><th id='2tCGQ'></th></span></q></dt></tr></i><div id='2tCGQ'><tfoot id='2tCGQ'></tfoot><dl id='2tCGQ'><fieldset id='2tCGQ'></fieldset></dl></div>