MS Sqlserver优化实战(解决CPU利用率高及查询操作速度慢)

转载 2011年03月05日 21:39:00

最近我们医院的LIS系统经常出现速度极慢的问题,而且由于LIS系统的数据库表设计本身存在问题,刚用了一年的时间某张表的数据就已经达到3千万条。

 

服务器:HP刀片机 1个CPU(双核),2G内存,高峰期时有700个连接。

操作系统:Windows server 2003。

数据库:Ms SqlServer2000 各种补丁都已打全。

现状:服务器CPU利用率高,工作站各种操作极慢。

 

针对以上情况,为了解决问题,而表结构设计及程序也无法及时大规模优化的前提下,采取了以下解决方案。

1、首先,查找出占用CPU及IO资源较多的语句,可以采用Sql Server 的事件探查器。

打开事件探查器,新建一个跟踪,我们可以采用跟踪某个工作站的形式进行,工作站的SPID可以在sysprocesses通过MAC地址来查,select spid from sysprocesses WHERE net_address='MAC地址';

 

如下图,请输入SPID后点击运行:

2、当跟踪运行一段时间后,将跟踪结果保存为跟踪文件,进行分析,找出CPU及READS占较多的语句,如下图所示,CPU为922表明占用异常,可以进行优化:

 

3、将得到的语句拿出来进行分析 ,发现有以下问题:

(1)     在条件中有函数;

(2)     patientid  没有索引,但这个字段的使用频率较高;

 SELECT       vi_patient.patientname ,vi_patient.sex ,vi_patient.examinaim ,vi_patient.checkeropinion ,vi_patient.section ,vi_patient.depart_bed ,vi_patient.sampleno ,vi_patient.fee ,vi_patient.receivetime ,vi_patient.patientid ,vi_patient.resultstatus ,vi_patient.nl ,vi_patient.nldw ,vi_patient.fphm ,vi_patient.doctadviseno , vi_patient.labdepartment  FROM vi_patient     WHERE VI_PATIENT.RECEIVETIME >= convert(datetime,'2008-11-03 00:00:00',120) and VI_PATIENT.RECEIVETIME <= convert(datetime,'2008-11-24 23:59:59',120) and VI_PATIENT.patientid  ='300039716' ORDER BY VI_PATIENT.RECEIVETIME DESC

    解决方法:将语句作以下更改,并增加patientid  字段的索引,执行效率明显提高。虽然增加索引可能会减低插入及更新的效率,但综合分析patientid  查询的频率更高。

SELECT    vi_patient.patientname ,vi_patient.sex ,vi_patient.examinaim ,vi_patient.checkeropinion ,vi_patient.section ,vi_patient.depart_bed ,vi_patient.sampleno ,vi_patient.fee ,vi_patient.receivetime ,vi_patient.patientid ,vi_patient.resultstatus ,vi_patient.nl ,vi_patient.nldw ,vi_patient.fphm ,vi_patient.doctadviseno , vi_patient.labdepartment  FROM vi_patient     WHERE  VI_PATIENT.patientid  ='300039716'and VI_PATIENT.RECEIVETIME between '2008-11-03 00:00:00'  and  '2008-11-24 23:59:59'

ORDER BY VI_PATIENT.RECEIVETIME DESC

4、联合索引的问题,这个表的记录条数达到3千万条,如下图,有以下联合索引:

 

但在实际使用中一直都在单独使用MEASURETIME作为查询条件,作为联合索引使用时一定要慎重,在查询时要么索引所有的字段都在WHERE条件中,或索引的第一个字段在WHERE条件中,才会用到索引,其他字段单独作为查询条件其实该联合索引是不起任何作用的。

解决方法:增加单独的MEASURETIME索引,当使用MEASURETIME为条件查询时执行效率提高的很明显。

5、因服务器内存使用并没有问题,决定将一些常用的表常住内存,使用以下语句:

DECLARE @db_id int, @tbl_id int

USE lis

SET @db_id = DB_ID('lis')

SET @tbl_id = OBJECT_ID('L_SAMPLETYPE')

DBCC  PINTABLE (@db_id, @tbl_id)

查询某张表是否常住内存:

Select ObjectProperty(Object_ID('L_SAMPLETYPE'),'TableIsPinned')

 

大家在实际使用过程中一定要注意,不能将太大的表常住内存,因为当服务器高速缓存满时,数据库将无法使用,只有重新启动才能使用。

 

6、更新一些重要表的重要索引,采用以下语句:

DBCC INDEXDEFRAG  (lis,l_testresult,idx_testid_measuertime);

采取INDEXDEFRAG更新索引,可以做到在更新索引时不锁表。

 

6、总结:针对以上优化及改动后,服务器的CPU使用率有明显下降,工作站的操作速度也提升很多,达到了一定的效果。

在设计数据表结构时就应考虑程序的执行效率问题,如针对大表可以采取分表的策略,定期进行转储,尽量保证业务表的数据量一直很小。索引的设计一定要考虑实际的应用,一定要合理,并不是多多益善,也不是越少越好。如果系统已大规模应用再改表结构设计就不那么容易了,我采取以上方法只能治标但不能治本。

 

可能我的方法并不适合你,但见仁见智,仅供参考。

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/caok/archive/2008/11/25/3368216.aspx

SQL Server中查询CPU占用高的SQL语句

SQL Server中查询CPU占用高的情况,会用到sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests   一、查看当前的数据库用户连...
  • isoleo
  • isoleo
  • 2015年03月02日 18:09
  • 5762

SQLSERVER排查CPU占用高的情况

今天中午,有朋友叫我帮他看一下数据库,操作系统是Windows2008R2 ,数据库是SQL2008R2 64位64G内存,16核CPU硬件配置还是比较高的,他说服务器运行的是金蝶K3软件,数据库...
  • hliq5399
  • hliq5399
  • 2016年06月12日 13:49
  • 1495

亿级规模的Elasticsearch优化实战

本次分享主要包含两个方面的实战经验:索引性能和查询性能。 一. 索引性能(Index Performance) 首先要考虑的是,索引性能是否有必要做优化? 索引速度提高...
  • opensure
  • opensure
  • 2015年08月13日 19:43
  • 52335

python 按照cpu的使用率对top中的进程排序(排序表格)

问题:解决如何在python中对表格(二维)进行排序?例如,按照cpu和mem的使用率对top中的进程排序解决方法: 1. 使用numpy或者panda中的方法对二维矩阵进行操作 2. 使用二维数...
  • justheretobe
  • justheretobe
  • 2016年04月17日 18:40
  • 1064

CPU利用率与负载的关系

CPU利用率在过去常常被我们这些外行认为是判断机器是否已经到了满负荷的一个标准,看到50%-60%的使用率就认为机器就已经压到了临界了。 CPU利用率是对一个时间段内CPU使用状况的统计,通过这个指标...
  • qq_16209077
  • qq_16209077
  • 2016年02月28日 22:25
  • 15903

性能指标之资源指标-CPU-利用率

假设EC=2,VP=8,EC利用率为200%,VP利用率为50%。在测试报告中描述CPU利用率为50%(按照CPU为8核计算,其中EC为2C,其他CPU为借用)。 2、最佳实践交易类测试通常在CPU...
  • lin443514407lin
  • lin443514407lin
  • 2017年03月21日 09:57
  • 526

SQL Server数据库查询速度慢原因及优化方法

数据库查询慢的分析   查询速度慢的原因:   1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)    2、I/O吞吐量小,形成了瓶颈效应。     3、没有创...
  • hustwht
  • hustwht
  • 2016年08月08日 23:30
  • 1067

centOs 查看系统cpu使用率等--top

在系统维护的过程中,随时可能有需要查看 CPU 使用率,并根据相应信息分析系统状况的需要。在 CentOS 中,可以通过 top 命令来查看 CPU 使用状况。运行 top 命令后,CPU 使用状态会...
  • u011567667
  • u011567667
  • 2014年01月09日 13:00
  • 4852

查看linux cpu和内存利用率

在系统维护的过程中,随时可能有需要查看 CPU 使用率,并根据相应信息分析系统状况的需要。在 CentOS 中,可以通过 top 命令来查看 CPU 使用状况。运行 top 命令后,CPU 使用状态会...
  • weiyuefei
  • weiyuefei
  • 2016年08月30日 23:46
  • 3475

Linux下CPU的利用率

CPU利用率是对系统进行性能分析的重要因素,本文将说明CPU时间的组成以及利用率的计算方法。 内核中的时间 具体说明CPU的各种时间之前,先说明内核中几个重要的时间概念。 HZ是系统...
  • guomei
  • guomei
  • 2015年07月01日 00:01
  • 1390
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MS Sqlserver优化实战(解决CPU利用率高及查询操作速度慢)
举报原因:
原因补充:

(最多只允许输入30个字)