Report(数据库性能检测)

《Report(数据库性能检测)》由会员分享，可在线阅读，更多相关《Report(数据库性能检测)（21页珍藏版）》请在装配图网上搜索。

1、广东联通数据库性能检查报告 Prepared for广东联通公司2009年1月15日Version 1.0Prepared by邱诗扬Premier Field Engineershiyang.qiu目录1总结12数据收集和分析工具23发现的问题与建议33.1内存不足33.2磁盘慢33.3编译，重编译过多43.4不成比例的连接数43.5过高的登录注销数43.6索引设计和维护53.7阻塞类型分析53.8在选择语句中使用用户函数63.9使用游标73.10JOIN没有使用ON从句73.11测试环境和生产环境的混合73.12不恰当的文件自动增长率74附录1 重要性能计数器数据85附录2 OAS库中没有

2、任何索引和没有聚集索引的表116附录3 阻塞类型统计177参考资料181 总结本报告为广东联通公司SQLCLUSTER03实例的性能检查报告。广东联通公司的OA系统的响应时间过长，希望微软工程师从数据库层进行性能检查，找出性能瓶颈，通过优化性能差的语句缩短查询时间。本次调优不涉及应用层的代码分析。在检查中发现，不良索引设计是引起性能问题的最主要原因。2 数据收集和分析工具1 用PSSDIAG工具捕获如下主要信息（1月12-13日）： SQL Server Profiler（至存储过程/批处理级别） Perfmon（间隔15秒） 阻塞情况2 用Relog分析Perfmon的输出。3 用RML分析

3、Profiler Trace，找出性能不良的查询。4 用阻塞脚本分析阻塞情况。5 检查OAS库存储过程和用户定义函数的代码。3 发现的问题与建议3.1 内存不足问题描述详细计数器值请参考附录1。本服务器物理内存为4GB，在Boot.ini中打开 3GB的开关。SQL Server能用到的内存则为3GB。性能计数器Target Server Memory 和 Total Server Memory 都是 2.5GB。它们揭示的是SQL Server用于数据缓存能用到和请求到的内存数。由此可见SQL Server已经用尽其能请求到的全部内存。页生命期（Page life expectancy），揭

4、示的是数据页保存在缓存里的平均时间。如果该值低于300秒，就有潜在可能是SQL Server能用能多的内存来提高性能。MemoryAvailable Mbytes，揭示的是操作系统级别有多少可用的内存。该数值应该大于100兆。Page lookups/sec除于Batch Requests/sec应该小于100。Page reads/sec和Page writes/sec应该小于90，这些值太大都揭示了内存的压力。综合从这几个值来看，有如下推论：服务器存在内存压力。根据其后的分析可以得知，不良的索引设计引起的不必要IO操作导致了内存和磁盘的压力。影响程度高建议可以添加物理内存缓解压力。备注由不

5、必要的IO操作引起3.2 磁盘慢 问题描述详细计数器值请参考附录1。对于磁盘计数器，在这里我们关注的是Avg. Disk sec/Read和Avg. Disk sec/Write。如果计数器小于0.008秒（8毫秒），磁盘性能为之优秀；0.008 0.012秒为之良好；0.012 0.20秒为之中；大于0.20秒为之差。由此可见服务器的G盘读写都存在瓶颈。根据其后的分析可以得知，不良的索引设计引起的不必要IO操作导致了内存和磁盘的压力。影响程度高建议目前所有数据库的数据文件日志文件都放在G盘上，可以考虑根据数据库的读写繁忙程度将其迁移到不同的物理磁盘上。目前最忙碌的数据库为OAS、SWAP（全

6、库无索引）、SMSBASE和DBPRO。备注由不必要的IO操作引起3.3 编译，重编译过多问题描述编译和重编译次数过多。在SQL Server 2000里每秒编译的次数应该小于每秒批处理请求数的10%。编译和重编译消耗的是CPU的资源。影响程度中建议使用存储过程，而不是在应用端发送动态SQL语句。备注3.4 不成比例的连接数问题描述平均的用户连接数每秒有3687个，但是活动的还不到十个。经检查目前应用多处配置连接池的大小为200，而且存在测试环境的前端直接连接到生产环境数据库的情况。影响程度中建议配置连接池大小为50。检查应用层是否存在连接泄露的问题。分离测试环境和生产环境。备注3.5 过高的

7、登录注销数问题描述Logins/sec和Logouts/sec平均值为15.8。这说明应用层存在没使用连接池连接数据库的代码。这会增加最终用户请求整个的反应时间，也会消耗SQL Server额外的资源。影响程度中建议检查应用层哪里存在不使用连接池的代码，使用连接池。备注3.6 索引设计和维护问题描述Index Searches/sec比Full Scans/sec ，揭示的是数据库索引的使用情况。此值应该大于1000。服务器上该比值仅有190，由此值可以看出索引设计使用情况不理想。Forwarded Records/sec，每秒通过正向记录指针提取的记录数。该事件只发生在没有聚集索引的表上。该

8、值应该小于Batch Requests/sec的10%，如果太大则说明存在没有聚集索引的表。在以下的最长语句和数据库检查中，可以发现有不少的数据库表并没有聚集索引或非聚集索引。这是引起磁盘高IO和内存瓶颈的根源所在。影响程度高建议对SWAP整库建立相应的索引。对OAS库里没有聚集索引的表建立聚集索引，对外键的列建立非聚集索引，对常用查询的列建立非聚集索引。对索引定时进行维护，对于碎片化高于30%的索引，使用DBCC DBREINDEX的命令重建索引；对碎片化低于30%的索引，使用DBCC INDEXDEFRAG重组索引。备注OAS库的详情请参考附录2。3.7 阻塞类型分析问题描述阻塞是数据库解

9、决并发问题的正常行为。但如果观察到几种普遍等待类型，我们就应该尝试看看在那些方面是否存在瓶颈。在阻塞分析中，可以看到有PAGEIOLATCH_ 类 、WRITELOG和CXPACKET三种比较耗时的阻塞。其中PAGEIOLATCH_ 类是与磁盘-内存交换数据相关、WRITELOG是等待写日志，这两者揭示了已知的内存和磁盘瓶颈。而CXPACKET是和并行计算相关的阻塞，此等待类型全是在并行查询的时候发生的，表明SPID在等待一个并行计算完成或开始。影响程度低建议针对CXPACKET阻塞，可以禁用并行计算。备注详情请参考附录3。3.8 在选择语句中使用用户函数问题描述在IO性能最差的语句中，看到如

10、下的语句：SELECT USER_NAME AS 姓名,(CASE USER_SEX WHEN 0 THEN 男 WHEN 1 THEN 女 END )AS 性别,oas.sf_get_companyname(user_id) as 公司,oas.sf_get_deptName(user_id) as 部门,oas.sf_duty_getdutyname(duty_id) as 职位,MOBILE_PHONE AS 手机, OFFICE_PHONE1 AS 办公电话1, OFFICE_PHONE2 AS 办公电话2,FAX_PHONE AS 公司传真,USER_ID + UNICOMGD.CO

11、M AS 电子邮件地址,-人员排序-oas.sf_duty_getdutylevel(duty_id) as dutylevel,(case Dept_ID when 15 then -999 ELSE dept_id end ) as dept_oryderby, - 用于部门排序1 AS foxaddrIDFROM oas.T_PUB_USERwhere company_id =2order by Company_Id,dept_oryderby,dutylevel,OrderBy而这几个用户函数实际上又是子查询。影响程度高建议重写该查询语句，不要使用用户函数。备注3.9 使用游标问题描述

12、在代码检查中发现在主表上使用游标进行循环的语句。这种语句会造成过长的事务，过多的锁从而影响性能。如Statistic_WipeOffTimeOut， AnnexSYNCOK， StatisticWorkFlow_Company。影响程度中建议建议改写这些存储过程，不使用游标，或者将中间结果保存在临时表对象中，然后在临时表对象上打开游标。备注3.10 JOIN没有使用ON从句问题描述在数据捕获期捕获到Missing Join Predicate的事件，说明有的JOIN语句没用ON从句。这将导致两张全表的叉乘，从而引起不必要的IO操作。影响程度中建议为JOIN语句加上ON从句。备注3.11 测试环

13、境和生产环境的混合问题描述测试环境的前端直接连接到生产环境的数据库。这样不单会带来性能问题，也带来了数据安全性的问题。影响程度中建议建议测试环境和生产环境分离。备注3.12 不恰当的文件自动增长率问题描述OAS和SMSBASE的文件大小已经超过10GB，而其数据文件自增长率依旧设置为10%。如果该动作发生在业务时间，将造成长时间的系统停顿。影响程度中建议使用固定增长值，如500MB替代10%。备注4 附录1 重要性能计数器数据计数器实例最小值最大值平均值MemoryAvailable MBytes567834777.588MemoryFree System Page Table Entries

14、8,024.008,841.008,439.75MemoryPages Input/sec0312.1986.16MemoryPages/sec0334.59821.659Paging File% Usage?C:pagefile.sys12.79714.91113.875Paging File% Usage_Total12.79714.91113.875Paging File% Usage Peak?C:pagefile.sys22.59322.59322.593Paging File% Usage Peak_Total22.59322.59322.593Process% Privilege

15、d Timesqlservr053.43817.895Process% Processor Timesqlservr1.867758.75417.544Processor% Privileged Time_Total0.33336.0168.367Processor% Privileged Time0042.8139.531Processor% Privileged Time1032.8137.424Processor% Privileged Time20.31375.62510.366Processor% Privileged Time3040.9386.524Processor% Priv

16、ileged Time4044.6888.493Processor% Privileged Time5045.3137.825Processor% Privileged Time60.26775.6259.85Processor% Privileged Time70.26761.256.924Processor% Processor Time_Total099.80559.947Processor% Processor Time0099.68851.058Processor% Processor Time1010052.636Processor% Processor Time2010058.7

17、91Processor% Processor Time3010059.316Processor% Processor Time4010062.914Processor% Processor Time5010063.659Processor% Processor Time6099.68864.419Processor% Processor Time7010066.811SystemContext Switches/sec191.81563,047.6911,654.20SystemProcessor Queue Length0280.819% Idle Time0 C:096.89881.896

18、Avg. Disk sec/Read0 C:00.0980.002Avg. Disk sec/Write0 C:0.0070.1570.015% Idle Time1 E:35.586100.198.544Avg. Disk sec/Read1 E:00.3580.004Avg. Disk sec/Write1 E:00.660.006% Idle Time2 F:16.118100.16699.397Avg. Disk sec/Read2 F:00.0170Avg. Disk sec/Write2 F:00.5990.006% Idle Time3 G:094.30643.008Avg. D

19、isk sec/Read3 G:0.0021.4770.04Avg. Disk sec/Write3 G:08.2020.097SQLServer:Access MethodsForwarded Records/sec0821.0954.156SQLServer:Access MethodsFreeSpace Scans/sec0166.93619.719SQLServer:Access MethodsFull Scans/sec0148.39835.347SQLServer:Access MethodsIndex Searches/sec021,102.086,680.15SQLServer

20、:Access MethodsPage Splits/sec03.80.406SQLServer:Access MethodsRange Scans/sec08,383.822,857.50SQLServer:Access MethodsScan Point Revalidations/sec0430.4081.568SQLServer:Access MethodsWorkfiles Created/sec0182.40656.785SQLServer:Access MethodsWorktables Created/sec0119.00521.27SQLServer:Buffer Manag

21、erBuffer cache hit ratio96.45899.88499.653SQLServer:Buffer ManagerCheckpoint pages/sec01,982.6223.429SQLServer:Buffer ManagerFree pages33,478.001,561.34SQLServer:Buffer ManagerLazy Writes/Sec0162.59911.779SQLServer:Buffer ManagerPage life expectancy0884291.87SQLServer:Buffer ManagerPage lookups/sec0

22、162,566.3570,776.30SQLServer:Buffer ManagerPage reads/sec05,286.28389.404SQLServer:Buffer ManagerPage writes/sec01,902.81140.641SQLServer:General StatisticsLogins/sec047.215.807SQLServer:General StatisticsLogouts/sec052.60215.794SQLServer:LatchesLatch Waits/sec0621.42276.762SQLServer:LatchesTotal La

23、tch Wait Time (ms)0462,562.465,523.86SQLServer:LocksLock Requests/sec(_Total)_Total0359,291.81110,555.87SQLServer:LocksLock Wait Time (ms)(_Total)_Total086,122.09515.999SQLServer:LocksLock Waits/sec(_Total)_Total024.3990.419SQLServer:LocksNumber of Deadlocks/sec(_Total)_Total00.20SQLServer:Memory Ma

24、nagerMemory Grants Pending000SQLServer:Memory ManagerTarget Server Memory(KB)2,691,384.002,724,984.002,707,275.82SQLServer:Memory ManagerTotal Server Memory (KB)2,691,384.002,724,984.002,707,275.835 附录2 OAS库中没有任何索引和没有聚集索引的表没有任何索引的表：表行数t_workflow_usermailhisstory_bak13513254T_Flow_EndLogFieldHistory_

25、20042819808T_Flow_EndLogStatusHistory_20052320491T_Doc_Annex_bak200608241963569T_Flow_EndLogFieldHistory_20051955621T_Flow_EndLogMsgHistory_2004950991T_SYNC_FILE_LOG807715T_Flow_EndLogFieldHistory_2003674016T_Flow_EndLogMsgHistory_2005661430docatt630607docdelinfo485715t_error_info335594T_Flow_Repeat

26、210757T_Flow_EndLogMsgHistory_2003203861T_WorkFlow_StatisticReport200790987t_del_history284269T_WorkFlow_StatisticReport200683419T_WorkFlow_StatisticReport200881240T_WorkFlow_StatisticReport200569202T_INSTANCE_FLOW_DISPOSAL_OLD66853T_INSTANCE_FLOW_STATUS_OLD66853T_Flow_EndLogStatusHistory_200654010T

27、_Flow_EndLogFieldHistory_200648826T_Gd_GdMsg41453T_Backup_WorkFlow_StatisticReport200528937T_Flow_UpdateLog27588T_Salary_Standard27298T_Flow_FileNumber_bak22688T_Flow_EndLogMsgHistory_200622198T_Meeting_NoticeUser21494T_BBS_Vote18662T_WorkFlow_No18001T_Flow_FileWater_bak16119t_sms_group13468T_Pub_Us

28、erRole_bak13142T_Project_UserRole11964init_portal_pwd11724AD2DBUMapping11001T_Flow_Number9668T_WriteLog7941T_Office_Calendar7026t_temp_file6042tb_addr4710T_CollAim_Att_bak4590T_WorkFlow_StatisticReport20094389T_PERSON2752T_Flow_Status_Roles2679T_del_history12246T_Stock_Out2044t_cs1943T_FlowRetrieve_

29、Logs1727T_Gd_Value1389T_Neikong_flow_by_post_dept$1096result2003959result2003F932AD2DBDMapping921T_IdeaTool_Log878T_Flow_TypeCoding854t_pub_idsgroup2role788T_Stock_In775waitstats770T_Flowtrace_User746T_Doc_Permission672T_PUB_PARTJOB20081217back657T_FLOW_TYPE_BAK656T_WorkPlan_Admin565T_Office_Consign

30、525T_Flow_Email478T_as437t_bs437T_NEIKONG_DEPT_FLOW437T_NEIKONG_FLOW436T_ORGANIZATION436choice_engage430aaaa379T_Flow_EndLogMsgHistory_2002358T_PHONE_LAND339T_HumanVote314META_OLAP_USER_PAR291T_WorkFlow_StatisticAdmin291temp_table_size273T_Stock241T_NetS_File230T_NETS_COMMENT213t_immail_answer206T_F

31、low_NextSteps193T_Flow_EndLogFieldHistory_2002190T_OTOOLS_DETAIL185wps_user176a_t_pub_user1175T_HELP_DATA163T_Compare_0201162T_Gd_Right152T_Workflow_XuQiuInfo151T_ProjectVote131docerr122T_DeptPage_Power105aaaaz100T_CityCataLog_Link100huiyi$99trace_events98T_Flow_Page_Roles96T_BAIRIZHENGSHOU_IMG89T_T

32、oolDown_UserLevel81T_OTOOLS_SUM79T_FlowWeb_Manage78T_Room_User67GetTablesIndex61T_Flow_Page_Rights55T_ToolDown_File48t_anquan_info_bak46T_Gd_X43capital41Swap_OrgCode41T_Stock_Admin38T_Flow_WindowsClass32T_NEIKONG_FLOW_BY_USER28AD2DBCMapping26T_NEIKONG_USER26t_pub_docdept24T_PUB_PARAM24tabpy24t_baoxi

33、an_info23T_SYNC_MODULECOMPANY23T_SYNC_COMPANY22t_mobile_smscheck21T_Workflow_Notify_SMessage20t_immail_consign18T_Result_Catalog18t_pub_syldapuser17t_baoxian_user13T_POPSET_ADMIN13T_DEPTPAGE_CATALOGURLS12T_Gd_Y12Results310t_anquan_user10AD2DBPropMappings8T_Gd_Type8T_ToolDown_Sort8t_reform_user7T_Res

34、ult_GradeStandard7t_share_user7t_immail_box6t_workflow_trando6tab_stock_StockNowDataInfo6t_bairizhengshou_user5output_list4T_PUB_IDSGROUPLOG4T_Port_Information3T_Result_UserPower2T_ToolManager_Static2T_WorkFlow_GlobalData2tab_message_mo2choice_expire1T_Client_Role1T_GD_Reback1T_NEIKONG_IMG1T_NEIKONG

35、_INFO1T_NetS_PTYPE1T_Result_Subject1t_unihappen_msg1T_WorkPlan_NoShowDept1oas.T_Doc_File_Bak20040sm_interface0sm_interface_group0sm_interface_qunfa0SWAP_HMag0T_DeptPage_PowerType0T_Doc_Annex_No0T_Gd_EndValueHistory0T_Gd_RightDef0T_NEIKONG_FLOW_BY_POST0T_NeiKong_UserTest0T_Port_NewsRemark0T_Pub_FeedB

36、ack0T_Pub_GroupRole0t_pub_moduleadmin0T_Pub_WFTool_Control0T_Result_Attachment0T_Result_Content0T_Result_Role0T_Result_UserRole0T_Score_List0T_SMS_DeliverSend0t_sms_send_temp0T_Stock_Bill0T_TimeSpan0T_ToolManager_Userlevel0t_workflow_usermailhistory0没有聚集索引的表：表行数T_Doc_Annex_del_20064911381T_INSTANCE_

37、FLOW_STATUS3864362T_FLOW_ENDLOGSTATUS_20063784982T_FLOW_ENDLOGFIELD_20073546050t_workflow_recmailuserhistory3513254T_FLOW_ENDLOGATT_20073499158T_FLOW_ENDLOGFIELD_20063219378T_FLOW_ENDLOGATT_20063170552T_FLOW_ENDLOGFIELD_20052862700T_FLOW_ENDLOGFIELD_20042837502T_Doc_File_del_20061702550T_Doc_File169

38、9555T_Doc_Annex_20061320494waitDelAnnex1320488T_Doc_Annex_20071301681T_Doc_Annex_20051158451T_FLOW_LOGMSG_20061034230T_FLOW_LOGMSG_2005970204T_FLOW_LOGMSG_2004968542T_WorkFlow_UserMail_bak924240T_Doc_Annex911713T_FLOW_ENDLOGATT_2005909113T_FLOW_ENDLOGFIELD_2003675199T_Doc_File_2007466397T_Doc_File_2

39、006464284T_Doc_File_2005406070T_SMS_SendLogHistory272167T_SYNC_FILE205553T_FLOW_LOGMSG_2003205342T_INSTANCE_FLOW_DISPOSAL145010T_WorkFlow_UserMailHistory_new137664T_WorkFlow_ZaiBan58975T_FLOW_ENDLOGATT_200418014T_Deptpage_Directory3533t_pub_org1505T_FLOW_ENDLOGATT_20031196T_Deptpage_Manager498T_FLOW

40、_LOGMSG_2002363T_FLOW_ENDLOGFIELD_2002190T_PUB_duty94T_BAIRIZHENGSHOU30pbcatcol0pbcattbl0t_Del_history0T_Doc_Annex_20080T_Doc_File_20080T_FLOW_ENDLOGATT_20020T_FLOW_ENDLOGATT_20080T_Flow_EndLogAttHistory0T_FLOW_ENDLOGFIELD_20080T_FLOW_LOGMSG_20070T_FLOW_LOGMSG_20080T_PUB_TEMP_VOTE0T_System_Logs06 附录

41、3 阻塞类型统计namerequestswaittimesignalwaittimeavgRESOURCE_QUEUE246456025185920085412300102.19236SLEEP3595128448430084352000234.99716PAGEIOLATCH_SH17658607803800017569544.192631WAITFOR1414070704760707047605000.3366WRITELOG135889219421820120844014.292394PAGEIOLATCH_EX425781118762303088527.892813LCK_M_S147

42、34746429031912506.60309NETWORKIO2142896642602002.998755IO_COMPLETION79154356663962827927.1586711LATCH_EX280200504750041679018.013919LCK_M_IX36429205447778023.4725CXPACKET13114242438015927184.86961LCK_M_IS373125242025323357.6944LCK_M_X1433757802662627.8322PAGELATCH_EX2505043426413239931.3678065LCK_M_

43、U400114496889286.24PAGEIOLATCH_UP21109430021744.691943PAGELATCH_SH2544377849119603.0597414PAGELATCH_UP1257518553149491.4753877PAGESUPP22551132328365.021286RESOURCE_SEMAPHORE189729980738.619048LOGBUFFER1520170134.46667CMEMTHREAD3346125212200.3741781EXCHANGE25126325.04EC632165.3333333LATCH_UP1832161.7

44、777778MISCELLANEOUS28000OLEDB23201157800PIPELINE_INDEX_STAT5000LATCH_SH1000Total992920054863400024310680055.2546037 参考资料PSSDIAG 工具下载 - 了解更多 PSSDIAG背景资料，可以访问以下KB文章：MPSReport 工具下载 - 与 SQL Server相关的 MPSReport,，可以访问以下KB文章：在 Windows Server 2003 和 Windows 2000上的大内存支持 sys.dm_db_index_physical_stats SQL Server等待类型