SQL Server性能优化（3）使用SQL Server Profiler查询性能瓶颈

时间：2023-12-14 22:01:14

关于SQL Server Profiler的使用，网上已经有很多教程，比如这一篇文章：SQL Server Profiler：使用方法和指标说明。微软官方文档：https://msdn.microsoft.com/zh-cn/library/ms179428(v=sql.105).aspx有更详细的介绍。

经过使用Profiler进行监视，得到监视结果。

SQL Server性能优化（3）使用SQL Server Profiler查询性能瓶颈

==================================以下是和文章标题无关的内容，举例说明优化语句=========================================

一个查询耗时非常大的语句

select t.表地址,t.当前热量,t.热功率,t.瞬时流量,t.累计流量,t.供水温度,t.回水温度,t.温差,t.累计工作时间,t.采集时间,t.社区编号,t.楼房编号,t.房间号

from measure_heat t

where 采集时间 = (select max(采集时间) from measure_heat where 表地址 = t.表地址 and 采集时间 >= '2013-11-11'  and 采集时间 <= '2014-4-11')

这是一个用max函数查询大范围数据的语句。经过查询计划发现，采集时间列没有索引，导致查询时会有扫描表的操作。后来给采集时间列加上索引，再采用不同的查询语句进行分析。以下是分析结果

--如果查询很大范围的数据库，发现使用max函数是效率最高的，其他的排序函数效率一般。

--因为采集时间是有序的

------------------------使用max函数--------------------------

SET STATISTICS IO ON

DBCC DROPCLEANBUFFERS    --关闭缓存

DBCC FREEPROCCACHE        --关闭缓存

SELECT t.表地址, t.当前热量, t.热功率, t.瞬时流量, t.累计流量,

    t.供水温度, t.回水温度, t.温差, t.累计工作时间, t.采集时间,

    t.社区编号,t.楼房编号,t.房间号

FROM Measure_heat t

WHERE 采集时间 =

    (

    ---很多时间花在这个地方，也就是如何能获得一个表的最近的采集时间。

    select max(采集时间)

    from measure_heat

    where 表地址 = t.表地址

    and 采集时间 >= '2013-11-11'

    and 采集时间 <= '2014-1-11'

    )    

---------------------使用ROW_NUMBER函数-----------------------

--create nonclustered index testMeasure_heat on Measure_heat  (采集时间, 表地址)

SET STATISTICS IO ON

DBCC DROPCLEANBUFFERS    --关闭缓存

DBCC FREEPROCCACHE        --关闭缓存

--如果存在，删除缓存表

--IF exists(SELECT * FROM #TableID)

--存入数据库内存表

SELECT * INTO #TableID FROM

(

    SELECT ROW_NUMBER() OVER (PARTITION BY 房间号 ORDER BY 采集时间 DESC) as rowID, id

    FROM Measure_heat

    WHERE 采集时间>='2013-11-1 0:00:00'

    AND  采集时间<='2014-1-11 0:00:00'

    --AND 房间号 = 119

) T

WHERE T.rowID = 1

SELECT * FROM #TableID

SELECT t.表地址, t.当前热量, t.热功率, t.瞬时流量, t.累计流量,

    t.供水温度, t.回水温度, t.温差, t.累计工作时间, t.采集时间,

    t.社区编号,t.楼房编号,t.房间号

FROM  Measure_heat T RIGHT JOIN #TableID b ON t.id = b.id

ORDER BY t.id

DROP table #TableID

------------------------使用rank函数----------------------

DBCC DROPCLEANBUFFERS    --关闭缓存

DBCC FREEPROCCACHE        --关闭缓存

select * into #table2 from

(

    SELECT 表地址, RANK() OVER (PARTITION BY 表地址 ORDER BY 采集时间) AS RankTest

    FROM Measure_heat

    WHERE 采集时间>='2013-11-1 0:00:00' AND 采集时间<='2014-1-11 0:00:00'

) t

where ranktest = 1

--SELECT * FROM #table2

SELECT t.表地址, t.当前热量, t.热功率, t.瞬时流量, t.累计流量,

    t.供水温度, t.回水温度, t.温差, t.累计工作时间, t.采集时间,

    t.社区编号,t.楼房编号,t.房间号

FROM  Measure_heat T RIGHT JOIN  #table2 a on a.表地址 = t.表地址

AND T.采集时间 BETWEEN '2013-11-1 0:00:00' AND '2014-1-11 0:00:00'

drop table #table2

另一个使用Min函数的语句，性能也非常差

select t.*

from  Measure_heat t

where 社区编号='' and 采集时间 =(select min(采集时间 ) from  Measure_heat where 房间号 = t.房间号 AND 采集时间>='2013-11-6 0:00:00' AND  采集时间<'2013-11-7 0:00:00' )

SQL Server性能优化（3）使用SQL Server Profiler查询性能瓶颈

解决方法，在sql中建立内存表，先查出一部分，再利用第一部分的结果查询最终结果。最终可以秒查。

SET STATISTICS IO ON

DBCC DROPCLEANBUFFERS    --关闭缓存

DBCC FREEPROCCACHE        --关闭缓存

SELECT t.*

FROM  Measure_heat t

WHERE 社区编号='' and 采集时间 =

(

    SELECT min(采集时间 )

    FROM  Measure_heat

    where

    采集时间>='2013-11-6 0:00:00'

    AND  采集时间<'2013-11-7 0:00:00'

    AND 房间号 = t.房间号

)

ORDER BY t.id

------------使用内存表------------

SET STATISTICS IO ON

DBCC DROPCLEANBUFFERS    --关闭缓存

DBCC FREEPROCCACHE        --关闭缓存

--如果存在，删除缓存表

--IF exists(SELECT * FROM #TableID)

DROP table #TableID

--存入数据库内存表

SELECT * INTO #TableID FROM

(

    SELECT ROW_NUMBER() OVER (PARTITION BY 房间号 ORDER BY 采集时间 desc) as rowID, id

    FROM Measure_heat

    WHERE 采集时间>='2013-11-6 0:00:00'

    AND  采集时间<'2013-11-7 0:00:00'

    AND 社区编号=''

) T

WHERE T.rowID = 1

--SELECT * FROM #TableID

SELECT a.*

FROM  Measure_heat a RIGHT JOIN #TableID b ON a.id = b.id

ORDER BY A.id

参考：
1. SqlServer性能检测和优化工具使用详细

相关文章