T3 MSSQL自动优化 让你的数据库更加安全高效

1. 简介

微软的SQL Server数据库管理系统具有强大的自动优化功能，可以帮助数据库管理员加快性能并优化数据库。在过去的几个版本中，SQL Server使用了自动优化的技术。在SQL Server 2017中，Microsoft引入了一种新的自动优化技术，称为Query Store。Query Store允许管理员跟踪查询的性能，监控查询的历史以及恢复查询计划。

2. Query Store的功能

2.1 Query Store的概述

Query Store是一个用来跟踪执行计划的功能，它可以捕获系统内执行的所有查询（包括存储过程），确保查询计划在执行时保持一致。此外，Query Store还可以跟踪例如执行时间、CPU 周期、逻辑读取次数等各种指标。

2.2 Query Store的好处

Query Store能够帮助管理员更好地了解数据库性能问题的根本原因。当管理员检测到执行慢查询时，Query Store可以快速确定执行计划是否有问题。同时还可以比较计划之间的性能、对执行计划进行监控和更改或恢复之前的计划。

2.3 开启Query Store

Query Stroe是可以自动开启的，但是需要手动配置才可以开启所有的功能，包括数据颗粒度、数据保留期和数据重置策略。以下是示例代码，可以启用Query Store：

ALTER DATABASE [database_name] SET QUERY_STORE = ON;

3. Query Store的使用

3.1 查询正在执行的查询

通过查询Query Store的视图，您可以轻松地查看正在执行的查询。例如，以下查询将显示Query Store中的所有查询：

SELECT * FROM query_store_query;

3.2 查询最慢查询

查询最慢查询可以让您找到使用最长时间的查询。下面的查询将返回执行时间最长的查询：

SELECT TOP 10 
        rs.avg_duration 
        ,rs.last_execution_time
        ,q.query_id
        ,q.query_sql_text
FROM    sys.query_store_query_text AS qt
        JOIN sys.query_store_query AS q
            ON q.query_text_id = qt.query_text_id
        JOIN sys.query_store_runtime_stats AS rs
            ON rs.query_id = q.query_id
ORDER BY rs.avg_duration DESC;

3.3 查询分析

Query Store不仅可以帮助您找到执行缓慢的查询，而且还可以帮助您找到哪些查询计划或查询自身具有问题。下面是一个示例查询，该查询将查找Query Store中使用不同计划的查询：

SELECT 
        q.query_id
        ,qt.query_sql_text
        ,q.query_parameterization_type_desc
        ,qp.query_plan_id
        ,qt.query_text_id
        ,qs.last_execution_time
        ,qs.count_executions
        ,qs.avg_duration
        ,qs.max_duration
FROM    sys.query_store_query_text AS qt
        JOIN sys.query_store_query AS q
            ON q.query_text_id = qt.query_text_id
        JOIN sys.query_store_query_parameterization AS qp
            ON qp.query_id = q.query_id
        JOIN sys.query_store_query_plan AS qp1
            ON qp1.query_id = qp.query_id
            AND qp1.plan_id = qp.plan_id
        JOIN sys.query_store_runtime_stats AS qs
            ON qs.query_id = q.query_id
WHERE   qt.query_sql_text LIKE '%/employee/%'
        AND qs.last_execution_time >= '2018-01-01'
        AND qs.last_execution_time < '2019-01-01'
        AND q.query_parameterization_type_desc = 'Simple'
        AND qp.query_parameterization_type_desc = 'Simple'
ORDER BY q.query_id
        ,qs.last_execution_time;

4. 结论

通过使用SQL Server的Query Store功能，管理员可以轻松地监视数据库中正在执行的查询。查询故障排除和调优变得更加容易，同时也可以提高数据库的性能和可靠性。管理员还可以在任何时候回滚更改，并通过可对可视化方案检索恶意或异常查询来改善安全性。