1、数据库状态：【1】sys.databases 【2】exec sp_spaceused
2、数据文件状态：【1】sys.master_files 【2】查看ldf与mdf：sp_helpfile、 sys.database_files 【3】文件使用情况：sys.dm_db_file_space_usage
3、日志文件状态：【1】dbcc sqlperf(logspace) 【2】sys.master_files
4、数据文件/日志文件的I/O状态：【1】sys.dm_io_virtual_file_stats(db_id('test')
5、数据库对象状态、数据表状态（包含索引、视图）：
【1】exec sp_spaceused @objname ='temp_lock' / exec sp_spaceused '对象名'
【2】sys.dm_db_partition_stats 【3】sys.objects
6、索引碎片信息：
【1】dbcc showcontig(temp_lock)
【2】sys.dm_db_index_physical_stats(db_id,object_id,index_id,partition_id,limited/sampled/detailed)
【2.1】参数1：数据库id，参数2：对象id，参数3：索引id，参数4：分区id，参数5：查询运行模式 limited/sampled/detailed
【2.2】查询运行模式：（1）limited：iot表扫描索引非叶子节点，heap表全表扫 （2）sampled：1%数据的抽样统计信息 （3）detailed：所有数据页统计信息
7、哪些表需要重建：【1】exec('dbcc extentinfo(''test'') ')
8、tempdb数据库
详情
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 test go exec sp_spaceused go --可以@updateusage = 'true',会运行dbcc updateusage exec 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_files where 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 all 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 2 ) --日志文件id 5、数据库对象状态、数据表状态（包含索引、视图） --不一定准确:某个表的行数,保留大小，数据大小，索引大小，未使用大小 exec sp_spaceused @objname ='temp_lock' --准确:但有性能影响 exec sp_spaceused @objname ='temp_lock', @updateusage ='true' --按页统计，没有性能影响，有时不准确 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 <</CODE>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 <</CODE> 2 then p.row_count else 0 end) as row_counts --数据行数，包含表中的数据行数，不包含索引中的数据条目数 from sys.dm_db_partition_stats p inner join sys.objects o on p.object_id = o.object_id where p.object_id= object_id('表名') group by o.name
6、索引碎片信息： --按页或区统计，有性能影响，准确 --显示当前数据库中所有的表或视图的数据和索引的空间信息 --包含：逻辑碎片、区碎片(碎片率)、平均页密度 dbcc showcontig(temp_lock) --SQL Server推荐使用的动态性能函数，准确 select * from sys.dm_db_index_physical_stats( db_id('test'), --数据库id object_id('test.dbo.temp_lock'), --对象id null, --索引id null, --分区号 'limited' --default,null,'limited','sampled','detailed',默认为'limited' --'limited'模式运行最快，扫描的页数最少,对于堆会扫描所有页,对于索引只扫描叶级以上的父级页 --'sampled'模式会返回堆、索引中所有页的1%样本的统计信息，如果少于1000页，那么用'detailed'代替'sampled' --'detailed'模式会扫描所有页,返回所有统计信息 ) --查找哪些对象是需要重建的 use test go if OBJECT_ID('extentinfo') is not null drop table extentinfo go create 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 7、哪些表需要重建 insert extentinfo exec('dbcc extentinfo(''test'') ') go --每一个区有一条数据 select file_id, obj_id, --对象ID index_id, --索引id page_id, --这个区是从哪个页开始的,也就是这个区中的第一个页面的页面号 pg_alloc, --这个盘区分配的页面数量 ext_size, --这个盘区包含了多少页 partition_number, partition_id, iam_chain_type, --IAM链类型:行内数据,行溢出数据,大对象数据 pfs_bytes from extentinfo order by file_id, OBJ_ID, index_id, partition_id, ext_size 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(*) * 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] 8、tempdb数据库 --tempdb数据库的空间使用 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.text from 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) t where 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_usage select 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_count from 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