[大数据]SQL Server 性能优化

SET Statements
SET STATISTICS TIME
SET STATISTICS IO
SET STATISTICS PROFILE
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
redgate
启动并使用数据库引擎优化顾问
显示估计的执行计划
创建跟踪 (SQL Server Profiler)

使用DMV
使用DTA (database engine tuning advisor) 数据库引擎优化顾问
SQL Server Profiler 跟踪文件

1、查看执行时间和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、执行计划

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

5、查看【表】结构??

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

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

--查询表中已存在的索引
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)

效果图

*
*
*