Bài giảng Cơ sở dữ liệu nâng cao - Chapter 8: Monitoring SQL server

Bài giảng Cơ sở dữ liệu nâng cao - Chapter 8: Monitoring SQL server. Chương này trình bày một số nội dung: The goal of monitoring, performance monitor, dynamic management views, monitoring events, event notifications,...
Nội dung trích xuất từ tài liệu:
Bài giảng Cơ sở dữ liệu nâng cao - Chapter 8: Monitoring SQL serverMonitoringSQLServerThe Goal of MonitoringThegoalofmonitoringdatabasesistosee: What’sgoingoninsideSQLServer, HoweffectivelySQLServerisusingtheserverresources (CPU,Memory,I/O).TheinformationenablesDBAidentifyabnormalactivitiesThe Goal of MonitoringOnceyoudefineyourmonitoringgoalsyoushouldselect theappropriatetoolsformonitoring.Thefollowinglistdescribesbasicmonitoringtoolsto viewthecurrentactivities: PerformanceMonitor:ausefultoolthattracksresourceuse onMicrosoftoperatingsystems.  Itcanmonitorresourceusagefortheserverandprovide informationspecifictoSQLServereitherlocallyorfora remoteserver SQLProfiler:agraphicalapplicationthatenablesyouto captureatraceofeventsthatoccurredinSQLServer.The Goal of MonitoringThefollowinglistdescribesthebasicmonitoringtools: SQLTrace:theTSQLstoredprocedurewaytoinvokea SQLServertracewithouthavingtostartuptheSQL Profilerapplication.Itrequiresalittlemoreworktosetup, butit’salightweightwaytocaptureatrace  It’sscriptableenablestheautomationoftracecapture Defaulttrace:alightweighttracethatrunsinacontinuous loopandcapturesasmallsetofkeydatabaseandserver events.  usefulindiagnosingeventsthatmayhaveoccurredwhenno othermonitoringwasinplace.The Goal of MonitoringThefollowinglistdescribesthebasicmonitoringtools: ActivityMonitor:atoolgraphicallydisplaysthefollowing information:  ProcessesrunningonaninstanceofSQLServer  Locks  Useractivity  Blockedprocesses Dynamicmanagementviews:returnserverstate informationthatcanbeusedtomonitorthehealthofa serverinstance,diagnoseproblems,andtuneperformance. TransactSQL:Somesystemstoredproceduresprovide usefulinformationforSQLServermonitoring,suchas sp_who,sp_who2,sp_lock,andseveralothers.Performance MonitorPerformanceMonitorisanimportanttoolbecauseit enablestoknow: HowSQLServerisperforming HowWindowsisperforming.Threeserverresourcesneedstobemonitored: CPU Memory I/OPerformance MonitorCPUResourceCounters: SeveralcountersshowthestateoftheavailableCPU resources. BottlenecksduetoCPUresourceshortagesarefrequently causedbyproblemssuchas:  Moreusersthanexpected  Oneormoreusersrunningveryexpensivequeries  Routineoperationalactivitiessuchasindexrebuilding.Performance MonitorCPUResourceCounters: Thefollowingcounterswillhelptofindthecauseofthe bottlenecksothattoidentifythatthebottleneckisaCPU resourceissue:  Processor:%ProcessorTime:displaysthetotalpercentageof timespentprocessingnonidlethreads.Onamultipleprocessor machine,eachindividualprocessorcanbemonitored independently.  Process:%ProcessorTime(sqlservr):canbeusedtodetermine howmuchofthetotalprocessingtimecanbeattributedtoSQL Server.  System:ProcessorQueueLength:displaysthenumberof threadswaitingtobeprocessedbyaCPU.Performance MonitorDiskActivity: SQLServerreliesontheWindowsoperatingsystemto performI/Ooperations. Thedisksystemhandlesthestorageandmovementofdata onyoursystem.DiskI/Oisfrequentlythecauseof bottlenecksinasystem. Needtoobservemanyfactorsindeterminingthe performanceofthedisksystem SeveraldiskcountersreturndiskReadandWrite performanceinformation,aswellasdatatransfer information,foreachphysicaldiskoralldisks.Performance MonitorMemoryCounters: UsedbytheDBAtogetanoverallpictureofdatabaseI/O. Alackofmemorywillhaveadirectimpactondiskactivity. Whenoptimizingaserver,addingmemoryshouldalways beconsidered. ThesearesomeMemorycounters:  Memory:Pages/Sec:measuresthenumberofpagespersecond thatrepagedoutofmemorytodiskorpagedintomemoryfrom disk.  Memory:AvailableBytes:indicateshowmuchmemoryis availabletoprocesses.  Process:WorkingSet(sqlservr)TheSQLServerinstanceof theWorkingSetcountershowshowmuchmemoryisinuseby SQLServer.Performance MonitorMemoryCounters:  SQLServer:BufferManager:BufferCacheHitRatio measuresthepercentageoftimethatdatawasfoundinthe bufferwithouthavingtobereadfromdisk.  Thiscountershouldbeveryhigh,optimally90%orbetter. When ...