sqlserver的日常检查脚本归结

核心提示：SELECT @command = 'Use [' + '?' + '] select ' + '''' + '?' + '''' + ' AS DatabaseName,DB_ID(

SELECT @command = 'Use [' + '?' + '] select ' + '''' + '?' + '''' + ' AS DatabaseName,

DB_ID() AS DatabaseID,

QUOTENAME(DB_NAME(i.database_id), '+ '''' + '"' + '''' +')+ N'+ '''' + '.' + '''' +'+ QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id, i.database_id), '+ '''' + '"' + '''' +')+ N'+ '''' + '.' + '''' +'+ QUOTENAME(OBJECT_NAME(i.object_id, i.database_id), '+ '''' + '"' + '''' +') as full_obj_name, 

i.index_id,

o.name, 

i.index_type_desc, 

i.index_depth,

i.index_level,

i.avg_fragmentation_in_percent as [AVG Fragmentation], 

i.fragment_count, 

i.rnk as Rank

from (

select *, DENSE_RANK() OVER(PARTITION by database_id ORDER BY avg_fragmentation_in_percent DESC) as rnk

from sys.dm_db_index_physical_stats(DB_ID(), default, default, default,'+ '''' + 'limited' + '''' +')

where avg_fragmentation_in_percent >0 AND 

INDEX_ID > 0 AND 

Page_Count > 500 

) as i

join sys.indexes o on o.object_id = i.object_id and o.index_id = i.index_id

where i.rnk <= 25

order by i.database_id, i.rnk;'

INSERT #INDEXFRAGINFO EXEC sp_MSForEachDB @command 

SELECT substring(databasename,0,30) as databasename ,ltrim(databaseID) as databaseID,substring(full_obj_name,0,50) as full_obj_name ,

ltrim(index_id) as index_id, [name], 

index_type_desc, ltrim(index_depth) as index_depth, ltrim(index_level) as index_level, ltrim([AVG Fragmentation]) as [AVG Fragmentation] ,

 ltrim(fragment_count) as fragment_count, ltrim([Rank]) as [Rank]

FROM #INDEXFRAGINFO

Where DatabaseID > 4

order by [RANK];

DROP TABLE #INDEXFRAGINFO

END

GO

print '*********************************'

print '                             '

print '                             '

print '                             '

print '----------------------------'

print '4.2找出很少使用的index         '

print '----------------------------'

print '*********************************'

declare @dbid int

select @dbid = db_id()

select objectname=object_name(s.object_id), s.object_id

, indexname=i.name, i.index_id

, user_seeks AS 搜索次数, user_scans AS 扫描次数, 

user_lookups AS 查找次数, user_updates 更新次数

from sys.dm_db_index_usage_stats s,

sys.indexes i