数据表读写、空间和碎片

--各数据表读写情况 2016-08-01 17:57

--各数据表读写情况
select o.name,sum(user_seeks)+sum(user_scans) reads 
,max(user_updates) writes
from sys.dm_db_index_usage_stats ddus
inner join sys.tables o
on ddus.object_id=o.object_id
where database_id = db_id()
group by o.name 
order by reads desc

user_lookups是由非覆盖的非聚集索引引起，实际查询次数已经包含在user_seeks
update/delete操作，会先select，再具体操作，因此reads项也会包含update/delete的操作
writes项包括insert/update/delete
大致参考，了解哪些表读/写相对较多还是可以的
sys.dm_db_index_usage_stats数据从服务重启算起，或者数据库shutdown(auto_close选项设置为True，当最后一个用户退出数据库的时候，数据库就会shutdown，释放资源)时，会清空此DMV.
--获取库下占用空间前N的表

--获取库下占用空间前N的表
select db_name() AS [DatabaseName]
,object_name(a.object_id) TableName,a.row_count
,a.reservedspace_mb+isnull(b.reservedspace_mb,0) reservedspace_mb 
,a.usedspace_mb dataspace_mb
,isnull(b.usedspace_mb,0) indexspace_mb
,g.groupname,i.indid,getdate() inserttime
into DBA_spaceused
from(
select top 2000 a.object_id,sum(row_count) row_count
,sum(a.reserved_page_count)*8/1024 reservedspace_mb,sum(a.used_page_count)*8/1024 usedspace_mb
from sys.dm_db_partition_stats a(nolock) JOIN sys.all_objects b(NOLOCK) 
ON a.object_id=b.object_id
where a.index_id<=1 
AND b.type='U' AND b.is_ms_shipped=0
group by a.object_id
order by reservedspace_mb desc
)a
left join(
select a.object_id,sum(row_count) row_count
,sum(a.reserved_page_count)*8/1024 reservedspace_mb,sum(a.used_page_count)*8/1024 usedspace_mb
from sys.dm_db_partition_stats a(nolock) JOIN sys.all_objects b(NOLOCK) 
ON a.object_id=b.object_id
where a.index_id>1 
AND b.type='U' AND b.is_ms_shipped=0
group by a.object_id
)b
on a.object_id=b.object_id
left join (select id,indid,groupid from sysindexes where indid<=1) i
on a.object_id=i.id
left join sysfilegroups g
on i.groupid= g.groupid
--where i.groupid is null
order by reservedspace_mb desc,row_count

--获取当前数据库中碎片最多的索引

--获取当前数据库中碎片最多的索引：
-- Get fragmentation info for all indexes
-- above a certain size in the current database
-- Note: This could take some time on a very large database
SELECT DB_NAME(database_id) AS [DatabaseName],
OBJECT_NAME(ps.OBJECT_ID) AS [TableName],
i.name AS [IndexName], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count,getdate() inserttime
into DBA_fragment
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id]
AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
-- Helps determine whether you have fragmentation in your relational indexes
-- and how effective your index maintenance strategy is

--获取区信息

--获取区信息
create table DBA_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 DBA_extentinfo 
exec('dbcc extentinfo(DBname)')

dbcc extentinfo(AdventureWorks2008R2,'Sales.SalesOrderDetail')
--查找碎片较高表/索引

--查找碎片较高表/索引
;with ext as(
select [file_id],obj_id,index_id,partition_id,ext_size
,count(*) actual_extent_count,sum(pg_alloc) actual_page_count
,ceiling(sum(pg_alloc)*1.0/ext_size) possible_extent_count
,(ceiling(sum(pg_alloc)*1.00/ext_size)*100.00)/count(*) [possible_extents/actual_extents]
from dbo.DBA_extentinfo 
group by [file_id],obj_id,index_id,partition_id,ext_size
having sum(pg_alloc)>500--页数较多
)
select object_name(e.obj_id) tablename,e.index_id,i.name indexname,e.ext_size
,e.actual_extent_count,e.actual_page_count,e.possible_extent_count,e.[possible_extents/actual_extents]
,f.avg_fragmentation_in_percent,s.row_count
from ext e
inner join sys.indexes i
on e.obj_id=i.object_id
and e.index_id=i.index_id
inner join dbo.DBA_fragment f
on object_name(e.obj_id)=f.tablename
and i.name=f.indexname
inner join dbo.DBA_spaceused s
on object_name(e.obj_id)=s.tablename
where [possible_extents/actual_extents]<80
and f.avg_fragmentation_in_percent>30--外部碎片较高
order by f.tablename,e.index_id

对于碎片高的表/索引，考虑重建索引。