分区表的数据删除

问题：堆表按天做了分区，表中只保留最近7天的数据。最近发现此表的数据空间明显比之前大，之前2G：现在6G，持续关注几天表中记录数保持平衡，但数据空间却在进一步增长。对应表所在的文件组也不停在自增长。

分析：使用sys.dm_db_index_physical_stats查看表的碎片情况，发现在已删除记录的分区中堆的区碎片(avg_fragmentation_in_percent)、数据页总数(page_count)不为零。


查看另一个库下此表的备份表(同为分区表，保留最近30天的数据)，表的数据空间才4G。30天前对应的分区中没有任何碎片、数据页信息。它们唯一的不同就是对历史数据处理方法：一个是每天通过delete的方式将7天前的数据删除；另一个是通过switch交换分区，然后truncate交换出去的数据。
解决：创建用于交换分区的Trun表(字段、压缩选项与原表一致)，尝试将已删除数据的旧分区switch出来，报错表已分区，但索引XX没有分区。表上一个索引没有创建在分区上，查看索引使用情况，查找/扫描次数均为0，删除索引。再次switch partition成功。
交换部分分区后查看原表数据空间下降，Trun表碎片信息与切换前原表保留一致，交换后占用空间也增上去，truncate Trun表，再进行后续操作。


总结：一个问题往往要从多方面分析，对于空间问题可以从以下方面入手。查看数据文件使用情况(dbcc showfilestats)、查看自动增长情况(默认跟踪)、查找库下占用空间前N的表、查看表占用/剩余空间情况(sp_spaceused)、查看表碎片情况(sys.dm_db_index_physical_stats)、查看索引使用情况(sys.dm_db_index_usage_stats)

--数据文件使用情况
dbcc showfilestats
select 174634*8*8/1024,(174634-169743)*8*8/1024
--日志文件使用情况
dbcc sqlperf(logspace)
dbcc loginfo()

--查看自动增长情况
select * from sys.trace_events
DECLARE @tracefile NVARCHAR(MAX)
SET @tracefile = (SELECT LEFT([path],LEN([path])-CHARINDEX('',REVERSE([path])))+ 'log.trc' FROM sys.traces WHERE [is_default] = 1)
SELECT TOP 1000
 gt.[HostName]
,gt.[ServerName]
,gt.[DatabaseName]
,gt.[SPID]
,gt.[ObjectName]
,gt.[objecttype] [ObjectTypeID]
,sv.[subclass_name] [ObjectType]
,e.[category_id] [CategoryID]
,c.[Name] [Category]
,gt.[EventClass] [EventID]
,e.[Name] [EventName]
,gt.[LoginName]
,gt.[ApplicationName]
,gt.[StartTime]
,gt.[TextData]
FROM fn_trace_gettable(@tracefile, DEFAULT) gt
LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype]
INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id]
INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id]
WHERE gt.[spid] > 50  --50以内的spid为系统使用
  AND gt.[DatabaseName] = 'TraceDB'  --根据DatabaseName过滤
  --AND gt.[ObjectName] = 'Trace_log'  --根据objectname过滤
  --AND e.[category_id]  = 5  --category 5表示对象，8表示安全
  AND e.[trace_event_id] = 92 --sp_trace_setevent(20Audit Login Failed、46Object:Created、47Object:Deleted、92DataGrow、93LogGrow、94DataShrink、95LogShrink、164Object:Altered)
ORDER BY [StartTime] DESC

--查找库下占用空间前N的表
select object_name(a.object_id) tablename,a.row_count,a.space_in_M,a.space_in_G
,isnull(b.space_in_M,0) index_in_M,isnull(b.space_in_G,0) index_in_G 
,g.groupname,i.indid from(
select top 200 a.object_id,sum(row_count) row_count
,sum(a.reserved_page_count)*8/1024 space_in_M,sum(a.reserved_page_count)*8/1024.00/1024 space_in_G
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 space_in_M desc
)a
left join(
select a.object_id,sum(row_count) row_count
,sum(a.reserved_page_count)*8/1024 space_in_M,sum(a.reserved_page_count)*8/1024.00/1024 space_in_G
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 (a.space_in_M) desc

--查看表占用/剩余空间情况
sp_spaceused 'TradeDetail'

--查看表碎片情况
select [object_id],partition_number,index_type_desc,alloc_unit_type_desc,index_depth
      ,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count
  from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('TradeDetail'),NULL,NULL,'LIMITED')

--查看索引使用情况
SELECT o.name,i.name,i.index_id,ddius.user_seeks,ddius.user_scans,ddius.user_lookups,ddius.user_updates
,ddius.last_user_seek,ddius.last_user_scan,ddius.last_user_lookup,ddius.last_user_update
  FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.objects o
ON ddius.[object_id]=o.[object_id]
INNER JOIN sys.indexes i
ON ddius.[object_id]=i.[object_id]
AND ddius.index_id=i.index_id
WHERE database_id = DB_ID()
AND ddius.object_id=OBJECT_ID('TradeDetail')

当然在处理的过程中还会遇到一些其他问题，问题又可以衍生出其他话题，往往接触得越多，把知识点进行整理归类，就可以融会贯通。