数据库日常监控脚本.pdfVIP

数据库日常监控脚本 1. SQL2005查看阻塞进程的 SQL脚本 .txt with Lock(dbName,spid,blocked,sql_handle) as ( select db_name(dbid),0,blocked,sql_handle from master..sysprocesses a where Blocked0 and not exists(select 1 from Master..Sysprocesses where blocked=a.spid) union select db_name(dbid),spid,blocked,sql_handle from master..sysprocesses a where Blocked0 ) select * from Lock a cross apply sys.dm_exec_sql_text(a.sql_handle)b 2. 查看 SQL SEVER CPU活动 ,工作情况 .txt select @@cpu_busy,--自上次启动以来的工作时间 @@timeticks, --每个时钟周期对应的微秒数 @@cpu_busy*cast(@@timeticks as float)/1000 as cpu工作时间 (秒), @@idle*cast(@@timeticks as float)/1000 as CPU空闲时间 (秒 ), getdate() as 当前时间 4. 查看处理所有表的索引碎片 .txt 下面这条语句，可以查看当前数据库中所有索引的碎片情况： select object_name(ps.object_id) as 表名 , ix.name as 索引名称 , ps.index_type_desc as 索引类型 , alloc_unit_type_desc as 存储单元类型 ,--(行内数据 ,溢出行 ,大数据行 ) index_depth as 索引深度 , avg_fragmentation_in_percent as 索引碎片的百分比 ,-- =30,便需要考 虑重建索引 rebuild avg_fragment_size_in_pages as 索引碎片在索引页中的数量 from sys.dm_db_index_physical_stats(null,null,null,null,Sampled) ps join sys.indexes ix on ps.[object_id] = ix.[object_id] and ps.index_id = ix.index_id where avg_fragmentation_in_percent=30 下面这条语句重建索引： ALTER INDEX ALL ON [dbo].[YourTableName] REBUILD WITH ( SORT_IN_TEMPDB = ON ,STATISTICS_NORECOMPUTE = ON ,ONLINE = ON); 5. 查看数据库脱机时间 .txt EXEC sp_configure show advanced options, 1 RECONFIGURE go EXEC sp_configure xp_cmdshell, 1 RECONFIGURE GO select a.name,a.database_id,a.create_date,b.physical_name into #a from sys.databases a left join sys.master_files b on a.database_id=b.database_id where has_