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

View Code

　　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

View Code

　　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