sqlserver分析性能常用语句

1.设置统计信息,查看IO和时间

通过执行时间,磁盘IO和执行计划查看sql语句情况,包括编译执行时间,索引扫描查找,读写等等
查看sql语句的执行时间,表扫描,物理读,逻辑读情况:
SET STATISTICS TIME ON
SET STATISTICS IO ON

 

2.检查数据库空间使用情况

 1 select o.name,
 2         SUM(p.reserved_page_count) as reserved_page_count,
 3         SUM(p.used_page_count) as used_page_count,
 4         SUM(
 5         case
 6         when(p.index_id<2) then (p.in_row_data_page_count+
 7         p.lob_used_page_count+p.row_overflow_used_page_count)
 8         else p.lob_used_page_count+p.row_overflow_used_page_count
 9         end
10         ) as DataPages,
11         SUM(
12         case
13         when (p.index_id<2) then row_count
14         else 0
15         end
16         ) as rowCounts
17         from sys.dm_db_partition_stats p inner join sys.objects o
18         on p.object_id=o.object_id
19         group by o.name
20         having SUM(p.used_page_count) > 3500
View Code

 

3.查找物理读和逻辑读前100条sql

 1 -- 物理读取页面最多的100条语句
 2 SELECT TOP 100 
 3 qs.total_physical_reads,qs.execution_count,
 4 qs.total_physical_reads /qs.execution_count as avg_io,
 5 qt.text, db_name(qt.dbid) as dbname, qt.objectid
 6 FROM sys.dm_exec_query_stats qs 
 7 CROSS apply sys.dm_exec_sql_text(qs.sql_handle) as qt
 8 ORDER BY qs.total_physical_reads desc
 9 GO
10 
11 -- 逻辑读取页面最多的100条语句
12 SELECT TOP 100
13 qs.total_logical_reads,qs.execution_count,
14 qs.total_logical_reads /qs.execution_count as avg_io,
15 qt.text, db_name(qt.dbid) as dbname
16 FROM sys.dm_exec_query_stats qs
17 cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
18 ORDER BY qs.total_logical_reads desc
19 GO
View Code

 

4.查看SQLServer索引的使用情况

前言:
众所周知,索引对性能的提升有巨大作用。但是这是有代价的,索引需要磁盘空间来存放它的B-Tree,并且用于在每次DML执行后更新信息,所以应该在一定时期内检查有无无用索引。

准备工作:
记住别在重启服务器之后进行信息收集,因为DMO将会清空,而数据会非常不准确甚至是错误的。所以应该在运行了一段时间后(业务周期)再做收集。
业务周期根据实际情况而定。一些表和查询会每天频繁使用,但是一些类似报表功能可能是一月甚至一年执行一次,所以,除非必要,不然就让所有东西一直运行,知道你的业务周期完毕。这样会得到相对准确的信息。

步骤:
执行以下语句:

分析:
为了获得索引名、索引类型、表名,有必要关联sys.Indexes和sys.objects表。
通常情况下,当一个索引被使用了,那么在sys.dm_db_index_usage_stats上的user_seek、user_scan、或者user_lookup列上会有一些值。如果索引从来未被使用,那么这些值就为0,也就是没必要保留。
扩充信息:
在决定索引是否有效时,还需要结合你的专业只是。有些索引之所以没有统计数据是因为还没有到使用的周期,或者周期性地重启了服务器,在删除之前,要考虑:
1、 是否为主键或者唯一键,因为及时这部分没有数值,但是它们还是可以很好地保证数据的一致性。
2、 唯一索引帮助优化器创建更有效的执行计划,及时这些索引未被使用,但是也提供了数据分布的相关信息。

语句:

 1 use dbName --要查看的数据库
 2 
 3 SELECT db_name(database_id) as N'数据库名称',
 4           ind.index_id '索引的ID',
 5         obj.name as N'表名',
 6         ind.name '索引的名称',
 7         ind.type_desc '索引类型的说明',
 8         indUsage.user_seeks N'用户索引查找次数',
 9         indUsage.user_scans N'用户索引扫描次数',
10         indUsage.last_user_seek N'用户上次执行搜索的时间',
11         indUsage.last_user_scan N'用户上次执行扫描的时间',
12         indUsage.user_lookups ,
13         indUsage.user_updates '通过用户查询执行的更新次数',
14         rows as N'表中的行数'
15 FROM    sys.indexes AS ind
16         INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id
17         LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id
18                                                           AND ind.index_id = indUsage.index_id
19                                                           
20     LEFT join sysindexes c on c.id = ind.object_id AND c.indid = 1                                                          
21 WHERE  database_id=db_id('wqbnew')   ---改成要查看的数据库
22         AND ind.type_desc <> 'HEAP' --不是堆
23         AND obj.type <> 'S' --不是系统基表
24         
25         --        AND OBJECTPROPERTY(obj.object_id, 'isusertable') = 1
26         --AND ( ISNULL(indUsage.user_seeks, 0) = 0
27         --       AND ISNULL(indUsage.user_scans, 0) = 0
28         --      AND ISNULL(indUsage.user_lookups, 0) = 0
29         --    )          
30         AND obj.name =''    --表名
31             
32 ORDER BY user_seeks,user_scans,indUsage.user_lookups,obj.name,ind.name
View Code

 5.查看数据页消耗的空间大小,用来排查 哪些表的设计比较糟糕

1 SELECT
2    DB_NAME(database_id),
3    SUM(free_space_in_bytes) / 1024 AS 'Free_KB'
4 FROM sys.dm_os_buffer_descriptors
5 WHERE database_id <> 32767
6 GROUP BY database_id
7 ORDER BY SUM(free_space_in_bytes) DESC
8 GO

 6.查看消耗CPU最大的前100条sql语句

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

7.可以使用以下语句查看内存消耗较高的sql语句,针对这些语句进行优化

SELECT SS.SUM_EXECUTION_COUNT,
T.TEXT,
SS.SUM_TOTAL_ELAPSED_TIME,
SS.SUM_TOTAL_WORKER_TIME,
SS.SUM_TOTAL_LOGICAL_READS,
SS.SUM_TOTAL_LOGICAL_WRITES
FROM (SELECT S.PLAN_HANDLE,
SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
FROM SYS.DM_EXEC_QUERY_STATS S
GROUP BY S.PLAN_HANDLE
) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDER BY SUM_TOTAL_LOGICAL_READS DESC

  

转载于:https://www.cnblogs.com/zfylzl/p/6053149.html

  • 0
    点赞
  • 0
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值