MSSQL 维护小记（清理进程、重建索引）

------------------------------清理进程-----------------------------------

declare @deleteSleepSession nvarchar(100) --申明一个变量
declare tablelist cursor local --申明一个本地游标
for
select 'kill '+rtrim(spid)
from master.dbo.sysprocesses --数据库系统进程表
where status='sleeping' --状态为休眠状态
and spid>=50 --因spid<=50的为SQL Server本身的系统进程
and last_batch< DATEADD(MINUTE,-30,GETDATE()) --最后批处理时间大于当前时间30分钟
and program_name = 'XXXXXXXXXX'  --名称
--and login_time< DATEADD(MINUTE,-30,GETDATE()) --提交时间大于当前时间30分钟
open tablelist
fetch tablelist into @deleteSleepSession --执行查询,返回结果集插入至游标中
while @@fetch_status=0
-- 0 FETCH 语句成功
-- -1 FETCH 语句失败或此行不在结果集中
-- -2 被提取的行不存在
begin
exec(@deleteSleepSession) --执行变量
fetch tablelist into @deleteSleepSession
end
close tablelist --关闭游标
deallocate tablelist --删除游标引用

---------------------------------重建索引---------------------------------------------

select *, 'alter index all on dbo.['+TableName+'] rebuild;' as forsql

from (
SELECT OBJECT_NAME(ips.object_id) AS TableName,
ips.index_id,
name AS IndexName,
avg_fragmentation_in_percent,
DB_NAME(ips.database_id) AS DatabaseName
FROM sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), NULL, NULL, NULL, NULL) AS ips
INNER JOIN sys.indexes AS si
ON si.object_id = ips.object_id
AND si.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 0
AND si.index_id <> 0
)t