主页 / dba / 问题 / 343342
Accepted
Aleksey Vitsko
Asked: 2024-10-31 23:02:38 +0800 CST

如何找出 SELECT 和 UPDATE 语句查询表中的哪些列

  • 772

我们有一张包含 54 列的表，它被许多应用程序、报告和用户使用。该表来自旧系统，某些列可能已过时。

我们想要找出查询实际使用了哪些列SELECT。UPDATE如何找出答案？

如果可能的话，我们希望获得以下格式：

Column1 - SELECT - 1,890,123 times 
Column1 - UPDATE - 2,394 times 
Column2 - SELECT - 5,222,456 times 
Column2 - UPDATE - 567 times 
Column3...

一个想法是扫描所有 GIT 存储库（应用程序源代码）以查找这些列，但有些应用程序实际上可能不在 GIT 中。而且报告也不在 GIT 中。

以上研究是为了：

  1. 找出根本没有查询的列，并可能将其删除。
  2. 要找出经常查询但未更新的列，请查看创建非聚集列存储索引的可能性。
sql-server

1 个回答

  1. Best Answer

    您可以在可能包含引用此表的查询的每个数据库上启用查询存储。例如，表 Post 在 StackOverflow 数据库中查询，但也可以在其他数据库中查询。

    一旦启用，使用查询存储视图按查询类型（例如，SELECT 和 UPDATE）计算每列的执行次数。

    我使用 sp_MSforeachdb 查询每个数据库中的查询存储视图。

    该代码示例针对的是 StackOverflow 的 Posts 表。您可以根据自己的情况进行修改。

    /** SET UP THE TABLE #ColumnsToSearch**/
DROP TABLE IF EXISTS #ColumnsToSearch
CREATE TABLE #ColumnsToSearch
(
    column_ varchar(50)
)

INSERT INTO #ColumnsToSearch
VALUES
('Id'),
('AcceptedAnswerId'),
('AnswerCount'),
('Body'),
('ClosedDate'),
('CommentCount'),
('CommunityOwnedDate'),
('CreationDate'),
('FavoriteCount'),
('LastActivityDate'),
('LastEditDate'),
('OnwerUserId'),
('ParentId'),
('PostTypeId'),
('Score'),
('Tags'),
('Title')

/** SET UP THE TABLE #ResultsByDatabase**/
DROP TABLE IF EXISTS #ResultsByDatabase
CREATE TABLE #ResultsByDatabase
(
    db VARCHAR(50),
    column_ varchar(50),
    QueryType VARCHAR(10),
    count_executions bigint
);

/**COLLECT BY DATABASE**/
DECLARE @sql AS VARCHAR(MAX);

SET @sql
= '
USE [?];
INSERT INTO #ResultsByDatabase
SELECT 
    DB_NAME(),
    column_,
    QueryType,
    sum(count_executions) AS count_executions
FROM 
(
    SELECT c.column_,
        CASE WHEN query_sql_text LIKE ''%SELECT%'' THEN ''SELECT'' ELSE ''UPDATE'' END AS QueryType,
        qsrs.count_executions 
    FROM sys.query_store_query_text qsqt
    INNER JOIN sys.query_store_query qsq
        ON qsqt.query_text_id = qsq.query_text_id
    INNER JOIN sys.query_store_plan qsp
        ON qsq.query_id = qsp.query_id
    INNER JOIN sys.query_store_runtime_stats qsrs
        ON qsp.plan_id = qsrs.plan_id
    INNER JOIN #ColumnsToSearch c
        ON query_sql_text LIKE ''%''+c.column_ +''%'' -- filter queries contain the columns of the tables
    WHERE 
        query_sql_text LIKE ''%Posts%'' -- filter queries contain the table name
        AND (query_sql_text LIKE ''%SELECT%'' OR query_sql_text LIKE ''%UPDATE%'') -- filter queries contain the SELECT or UPDATE keywords
) x
GROUP BY column_,
    QueryType'

EXEC sp_MSforeachdb @sql

/**FINAL RESULT**/
SELECT 
    column_,
    QueryType,
    SUM(count_executions) count_executions
FROM #ResultsByDatabase
GROUP BY column_,
    QueryType

    返回的结果将如下所示：

    查询结果显示列、查询类型（选择或更新）和执行计数

    您可以配置查询存储 - 在收集执行计数之前将数据保留更长时间，以涵盖罕见的执行。例如，在查询查询存储视图并对表做出任何决策之前，收集并保留一年的数据。

    查询存储配置

    • 4

相关问题