sqlnexus

1）打开sql profiler，设置跟踪属性（启动所选跟踪之前设置）

SQL Server Profiler怎么跟踪指定数据库标识ID：记得勾选“显示所有列” ，参考文档如下：https://jingyan.baidu.com/article/647f0115be128a7f2048a87d.html

2）导出监控脚本

3）运行生成SQL脚本，设置监控信息生成的文件路径、和文件大小设置（maxfilesize默认是5M）

4）运行要测试的SQL脚本

use [LVDU_MDM]
go

select * from MDM_User where status=1--运行2次

select * from MDM_User where UserID in --运行2次
(
select UserID from MDM_User where Status=1
)

5）查看并关闭trace

--查看所有的trace
select * from sys.traces
--设置trace的状态 0代表停止，1代表开启，2代表删除，第一个参数代表traceid
sp_trace_setstatus 3,0--第一个参数是id，第二个是satus；先停止，再删除
go
--sp_trace_setstatus 2,1
--go
sp_trace_setstatus 3,2
go

6）打开sqlnexus，连接本地sql数据库(记得一定要用Windows Authentication)

7）导入监控文件

因为我们之前导出的监控文件（F:\install\sql\sqknexus\test\test20190122.trc）权限问题，我们将其拷贝到（F:\install\sql\sqknexus\result\test20190122.trc）

导入之后将生成sqlnexus数据库，如果本地已经有sqlnexus数据请删除

8）查看SQL脚本资源占用情况

use [sqlnexus]
go

SELECT * FROM ReadTrace.tblBatches
WHERE HashID='-494636349365297807'

通过SQL脚本分析数据

use [sqlnexus]
GO

--访问次数
SELECT HashID,COUNT(1) FROM ReadTrace.tblBatches 
GROUP BY HashID
ORDER BY COUNT(1) DESC

SELECT * FROM ReadTrace.tblBatches WHERE HashID='3340643938623684439'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='7461636766371616973'

--Total Reads
SELECT * FROM 
(
	SELECT HashID,SUM(Reads) Reads FROM ReadTrace.tblBatches 
	GROUP BY HashID
) tbl1 ORDER BY tbl1.Reads DESC 

SELECT SUM(Reads) FROM ReadTrace.tblBatches  WHERE HashID='-6680791079876588902'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='-6680791079876588902'
SELECT SUM(Reads) FROM ReadTrace.tblBatches  WHERE HashID='7461636766371616973'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='7461636766371616973'

--Total Writes
SELECT * FROM 
(
	SELECT HashID,SUM(Writes) Writes FROM ReadTrace.tblBatches 
	GROUP BY HashID
) tbl1 ORDER BY tbl1.Writes DESC 

SELECT SUM(Writes) FROM ReadTrace.tblBatches  WHERE HashID='6096396667503197959'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='6096396667503197959'
SELECT SUM(Writes) FROM ReadTrace.tblBatches  WHERE HashID='-6680791079876588902'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='-6680791079876588902'

--Total Duration
SELECT * FROM 
(
	SELECT HashID,SUM(Duration) Duration FROM ReadTrace.tblBatches 
	GROUP BY HashID
) tbl1 ORDER BY tbl1.Duration DESC 

SELECT SUM(Duration) FROM ReadTrace.tblBatches  WHERE HashID='-6680791079876588902'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='-6680791079876588902'
SELECT SUM(Duration) FROM ReadTrace.tblBatches  WHERE HashID='7461636766371616973'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='7461636766371616973'


--Total CPU
SELECT * FROM 
(
	SELECT HashID,SUM(CPU) CPU FROM ReadTrace.tblBatches 
	GROUP BY HashID
) tbl1 ORDER BY tbl1.CPU DESC 

SELECT SUM(CPU) FROM ReadTrace.tblBatches  WHERE HashID='-6680791079876588902'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='-6680791079876588902'
SELECT SUM(CPU) FROM ReadTrace.tblBatches  WHERE HashID='7461636766371616973'
SELECT * FROM ReadTrace.tblBatches WHERE HashID='7461636766371616973'