【SQL Server DBA】日常巡检1:数据库空间、状态、使用的监控
来源:互联网 发布:linux怎么运行sh脚本 编辑:程序博客网 时间:2024/05/05 04:41
1、数据库
--所有数据库的大小exec sp_helpdb--所有数据库的状态select name, user_access_desc, --用户访问模式 state_desc, --数据库状态 recovery_model_desc, --恢复模式 page_verify_option_desc, --页检测选项 log_reuse_wait_desc --日志重用等待from sys.databases--某个数据库的大小:按页面计算空间,有性能影响,基本准确,有时不准确use testgoexec sp_spaceused go--可以@updateusage = 'true',会运行dbcc updateusageexec sp_spaceused @updateusage = 'true'--对某个数据库,显示目录视图中的页数和行数错误并更正DBCC UPDATEUSAGE('test')
2、数据文件
--查看某个数据库中的所有文件及大小sp_helpfile --查看所有文件所在数据库、路径、状态、大小select db_name(database_id) dbname, type_desc, --数据还是日志 name, --文件的逻辑名称 physical_name, --文件的物理路径 state_desc, --文件状态 size * 8.0/1024 as '文件大小(MB)' from sys.master_files--按区extent计算空间,没有性能影响,基本准确,把TotalExtents*64/1024,单位为MB--同时也适用于计算tempdb的文件大小,但不包括日志文件dbcc showfilestats
3、日志文件
--查看日志文件所在数据库、路径、状态、大小select db_name(database_id) dbname, type_desc, --数据还是日志 name, --文件的逻辑名称 physical_name, --文件的物理路径 state_desc, --文件状态 size * 8.0/1024 as '文件大小(MB)' from sys.master_fileswhere type_desc = 'LOG'--所有数据库的日志的大小,空间使用率dbcc sqlperf(logspace)
4、数据文件、日志文件的I/O统计信息
--数据和日志文件的I/O统计信息,包含文件大小select database_id, file_id, file_handle, --windows文件句柄 sample_ms, --自从计算机启动以来的毫秒数 num_of_reads, num_of_bytes_read, io_stall_read_ms, --等待读取的时间 num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, --用户等待文件完成I/O操作所用的总时间 size_on_disk_bytes --文件在磁盘上所占用的实际字节数 from sys.dm_io_virtual_file_stats(db_id('test'), --数据库id 1 ) --数据文件id union allselect database_id, file_id, file_handle, --windows文件句柄 sample_ms, --自从计算机启动以来的毫秒数 num_of_reads, num_of_bytes_read, io_stall_read_ms, --等待读取的时间 num_of_writes, num_of_bytes_written, io_stall_write_ms, io_stall, --用户等待文件完成I/O操作所用的总时间 size_on_disk_bytes --文件在磁盘上所占用的实际字节数from sys.dm_io_virtual_file_stats( db_id('test'), --数据库id 2 ) --日志文件id
5、对象,包括:表、索引、索引视图等
--不一定准确:某个表的行数,保留大小,数据大小,索引大小,未使用大小exec sp_spaceused @objname ='temp_lock'--准确:但有性能影响exec sp_spaceused @objname ='temp_lock', @updateusage ='true'--按页统计,没有性能影响,有时不准确/*======================================================一次计算多个对象的空间使用情况sys.dm_db_partition_stats返回当前数据库中每个分区(表和索引)的页和行计数信息========================================================*/ select o.name, sum(p.reserved_page_count) as reserved_page_count, --保留页,包含表和索引 sum(p.used_page_count) as used_page_count, --已使用页,包含表和索引 sum(case when p.index_id <2 then p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count else p.lob_used_page_count + p.row_overflow_used_page_count end) as data_pages, --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据 sum(case when p.index_id < 2 then p.row_count else 0 end) as row_counts --数据行数,包含表中的数据行数,不包含索引中的数据条目数 from sys.dm_db_partition_stats pinner join sys.objects o on p.object_id = o.object_id where p.object_id= object_id('表名')group by o.name --按页或区统计,有性能影响,准确 --显示当前数据库中所有的表或视图的数据和索引的空间信息--包含:逻辑碎片、区碎片(碎片率)、平均页密度 dbcc showcontig(temp_lock)--SQL Server推荐使用的动态性能函数,准确select *from sys.dm_db_index_physical_stats(db_id('test'), --数据库idobject_id('test.dbo.temp_lock'), --对象idnull, --索引idnull, --分区号'limited' --default,null,'limited','sampled','detailed',默认为'limited' --'limited'模式运行最快,扫描的页数最少,对于堆会扫描所有页,对于索引只扫描叶级以上的父级页 --'sampled'模式会返回堆、索引中所有页的1%样本的统计信息,如果少于1000页,那么用'detailed'代替'sampled' --'detailed'模式会扫描所有页,返回所有统计信息)--查找哪些对象是需要重建的use testgoif OBJECT_ID('extentinfo') is not nulldrop table extentinfogocreate table extentinfo ( [file_id] smallint, page_id int, pg_alloc int, ext_size int, obj_id int, index_id int, partition_number int,partition_id bigint,iam_chain_type varchar(50), pfs_bytes varbinary(10) ) go /*====================================================================查询到的盘区信息是数据库的数据文件的盘区信息,日志文件不以盘区为单位命令格式: DBCC EXTENTINFO(dbname,tablename,indexid)DBCC EXTENTINFO('[test]','extentinfo',0)======================================================================*/insert extentinfo exec('dbcc extentinfo(''test'') ')go--每一个区有一条数据select file_id, obj_id, --对象IDindex_id, --索引idpage_id, --这个区是从哪个页开始的,也就是这个区中的第一个页面的页面号pg_alloc, --这个盘区分配的页面数量ext_size, --这个盘区包含了多少页partition_number,partition_id,iam_chain_type, --IAM链类型:行内数据,行溢出数据,大对象数据pfs_bytes from extentinfoorder by file_id, OBJ_ID, index_id, partition_id, ext_size /*=====================================================================================================数据库的数据文件的盘区信息,通过计算每个对象理论上区的数量和实际数量,如果两者相差很大,那就应该重建对象.1.每一条记录就是一个区2.如果pg_alloc比ext_size小,也就是实际每个区分配的页数小于理论上这个区的页数, 那么就会多一条记录,把本应该属于这个区的页放到多出来的这条记录对应的区中, 那么原来只有一条记录(也就是一个区),现在就有2条记录(也就是2个区), 导致实际的区数量2大于理论上的区数量1.========================================================================================================*/select file_id, obj_id, index_id, partition_id, ext_size, count(*) as '实际区的个数', sum(pg_alloc) as '实际包含的页数', ceiling(sum(pg_alloc) * 1.0 / ext_size) as '理论上的区的个数', ceiling(sum(pg_alloc) * 1.0 / ext_size) / count(*) * 100.00 as '理论上的区个数 / 实际区的个数' from extentinfo group by file_id, obj_id, index_id, partition_id, ext_size having ceiling(sum(pg_alloc)*1.0/ext_size) < count(*) --过滤: 理论上区的个数 < 实际区的个数,也就是百分比小于100%的order by partition_id, obj_id, index_id, [file_id]
6、tempdb数据库
--tempdb数据库的空间使用/*======================================================tempdb中包含的对象:用户对象:是用户显式创建的,这些对象位于用户会话的作用域, 可以位于创建对象的例程(存储过程、触发器、函数)的作用域中。1.用户定义的表、索引2.系统表、索引3.全局临时表、索引4.局部临时表、索引5.表变量6.表值函数中返回的表内部对象:是根据需要由SQL Server数据库引擎创建的,用于处理SQL Server语句, 内部对象可以在语句作用域中创建、删除。 每个内部对象至少需要9个页面,一个IAM页,一个区包含了8个页。1.游标、假脱机操作、临时的大型对象(LOB),存储的工作表2.哈希联接、哈希聚合操作的工作文件3.如果设置了sort_in_tempdb选项,那么创建、重新生成索引的重建排序结果存放在tempdb; group by、order by、union操作的中间结果。版本存储区:是数据页的集合,包含了支持行版本控制功能的所需的数据,主要支持快照事务隔离级别, 以及一些其他的提高数据库并发性能的新功能。 1.公用版本存储区:在使用快照隔离级别、已提交读隔离级别的数据库中,由数据修改事务生成的行版本。 2.联机索引生成版本存储区:为了实现联机索引操作而为数据修改事务生成的行版本, 多个活动结果集,after触发器生成的行版本。 上面也提到了,由于sys.allocation_units和sys.partitions视图没有记录tempdb中的内部对象、版本存储区所以这2个视图和sp_spaceused,不能准确反应出tempdb的空间使用。分析tempdb现有的工作负载:1.设置tempdb的自动增长2.通过模拟单独的查询、工作任务,监控tempdb空间使用3.通过模拟执行一些系统维护操作(重新生成索引),监控tempdb空间使用4.根据2和3中tempdb的空间使用量,预测总工作负荷会使用的空间,并针对任务的并发度调整这个值. 5.根据4得到的值,设置生成环境中tempdb的初始大小,并开启自动增长. 另外,tempdb的文件个数和大小,不仅需要满足实际使用需要,还要考虑性能优化.监控tempdb的空间使用方法:1.可以通过SQL Trace来跟踪,但是由于不能预期造成大量使用tempdb语句在什么时候运行, 而且SQL Trance操作比较昂贵,如果一直开着会产生大量的跟踪文件,对硬盘的负担也比较重,一般不用. 2.轻量级的监控是通过一定时间间隔运行能够监控系统运行的dbcc命令、动态性能视图-函数, 把结果记录在文件中,这对于很繁忙的系统是不错的选择。 ========================================================*/Select DB_NAME(database_id) as DB, max(FILE_ID) as '文件id', SUM (user_object_reserved_page_count) as '用户对象保留的页数', ----包含已分配区中的未使用页数 SUM (internal_object_reserved_page_count) as '内部对象保留的页数', --包含已分配区中的未使用页数 SUM (version_store_reserved_page_count) as '版本存储保留的页数', SUM (unallocated_extent_page_count) as '未分配的区中包含的页数', --不包含已分配区中的未使用页数 SUM(mixed_extent_page_count) as '文件的已分配混合区中:已分配页和未分配页' --包含IAM页 From sys.dm_db_file_space_usage Where database_id = 2 group by DB_NAME(database_id) --能够反映当时tempdb空间的总体分配,申请空间的会话正在运行的语句SELECT t1.session_id, t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count, t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count, t.textfrom sys.dm_db_session_space_usage t1 --反映每个session的累计空间申请 inner join sys.dm_exec_sessions as t2 on t1.session_id = t2.session_id inner join sys.dm_exec_requests t3 on t2.session_id = t3.session_id cross apply sys.dm_exec_sql_text(t3.sql_handle) twhere t1.internal_objects_alloc_page_count>0 or t1.user_objects_alloc_page_count >0 or t1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0 --返回tempdb中页分配和释放活动,--只有当任务正在运行时,sys.dm_db_task_space_usage才会返回值--在请求完成时,这些值将按session聚合体现在SYS.dm_db_session_space_usageselect t.session_id, t.request_id, t.database_id, t.user_objects_alloc_page_count, t.internal_objects_dealloc_page_count, t.internal_objects_alloc_page_count, t.internal_objects_dealloc_page_countfrom sys.dm_db_task_space_usage t inner join sys.dm_exec_sessions e on t.session_id = e.session_id inner join sys.dm_exec_requests r on t.session_id = r.session_id and t.request_id = r.request_id
1 0
- 【SQL Server DBA】日常巡检1:数据库空间、状态、使用的监控
- 【SQL Server DBA】日常巡检语句3:特定监控(阻塞、top语句、索引、作业)
- 【SQL Server DBA】日常巡检语句3:特定监控(阻塞、top语句、索引、作业)
- 【SQL Server DBA】日常巡检2:windows性能监控器
- DBA日常巡检手册
- 监控SQL Server的磁盘使用空间
- SQL SERVER日常运维巡检系列
- SQL SERVER日常运维巡检系列之一——服务器状态及各硬件指标
- SQL Server 数据库巡检脚本
- SQL Server 数据库巡检脚本
- SQL Server 数据库巡检脚本
- SQL Server数据库巡检脚本
- SQL Server 数据库巡检脚本
- SQL Server 数据库巡检脚本
- MSSql DBA 日常巡检手册
- MongoDB DBA的日常巡检及执行计划分析
- SQL SERVER日常运维巡检系列之二——数据库
- SQL SERVER日常运维巡检系列之四——数据库备份
- [Java]吉他谱浏览器 0.1
- treap分离合并 区间操作 poj 3468
- UIApplicationDelegate详解
- Android底部菜单栏(用TabHost一次性加载耗内存)
- 计算程序运行时间的函数clock()
- 【SQL Server DBA】日常巡检1:数据库空间、状态、使用的监控
- 笔记2(字符串翻转)
- OCP-1Z0-053-V13.02-445题
- android 语言国际化 + 点击按钮出现menu + 点击menu出现dialog对话框 + radiobutton 点击事件
- zeromq
- xml、properties配置文件的读取
- 分治法,循环赛日程表
- jquery学习心得,常用的操作
- android JNI学习六