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

MS SQL Server查询优化方法 查询速度慢的原因很多,常见如下几种

1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)  2、I/O吞吐量小,形成了瓶颈效应。  3、没有创建计算列导致查询不优化。  4、内存不足  5、网络速度慢  6...
  • nanchangniat
  • nanchangniat
  • 2013-12-14 15:27:58
  • 1047

性能优化(四)高CPU利用率

还是上一篇中提到过的API,通过调查我们发现这个API在需要返回的数据量比较大的有些单次调用中CPU可能达到90%到100%。所以即便是调用量不大的情况下,这个API一样是造成整个系统性能低下的最大贡...
  • nokiaisacat
  • nokiaisacat
  • 2016-12-28 20:33:58
  • 576

服务器CPU利用率95%以上,高耗性能sql优化

交易明细查询模块,大于10并发以上,服务器CPU利用率达到95%以上,负载超高,监控到如下SQL大量等待,消耗资源严重,需要优化sql 数据库服务器CPU利用率达到95%以上,负载超高,服务...
  • haoluojie
  • haoluojie
  • 2017-01-16 16:17:21
  • 658

关于路由器CPU利用率过高的解决方案

此解决方案翻译自于思科网站上的资料. show process cpu 如显示IP input process is using a lot of CPU resources,检查以下情况: 一、Fa...
  • gotonet
  • gotonet
  • 2006-10-29 11:43:00
  • 962

提高MySQL效率降低MySQLCPU占用率,修改mysql的一些默认参数

Fixing Poor MySQL Default Configuration Values Some random bits scribbled by Jeremy Zawodny   I...
  • RoronoaZoro
  • RoronoaZoro
  • 2012-09-27 15:55:42
  • 642

简单几步优化Ubuntu 降低CPU使用率

1、禁用 IPv6     目前 IPv6 还没有到来,Ubuntu 这个超前的功能暂时可以屏蔽掉,以加快速度。     编辑 sudo gedit /etc/modprobe.d/aliases...
  • esinzhong
  • esinzhong
  • 2012-03-06 18:24:40
  • 4611

如何解决CPU使用率过高问题

在开发过程中,我们会遇到手机的CPU使用率过高而引发的问题,那接下来,我对这方面知识做些整理及归纳 1. CPU使用背景知识: CPU利用率是指:CPU执行非系统空闲进程的时间 / CPU总的执行...
  • luqiang454171826
  • luqiang454171826
  • 2012-04-10 19:23:54
  • 16024

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

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

Linux中CPU使用率低负载高

一、原因总结     等待磁盘I/O完成的进程过多,导致进程队列长度过大,但是cpu运行的进程却很少,这样就导致负载过大,但cpu使用率低。 二、场景分析 (1)磁盘读写请求过多就会导致大量...
  • m0_37886429
  • m0_37886429
  • 2017-11-14 13:40:15
  • 605

云服务器 ECS Linux 系统 CPU 占用率较高问题排查思路

https://help.aliyun.com/knowledge_detail/41225.html?spm=5176.7841174.2.2.ifP9Sc 注意:本文相关配置及说明已在 Ce...
  • LANGZI7758521
  • LANGZI7758521
  • 2016-11-23 10:53:59
  • 509
收藏助手
不良信息举报
您举报文章:MS Sqlserver优化实战(解决CPU利用率高及查询操作速度慢)
举报原因:
原因补充:

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