Report數(shù)據(jù)庫性能檢測

1、廣東聯(lián)通數(shù)據(jù)庫性能檢查報告 Prepared for廣東聯(lián)通公司2009年1月15日Version 1.0Prepared by邱詩揚Premier Field Engineershiyang.qiu目錄1總結(jié)12數(shù)據(jù)收集和分析工具23發(fā)現(xiàn)的問題與建議33.1內(nèi)存不足33.2磁盤慢33.3編譯，重編譯過多43.4不成比例的連接數(shù)43.5過高的登錄注銷數(shù)43.6索引設(shè)計和維護53.7阻塞類型分析53.8在選擇語句中使用用戶函數(shù)63.9使用游標73.10JOIN沒有使用ON從句73.11測試環(huán)境和生產(chǎn)環(huán)境的混合73.12不恰當?shù)奈募詣釉鲩L率74附錄1 重要性能計數(shù)器數(shù)據(jù)85附錄2 OAS庫中沒有

2、任何索引和沒有聚集索引的表116附錄3 阻塞類型統(tǒng)計177參考資料181 總結(jié)本報告為廣東聯(lián)通公司SQLCLUSTER03實例的性能檢查報告。廣東聯(lián)通公司的OA系統(tǒng)的響應時間過長，希望微軟工程師從數(shù)據(jù)庫層進行性能檢查，找出性能瓶頸，通過優(yōu)化性能差的語句縮短查詢時間。本次調(diào)優(yōu)不涉及應用層的代碼分析。在檢查中發(fā)現(xiàn)，不良索引設(shè)計是引起性能問題的最主要原因。2 數(shù)據(jù)收集和分析工具1 用PSSDIAG工具捕獲如下主要信息（1月12-13日）：· SQL Server Profiler（至存儲過程/批處理級別）· Perfmon（間隔15秒）· 阻塞情況2 用Relog分析P

3、erfmon的輸出。3 用RML分析Profiler Trace，找出性能不良的查詢。4 用阻塞腳本分析阻塞情況。5 檢查OAS庫存儲過程和用戶定義函數(shù)的代碼。3 發(fā)現(xiàn)的問題與建議3.1 內(nèi)存不足問題描述詳細計數(shù)器值請參考附錄1。本服務器物理內(nèi)存為4GB，在Boot.ini中打開 3GB的開關(guān)。SQL Server能用到的內(nèi)存則為3GB。性能計數(shù)器Target Server Memory 和 Total Server Memory 都是 2.5GB。它們揭示的是SQL Server用于數(shù)據(jù)緩存能用到和請求到的內(nèi)存數(shù)。由此可見SQL Server已經(jīng)用盡其能請求到的全部內(nèi)存。頁生命期（Page

4、life expectancy），揭示的是數(shù)據(jù)頁保存在緩存里的平均時間。如果該值低于300秒，就有潛在可能是SQL Server能用能多的內(nèi)存來提高性能。MemoryAvailable Mbytes，揭示的是操作系統(tǒng)級別有多少可用的內(nèi)存。該數(shù)值應該大于100兆。Page lookups/sec除于Batch Requests/sec應該小于100。Page reads/sec和Page writes/sec應該小于90，這些值太大都揭示了內(nèi)存的壓力。綜合從這幾個值來看，有如下推論：服務器存在內(nèi)存壓力。根據(jù)其后的分析可以得知，不良的索引設(shè)計引起的不必要IO操作導致了內(nèi)存和磁盤的壓力。影響程度高建

5、議可以添加物理內(nèi)存緩解壓力。備注由不必要的IO操作引起3.2 磁盤慢 問題描述詳細計數(shù)器值請參考附錄1。對于磁盤計數(shù)器，在這里我們關(guān)注的是Avg. Disk sec/Read和Avg. Disk sec/Write。如果計數(shù)器小于0.008秒（8毫秒），磁盤性能為之優(yōu)秀；0.008 0.012秒為之良好；0.012 0.20秒為之中；大于0.20秒為之差。由此可見服務器的G盤讀寫都存在瓶頸。根據(jù)其后的分析可以得知，不良的索引設(shè)計引起的不必要IO操作導致了內(nèi)存和磁盤的壓力。影響程度高建議目前所有數(shù)據(jù)庫的數(shù)據(jù)文件日志文件都放在G盤上，可以考慮根據(jù)數(shù)據(jù)庫的讀寫繁忙程度將其遷移到不同的物理磁盤上。目前

6、最忙碌的數(shù)據(jù)庫為OAS、SWAP（全庫無索引）、SMSBASE和DBPRO。備注由不必要的IO操作引起3.3 編譯，重編譯過多問題描述編譯和重編譯次數(shù)過多。在SQL Server 2000里每秒編譯的次數(shù)應該小于每秒批處理請求數(shù)的10%。編譯和重編譯消耗的是CPU的資源。影響程度中建議使用存儲過程，而不是在應用端發(fā)送動態(tài)SQL語句。備注3.4 不成比例的連接數(shù)問題描述平均的用戶連接數(shù)每秒有3687個，但是活動的還不到十個。經(jīng)檢查目前應用多處配置連接池的大小為200，而且存在測試環(huán)境的前端直接連接到生產(chǎn)環(huán)境數(shù)據(jù)庫的情況。影響程度中建議配置連接池大小為50。檢查應用層是否存在連接泄露的問題。分離測

7、試環(huán)境和生產(chǎn)環(huán)境。備注3.5 過高的登錄注銷數(shù)問題描述Logins/sec和Logouts/sec平均值為15.8。這說明應用層存在沒使用連接池連接數(shù)據(jù)庫的代碼。這會增加最終用戶請求整個的反應時間，也會消耗SQL Server額外的資源。影響程度中建議檢查應用層哪里存在不使用連接池的代碼，使用連接池。備注3.6 索引設(shè)計和維護問題描述Index Searches/sec比Full Scans/sec ，揭示的是數(shù)據(jù)庫索引的使用情況。此值應該大于1000。服務器上該比值僅有190，由此值可以看出索引設(shè)計使用情況不理想。Forwarded Records/sec，每秒通過正向記錄指針提取的記錄數(shù)。

8、該事件只發(fā)生在沒有聚集索引的表上。該值應該小于Batch Requests/sec的10%，如果太大則說明存在沒有聚集索引的表。在以下的最長語句和數(shù)據(jù)庫檢查中，可以發(fā)現(xiàn)有不少的數(shù)據(jù)庫表并沒有聚集索引或非聚集索引。這是引起磁盤高IO和內(nèi)存瓶頸的根源所在。影響程度高建議對SWAP整庫建立相應的索引。對OAS庫里沒有聚集索引的表建立聚集索引，對外鍵的列建立非聚集索引，對常用查詢的列建立非聚集索引。對索引定時進行維護，對于碎片化高于30%的索引，使用DBCC DBREINDEX的命令重建索引；對碎片化低于30%的索引，使用DBCC INDEXDEFRAG重組索引。備注OAS庫的詳情請參考附錄2。3.7

9、 阻塞類型分析問題描述阻塞是數(shù)據(jù)庫解決并發(fā)問題的正常行為。但如果觀察到幾種普遍等待類型，我們就應該嘗試看看在那些方面是否存在瓶頸。在阻塞分析中，可以看到有PAGEIOLATCH_ 類 、WRITELOG和CXPACKET三種比較耗時的阻塞。其中PAGEIOLATCH_ 類是與磁盤-內(nèi)存交換數(shù)據(jù)相關(guān)、WRITELOG是等待寫日志，這兩者揭示了已知的內(nèi)存和磁盤瓶頸。而CXPACKET是和并行計算相關(guān)的阻塞，此等待類型全是在并行查詢的時候發(fā)生的，表明SPID在等待一個并行計算完成或開始。影響程度低建議針對CXPACKET阻塞，可以禁用并行計算。備注詳情請參考附錄3。3.8 在選擇語句中使用用戶函數(shù)問

10、題描述在IO性能最差的語句中，看到如下的語句：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

11、AS 辦公電話2,FAX_PHONE AS 公司傳真,USER_ID + 'UNICOMGD.COM' 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,duty

12、level,OrderBy而這幾個用戶函數(shù)實際上又是子查詢。影響程度高建議重寫該查詢語句，不要使用用戶函數(shù)。備注3.9 使用游標問題描述在代碼檢查中發(fā)現(xiàn)在主表上使用游標進行循環(huán)的語句。這種語句會造成過長的事務，過多的鎖從而影響性能。如Statistic_WipeOffTimeOut， AnnexSYNCOK， StatisticWorkFlow_Company。影響程度中建議建議改寫這些存儲過程，不使用游標，或者將中間結(jié)果保存在臨時表對象中，然后在臨時表對象上打開游標。備注3.10 JOIN沒有使用ON從句問題描述在數(shù)據(jù)捕獲期捕獲到Missing Join Predicate的事件，說明有的J

13、OIN語句沒用ON從句。這將導致兩張全表的叉乘，從而引起不必要的IO操作。影響程度中建議為JOIN語句加上ON從句。備注3.11 測試環(huán)境和生產(chǎn)環(huán)境的混合問題描述測試環(huán)境的前端直接連接到生產(chǎn)環(huán)境的數(shù)據(jù)庫。這樣不單會帶來性能問題，也帶來了數(shù)據(jù)安全性的問題。影響程度中建議建議測試環(huán)境和生產(chǎn)環(huán)境分離。備注3.12 不恰當?shù)奈募詣釉鲩L率問題描述OAS和SMSBASE的文件大小已經(jīng)超過10GB，而其數(shù)據(jù)文件自增長率依舊設(shè)置為10%。如果該動作發(fā)生在業(yè)務時間，將造成長時間的系統(tǒng)停頓。影響程度中建議使用固定增長值，如500MB替代10%。備注4 附錄1 重要性能計數(shù)器數(shù)據(jù)計數(shù)器實例最小值最大值平均值Mem

14、oryAvailable MBytes567834777.588MemoryFree System Page Table Entries8,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.

15、593Paging File% Usage Peak_Total22.59322.59322.593Process% Privileged Timesqlservr053.43817.895Process% Processor Timesqlservr1.867758.75417.544Processor% Privileged Time_Total0.33336.0168.367Processor% Privileged Time0042.8139.531Processor% Privileged Time1032.8137.424Processor% Privileged Time20.3

16、1375.62510.366Processor% Privileged Time3040.9386.524Processor% Privileged 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.058Pr

17、ocessor% Processor Time1010052.636Processor% Processor Time2010058.791Processor% Processor Time3010059.316Processor% Processor Time4010062.914Processor% Processor Time5010063.659Processor% Processor Time6099.68864.419Processor% Processor Time7010066.811SystemContext Switches/sec191.81563,047.6911,65

18、4.20SystemProcessor Queue Length0280.819% Idle Time0 C:096.89881.896Avg. 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.0170

19、Avg. Disk sec/Write2 F:00.5990.006% Idle Time3 G:094.30643.008Avg. Disk 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.347S

20、QLServer:Access MethodsIndex Searches/sec021,102.086,680.15SQLServer: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:Acc

21、ess MethodsWorktables Created/sec0119.00521.27SQLServer:Buffer ManagerBuffer 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 ManagerPag

22、e life expectancy0884291.87SQLServer:Buffer ManagerPage lookups/sec0162,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.794SQL

23、Server:LatchesLatch Waits/sec0621.42276.762SQLServer:LatchesTotal Latch 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.419SQLServe

24、r:LocksNumber of Deadlocks/sec(_Total)_Total00.20SQLServer:Memory ManagerMemory 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庫中沒有任何索引和沒有聚集索引的表沒有任何索引的表：

25、表行數(shù)t_workflow_usermailhisstory_bak13513254T_Flow_EndLogFieldHistory_20042819808T_Flow_EndLogStatusHistory_20052320491T_Doc_Annex_bak200608241963569T_Flow_EndLogFieldHistory_20051955621T_Flow_EndLogMsgHistory_2004950991T_SYNC_FILE_LOG807715T_Flow_EndLogFieldHistory_2003674016T_Flow_EndLogMsgHistory_2

26、005661430docatt630607docdelinfo485715t_error_info335594T_Flow_Repeat210757T_Flow_EndLogMsgHistory_2003203861T_WorkFlow_StatisticReport200790987t_del_history284269T_WorkFlow_StatisticReport200683419T_WorkFlow_StatisticReport200881240T_WorkFlow_StatisticReport200569202T_INSTANCE_FLOW_DISPOSAL_OLD66853

27、T_INSTANCE_FLOW_STATUS_OLD66853T_Flow_EndLogStatusHistory_200654010T_Flow_EndLogFieldHistory_200648826T_Gd_GdMsg41453T_Backup_WorkFlow_StatisticReport200528937T_Flow_UpdateLog27588T_Salary_Standard27298T_Flow_FileNumber_bak22688T_Flow_EndLogMsgHistory_200622198T_Meeting_NoticeUser21494T_BBS_Vote1866

28、2T_WorkFlow_No18001T_Flow_FileWater_bak16119t_sms_group13468T_Pub_UserRole_bak13142T_Project_UserRole11964init_portal_pwd11724AD2DBUMapping11001T_Flow_Number9668T_WriteLog7941T_Office_Calendar7026t_temp_file6042tb_addr4710T_CollAim_Att_bak4590T_WorkFlow_StatisticReport20094389T_PERSON2752T_Flow_Stat

29、us_Roles2679T_del_history12246T_Stock_Out2044t_cs1943T_FlowRetrieve_Logs1727T_Gd_Value1389T_Neikong_flow_by_post_dept$1096result2003959result2003F932AD2DBDMapping921T_IdeaTool_Log878T_Flow_TypeCoding854t_pub_idsgroup2role788T_Stock_In775waitstats770T_Flowtrace_User746T_Doc_Permission672T_PUB_PARTJOB

30、20081217back657T_FLOW_TYPE_BAK656T_WorkPlan_Admin565T_Office_Consign525T_Flow_Email478T_as437t_bs437T_NEIKONG_DEPT_FLOW437T_NEIKONG_FLOW436T_ORGANIZATION436choice_engage430aaaa379T_Flow_EndLogMsgHistory_2002358T_PHONE_LAND339T_HumanVote314META_OLAP_USER_PAR291T_WorkFlow_StatisticAdmin291temp_table_s

31、ize273T_Stock241T_NetS_File230T_NETS_COMMENT213t_immail_answer206T_Flow_NextSteps193T_Flow_EndLogFieldHistory_2002190T_OTOOLS_DETAIL185wps_user176a_t_pub_user1175T_HELP_DATA163T_Compare_0201162T_Gd_Right152T_Workflow_XuQiuInfo151T_ProjectVote131docerr122T_DeptPage_Power105aaaaz100T_CityCataLog_Link1

32、00huiyi$99trace_events98T_Flow_Page_Roles96T_BAIRIZHENGSHOU_IMG89T_ToolDown_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_USER28AD2DB

33、CMapping26T_NEIKONG_USER26t_pub_docdept24T_PUB_PARAM24tabpy24t_baoxian_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_u

34、ser10AD2DBPropMappings8T_Gd_Type8T_ToolDown_Sort8t_reform_user7T_Result_GradeStandard7t_share_user7t_immail_box6t_workflow_trando6tab_stock_StockNowDataInfo6t_bairizhengshou_user5output_list4T_PUB_IDSGROUPLOG4T_Port_Information3T_Result_UserPower2T_ToolManager_Static2T_WorkFlow_GlobalData2tab_messag

35、e_mo2choice_expire1T_Client_Role1T_GD_Reback1T_NEIKONG_IMG1T_NEIKONG_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_N

36、EIKONG_FLOW_BY_POST0T_NeiKong_UserTest0T_Port_NewsRemark0T_Pub_FeedBack0T_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_workflo

37、w_usermailhistory0沒有聚集索引的表：表行數(shù)T_Doc_Annex_del_20064911381T_INSTANCE_FLOW_STATUS3864362T_FLOW_ENDLOGSTATUS_20063784982T_FLOW_ENDLOGFIELD_20073546050t_workflow_recmailuserhistory3513254T_FLOW_ENDLOGATT_20073499158T_FLOW_ENDLOGFIELD_20063219378T_FLOW_ENDLOGATT_20063170552T_FLOW_ENDLOGFIELD_20052862700T

38、_FLOW_ENDLOGFIELD_20042837502T_Doc_File_del_20061702550T_Doc_File1699555T_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_2005

39、909113T_FLOW_ENDLOGFIELD_2003675199T_Doc_File_2007466397T_Doc_File_2006464284T_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_LOGMSG_2002363T_FLOW_ENDLOGFIELD_2002190T_PUB_duty94T_BAIRIZHENGSHOU30pbcatcol0pbcattbl0t_Del_history0T_Doc_Annex_20080T_Doc_File_20080T_FLOW_ENDLOGATT_20020T_FLOW_ENDLOGATT_20080