1.查看执行时间和cpu占用时间
set statistics time on
select * from dbo.Product
set statistics time off
打开你查询之后的消息里面就能看到啦。
2.查看查询对I/0的操作情况
set statistics io on
select * from dbo.Product
set statistics io off
执行之后
扫描计数：索引或表扫描次数
逻辑读取：数据缓存中读取的页数
物理读取：从磁盘中读取的页数
预读：查询过程中，从磁盘放入缓存的页数
lob逻辑读取：从数据缓存中读取，image，text，ntext或大型数据的页数
lob物理读取：从磁盘中读取，image，text，ntext或大型数据的页数
lob预读：查询过程中，从磁盘放入缓存的image，text，ntext或大型数据的页数
如果物理读取次数和预读次说比较多，可以使用索引进行优化。
如果你不想使用sql语句命令来查看这些内容，方法也是有的，哥教你更简单的。
查询—>>查询选项—>>高级
被红圈套上的2个选上，去掉sql语句中的set statistics io/time on/off 试试效果。哦也，你成功啦。。
3.查看执行计划
选中查询语句，点击
然后看消息里面，会出现下面的图例
首先我这个例子的语句太过简单，你整个复杂的，包涵啊。
分析：鼠标放在图标上会显示此步骤执行的详细内容，每个表下面都显示一个开销百分比，分析站百分比多的的一块，可以根据重新设计数据结构，或这重写sql语句，来对此进行优化。如果存在扫描表，或者扫描聚集索引，这表示在当前查询中你的索引是不合适的，是没有起到作用的，那么你就要修改完善优化你的索引，具体怎么做，你可以根据我上一篇文章中的sql优化利器–数据库引擎优化顾问对索引进行分析优化。
方法2.
SET SHOWPLAN_ALL ON;
4.sql server的优化建议
Microsoft SQL Server 2008 >> 工具 >> SQL Server Profiler。
然后选择文件 >> 新建 >> 跟踪打开一个连接窗口，选择将要跟踪的服务器实例然后连接。打开如下“跟踪属性”对话框。
如果有许多跟踪，可以提供一个跟踪名称来帮助在以后进行分类。不同的跟踪模板可帮助建立用于不同目的的跟踪。
打开跟踪属性窗口后，单击“事件选择”选项卡，为跟踪提供更详细的定义。
4、使用多个文件
在大多数情况下，小型的数据库并不需要创建多个文件来存放数据。但是随着数据的增长，单个文件的弊端就会出现。
– 首先，使用多个文件分布到不同的磁盘分区(多个硬盘)能够几大提高IO性能。
– 其次，多个文件对于数据比较多的数据库来说，备份和恢复都会方便。
– 但是，多文件需要占用更多的磁盘空间，因为每个文件中都有自己的一套B树组织方式和自己的增长空间。当然也有自己的碎片。
– 总体来说，多个文件带来的优点是远远大于弊端的。
5. 维护语句
–日志收缩为1M
USE [数据库名];
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE;
DBCC SHRINKFILE ([数据库名_log], 1);
ALTER DATABASE [数据库名] SET RECOVERY FULL;
6. 维护计划
点击工具栏，拖动到空白区域，如果有上一个任务，可以拖动箭头指向新添加的任务
6.1. 执行T-SQL语句
USE [Sogal.YHZS];
— 压缩日志到1MB
ALTER DATABASE [Sogal.YHZS] SET RECOVERY SIMPLE;
DBCC SHRINKFILE ([Sogal.YHZS_log], 1);
ALTER DATABASE [Sogal.YHZS] SET RECOVERY FULL;
— 清除错误日志
EXEC sp_cycle_errorlog;
6.2.
备份数据库
“设置备份压缩” >> “压缩备份”，会比普通备份再RAR的压缩率低一些，不过可以自动进行，测试过普通压缩16g使用后未2G
网上资料如下可参考
6.3.
清除历史记录
PS：
需要开启Sql sever Agent（sql server代理）的服务，并设置为开机自动启动
问题2：在 sys.database_files 中找不到数据库 ‘XXX’ 的文件 ‘[XXX_3_log]’。该文件不存在或者已被删除。
分析：一定是从某个原始库backup然后restore过来的.这种情况下XXX的日志的逻辑文件名不一定叫’XXX_3_log’,不信,你可以
方法1：select type_desc,name from sys.master_files WHERE database_id=db_id(‘XXX’);
方法2：use XXX
select * from sys.database_files
可以看’name’列的输出,然后收缩那个名称里的日志文件就可以了.
一般restore后,如果库名和以前不一样
解决方法：
方法1：改为正确的日志逻辑名称
方法2：最好改下数据库和日志的2个逻辑文件名,保持后新明称一致.
alter database xhtest modify file(name=xhtest_old, newname=xhtest)
alter database xhtest modify file(name=xhtest_old_log, newname=xhtest_log)
6. Log文件夹中的文件过多
C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL
7. 死锁
— 查找死锁的进程
select
request_session_id spid,
OBJECT_NAME(resource_associated_entity_id) tableName
from
sys.dm_tran_locks
where
resource_type=’OBJECT’
— 干掉死锁的进程
kill 53
— 查询该会话正在执行的Sql语句
DBCC INPUTBUFFER (53)
资料：https://www.cnblogs.com/OpenCoder/p/5557514.html
— SQL Server Profiler查看死锁
Microsoft SQL Server 2008 >> 工具 >> SQL Server Profiler
>> 文件 >> 模板 >> 导入模板 >> 点击开始监控
Ctrl + F 查找“deadlock”找到死锁记录
8. 查询耗时较长的语句
SELECT creation_time N’语句编译时间’
,last_execution_time N’上次执行时间’
,total_physical_reads N’物理读取总次数’
,total_logical_reads/execution_count N’每次逻辑读次数’
,total_logical_reads N’逻辑读取总次数’
,total_logical_writes N’逻辑写入总次数’
, execution_count N’执行次数’
, total_worker_time/1000 N’所用的CPU总时间ms’
, total_elapsed_time/1000 N’总花费时间ms’
, (total_elapsed_time / execution_count)/1000 N’平均时间ms’
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
– qs.statement_start_offset)/2) + 1) N’执行语句’
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
– qs.statement_start_offset)/2) + 1) not like ‘%fetch%’
AND last_execution_time <‘20170519 17:35’
ORDER BY total_elapsed_time / execution_count DESC;
