sql2008 跟踪优化

工具-->sql server profiler

 

1.新建跟踪-->选择T-SQL模板-->保存到文件-->确定运行

保持数据采集一到两小时，再将trc文件导入进一个新数库据，再使用下面的sql语句进行统计

 

如果需要统计死锁，可以选择死锁模板，或在事件选择里“查看所有事件”，指定死锁事件

 

2.效率统计

 

/* 统计某次监控的数据， 50毫秒以上，使用监控模板：pgy_big_query */
SELECT 
    MIN(StartTime) AS 开始时间
    , MAX(StartTime) AS 结束时间
    , (DATEDIFF(ss, MIN(StartTime), MAX(StartTime))/60) AS 持续时长
    , COUNT(*) AS [数量(>=50ms)]
    , COUNT(*) / (DATEDIFF(ss, MIN(StartTime), MAX(StartTime))/60) AS [次/分钟]
    , AVG(Reads) AS Reads
    , AVG(CPU) AS CPU
    , AVG(DURATION) AS Duration
    , AVG(WRITES) AS Writes
    , SUM(Reads) AS LargeRead
    , SUM(WRITES) AS LargeWrite
    , SUM(DURATION ) / 1000 AS LargeDuration
    , SUM(CPU) AS LargeCPU
FROM ZBIRD.TR_ALL_IO_20110329
WHERE ApplicationName NOT LIKE 'Microsoft SQL Server Management Studio%'

/* 按语句分组，计算耗时、耗费IO等 */
SELECT
    SUBSTRING(TextData, 1, 30) AS TextData
    , COUNT(*) AS [数量(>=50ms)]
    , AVG(Reads) AS Reads
    , AVG(CPU) AS CPU
    , AVG(DURATION / 1000) AS Duration
    , AVG(WRITES) AS Writes
    , SUM(Reads) AS LargeRead
    , SUM(WRITES) AS LargeWrite
    , SUM(DURATION ) / 1000 AS LargeDuration
    , SUM(CPU) AS LargeCPU
FROM ZBIRD.TR_ALL_IO_20110329
WHERE ApplicationName NOT LIKE 'Microsoft SQL Server Management Studio%'
GROUP BY SUBSTRING(TextData, 1, 30)
ORDER BY LargeRead DESC

/* 查询详细的语句 */
SELECT TextData,LoginName,CPU,Reads,Writes,Duration / 1000    as [执行时间]    --[Endtime-Startime]
FROM ZBIRD.TR_ALL_IO_20110329 
WHERE TextData LIKE '%SELECT "vCacceyInfo"."caccey_%'

 

 

3.阻塞情况统计(也就是死锁)

 

SET DATEFIRST 1
go
/* 根据现有的阻塞情况监控，查询每天阻塞的情况 */

SELECT
    DATEPART(WEEKDAY, TransDate) AS WeekDay 
    , CONVERT(CHAR(10), TransDate, 121) AS LOG_DATE
    , COUNT(*)
FROM dblock_information
WHERE TransDate >= '2010-08-01'
GROUP BY CONVERT(CHAR(10), TransDate, 121), DATEPART(WEEKDAY, TransDate)
ORDER BY LOG_DATE;

/* 对象每天的阻塞数量 */
if object_id('tempdb..#t', 'U') is not null
    drop table #t
go
select top 92 identity(int, 1, 1) as id 
into #T
from sys.objects
go
declare @INIT_DATE    DATETIME
SET @INIT_DATE = '2010-06-25'

SELECT 
    DATEPART(WEEKDAY, dateadd(dd, id, @INIT_DATE)) AS WeekDay 
    , CONVERT(CHAR(10), dateadd(dd, id, @INIT_DATE), 121) AS LOG_DATE
    , COUNT(B.EventInfo)
FROM #T a
LEFT JOIN dblock_information B ON CONVERT(CHAR(10), dateadd(dd, A.id, @INIT_DATE), 121) = CONVERT(CHAR(10), B.TransDate, 121)
    AND B.EventInfo like '%sp_sstorh_Confirm%'
WHERE dateadd(dd, id, @INIT_DATE) < GETDATE()
GROUP BY DATEPART(WEEKDAY, dateadd(dd, id, @INIT_DATE)), CONVERT(CHAR(10), dateadd(dd, id, @INIT_DATE), 121)
ORDER BY LOG_DATE;

/* 脚本按类型分类，并统计各脚本的阻塞数量 */
SELECT
    CASE WHEN CHARINDEX('INSERT', EventInfo) > 0 THEN 'INSERT ' + SUBSTRING(EventInfo, CHARINDEX('INSERT', EventInfo) + 12, 10)
        WHEN CHARINDEX('UPDATE', EventInfo) > 0 THEN 'UPDATE ' + SUBSTRING(EventInfo, CHARINDEX('UPDATE', EventInfo) + 7, 10)
        WHEN CHARINDEX('SELECT', EventInfo) > 0 THEN 'SELECT ' + SUBSTRING(EventInfo, CHARINDEX('SELECT', EventInfo) + 9, 15)
        WHEN CHARINDEX('DELETE', EventInfo) > 0 THEN 'DELETE ' + SUBSTRING(EventInfo, CHARINDEX('DELETE', EventInfo) + 15, 15)
        WHEN CHARINDEX('EXEC', EventInfo) > 0 THEN SUBSTRING(EventInfo, 1, 20)
        ELSE EventInfo
    END AS Context
    , COUNT(*) AS CNT
FROM dblock_information
GROUP BY CASE WHEN CHARINDEX('INSERT', EventInfo) > 0 THEN 'INSERT ' + SUBSTRING(EventInfo, CHARINDEX('INSERT', EventInfo) + 12, 10)
        WHEN CHARINDEX('UPDATE', EventInfo) > 0 THEN 'UPDATE ' + SUBSTRING(EventInfo, CHARINDEX('UPDATE', EventInfo) + 7, 10)
        WHEN CHARINDEX('SELECT', EventInfo) > 0 THEN 'SELECT ' + SUBSTRING(EventInfo, CHARINDEX('SELECT', EventInfo) + 9, 15)
        WHEN CHARINDEX('DELETE', EventInfo) > 0 THEN 'DELETE ' + SUBSTRING(EventInfo, CHARINDEX('DELETE', EventInfo) + 15, 15)
        WHEN CHARINDEX('EXEC', EventInfo) > 0 THEN SUBSTRING(EventInfo, 1, 20)
        ELSE EventInfo
    END
ORDER BY CNT DESC

/* 统计昨天主要阻塞的脚本 */
SELECT SUBSTRING(EventInfo, 1, 30) AS Texts
    , COUNT(*) AS CNT
FROM DBLOCK_INFORMATION
WHERE TransDate >= CONVERT(CHAR(10), GETDATE() -1, 112) AND TransDate < CONVERT(CHAR(10), GETDATE() , 112)
GROUP BY SUBSTRING(EventInfo, 1, 30)
ORDER BY CNT DESC

/* 查看引起阻塞的语句详细信息 */
SELECT * 
FROM DBLOCK_INFORMATION 
WHERE EventInfo LIKE '%zbird.dbo.CONFIRM_OSALEHID;1%' 
    AND TransDate >= CONVERT(CHAR(10), GETDATE() -1, 112) AND TransDate < CONVERT(CHAR(10), GETDATE() , 112)
ORDER BY TransDate

 

 

4.进程查询

 

-- 查询进程状况
select *
from sys.sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle)
where text like ' SELECT "vCacceyInfo_strns"."caccey_Name"%'


-- 生成杀死进程的语句 
select 'kill ' + convert(varchar(20), spid)
from sys.sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle)
where text like ' SELECT "vCacceyInfo_strns"."caccey_Name"%'