SQLServer性能监控指标说明备课讲稿

SQLServer性能监控指标说明艳计。 GBPAE/AWE 8 1.2.I/O 9 .SMP 151.5.2.NUMA 151.5.3.SOFTNUMA 15 2.3.Cpu 19 3.1.Cpu 20 l 1.1.1.物理地址空间1.1.2.虚拟地址空间1.1.3.虚拟内存管理器虚拟内存管理器(vmm)管理所有内存，并在系统中所有使用内存的进程程在需要内存时提供4gb的虚拟内存的错bGB内存bit系统中进程内存数量的一种方法是将分配给内核的一部分空间拿1.1.5./PAE1.1.6.AWE地址窗口扩展(AWE)是windows的api，允许32bit的进程映射虚拟地址GB、/PAE、还是/AWE4bit系统内存配置1.1.9.内存预计大小内存估计大小=(每个所有者64字节+32字节)*锁的数量+(3*networkpacketsizeKB+94KB)*会话数会话有多个活动结果集，那么内存使用量为(3+3*num_logical_connections)*network_packet_size+94KBSqlserver启动时根据配置(系统最大物理内存数，系统最大线程数和其他参存储设计 (预期磁盘驱动器用法)。高占空比意味着驱动器被设计为用于持续的7*24小串行ATA(SATA)和串行SCSI(SAS)是现今最常见的新驱动器。串行代，基本上与并行产品相同，不同在于磁盘内加了新的高速直连式存储(DAS)是讲一个服务器直接连接到磁盘阵列上，提供最佳的存储区域网络(SAN)提供了一个公共存储池，给网络提供健壮动态的存量高速缓存。果没有使用SAN，可以将设备管理器队列长度设置为64到128。1.2.4.配置分区1.2.5.扇区对齐扇区是磁盘最小存储空间，通常为512字节。磁道是位于磁盘一个圆周上道有63个扇区。块是扇区的逻辑称谓。由于不同存储供应商在定义磁道时的差异导致对齐错误，会出现写入跨越有两种方法进行扇区对齐。DISKPAR(快对齐)和DISKPART(KB对1.2.6.卷的类型卷是磁盘分区的逻辑抽象，分基本卷和动态卷。基本卷提供多年以来使用和软件驱动RAID。随着硬件的发展，动态磁盘被放弃了。1.2.7.NTFS分配空间的大小测试表明，对于数据文件和日志文件的分区，最佳单元大小是64KB。1.2.8.磁盘破碎统没有足够的连续空间磁盘表明的多个文件片，那么最好的2.整理磁盘碎片3.安装所有应用程序4.整理磁盘碎片5.以最大大小创建数据文件和日志文件止sqlserver,检查碎片，有需要整理磁盘碎片7.禁用自增长，或者将增量设置大一些。8.周期性进行磁盘碎片整理。1.4.1.多核1.4.2.超线程64bit1.4.4.X64或IA641.4.5.高速缓存高速缓存产生的原因在于处理器速度远远高于主存储器，存储器无法跟上处理器对内存的渴望。处理器设计者为了解决这一问题，增加了多层高速缓主要是指机器架构，如单插槽，双插槽，四插槽，甚至对称多处理器(SMP).SMP.5.2.NUMA非一致性内存访问(NUMA)是一种超越SMP的可伸缩性的架构，NUMA系共享一块内存。以下语句确认系统是否使用NUMA1.5.3.SOFTNUMA2.系统监控指标lselectcntr_valuefromsys.dm_os_performance_counterswhereobject_name='MSSQL$ZXY:BufferManager'andcounter_name='totalpages'存2.1.1.外部压力1、Process>workingset显示每一个进程虚拟地址空间中最近引用的页面规模。消耗内存最多的进程就是消耗大量内存的应用。指一个进程的4GB虚拟地址空间中被映射到RAM中的部分的大小，通常是该进程的虚拟内存中的活跃部分。serverBuffermanagertotalpagessqlserver3、Sqlserver>Sqlserver:buffermanager>targetpages显示sqlserver的bufferpool必需的2.1.2.内部内存压力2.1.3.确认内存瓶颈1、SQLServer:BufferManager>PageLifeExpectancy显示数据页在缓冲池中驻留的时间长度(单位是秒)。值越大，系统越健康。如果存在内存瓶颈，这个值会低于s者更少。2、SQLServer:BufferManager>Buffercachehitratio显示数据库内存命中率，所请求的数据或者说页面在缓冲池(物理内存)被找到的次数。如果这个值很低，说明3、缓冲池的大小由两个参数决定：sp_configure的minservermemory和maxservermemory。当其他应用使用了这些内存后，操作系统就不会再将这些内存分给sqlr4、SQLServer:BufferManager>Stolenpages显示了内存被其他进程挪用的页面。当这个指标与目标内存页面数比例较大时可能存在问题。5、SQLServer:MemoryManager>MemoryGrantsPending显示等待内存授权的进程队时理想状态。6、SQLServer:BufferManager>Checkpointpages/sec显示检查点操作每秒写入磁盘7、SQLServer:BufferManager>Lazywrites/sec显示每秒将脏页从小写到磁盘的次8、Memtoleave大小基于工作者线程的数目动态变化，是一个独立的内存地址范围，供PhysicalDiskAVGDiskAueueLength一个磁盘的队列长度。不要选择_TOTAL对4、如果存在磁盘性能问题，控制器使用率等问题，上面两个计数器的平均值高于正常5、SQLServer>AccessMethods>Fullscans/sec显示每秒请求完全索引扫描或者全表扫描6、SQLServer>AccessMethods>PageSplits/sec显示每秒页面拆分的次数。可以通过适当的索引维护或者好的填充因子来避免发生。2.2.1.基于配置的磁盘瓶颈1、数据文件和日志文件布局(将这两类文件隔离，并且将每一类文件也隔离)2、数据文件和日志文件自动增长管理(手动管理)5、磁盘/控制器争用(用存储厂商的性能监控工具)2.2.2.基于模式的磁盘瓶颈1、索引不当或者缺少索引，使用数据库优化顾问(databasetuningadvisor)来解决。或2、文件和文件组，将读取数据量大且频繁的表单独放到文件或者组合到文件组中，可以dmdbindexphysicalstats供当前碎片级别的所有信息；只有有聚簇索引的表才能重建索引；使用alterindex命令重建索引。2.2.3.指标1、SQLServer>GeneralStatistics>TempTablesCreationRate显示每秒创建的临时表和临时变量的数目。2、SQLServer>GeneralStatistics>TempTablesForDestruction显示不再需要的正在等到销毁的临时表的数目。3、Sys.dm_db_task_space_usage和sys.dm_exec_requests查看每一个服务器进程使用的tempdb1.5。2.2.4.存储性能压力测试指标1.IOPS是每秒能够为输入输出请求提供服务的度量。得到的子系统实际吞吐量和io请求ksizesqlIO3.MB/sec每秒有多少MB的数据进入或者离开子系统。4.Latency为每一个io完成所必须等待的时间长度。5.DISKQueuelength磁盘队列的长度，说明当前io请求时排队等候的io请求的数目。e存在瓶颈。e4、Processor>%UserTime>sqlservr显示sqlserver进程消耗的cpu时间的确切数量。这个最好从性能监视器来看。cilationssecsec定变量导致计划重新编译，或者说重新编译次数比编译次数过高的话，那么说明存在基于配置的cpu瓶颈很难有建议或者计数器来协助sp_configure的cpu相关配置(AffinityThreads)，故不加以描述。基于模式的cpu瓶颈2.4.数据库镜像性能3.服务器设置A4.数据库等待事件s4.1.常见等待类型这个次数过多说明需要查询参数化。IOLCKM某个资源的锁。LATCH_*5.数据库指标5.1.指标采集和排列5.1.1.操作系统资源.采集方式selectcpu_countas系统的逻辑CPU数,hyperthread_ratioas逻辑内核数与物理内核数的比,round(physical_memory_in_bytes/1024/1024,0)as物理内存,round(virtual_memory_in_bytes/1024/1024,0)as虚拟内存,bpool_committedas缓存池已提交内存页数,bpool_commit_targetas缓存池目标内存页数,bpool_visibleas进程虚拟地址空间可用内存页数,max_workers_countas最大工作线程数--,process_kernel_time_msas内核态毫秒数,process_user_time_msas用户态毫秒数,sqlserver_start_timeas启动时间fromsys.dm_os_sys_info.指标说明指标说指标说明我感觉是是否使用超线程的标志可用物理内存的数量对用户模式进程可用的虚拟内存的数量,可以用/3GB开关。此数量表示缓冲池中的已提交物理内存页存目标数量是使用诸如系统当前状态(包括它的负载、当前进程所请求的内存、计算机上安装的内存量以及配置参数)等多种输入计oolcommittargetbpool_committed值，则缓冲池将尝试获得oolcommittargetbpool_committed值，则缓冲池将收缩。AWE缓冲池已获得它的内存目标(bpool_committed=bpool_commit_target)，bpool_visible表示在访问缓冲池所分配的物理内存时使用的AWE映射窗口的大小。此映射窗口的大小由进程地址空间绑定，因此，库页之外的其他用途而消耗内存的内部组件会进一步减少可见数量。如果bpool_visible的值太低，则可能收到内存不足错误。指标名称系统的逻辑CPU数逻辑内核数与物理内核数的比物理内存虚拟内存缓存池已提交内存页数缓存池目标内存页数进程虚拟地址空间可用内存页数最大工作线程数数据类型5.1.2.重要统计信息.采集方式selectround(cast(sum(casewhencounter_name='Buffercachehitratio'thencntr_valueelse0end)asdecimal(38,16))/sum(casewhencounter_name='Buffercachehitratiobase'thencntr_valueelse0end)*100,2)asbfromsys.dm_os_performance_counterswherecounter_namelike'Buffercachehitratio%'selectinstance_name,round(cast(sum(casewhencounter_name='CacheHitRatio'thencntr_valueelse0end)asdecimal(38,16))/sum(casewhencounter_name='CacheHitRatioBase'thencntr_valueelse0end)*100,2)asbfromsys.dm_os_performance_counterswhereobject_name='MSSQL$ZXY:PlanCache'groupbyinstance_namehavingsum(casewhencounter_name='CacheHitRatioBase'thencntr_valueelse0end)<>0selectsum(casewhencounter_name='FullScans/sec'thencntr_valueelse0end)asfull_table_scans,sum(casewhencounter_name='RangeScans/sec'thencntr_valueelse0end)asrange_scansfromsys.dm_os_performance_counterswherecounter_namein('FullScans/sec','RangeScans/sec').指标说明5.1.3.等待事件按照平均等待事件来说明数据库等待事件.采集方式.指标说明 (毫秒)。该时间包括最长等待时间(毫秒)行等待时间(毫秒).等待事件说明5.1.4.数据文件和日志文件.采集方式下面分别说明数据库空间，数据库空间使用等信息，采集周期可以是每天一数据库空间：selectnameas数据库空间,data_space_idas空间编号,typeas空间类型,is_defaultas默认数据空间标志fromsys.data_spaces数据库空间使用信息：sp_helpdb返回信息如下：指定数据库详细空间使用信息：sp_helpdb数据库名称取返回的第二个结果集，具体信息如下：操作系统文件名(物理文件名称)。selectdbid,eqp.fileid,NumberReads,BytesRead/1024/1024asbytesread,IoStallReadMS,Numberwrites,Byteswritten/1024/1024asbytewitten,IoStallwriteMS,IoStallMSfrom(select*fromsysfiles)aacrossapplyfn_virtualfilestats(null,aa.fileid)eqp所有数据库临时表空间文件空间分配信息：as数据库,as文件的逻辑名称,c.filenameas文件物理名称,version_store_reserved_page_countas统一区总页数,unallocated_extent_page_count未分配取总页数,user_object_reserved_page_countas统一区用户对象用户对象总页数,internal_object_reserved_page_countas统一区文件内部用户对象总页数,mixed_extent_page_countas混合区总页数fromsys.dm_db_file_space_usageainnerjoinsysdatabasesbona.database_id=b.dbidinnerjoinsysfilescona.file_id=c.fileid.指标说明数据库空间：指标说指标说明FG=文件组PS=分区方案EX案时，将使用此数据空间。0=这不是默认数据空间。指标名称数据库空间名称空间编号空间类型默认数据空间标志数据类型char(2)指标说指标说明数据库编号文件编号文件读次数文件读字节数户等待文件的读取I/O完成所费的总时间(以毫秒为单位)。文件写次数文件写字节数件的写I/O完成所费的总时间(以毫秒为单位)。等待文件读写完成总时间(以毫秒为单位)指标名称dbidleidNumberReadsBytesReadIoStallReadMSNumberwritesByteswrittenIoStallwriteMSIoStallMS数据类型数据库临时表空间文件空间分配信息：数据类型Varchar)Varchar)Varchar60)t指标名称数据库名称文件的逻辑名称文件物理名称未分配区总页数文件的未分配区中的总页数,不包含已分配区指标说明统一区总页数统一区总页数统一区用户对象用户对象总统一区用户对象总页数包含已分配未使用统一区文件内部用户对象总统一区文件中内部对象总页数，包含已分配混合区总页数混合区已分配和未分配总页数之和。5.1.5.TEMPDB使用.采集方式.指标说明指标名称objectiduser_objects_alloc_page_countuser_objects_dealloc_page_countinternal_objects_alloc_page_countinternal_objects_dealloc_page_count数据类型Varchar260)指标说明对象编号为用户对象保留或分配的页数为用户对象释放并不再保留的页数为内部对象保留或分配的页数为内部对象释放并不再保留的页数系统当前资源高消耗sql.采集方式selectobjectid,total_cpu_time,total_exec_count,num_of_statment,total_logical_reads/total_exec_countasavg_logical_reads,total_logical_writes/total_exec_countasavg_logical_writes,total_physical_reads/total_exec_countasavg_physical_reads,textfrom(selectqs.sql_handle,sum(qs.total_worker_time)ascast(sum(qs.execution_count)asdecimal(38,16))ascount(*)asnum_of_statment,sum(total_logical_reads)astotal_logical_reads,total_cpu_time,total_exec_count,sum(total_logical_writes)assum(total_physical_reads)asfromsys.dm_exec_query_statsgroupbyqs.sql_handle)dttotal_logical_writes,total_physical_readsqscrossapplysys.dm_exec_sql_text(dt.sql_handle)eqpwhereobjectidisnotnull.指标说明指标说指标说明数据库对象Cpu自编译以来使用时间总毫秒数上次编译后执行次数语句个数平均逻辑读次数平均逻辑写次数平均物理读次数Sql脚本指标名称objectidTotalcputimetotal_exec_countnum_of_statmentavg_logical_readsavg_logical_writesavg_physical_readsText数据类型decimal(38,16)Intdecimal(38,16)decimal(38,16)decimal(38,16)5.1.7.当前阻塞和物理io多的会话请求信息.采集方式selectsession_id,request_id,start_time,status,command,sql_handle,plan_handle,database_id,user_id,connection_id,blocking_session_id,wait_type,wait_time,wait_resource,open_transaction_count,open_resultset_count,transaction_id,cpu_time,total_elapsed_time,reads,writes,logical_reads,lock_timeout,granted_query_memoryfromsys.dm_exec_requestswhere(logical_reads<>0andreads/logical_reads>=0.2)orblocking_session_id<>0.指标说明数据类型数据类型datetimenvarchar(30)Session_idestidStart_timeusCommandnvarchar(1CommandaryaryaryPlanhandleDatabase_idUser_idConnection_idBlocking_session_idWait_timeWaitresourcenvarchar(60)指标说明会话编号请求号请求达到的时间正在处理的命令的当前类型。常用命的ID定会话的会话信息不可用(或无法进行标识)。-2=阻塞资源由孤立的分布式事务拥-3=阻塞资源由延迟的恢复事务拥-4=由于内部闩锁状态转换而导致此如果请求当前被阻塞，则此列返回等待类型如果请求当前被阻塞，则此列返回当前等待的持续时间(以毫秒为单位)。如果请求当前被阻塞，则此列返回请资源。OpenOpen_transaction_count为此请求打开的事务数Open_resultset_count为此请求打开的结果集的个数