SQL Server 巡检脚本（DB级别）

guo

1739

6页

48次

2019-11-06

5墨值下载

注意：

很多脚本在2008和2012数据库中不能通用，常见原因有以下3个，应当根据实际情况改写或舍弃。

1.视图或函数不存在，或视图中的列不存在

2.视图或函数属于master，在2012中非master也能用，但在2008中必须usemaster后执行

3.一些语法差异，如2012中可以直接把db_id()作为表值函数的输入参数，但是在2008中必须写成：

1. Declare@dbidint

2. Select@dbid=DB_ID()

然后将@dbid作为输入参数赋值给表值函数。

USE<YourData baseName>;ma kesuretocha ngetoanactualdatabase onyour

instance！！！

1、当前数据库的数据文件和事务日志文件信息

1. SELECTf.nameAS[FileName],f.physical_nameAS[PhysicalName],

2. CAST((f.size/128.0)ASDECIMAL(15,2))AS[TotalSizeinMB],

3. CAST(f.size/128.0‐CAST(FILEPROPERTY(f.name,'SpaceUsed')ASint)/128.0ASDECIMAL(

4. AS[AvailableSpaceInMB],[file_id],fg.nameAS[FilegroupName]

5. FROMsys.database_filesASfWITH(NOLOCK)

6. LEFTOUTERJOINsys.data_spacesASfgWITH(NOLOCK)

7. ONf.data_space_id=fg.data_space_idOPTION(RECOMPILE);

2、当前数据库的文件的IO信息

1. Declare@dbidint

2. Select@dbid=DB_ID()

3. SELECTDB_NAME(@dbid)AS[DatabaseName],df.nameAS[LogicalName],vfs.[file_id],

4. df.physical_nameAS[PhysicalName],vfs.num_of_reads,vfs.num_of_writes,vfs.io_stall_read_ms

5. CAST(100.*vfs.io_stall_read_ms/(vfs.io_stall_read_ms+vfs.io_stall_write_ms)ASDECIMAL

6. CAST(100.*vfs.io_stall_write_ms/(vfs.io_stall_write_ms+vfs.io_stall_read_ms)ASDECIMAL

7. (vfs.num_of_reads+vfs.num_of_writes)AS[Writes+Reads],

8. CAST(vfs.num_of_bytes_read/1048576.0ASDECIMAL(10,2))AS[MBRead],

9. CAST(vfs.num_of_bytes_written/1048576.0ASDECIMAL(10,2))AS[MBWritten],

10. CAST(100.*vfs.num_of_reads/(vfs.num_of_reads+vfs.num_of_writes)ASDECIMAL(10,1))

11. CAST(100.*vfs.num_of_writes/(vfs.num_of_reads+vfs.num_of_writes)ASDECIMAL(10,1

12. CAST(100.*vfs.num_of_bytes_read/(vfs.num_of_bytes_read+vfs.num_of_bytes_written)

13. CAST(100.*vfs.num_of_bytes_written/(vfs.num_of_bytes_read+vfs.num_of_bytes_written

14. FROMsys.dm_io_virtual_file_stats(@dbid,NULL)ASvfs

15. INNERJOINsys.database_filesASdfWITH(NOLOCK)

16. ONvfs.[file_id]=df.[file_id]OPTION(RECOMPILE);

3、查询执行次数最多的100个SQL（MSSQL2012适用）

1. SELECTTOP(100)qs.execution_count,qs.total_rows,qs.last_rows,qs.min_rows,qs.max_rows

2. qs.last_elapsed_time,qs.min_elapsed_time,qs.max_elapsed_time,

3. total_worker_time,total_logical_reads,

4. SUBSTRING(qt.TEXT,qs.statement_start_offset/2+1,

5. (CASEWHENqs.statement_end_offset=‐1

6. THENLEN(CONVERT(NVARCHAR(MAX),qt.TEXT))*2

7. ELSEqs.statement_end_offsetEND‐qs.statement_start_offset)/2)ASquery_text

8. FROMsys.dm_exec_query_statsASqsWITH(NOLOCK)

9. CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)ASqt

10. WHEREqt.dbid=DB_ID()

11. ORDERBYqs.execution_countDESCOPTION(RECOMPILE);

4、查询执行次数最多的100个存储过程

1. SELECTTOP(100)p.nameAS[SPName],qs.execution_count,

2. ISNULL(qs.execution_count/DATEDIFF(Minute,qs.cached_time,GETDATE()),0)AS[Calls/

3. qs.total_worker_time/qs.execution_countAS[AvgWorkerTime],qs.total_worker_timeAS

4. qs.total_elapsed_time,qs.total_elapsed_time/qs.execution_countAS[avg_elapsed_time

5. qs.cached_time

6. FROMsys.proceduresASpWITH(NOLOCK)

7. INNERJOINsys.dm_exec_procedure_statsASqsWITH(NOLOCK)

8. ONp.[object_id]=qs.[object_id]

9. WHEREqs.database_id=DB_ID()

10. ORDERBYqs.execution_countDESCOPTION(RECOMPILE);

5、查询平均执行时间最长的25个存储过程

1. SELECTTOP(25)p.nameAS[SPName],qs.total_elapsed_time/qs.execution_countAS[avg_elapsed_time

2. qs.total_elapsed_time,qs.execution_count,ISNULL(qs.execution_count/DATEDIFF(Minute

3. GETDATE()),0)AS[Calls/Minute],qs.total_worker_time/qs.execution_countAS[AvgWorkerTime

4. qs.total_worker_timeAS[TotalWorkerTime],qs.cached_time

5. FROMsys.proceduresASpWITH(NOLOCK)

6. INNERJOINsys.dm_exec_procedure_statsASqsWITH(NOLOCK)

7. ONp.[object_id]=qs.[object_id]

8. WHEREqs.database_id=DB_ID()

9. ORDERBYavg_elapsed_timeDESCOPTION(RECOMPILE);

6、查询逻辑读最多的25个存储过程

1. SELECTTOP(25)p.nameAS[SPName],qs.total_logical_readsAS[TotalLogicalReads],

2. qs.total_logical_reads/qs.execution_countAS[AvgLogicalReads],qs.execution_count,

3. ISNULL(qs.execution_count/DATEDIFF(Minute,qs.cached_time,GETDATE()),0)AS[Calls/

4. qs.total_elapsed_time,qs.total_elapsed_time/qs.execution_count

5. AS[avg_elapsed_time],qs.cached_time