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

发布时间：2020-12-12 15:45:42 所属栏目：MsSql教程来源：网络整理

导读：????最近我们医院的 LIS 系统经常出现速度极慢的问题，而且由于 LIS 系统的数据库表设计本身存在问题，刚用了一年的时间某张表的数据就已经达到３千万条。 ? 服务器： HP 刀片机１个 CPU （双核），２ G 内存，高峰期时有７００个连接。操作系统： Windows

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

服务器：HP刀片机　１个CPU（双核），２G内存，高峰期时有７００个连接。

操作系统：Windows server 2003。

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

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

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

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

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

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

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

３、将得到的语句拿出来进行分析　，发现有以下问题：

（１）???? 在条件中有函数;

（２）???? 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.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

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

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

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

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

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')

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

６、更新一些重要表的重要索引，采用以下语句：

DBCC INDEXDEFRAG? (lis,l_testresult,idx_testid_measuertime);

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

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

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!