最新消息:

提升SQL Server速度方法整理汇总

Works admin 391浏览 0评论

提升SQL Server速度方法整理汇总

提升SQL Server速度 整理索引碎片

命令:DBCC SHOWCONTIG 先用此命令查询出数据库所有分表的碎片化情况,再根据需要用DBCC DBREINDEX命令进行(重建索引)碎片化整理.

eg:

DBCC SHOWCONTIG 正在扫描 ‘CM_TrackField’ 表…
表: ‘CM_TrackField’ (17695411);索引 ID: 0,数据库 ID: 28
已执行 TABLE 级别的扫描。
– 扫描页数…………………………..: 13
– 扫描区数…………………………: 7
– 区切换次数…………………………: 6
– 每个区的平均页数……………………: 1.9
– 扫描密度 [最佳计数:实际计数]…….: 28.57% [2:7]
– 区扫描碎片 ………………: 85.71%
– 每页的平均可用字节数……………………: 843.8
– 平均页密度(满)…………………: 89.57%
以上查询表内发现有碎片化超85%,需要(重建索引)整理,重建索引命令为:DBCC DBREINDEX(CM_TrackField,”,100)(请注意括号内的”符号为英文状态格式)。

日志收缩整理

命令:DBCC SQLPERF(LOGSPACE)

查询当前用户任务等待命令:select * from sys.sysprocesses

如果内存不足则,lastwaittype栏会看到较多的ASYNC_IO_COMPLETION等待类型。这是因为内存不足时:a.内存和磁盘间会频繁进行交互,磁盘负载增加 b.需要读取磁盘上的数据完成查询,磁盘负载增加。
也就是说这时候磁盘也出现了性能瓶颈,但是这只是“表面”的,我们要结合多个性能指标来认清根本原因是“内存不足”。
确定压力来源及解决办法: 
通过前的分析,确定了数据页缓存相关的内存瓶颈。就要分析为什么会这样及解决办法。主要分为如下5个方面:
1. 外部压力 
如果OS层面或者其它应用服务需要更多的内存,windows会压缩Database Pages的内存量。这时内存压力来自外部。可以查看如下性能计数器确定是否是外部压力:
1. SQL Server:Memory Manager-Total Server Memory:此计数器值会下降。
2. Memory:Available Mbytes:此值会下降到较低水平。
3. 在没有使用AWE或者Lock page in memory前提下,查看Process:Private Bytes-SqlServer和Process:Working Set-SqlServer,两者值会有显著下降。
解决方法:如果非DB专用服务器,则要权衡各个应用服务之间重要性来分配内存或者加大内存。尽量让服务器只运行SQL Server,成为DB专用服务器。
2. SQL Server自身对Database Page的使用压力
当Total Server Memory已经达到设定的Max Server Memory或者无法从OS获得更多内存,但是经常访问的数据量又远大于物理内存用于数据缓存的容量时,SQL Server被迫将内存的数据移入又移出,用于完成当前查询。
观察如下性能计数器:
1. SQL Server:Memory Manager-Total Server Memory 和 SQL Server:Memory Manager-Target Server Memory两者值将会相等。但是前者不会大于后者。
2. 将会出现“分析方法”所述之情况。
解决方法:既然SQL Server没有足够内存存放Database Page,那就要么增加SQL Server使用的内存量或者减少其使用的内存里。
增加:可以通增加物理内存,启用AWE等方法。
减少:可以通过横向扩展,有两台或者多台服务器分别载部分库;优化相关读取量较大的语句等。
3. Buffer Pool中的Stolen Memory压力 
正常情况下Buffer Pool中的Stolen Memory不会给Database Pages造成压力。因为Database Pages有压力,会触发Lazy Writes,同时SQL Server 会清理Stolen Memory中的执行计划缓存。
但是,如果用户申明了过多的对象,而没有登出,并且占用内存过多,就会压缩Database Pages.如:游标,自定义引用的执行计划等。
解决方法:通常是会表现为a)用户提交的请求因内存不足无法完成,701错误;b)需要压缩某些clerk的内存量,来完成用户请求,造成响应延时和缓慢。
通过查询sys.dm_os_memory_clerks的字段Single_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。
4. Multi-Page的压力
multi-page跟Buffer Pool共享OS的虚拟地址空间,如果multi-page使用过多内存,就会压缩Datbase pages。multi-page内存用量一般较小且相对固定,可能发生的情况有:
a. 未开启AWE的32位SQL Server只有2G地址空间,且用-g启动参数扩展的MemToLeave的上限。
b. 64位SQL Server调了内存泄露的第三方代码。
c. 使用带有大量参数或者较长的”IN”语句
d. 调高了Network Packet Size,大于或等于8KB,并且较多这种连接。
e. 大量复杂XML查询,或者第三代码。
解决方法: 通过查询sys.dm_os_memory_clerks的字段multi_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。

    随着项目的发展,处理的数据量也是与日剧,已然达到千万级别数据量,在日常操作中,难免会出现性能问题,很多时候不知从何入手,今天特地整理使用sql server的动态管理试图来检测性能的方案。
 
第一句 查看任务等待数
–wait type查询:(用于查看任务执行时的等待数)
  SELECT TOP 20
  wait_type ,
  max_wait_time_ms wait_time_ms ,
  signal_wait_time_ms ,
  wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms ,
  100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
  AS percent_total_waits ,
  100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
  AS percent_total_signal_waits ,
  100.0 * ( wait_time_ms – signal_wait_time_ms )
  / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
  FROM sys.dm_os_wait_stats
  WHERE wait_time_ms > 0      — remove zero wait_time
  AND wait_type NOT IN        — filter out additional irrelevant waits
  ( ‘SLEEP_TASK’, ‘BROKER_TASK_STOP’, ‘BROKER_TO_FLUSH’,
  ‘SQLTRACE_BUFFER_FLUSH’,’CLR_AUTO_EVENT’, ‘CLR_MANUAL_EVENT’,
  ‘LAZYWRITER_SLEEP’, ‘SLEEP_SYSTEMTASK’, ‘SLEEP_BPOOL_FLUSH’,
  ‘BROKER_EVENTHANDLER’, ‘XE_DISPATCHER_WAIT’, ‘FT_IFTSHC_MUTEX’,
  ‘CHECKPOINT_QUEUE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’,
  ‘BROKER_TRANSMITTER’, ‘FT_IFTSHC_MUTEX’, ‘KSOURCE_WAKEUP’,
  ‘LAZYWRITER_SLEEP’, ‘LOGMGR_QUEUE’, ‘ONDEMAND_TASK_QUEUE’,
  ‘REQUEST_FOR_DEADLOCK_SEARCH’, ‘XE_TIMER_EVENT’, ‘BAD_PAGE_PROCESS’,
  ‘DBMIRROR_EVENTS_QUEUE’, ‘BROKER_RECEIVE_WAITFOR’,
  ‘PREEMPTIVE_OS_GETPROCADDRESS’, ‘PREEMPTIVE_OS_AUTHENTICATIONOPS’,
  ‘WAITFOR’, ‘DISPATCHER_QUEUE_SEMAPHORE’, ‘XE_DISPATCHER_JOIN’,
  ‘RESOURCE_QUEUE’ )
  ORDER BY wait_time_ms DESC
说明:
  1. sys.dm_os_wait_stats 返回正在执行的线程所遇到的等待数的有关信息。
  需要对服务器具有 VIEW SERVER STATE 权限。
  
  2. 此动态管理视图的内容可通过运行以下命令来重置:
  DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); GO
第二句 查看索引碎片
   (执行时间根据数据库的数据量来决定的)
   SELECT TOP 20
   DB_NAME() AS DatbaseName
   , SCHEMA_NAME(o.Schema_ID) AS SchemaName
   , OBJECT_NAME(s.[object_id]) AS TableName
   , i.name AS IndexName
   , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
   FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
   INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
   AND s.index_id = i.index_id
   INNER JOIN sys.objects o ON i.object_id = O.object_id
   WHERE s.database_id = DB_ID()
   AND i.name IS NOT NULL
   AND OBJECTPROPERTY(s.[object_id],  ‘IsMsShipped’ ) = 0
   ORDER BY [Fragmentation %] DESC
说明:
    1. 大于30%的索引都可进行重建了
    2. sys.dm_db_index_physical_stats  返回指定表或视图的数据和索引的大小和碎片信息
    3. sys.indexes  每个表格对象(例如,表、视图或表值函数)的索引或堆都包含一行。

    4. sys.objects 在数据库中创建的每个用户定义的架构范围内的对象在该表中均对应一行。

第三句 不同类型索引操作的计数
–Unused查询:(查看当前数据库中,那个表的那个索引未被使用)
  SELECT TOP 20
  DB_NAME() AS DatabaseName
  , SCHEMA_NAME(o.Schema_ID) AS SchemaName
  , OBJECT_NAME(s.[object_id]) AS TableName
  , i.name AS IndexName
  , s.user_updates
  , s.system_seeks + s.system_scans + s.system_lookups
  AS [System usage]
  FROM sys.dm_db_index_usage_stats s
  INNER JOINsys.indexes i ON s.[object_id] = i.[object_id]
  AND s.index_id = i.index_id
  INNER JOIN sys.objects o ON i.object_id = O.object_id
  WHERE s.database_id = DB_ID()
  AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
  AND s.user_seeks = 0
  AND s.user_scans = 0
  AND s.user_lookups = 0
  AND i.name IS NOT NULL
  ORDER BY s.user_updates DESC
说明:
   1. sys.dm_db_index_usage_stats 返回不同类型索引操作的计数以及上次执行每种操作的时间。
第四句 
–Maintenance查询:(尚在学习中)
  SELECT TOP 20
  DB_NAME() AS DatabaseName
  , SCHEMA_NAME(o.Schema_ID) AS SchemaName
  , OBJECT_NAME(s.[object_id]) AS TableName
  , i.name AS IndexName
  , (s.user_updates ) AS [update usage]
  , (s.user_seeks + s.user_scans + s.user_lookups)
  AS [Retrieval usage]
  , (s.user_updates) –
  (s.user_seeks + user_scans +
  s.user_lookups) AS [Maintenance cost]
  , s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
  , s.last_user_seek
  , s.last_user_scan
  , s.last_user_lookup
  FROM sys.dm_db_index_usage_stats s
  INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
  AND s.index_id = i.index_id
  INNER JOIN sys.objects o ON i.object_id = O.object_id
  WHERE s.database_id = DB_ID()
  AND i.name IS NOT NULL
  AND OBJECTPROPERTY(s.[object_id], ‘IsMsShipped’) = 0
  AND (s.user_seeks + s.user_scans + s.user_lookups) > 0
  ORDER BY [Maintenance cost] DESC
第五句 查看缺失索引
(查看那些索引建立的不够完善,适当修改索引加入包含性列中)
–missing查询4:
  SELECT TOP 20
  ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0)
  AS [Total Cost]
  , d.[statement] AS [Table Name]
  , equality_columns
  , inequality_columns
  , included_columns
  FROM sys.dm_db_missing_index_groups g
  INNER JOIN sys.dm_db_missing_index_group_stats s
  ON s.group_handle = g.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details d
  ON d.index_handle = g.index_handle
  ORDER BY [Total Cost] DESC
说明:
     sys.dm_db_missing_index_details –返回关于缺失索引的详细信息。
     sys.dm_db_missing_index_group_stats – 返回缺失索引组的摘要信息
     sys.dm_db_missing_index_groups – 返回一个具体组的缺失索引的信息。
     sys.dm_db_missing_index_columns(index_handle) – 返回在一个索引中缺失的数据库表列的信息。这是一个函数,它要求传递index_handle。

检测碎片

在对表进而对表中定义的索引进行数据修改(INSERT、UPDATE 和 DELETE 语句)的整个过程中都会出现碎片。由于这些修改通常并不在表和索引的行中平均分布,所以每页的填充度会随时间而改变。对于扫描表的部分或全部索引的查询,这种碎片会导致附加的页读取。从而延缓了数据的并行扫描。

SQL Server 2005 中计算碎片的算法比 SQL Server 2000 中的算法更精确。因此,碎片值显得更高。例如,在 SQL Server 2000 中,如果一个表的第 11 页和第 13 页在同一区中,而第 12 页不在该区中,该表不被视为含有碎片。但是访问这些页需要两次物理 I/O 操作,因此,在 SQL Server 2005 中,这将算作碎片。

索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。对于堆,此值表示堆的区碎片。对于索引,此值表示索引的逻辑碎片。与 DBCC SHOWCONTIG 不同,这两种情况下的碎片计算算法都会考虑跨越多个文件的存储,因而结果是精确的。

备注:
   自己也在学习中,关于这四条语句的具体含义我会继续学习,并完善的,敬请关注!

转载请注明:哥姐博客网 blog.gjwap.cn » 提升SQL Server速度方法整理汇总

与本文相关的文章

您必须 登录 才能发表评论!