集腋成裘-11-sql性能优化

SQL Nexus是一个用于将SQL Trace数据、性能监视日志及T-SQL输出整合进一个单独的SQL Server数据库的工具。

先决条件

开始使用SQL Nexus之前，注意下面要做的事项：

安装Microsoft Report Viewer for SQL Server 2016 MSI控件，以便使用工具自带的客户端报表查看整合的报告；
安装Microsoft RML Utilities for SQL Server，以便SQL Nexus导入SQL Traces；
安装Microsoft System CLR Types for SQL Server 2016；
核实安装.NET Framework 4.7

一：问题定位

1：通过SQLserver自带的工具SQL Server Profiler

2：选择要监控的事件

3：导出执行的脚本，将任务置于后台执行，提高效率

4：执行导出的脚本，目的是开启监控

/****************************************************/
/* Created by: SQL Server 2012  Profiler          */
/* Date: 2019/01/16  21:18:17         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'd:\a\logTrace', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 45caddf4-8520-4a3a-88c9-1afd4251fc0b'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

开启监控（Trace）的脚本

5：检查步骤4执行的效果。

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

View Code

二、使用sqlnexus统计数据

1：连接服务器，为了方便把一中5的日志记录创建到该服务器的数据库

2：导入文本

3：主要看以下几个指标

三、提取并且优化sql

（1）sql语句参数化，或将其修改为存储过程。

理由1：未参数化的sql除非每次执行的语句完全一致否则每一次执行都将会先去执行编译，下面将展示此过程

 --步骤1：在Manage studio当中打开统计功能
SET STATISTICS IO ON --展示IO读写
SET STATISTICS TIME ON --展示执行时间
GO
--步骤2:执行sql语句
declare @P0001 nvarchar(124)
set @P0001 = N'select  top 1 ID,UserId,IosToken,AndroidToken from IOS_UserToken  where AndroidToken=@AndroidToken or IosToken=@AndroidToken'
declare @P0002 nvarchar(26)
set @P0002 = N'@AndroidToken varchar(100)'
declare @P0003 varchar(100)
set @P0003 = 'f02b23dd0c59a143e979616b1023411f6f350cb814be7c30989e8987aadcd9c4'
exec sp_executesql @P0001, @P0002, @AndroidToken = @P0003