SQL Server 性能优化 SQL Server Profiler 数据库引擎优化顾问

SET Statements                SET 语句设置局部变量的信息【推荐】
SET STATISTICS TIME    显示分析、编译和执行各SQL语句所需的毫秒数【显示SQL语句执行时间】
SET STATISTICS IO         显示SQL语句所生成的物理和逻辑 IO 活动量的相关信息

SET SHOWPLAN_ALL     不执行SQL语句，而是 SQL Server 返回有关执行语句（查询计划）的详细信息，并提供对语句和预期行的资源需求的估计（基数估计）
SET SHOWPLAN_TEXT  不执行SQL语句，而是 SQL Server 返回有关执行语句的详细信息
SET SHOWPLAN_XML    不执行SQL语句，而是 SQL Server 返回有关以定义好的 XML 文档格式执行上述语句的详细信息

SET STATISTICS PROFILE  显示语句的配置文件信息，对即席查询、视图和存储过程有效
redgate
启动并使用数据库引擎优化顾问
启动数据库引擎优化顾问
第 1 课：数据库引擎优化顾问中的基本导航 (DTA)
第 2 课：使用数据库引擎优化顾问​​​​
显示估计的执行计划
创建跟踪 (SQL Server Profiler)

使用DMV
使用DTA (database engine tuning advisor) 数据库引擎优化顾问

SQL Server Profiler 监控 跟踪文件

监控 慢SQL  
第 1 步：【常规】选项卡
跟踪名称(T):  无标题-1
使用模板(U): Standard (default)

第 2 步：【事件选择】选项卡
Stored Procedures【存储过程】
RPC:Completed 事件类  
TSQL【SQL语句】
SQL:BatchCompleted 事件类
SQL:BatchStarting 事件类

第 3 步：【事件选择】选项卡【列筛选器】
TextDate：包含执行的SQL语句或存储过程的文本
CPU：       事件使用的 CPU 时间(毫秒)
Duration：事件占用的时间，sql耗时
DatabaseID：数据库的ID【select DB_ID()，select database_id from sys.databases where name = 'dbTest'】
DatabaseName：正在执行SQL语句的数据库名称

设置如下：
TextDate 类似于 select
Duration 大于等于 5000 毫秒
DatabaseID         数值 1
DatabaseName   数据库名称 dbTest

第 4 步：选中【显示所有列】
DatabaseName 设置为选中状态

第 5 步：点击【运行】按钮

第 6 步：执行测试的sql语句，查看追踪器信息

declare @pageIndex int = 1
declare @pageSize int = 100

select i.Id,i.Amount,i.UserName 
from table_name i
where i.Amount> 0
order by i.Id asc 
offset (@pageIndex-1) * @pageSize rows 
fetch next @pageSize rows only
go

1、查看SQL语句执行时间和cpu，显示分析、编译和执行各语句所需的毫秒数。

-- 语法
-- SET STATISTICS TIME { ON | OFF } 

-- 实例
set statistics time on
go

select * from sys_user
go

set statistics time off
go

执行后在【消息】里可以看到

2、查看查询对I/O的操作情况

-- 语法
-- SET STATISTICS IO { ON | OFF }    

-- 实例
set statistics io on
go

select * from sys_user 
go

set statistics io off
go

执行之后的结果

3、set statistics profile { on | off }

-- 语法
-- SET STATISTICS PROFILE { ON | OFF } 

-- 实例
set statistics profile on
go

select * from sys_user 
go

set statistics profile off
go

新建查询 -> 查询 -> 查询选项 -> 高级 -> 【SET STATISTICS TIME、SET STATISTICS IO】

消息面板内容

4、执行计划 

鼠标悬浮到图标上会显示此步骤执行的详细内容。

执行计划【SHOWPLAN_TEXT】

SET SHOWPLAN_TEXT ON;
GO

SELECT i.Id,i.Amount,i.AvailableAmount,i.UserName 
FROM Test as i
WHERE i.AvailableAmount > 0
ORDER BY i.CheckTime
OFFSET 1 ROWS 
FETCH NEXT 10 ROWS ONLY
GO

SET SHOWPLAN_TEXT OFF;
GO

执行计划【SHOWPLAN_XML】

SET SHOWPLAN_XML ON;
GO

SELECT i.Id,i.Amount,i.AvailableAmount,i.UserName 
FROM Test as i
WHERE i.AvailableAmount > 0
ORDER BY i.CheckTime
OFFSET 1 ROWS 
FETCH NEXT 10 ROWS ONLY
GO

SET SHOWPLAN_XML OFF;
GO

执行计划【SHOWPLAN_ALL】

SET SHOWPLAN_ALL ON;
GO

SELECT i.Id,i.Amount,i.AvailableAmount,i.UserName 
FROM Test as i
WHERE i.AvailableAmount > 0
ORDER BY i.CheckTime
OFFSET 1 ROWS 
FETCH NEXT 10 ROWS ONLY
GO

SET SHOWPLAN_ALL OFF;
GO

5、查看表的磁盘、碎片情况

-- 使用sp_spaceused存储过程，查看表的磁盘使用情况
EXEC sp_spaceused 'table_name';

-- 检查表的碎片情况
DBCC SHOWCONTIG('table_name');

-- 碎片整理索引
-- DBCC INDEXDEFRAG('database_name', 'table_name', 'index_name');
 
-- 重建索引
-- DBCC DBREINDEX('table_name', 'index_name');

6、查看【表】结构  

select
--表名 = case when a.colorder=1 then d.name else '' end,
--表说明 = case when a.colorder=1 then isnull(f.value, '') else '' end,
--序号 = a.colorder,
列名 = a.name
--,数据类型 = b.name
,数据类型 = b.name + '(' + CONVERT(VARCHAR, COLUMNPROPERTY(a.id, a.name, 'PRECISION')) + ')'
--,长度 = COLUMNPROPERTY(a.id,a.name, 'PRECISION')
--,小数位 = isnull(COLUMNPROPERTY(a.id,a.name, 'Scale'), 0)
--,标识 = case when COLUMNPROPERTY(a.id,a.name, 'IsIdentity') = 1 then '是' else '' end
--,主键 = case when exists(select 1 from sysobjects where xtype = 'PK' and parent_obj = a.id and name in (
--           select name from sysindexes where indid in(select indid from sysindexkeys where id = a.id and colid=a.colid))) then '是' else '' end
--,占用字节数 = a.length
,允许空 = case when a.isnullable = 1 then '是' else '否' end
--,默认值 = isnull(e.text, '')
,说明 = isnull(g.[value], '')
from syscolumns a
left join systypes b on a.xusertype = b.xusertype
inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'dtproperties'
left join syscomments e on a.cdefault = e.id
left join sys.extended_properties g on a.id = G.major_id and a.colid = g.minor_id
left join sys.extended_properties f on d.id = f.major_id and f.minor_id = 0
where d.name = 'sys_user'              --表名
order by a.id,a.colorder

7、查看【索引】使用情况

--查询表中已存在的索引
exec sp_helpindex sys_user

-- 效果1
select db_name(database_id) as N'dbHuobi',             --数据库名
object_name(a.object_id) as N'TestTable',              --表名
b.name N'索引名称',
user_seeks N'用户索引查找次数',
user_scans N'用户索引扫描次数',
last_user_seek N'最后查找时间',
last_user_scan N'最后扫描时间',
rows as N'表中的行数'
from sys.dm_db_index_usage_stats a 
join sys.indexes b on a.index_id = b.index_id and a.object_id = b.object_id
join sysindexes c on c.id = b.object_id
where database_id=db_id('dbHuobi')                     --数据库名
and object_name(a.object_id) not like 'sys%'
and object_name(a.object_id) like 'TestTable'          --表名
and b.name like 'IX_TestTable_TestCol1'                --索引名
order by user_seeks,user_scans,object_name(a.object_id)
GO

-- 效果2
select db_name(database_id) as N'dbHuobi'              --数据库名
, object_name(a.object_id) as N'TestTable'             --表名
, b.name N'索引名称'
, user_seeks N'用户索引查找次数'
, user_scans N'用户索引扫描次数'
, max(last_user_seek) N'最后查找时间'
, max(last_user_scan) N'最后扫描时间'
, max(rows) as N'表中的行数'
from sys.dm_db_index_usage_stats a
join sys.indexes b on a.index_id = b.index_id and a.object_id = b.object_id
join sysindexes c on c.id = b.object_id
where database_id=db_id('dbHuobi')                     --数据库名
and object_name(a.object_id) not like 'sys%'
and object_name(a.object_id) like 'TestTable'          --表名
and b.name is not null
and b.name like 'IX_TestTable_TestCol1'                --索引名
group by db_name(database_id),object_name(a.object_id),b.name,user_seeks,user_scans
order by user_seeks,user_scans,object_name(a.object_id)

效果图

*
8、EF Core 抓取 Sql 语句
管理 NuGet 程序包(N)...

Microsoft.Extensions.Logging.Console

SqlDbContext.cs 文件

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace Web
{
    public class SqlDbContext : DbContext
    {
        public SqlDbContext(DbContextOptions<SqlDbContext> options)
            : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(LoggerFactory.Create(builler=> 
            {
                builler.AddConsole();
            }));
        }

        public virtual DbSet<V_Dict> Dict { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<V_Dict>(entity =>
            {
                entity.HasNoKey();
                entity.ToView("V_Dict");
            });
        }
    }
}

命令行窗口

输出窗口

*
*
*