sql server 性能调优之 SQL语句跟踪采集分析(信息包括:来源IP，耗时，线程状态等)
一. 概述
有些sql语句的，通过dmv分析知道了性能有问题，需要找到请求的来源出处时，下面这个存储过程就用派上用场了。可以把存储过程到加入SQL JOB作业中，根据需要调整JOB的时间运行间隔。在不需要收集时，需要把作业停掉，必免表数据过大。
1.1 创建表sql语句
USE xx GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_REP_Monitor_CreateTime]') AND type = 'D') BEGIN ALTER TABLE [dbo].[REP_Monitor] DROP CONSTRAINT [DF_REP_Monitor_CreateTime] END GO /****** Object: Table [dbo].[REP_Monitor] Script Date: 11/05/2018 13:45:40 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[REP_Monitor]') AND type in (N'U')) DROP TABLE [dbo].[REP_Monitor] GO /****** Object: Table [dbo].[REP_Monitor] Script Date: 11/05/2018 13:45:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[REP_Monitor]( [id] [int] IDENTITY(1,1) NOT NULL, [session_id] [int] NULL, [cpu_time] [int] NULL, [reads] [int] NULL, [writes] [int] NULL, [logical_reads] [int] NULL, [status] [varchar](50) NULL, [blocking_session_id] [int] NULL, [wait_time] [int] NULL, [individual_query] [text] NULL, [parent_query] [text] NULL, [duration] [int] NULL, [CreateTime] [datetime] NULL, [host_name] [varchar](100) NULL, [login_name] [varchar](100) NULL, [client_net_address] [varchar](100) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[REP_Monitor] ADD CONSTRAINT [DF_REP_Monitor_CreateTime] DEFAULT (getdate()) FOR [CreateTime] GO
1.2 收集的存储过程
ALTER PROC [dbo].[PROC_Monitor] AS BEGIN -- 获取当前的会话信息 WITH sess AS ( SELECT es.session_id, database_name = DB_NAME(er.database_id), er.cpu_time, er.reads, er.writes, er.logical_reads, login_name, er.status, blocking_session_id, wait_type, wait_resource, wait_time, individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1), parent_query = qt.text, program_name, host_name, nt_domain, start_time, DATEDIFF(MS,er.start_time,GETDATE()) as duration, (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan, con.client_net_address FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id INNER JOIN sys.dm_exec_connections con ON con.connection_id = er.connection_id AND con.session_id = er.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE es.session_id > 50 AND es.session_Id NOT IN (@@SPID) ) -- 获取当前的会话下的阻塞会话信息， union all 合并 SELECT * INTO #temp FROM sess UNION ALL SELECT es.session_id, database_name = '', 0, 0, 0, 0, login_name, es.status, 0, '', '', '', qt.text, parent_query = qt.text, program_name, host_name, nt_domain, es.last_request_start_time, DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration, NULL AS query_plan, '' FROM sys.dm_exec_sessions es INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt WHERE ec.most_recent_session_id IN ( SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess) ) ORDER BY 1, 2 -- 插入到历史表 REP_Monitor中 INSERT INTO dbo.REP_Monitor (session_id,cpu_time,reads,writes,logical_reads,[status],blocking_session_id,wait_time,individual_query,parent_query,duration,[host_name],login_name,client_net_address) SELECT session_id,cpu_time,reads,writes,logical_reads,[status],blocking_session_id,wait_time,individual_query,parent_query,duration,[host_name],login_name,client_net_address FROM #temp END GO
1.3 查询收集的表，分析sql
SELECT [StockID],[MinVal],[Price],[HKPrice],[USDPrice] FROM [PUB_xxx] WITH(NOLOCK) Where ([StockID] IN (168411702,168411703,168411704,168411705,168411706,168411707,168411718,168411719,168411720,168411721,168411722, 168411738,168411739,168411740,168411741,168411742,168411743,168411744,168411759,168411760,168411761,168411762, 168411763,168411764,168411765,168411766,168411767,168411782,168411783,168411784,168411785,168411786,168411787, 168411788,168411806,168411807,168411808,168411809,168411810,168411811,168411826,168411827,168411828,168411846, 168411847,168411848,168411849,168411867,168411868,168411888,168411889,168411890,168411891,168411892,168411915, 168411916,168411932,168411933,168411934,168411935,168411936,168411954,168411955,168411956,168411976,168411977,168411978, 168411998,168411999,168412000,168412001,168412022,168412023,168412024,168412025,168412026,168412045,168412046,168412065, 168412066,168412067,168412086,168412087,168412088,168412089,168412111,168412112,168412113,168412129,168412130,168412131, 168412132,168412133,168412146,168412147,168412148,168412149,168412151,168412152,168412153,168412154,168412155,168412156, 168412157,168412158,168412159,168412172,168412173,168412174,168412193,168412194,168412195,168412196,168412218,168412219,168412240, 168412241,168412242,168412260,168412261,168412262,168412281,168412282,168412283,168412308,168412309,168412310,168412311,168412352,168412353, 168412354,168412355,168412356,168412370,168412371,168412392,168412393,168412394,168412395,168412396,168412397,168412413,168412414,168412415, 168412416,168412417,168412428,168412435,168412436,168412437,168412453,168412455,168412456,168412457,168412458,168412459,168412460,168412474,168412478, 168412479,168412501,168412502,168412503,168412516,168412517,168412521,168412522,168412523,168412524,168412525,168412535,168412536,168412544,168412545, 168412546,168412547,168412548,168412566,168412567,168412583,168412587,168412588,168412589,168412609,168412610,168412632, 168412633,168412634,168412635,168412636,168412637,168412638,168412639,168412657,168412658,168412659,168412660,168412677,168412699,168412700)) Order By [MinVal] Asc
