（1）DBA查询：数据库

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

db2查看数据库名称，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 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 showfilestats3、日志文件状态--查看日志文件所在数据库、路径、状态、大小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'),   --数据库id1 )  --数据文件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'),   --数据库id2 )  --日志文件id5、数据库对象状态、数据表状态（包含索引、视图）--不一定准确:某个表的行数,保留大小，数据大小，索引大小，未使用大小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>2then p.in_row_data_page_count +p.lob_used_page_count +p.row_overflow_used_page_countelse p.lob_used_page_count +p.row_overflow_used_page_countend) as data_pages,  --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据sum(case when p.index_id <</CODE> 2then p.row_countelse 0end) as row_counts   --数据行数，包含表中的数据行数，不包含索引中的数据条目数from sys.dm_db_partition_stats pinner join sys.objects oon p.object_id = o.object_idwhere 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'),                      --数据库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))go7、哪些表需要重建insert extentinfoexec('dbcc extentinfo(''test'') ')go--每一个区有一条数据select  file_id,obj_id,               --对象IDindex_id,             --索引id
                  page_id,              --这个区是从哪个页开始的,也就是这个区中的第一个页面的页面号pg_alloc,             --这个盘区分配的页面数量
          ext_size,             --这个盘区包含了多少页
  partition_number,partition_id,iam_chain_type,       --IAM链类型:行内数据,行溢出数据,大对象数据
         pfs_bytesfrom extentinfoorder by file_id,OBJ_ID,index_id,partition_id,ext_sizeselect 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 extentinfogroup by file_id,obj_id,index_id,partition_id,ext_sizehaving 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空间的总体分配,申请空间的会话正在运行的语句SELECTt1.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 t2on t1.session_id = t2.session_id          inner join sys.dm_exec_requests t3on t2.session_id = t3.session_id               cross apply sys.dm_exec_sql_text(t3.sql_handle) twhere  t1.internal_objects_alloc_page_count>0   ort1.user_objects_alloc_page_count >0      ort1.internal_objects_dealloc_page_count>0 ort1.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 eon t.session_id = e.session_id       inner join sys.dm_exec_requests  r   on t.session_id = r.session_id andt.request_id = r.request_id