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 <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 p
inner 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'), --数据库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
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(*) * 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数据库的空间使用
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
