mssql常用脚本

那些你一定会用到的 SQL Server 查询脚本，本文脚本基于 SQL Server 2008 - 2019 测试，可用于定期巡检。

1.运行状态

  • 频繁使用的表（以user_seeks倒排序）TOP 200
IF OBJECT_ID('tempdb..#tables', 'U') <> 0 
DROP TABLE #tables 
GO 

SELECT db_id() AS database_id,db_name() AS database_name,s.name AS schema_name,t.name AS table_name,t.object_id AS table_id INTO #tables FROM sys.tables AS t join sys.schemas AS s ON t.schema_id=s.schema_id WHERE 1 = 0 
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') begin USE [?]; insert into #tables SELECT db_id(),db_name(),s.name,t.name,t.object_id FROM sys.tables AS t join sys.schemas AS s ON t.schema_id=s.schema_id end' 

SELECT TOP 200 tmp.database_name,tmp.schema_name,tmp.table_name,sd.index_id,sd.user_seeks,sd.user_scans,sd.user_lookups,sd.user_updates 
FROM sys.dm_db_index_usage_stats as sd 
INNER JOIN #tables as tmp ON db_name(sd.database_id)=tmp.database_name and sd.object_id= tmp.table_id 
ORDER BY (sd.user_seeks + sd.user_scans + sd.user_lookups + sd.user_updates) DESC
  • 连接数使用率
SELECT 
  (SELECT COUNT(1) FROM sys.dm_exec_connections WHERE session_id > 50) AS active_connections,
  CONVERT(INT, ( SELECT value FROM sys.configurations WHERE name = 'user connections' )) AS configured_user_connections,
  @@MAX_CONNECTIONS AS max_connections,
  CASE WHEN CONVERT(INT, (SELECT value FROM sys.configurations WHERE name = 'user connections')) = 0 
    THEN 0.00
    ELSE CAST(
      (SELECT COUNT(1) FROM sys.dm_exec_connections WHERE session_id > 50) * 100.0 / 
       CONVERT(INT, (SELECT value FROM sys.configurations WHERE name = 'user connections'))
       AS DECIMAL(18,2)
    )
END AS connection_usage_percent;
  • 实例配置信息
--.查看基本配置 
SET NOCOUNT ON; 
SET QUOTED_IDENTIFIER ON; 

DECLARE @SVer TABLE(ID int, Name sysname, Internal_Value int, Value nvarchar(512)) 
DECLARE @drivers TABLE(drive sysname, MB_Avail bigint) 
DECLARE @instance_info TABLE(sid int IDENTITY(1,1), config_name nvarchar(40), run_value nvarchar(256)) 
DECLARE @manufacturer TABLE(LogDate datetime, ProcessInfo sysname, Info nvarchar(1024)) 
DECLARE @license TABLE(LogDate datetime, ProcessInfo sysname, Info nvarchar(1024)) 
DECLARE @port TABLE(LogDate datetime, ProcessInfo sysname, Info nvarchar(1024)) 

INSERT INTO @SVer EXEC master.dbo.xp_msver; 
INSERT INTO @drivers EXEC master..xp_fixeddrives; 

--.优化.若当前sqlserverlog过大则直接读取会非常慢,改为读取最小日志中的Server信息
--INSERT INTO @manufacturer EXEC master.sys.xp_readerrorlog 0, 1, N'Manufacturer';
DECLARE @MinID tinyint 
DECLARE @tbLog TABLE(Archive int, LogDate datetime, LogFileSize bigint) 
INSERT INTO @tbLog EXEC master.dbo.sp_enumerrorlogs 
SELECT @MinID=Archive FROM @tbLog WHERE LogFileSize = (SELECT min(LogFileSize) FROM @tbLog) 
DECLARE @errorlog2 TABLE(logdate datetime,info varchar(20),text nvarchar(max)) 
INSERT INTO @manufacturer EXEC master.sys.xp_readerrorlog @MinID,1,N'Manufacturer'; 
INSERT INTO @license EXEC master.sys.xp_readerrorlog @MinID,1,N'processors'; 
INSERT INTO @port EXEC master.sys.xp_readerrorlog @MinID,1,N'established'; 

--.Version 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Manufacturer',Info FROM @manufacturer 
INSERT INTO @instance_info(config_name,run_value) SELECT 'InstanceName',@@SERVERNAME 
INSERT INTO @instance_info(config_name,run_value) SELECT 'ServerName',CAST(SERVERPROPERTY('ServerName') AS nvarchar(128)) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'ProductVersion',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(40)) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Version',REPLACE(SUBSTRING(@@VERSION,1,CHARINDEX('-', (@@VERSION))),'-','') 
INSERT INTO @instance_info(config_name,run_value) SELECT 'ProductLevel',CAST(SERVERPROPERTY('ProductLevel') AS VARCHAR(40)) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Edition',CAST(SERVERPROPERTY('Edition') AS VARCHAR(60)) 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,Value FROM @SVer WHERE [Name] = 'Language' 

--.CPU 
DECLARE @ProcessorNameString nvarchar(512) 
-- EXEC master.sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString', @ProcessorNameString OUTPUT 
-- INSERT INTO @instance_info(config_name,run_value) SELECT 'ProcessorNameString',@ProcessorNameString 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Physical_CPU_Count',cpu_count/hyperthread_ratio FROM sys.dm_os_sys_info WITH(NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Hyperthread_Ratio',hyperthread_ratio FROM sys.dm_os_sys_info WITH(NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Logical_CPU_Count',cpu_count FROM sys.dm_os_sys_info WITH(NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'CPU_Usage_Percent',CAST(100-SystemIdle AS numeric(18,2)) FROM (SELECT SystemIdle=record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int') FROM (SELECT TOP 1 CONVERT(xml,record) AS [record] FROM master.sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%%' ORDER BY [timestamp] DESC) AS x ) AS y 

--.Memory 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Physical_Memory_MB',total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory WITH (NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Available_Memory_MB',available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory WITH (NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Memory_Usage_Percent',CAST(1-CAST(available_physical_memory_kb AS numeric(18,2))/CAST(total_physical_memory_kb AS numeric(18,2)) AS numeric(18,2))*100 FROM sys.dm_os_sys_memory WITH (NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'System_Memory_State',system_memory_state_desc FROM sys.dm_os_sys_memory WITH (NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(NVARCHAR(20),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'min server memory (MB)' 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(NVARCHAR(20),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'max server memory (MB)' 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(NVARCHAR(20),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'awe enabled' 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Available_GB: '+drive,MB_Avail/1024 FROM @drivers

DECLARE @ProductName nvarchar(512), @SmoLoginMode int, @SmoAuditLevel int, @param nvarchar(512) 
DECLARE @SmoDefaultFile nvarchar(512), @SmoDefaultLog nvarchar(512), @BackupDirectory nvarchar(512) 
-- EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows NT\CurrentVersion',N'ProductName', @ProductName OUTPUT 
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT 
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT 
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT 
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @SmoLoginMode OUTPUT 
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @SmoAuditLevel OUTPUT 
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @param OUTPUT 
INSERT INTO @instance_info(config_name,run_value) SELECT 'ProductName',@ProductName 
INSERT INTO @instance_info(config_name,run_value) SELECT 'DefaultData',@SmoDefaultFile 
INSERT INTO @instance_info(config_name,run_value) SELECT 'DefaultLog',@SmoDefaultLog 
INSERT INTO @instance_info(config_name,run_value) SELECT 'DefaultBackup',@BackupDirectory 
INSERT INTO @instance_info(config_name,run_value) SELECT 'DefaultLogin' ,CASE WHEN @SmoLoginMode='1' THEN 'Windows Authentication mode' ELSE 'SQL Server and Windows Authentication mode' END 
INSERT INTO @instance_info(config_name,run_value) SELECT 'AuditLevel', CASE WHEN @SmoAuditLevel = 0 THEN 'None' WHEN @SmoAuditLevel = 1 THEN 'Successful Logins Only' WHEN @SmoAuditLevel = 2 THEN 'Failed Logins Only' WHEN @SmoAuditLevel = 3 THEN 'Both Failed and Successful Logins' END 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(NVARCHAR(20),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'backup compression default' 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(NVARCHAR(20),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'max degree of parallelism' 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(NVARCHAR(20),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'cost threshold for parallelism' 
INSERT INTO @instance_info(config_name,run_value) SELECT 'DefaultErrorLog',@param+'\Log' 
INSERT INTO @instance_info(config_name,run_value) SELECT 'Instance_Collation',CAST(SERVERPROPERTY ('Collation') AS VARCHAR(40)) 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(VARCHAR(10),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'Database Mail XPs' 
INSERT INTO @instance_info(config_name,run_value) SELECT Name,CONVERT(VARCHAR(10),Value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE Name = 'default trace enabled' 
-- INSERT INTO @instance_info(config_name,run_value) SELECT 'TracePath',path FROM sys.traces WITH(NOLOCK) WHERE status = 1 
INSERT INTO @instance_info(config_name,run_value) SELECT 'local_net_address',CAST(ConnectionProperty('local_net_address') AS varchar(20)) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'local_tcp_port',CAST(ConnectionProperty('local_tcp_port') AS varchar(10)) 
INSERT INTO @instance_info(config_name,run_value) SELECT TOP 1 'local_net_address',local_net_address FROM SYS.DM_EXEC_CONNECTIONS WITH(NOLOCK) WHERE local_net_address IS NOT NULL AND net_transport = 'TCP' AND local_net_address not in('127.0.0.1','::1') AND len(local_net_address)<16
INSERT INTO @instance_info(config_name,run_value) SELECT 'sqlserver_start_time',convert(nvarchar(20),sqlserver_start_time,120) FROM sys.dm_os_sys_info WITH(NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'session_count',COUNT(session_id) FROM sys.dm_exec_sessions WITH(NOLOCK) 
INSERT INTO @instance_info(config_name,run_value) SELECT 'ListeningPort',Info FROM @port 
INSERT INTO @instance_info(config_name,run_value) SELECT 'LicenseCAL',Info FROM @license 

--.为了避免导出excel格式错乱, 将 run_value 中的逗号分隔符替换为.
SELECT sid,config_name,replace(run_value,',','.') as run_value FROM @instance_info
  • 磁盘使用率
--.The following script is only supported in SQL Server 2008 R2 SP3(10.50.6000) and later versions.
SET NOCOUNT ON;

DECLARE @version SYSNAME 
DECLARE @exec NVARCHAR(1024) 
SET @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(40)) 
SET @version = SUBSTRING(@version,1,CHARINDEX('.',@version)-1) 

IF @version >= 11 
BEGIN 
  SET @exec = 'SELECT DISTINCT UPPER(vs.volume_mount_point) AS disk_partition, vs.file_system_type, vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [total_size_gb]' + CHAR(10) 
    + ',CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [available_size_gb], CAST(1-CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [space_used_pct]' + CHAR(10) 
    + 'FROM sys.master_files AS mf WITH(NOLOCK)' + CHAR(10) 
    + 'CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs ORDER BY disk_partition ASC' 
  EXECUTE (@exec) 
END
  • IOPS及QPS：采样间隔10秒
--.采集连接数及QPS 
SET NOCOUNT ON;

DECLARE 
 @RecTimePre DATETIME
,@RecTimeAft DATETIME
,@Interval INT
,@Total_IOPS_Count_Pre BIGINT
,@Total_QPS_Count_Pre BIGINT
,@Total_Throughout_Pre  BIGINT
,@Total_DeadLock_Count_Pre BIGINT
,@Total_LockWait_Count_Pre BIGINT
,@Total_IOPS_Count_Aft BIGINT
,@Total_QPS_Count_Aft BIGINT
,@Total_Throughout_Aft  BIGINT
,@Total_DeadLock_Count_Aft BIGINT
,@Total_LockWait_Count_Aft BIGINT
,@Total_Connections BIGINT

SET @RecTimePre=GETDATE() 
SELECT @Total_IOPS_Count_Pre=SUM(num_of_reads + num_of_writes) FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) 
SELECT @Total_Throughout_Pre=SUM(num_of_bytes_read+num_of_bytes_written) FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT) 
SELECT @Total_QPS_Count_Pre=cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:SQL Statistics' AND counter_name = 'Batch Requests/sec' 
SELECT @Total_DeadLock_Count_Pre=cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total' 
SELECT @Total_LockWait_Count_Pre=cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Lock Waits/sec' AND instance_name = '_Total' 

--.采集间隔.
WAITFOR DELAY '00:00:10'

SET @RecTimeAft=GETDATE() 
SET @Interval=DATEDIFF(ss,@RecTimePre,@RecTimeAft) 
SELECT @Total_IOPS_Count_Aft=SUM(num_of_reads + num_of_writes) FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT)
SELECT @Total_QPS_Count_Aft=cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:SQL Statistics' AND counter_name = 'Batch Requests/sec'
SELECT @Total_DeadLock_Count_Aft=cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'
SELECT @Total_LockWait_Count_Aft=cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Locks' AND counter_name = 'Lock Waits/sec' AND instance_name = '_Total'
SELECT @Total_Throughout_Aft=SUM(num_of_bytes_read+num_of_bytes_written) FROM sys.dm_io_virtual_file_stats(DEFAULT, DEFAULT)  

SELECT @Total_Connections=COUNT(1) 
FROM Sys.dm_exec_requests AS r WITH(NOLOCK) 
RIGHT OUTER JOIN Sys.dm_exec_sessions AS s WITH(NOLOCK) ON r.session_id = s.session_id 
RIGHT OUTER JOIN Sys.dm_exec_connections AS c WITH(NOLOCK) ON s.session_id = c.session_id 
WHERE s.session_id >50 AND s.session_id<>@@SPID 

SELECT 
  CONVERT(varchar(16),GETDATE(),120)+':00' AS BeginTime 
 ,@Interval AS Interval 
 ,@Total_Connections AS Connections 
 ,(@Total_IOPS_Count_Aft-@Total_IOPS_Count_Pre)/@Interval AS IOPS 
 ,(@Total_QPS_Count_Aft-@Total_QPS_Count_Pre)/@Interval AS QPS 
 ,(@Total_DeadLock_Count_Aft-@Total_DeadLock_Count_Pre)/@Interval AS DeadLock 
 ,(@Total_LockWait_Count_Aft-@Total_LockWait_Count_Pre)/@Interval AS LockWait

2.扩展性评估

  • 数据库使用情况
IF OBJECT_ID('tempdb..#dblog', 'U') <> 0 
DROP TABLE #dblog 
GO 

CREATE TABLE #dblog(database_name sysname, log_size_mb numeric(18,2), log_space_used  numeric(18,2), log_status int) 
INSERT INTO #dblog EXECUTE ('dbcc sqlperf(logspace) with no_infomsgs') 
GO 

WITH cte_dbsize AS ( SELECT DB_NAME(database_id) AS dbname, CAST(SUM(size)/128.0 AS numeric(18,2)) AS database_size_mb FROM sys.master_files WITH(NOLOCK) WHERE state=0 GROUP BY database_id ) 
SELECT sd.name AS database_name,database_id,owner_sid,cte.database_size_mb,tmp.log_size_mb,tmp.log_space_used,tmp.log_status,create_date,compatibility_level,user_access_desc,is_auto_shrink_on,state_desc,recovery_model_desc,log_reuse_wait_desc 
FROM master.sys.databases AS sd WITH(NOLOCK) 
INNER JOIN cte_dbsize AS cte WITH(NOLOCK) ON sd.name = cte.dbname 
INNER JOIN #dblog AS tmp WITH(NOLOCK) ON tmp.database_name = sd.name 
ORDER BY sd.database_id
  • 大库（以数据大小倒排序）TOP x
DECLARE 
  @database_id int, 
  @database_name sysname, 
  @sql_string nvarchar(2000) 

DECLARE @file_size TABLE ( 
    [database_name] [sysname] NULL, 
    [group_id] [smallint] NULL, 
    [group_name] sysname NULL, 
    [file_id] [smallint] NULL, 
    [file_size_mb] [decimal](12, 2) NULL, 
    [space_used_mb] [decimal](12, 2) NULL, 
    [free_space_mb] [decimal](12, 2) NULL, 
    [name] [sysname] NOT NULL, 
    [file_name] [nvarchar](260) NOT NULL ) 

SELECT TOP 1 @database_id = database_id ,@database_name = name 
FROM sys.databases WITH(NOLOCK) 
WHERE database_id > 4 AND state = 0 
ORDER BY database_id 

WHILE @database_name IS NOT NULL 
BEGIN 
  SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10) 
  SET @sql_string = @sql_string + '
  SELECT 
    DB_NAME() 
    ,sysfilegroups.groupid 
    ,sysfilegroups.groupname 
    ,fileid 
    ,convert(decimal(12,2),cast(round(sysfiles.size/128.000,2) as float)) as file_size 
    ,convert(decimal(12,2),cast(round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2) as float)) as space_used 
    ,convert(decimal(12,2),cast(round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2) as float)) as free_space 
    ,sysfiles.name 
    ,sysfiles.filename 
  FROM sys.sysfiles WITH(NOLOCK) 
  LEFT OUTER JOIN sys.sysfilegroups WITH(NOLOCK) ON sysfiles.groupid = sysfilegroups.groupid' 

  INSERT INTO @file_size EXEC sp_executesql @sql_string    

  --Grab next database 
  SET @database_name = NULL 
  SELECT TOP 1 @database_id = database_id, @database_name = name 
  FROM sys.databases 
  WHERE database_id > 4 AND state = 0 AND database_id > @database_id 
  ORDER BY database_id 
END 

--File Sizes 
SELECT database_name, ISNULL(group_name,'LOG') group_name, file_id, name, file_size_mb, space_used_mb, free_space_mb, file_name 
FROM @file_size 
ORDER BY file_size_mb DESC
  • 大表（以数据大小倒排序）TOP 200
IF OBJECT_ID('tempdb..#tb', 'U') <> 0 
DROP TABLE #tb 
GO 

CREATE TABLE #tb(database_name varchar(60),schema_name varchar(60),table_name varchar(100),type_desc varchar(20),table_rows bigint,total_pages bigint,used_pages bigint) 
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') BEGIN use [?]; insert into #tb SELECT db_name(),s.name,object_name(t.object_id),u.type_desc,max(p.rows),sum(u.total_pages),sum(u.used_pages) FROM sys.allocation_units AS u JOIN sys.partitions AS p ON u.container_id = p.hobt_id JOIN sys.tables AS t ON p.object_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE p.index_id IN (0,1) AND u.type = 1 AND p.rows > 0 GROUP BY s.name,t.object_id,object_name(t.object_id),u.type_desc END' 
SELECT TOP 200 database_name,schema_name,table_name,type_desc,table_rows,CONVERT(NUMERIC(18,2),total_pages * 8 / 1024.0) AS total_pages_mb,CONVERT(NUMERIC(18,2),used_pages * 8 / 1024.0) AS used_pages_mb FROM #tb ORDER BY total_pages DESC
  • 自增id上限使用情况
IF OBJECT_ID('tempdb..#tb', 'U') <> 0 
DROP TABLE #tb 
GO 
CREATE TABLE #tb(database_name varchar(60),schema_name varchar(60),table_name varchar(60),column_name varchar(60),column_type varchar(20)); 
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') BEGIN use [?]; insert into #tb SELECT db_name(),s.name,t.name,c.name,p.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON t.object_id = c.object_id LEFT JOIN sys.types AS p ON c.system_type_id = p.user_type_id WHERE c.is_identity = 1 END' 
GO 

IF OBJECT_ID('tempdb..#seed', 'U') <> 0 
DROP TABLE #seed 
GO 
CREATE TABLE #seed(id int identity(1,1),database_name varchar(60),schema_name varchar(60),table_name varchar(60),column_name varchar(60),column_type varchar(20),current_identity bigint) 
INSERT INTO #seed(database_name,schema_name,table_name,column_name,column_type) SELECT database_name,schema_name,table_name,column_name,column_type FROM #tb ORDER BY database_name,table_name
DECLARE @i int, @j int, @database_name varchar(60), @schema_name varchar(60), @table_name varchar(60), @sql varchar(200), @identity_current bigint 
SELECT @i=1,@j=MAX(id) FROM #seed 
WHILE @i <= @j 
BEGIN 
  SELECT @database_name = database_name, @schema_name = schema_name, @table_name = table_name FROM #seed WHERE id = @i 
  SET @sql = 'UPDATE #seed SET current_identity = IDENT_CURRENT('''+@database_name+'.'+@schema_name+'.'+@table_name+''') where id = '+CAST(@i AS VARCHAR(20))+'' 
  EXECUTE (@sql) 
  SET @i = @i + 1 
END 

SELECT 
  database_name,schema_name,table_name,column_name,column_type,current_identity 
  ,CASE column_type 
    WHEN 'tinyint' THEN 255 
    WHEN 'smallint' THEN 32767 
    WHEN 'int' THEN 2147483647 
    WHEN 'bigint' THEN 9223372036854775807 
    ELSE NULL 
    END AS max_identity 
  ,CASE 
    WHEN current_identity IS NULL THEN 0.00 
    ELSE CAST( 
      (CAST(CONVERT(BIGINT, current_identity) AS FLOAT) / 
        CASE column_type 
          WHEN 'tinyint' THEN 255.0 
          WHEN 'smallint' THEN 32767.0 
          WHEN 'int' THEN 2147483647.0 
          WHEN 'bigint' THEN 9223372036854775807.0 
        ELSE 1.0 
    END) * 100 AS DECIMAL(5,2)) 
  END AS identity_used_percent 
FROM #seed 
WHERE (column_type = 'tinyint' AND current_identity > 240) OR (column_type = 'smallint' AND current_identity > 30000) OR (column_type = 'int' AND current_identity > 1900000000) OR (column_type = 'bigint' AND current_identity > 8000000000000000000) 
ORDER BY identity_used_percent DESC

3.稳定性评估

  • 单表索引过多(超过6个)：过多的索引降低了 insert 及 update 的效率
IF OBJECT_ID('tempdb..#tb', 'U') <> 0 
DROP TABLE #tb 
GO 

CREATE TABLE #tb(database_name varchar(60),schema_name varchar(60),table_name varchar(60),index_id int,index_name varchar(100)); 
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') BEGIN use [?]; insert into #tb select db_name(), sc.name, t.name, si.index_id, si.name from sys.indexes as si join sys.tables as t on si.object_id = t.object_id join sys.schemas as sc on t.schema_id = sc.schema_id where t.type = ''U'' and si.index_id > 0 END'
SELECT TOP 200 database_name,schema_name,table_name,count(index_name) as index_count FROM #tb GROUP BY database_name,schema_name,table_name HAVING count(index_name)>=6 ORDER BY count(index_name) DESC
  • 索引缺失(性能提升90%)：全表扫描将锁表，数据增多或高并发将造成灾难
SELECT TOP 200 
   db_name(mid.database_id) AS database_name 
  ,mid.[object_id] AS objectid 
  ,mid.statement 
  ,CONVERT (decimal (28,2), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure 
  ,migs.avg_user_impact 
  ,CONVERT (decimal (28,2),migs.avg_total_user_cost) AS avg_total_user_cost
--,mid.index_handle 
  ,migs.user_seeks 
  ,migs.user_scans 
--,migs.last_user_seek 
--,migs.last_user_scan 
  ,'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
    + ' ON ' + mid.statement 
    + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') 
    + ')' 
    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement 
FROM sys.dm_db_missing_index_groups AS mig WITH(NOLOCK) 
INNER JOIN sys.dm_db_missing_index_group_stats AS migs WITH(NOLOCK) ON migs.group_handle = mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details AS mid WITH(NOLOCK) ON mig.index_handle = mid.index_handle 
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
  • 索引碎片(超过70%)：过多碎片的索引会降低查询性能，导致应用程序响应慢
--.查看所有db所有table的索引碎片(行数超过1W+碎片超过70%,首次执行耗时约6m14s,再次执行耗时6s) 
--.2026.06.09.在一个500G的实例上运行37m未结束...
SET NOCOUNT ON; 

IF OBJECT_ID('tempdb..#tb', 'U') <> 0 
DROP TABLE #tb 
GO 

--.提前过滤掉行数小于10000的表
CREATE TABLE #tb(database_name varchar(60),database_id int,schema_name varchar(60),table_id bigint,table_name varchar(60),index_id int,index_name varchar(100),rows bigint); 
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') BEGIN use [?]; insert into #tb select distinct db_name(),db_id(),sc.name,t.object_id,t.name,si.index_id,si.name,ss.rows from sys.indexes as si join sys.tables as t on si.object_id = t.object_id join sys.schemas as sc on t.schema_id = sc.schema_id join sys.sysindexes as ss on ss.id = t.object_id where t.type = ''U'' and si.index_id > 0 and ss.rows > 10000 END' 

--.防止数据太多,仅返回top 200 
SELECT TOP 200 
   tmp.database_name 
  ,tmp.schema_name 
  ,tmp.table_name 
  ,tmp.rows AS table_rows 
  ,tmp.index_name 
  ,CAST(sdb.avg_fragmentation_in_percent AS DECIMAL(18,2)) AS index_frag_percent 
  ,'ALTER INDEX '+tmp.index_name+' ON '+tmp.database_name+'.'+tmp.schema_name+'.'+tmp.table_name+' REBUILD WITH(ONLINE=ON)' AS index_rebuild_sql 
-- 使用 CROSS APPLY 替代全表扫描, 只针对符合条件的索引调用 sys.dm_db_index_physical_stats 而不是扫描所有索引
-- FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, 'LIMITED') AS sdb INNER JOIN #tb as tmp ON sdb.database_id = tmp.database_id AND sdb.object_id = tmp.table_id AND sdb.index_id = tmp.index_id 
FROM #tb AS tmp CROSS APPLY sys.dm_db_index_physical_stats(tmp.database_id, tmp.table_id, tmp.index_id, NULL, 'LIMITED') AS sdb 
WHERE sdb.index_id > 0 --排除堆表 
AND sdb.partition_number = 1 --排除分区表partition_number>1的索引 
AND sdb.avg_fragmentation_in_percent > 70 -- 碎片超过70% 
ORDER BY sdb.avg_fragmentation_in_percent DESC
  • 无效索引(使用次数为0)：索引已创建但从未被使用（查询次数/访问次数为0）
IF OBJECT_ID('tempdb..#tb', 'U') <> 0 
DROP TABLE #tb 
GO 

CREATE TABLE #tb(database_name varchar(60),database_id int,schema_name varchar(60),schema_id int,table_id bigint,table_name varchar(60),index_id int,index_name varchar(100),rows bigint,is_disabled int,is_primary_key int,is_unique_constraint int); 
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') BEGIN use [?]; insert into #tb select distinct db_name(),db_id(),sc.name,sc.schema_id,t.object_id,t.name,si.index_id,si.name,ss.rows,si.is_disabled,si.is_primary_key,si.is_unique_constraint from sys.indexes as si join sys.tables as t on si.object_id = t.object_id join sys.schemas as sc on t.schema_id = sc.schema_id join sys.sysindexes as ss on ss.id = t.object_id and ss.indid = 1 where t.type = ''U'' and si.index_id > 0 AND si.type_desc <> ''HEAP'' END'

SELECT TOP 200 
   tmp.database_name 
  ,tmp.schema_name 
  ,tmp.table_name 
  ,tmp.index_name 
  ,tmp.is_primary_key 
  ,tmp.is_unique_constraint 
  ,tmp.is_disabled 
  ,ISNULL(ius.user_updates,0) AS user_updates   --.index write
  ,ISNULL(ius.user_seeks,0) AS user_seeks       --.index read
  ,ISNULL(ius.user_scans,0) AS user_scans       --.index read
  ,ISNULL(ius.user_lookups,0) AS user_lookups   --.index read
  ,CASE 
     WHEN tmp.is_primary_key = 1 THEN 'ALTER TABLE [' + tmp.database_name + ']' + '.' + '[' + tmp.schema_name + ']' + '.' + '[' + tmp.table_name + '] DROP CONSTRAINT [' + tmp.index_name + ']' 
     WHEN tmp.is_unique_constraint = 1 THEN 'ALTER TABLE [' + tmp.database_name + ']' + '.' + '[' + tmp.schema_name + ']' + '.' + '[' + tmp.table_name + '] DROP CONSTRAINT [' + tmp.index_name + ']' 
     ELSE 'DROP INDEX [' + tmp.index_name + '] ON [' + tmp.database_name + ']' + '.' + '[' + tmp.schema_name + ']' + '.' + '[' + tmp.table_name + ']' 
   END AS drop_index_statement 
FROM #tb AS tmp 
LEFT JOIN sys.dm_db_index_usage_stats AS ius WITH(NOLOCK) ON tmp.table_id = ius.object_id AND tmp.index_id = ius.index_id 
WHERE ( ius.index_id IS NULL OR (ius.user_updates > 0 AND ius.user_seeks = 0 AND ius.user_scans = 0 AND ius.user_lookups = 0) ) 
ORDER BY tmp.database_name, tmp.table_name, tmp.index_name
  • 慢查询（以 avg_logical_reads 倒排序）top (200)
IF OBJECT_ID('tempdb..#slowlog', 'U') <> 0 
DROP TABLE #slowlog 
GO 

SELECT 
   qs.execution_count 
  ,qs.total_logical_reads 
  ,(qs.total_logical_reads / NULLIF(qs.execution_count, 0)) AS avg_logical_reads 
  ,qs.total_logical_writes 
  ,(qs.total_logical_writes / NULLIF(qs.execution_count, 0)) AS avg_logical_writes 
  ,qs.total_physical_reads 
  ,(qs.total_physical_reads / NULLIF(qs.execution_count, 0)) AS avg_physical_reads 
  ,(qs.total_logical_reads + qs.total_logical_writes) AS total_logical_io 
  ,(qs.total_logical_reads + qs.total_logical_writes) / NULLIF(qs.execution_count, 0) AS avg_logical_io 
  ,(qs.total_logical_reads + qs.total_physical_reads + qs.total_logical_writes) / NULLIF(qs.execution_count, 0) AS average_io 
  ,qs.total_worker_time/1000 AS total_worker_time_ms 
  ,(qs.total_worker_time/1000) / NULLIF(qs.execution_count, 0) AS avg_worker_time_ms 
  ,qs.total_elapsed_time/1000 AS total_elapsed_time_ms 
  ,(qs.total_elapsed_time/1000) / NULLIF(qs.execution_count, 0) AS avg_elapsed_time_ms 
  ,qs.last_execution_time 
  ,qs.plan_generation_num 
  ,db_name(st.dbid) AS database_name 
  ,st.objectid 
  ,SUBSTRING(st.text,(qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text    --.未将换行符替换为空格之前的 query_text
INTO #slowlog 
FROM ( 
  SELECT TOP 200
    qs.sql_handle,
    qs.plan_handle,
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_physical_reads,
    qs.total_worker_time,
    qs.total_elapsed_time,
    qs.last_execution_time,
    qs.plan_generation_num,
    qs.statement_start_offset,
    qs.statement_end_offset
  FROM sys.dm_exec_query_stats AS qs WITH(NOLOCK)
  WHERE qs.total_worker_time > 0 AND qs.execution_count > 0 
  ORDER BY qs.total_logical_reads DESC 
) AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp 
ORDER BY total_logical_reads DESC OPTION (RECOMPILE) 

--.为了避免导出excel格式错乱, 将 query_text 中的换行符及制表符替换为空格, 将分隔符,改为.
UPDATE #slowlog SET query_text = REPLACE(REPLACE(REPLACE(query_text, CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') 
UPDATE #slowlog SET query_text = REPLACE(query_text, ',', '.') 
SELECT * FROM #slowlog
  • 空表(行数为0)：这些表的行数为0，疑似空表或临时表，请确认是否在用
IF OBJECT_ID('tempdb..#tb', 'U') <> 0 
DROP TABLE #tb 
GO 

CREATE TABLE #tb(database_name varchar(60),schema_name varchar(60),table_name varchar(100),type_desc varchar(20),table_rows bigint,total_pages bigint,used_pages bigint) 
EXECUTE master.sys.sp_MSforeachdb 'if ''?'' not in (''master'',''model'',''msdb'',''tempdb'') BEGIN use [?]; insert into #tb SELECT db_name(),s.name,object_name(t.object_id),u.type_desc,max(p.rows),sum(u.total_pages),sum(u.used_pages) FROM sys.allocation_units AS u JOIN sys.partitions AS p ON u.container_id = p.hobt_id JOIN sys.tables AS t ON p.object_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE p.index_id IN (0,1) AND u.type = 1 AND p.rows = 0 GROUP BY s.name,t.object_id,object_name(t.object_id),u.type_desc END' 
SELECT TOP 200 database_name,schema_name,table_name,type_desc,table_rows,CONVERT(NUMERIC(18,2),total_pages * 8 / 1024.0) AS total_pages_mb,CONVERT(NUMERIC(18,2),used_pages * 8 / 1024.0) AS used_pages_mb FROM #tb ORDER BY database_name,schema_name,table_name

4.安全性评估

  • 数据库备份记录：合理的备份方案可保证在灾难发生时将数据损失降到最低
DECLARE @tb TABLE(database_name nvarchar(40) COLLATE Chinese_PRC_CI_AS,last_backup_date varchar(20) COLLATE Chinese_PRC_CI_AS,backup_type varchar(30) COLLATE Chinese_PRC_CI_AS); 
INSERT INTO @tb SELECT database_name, MAX(ISNULL(CONVERT(varchar,backup_finish_date,120),'')) AS last_backup_date 
  , CASE type 
     WHEN 'D' THEN 'last_full_backup_date' 
     WHEN 'L' THEN 'last_log_backup_date' 
     WHEN 'I' THEN 'last_diff_backup_date' 
     WHEN 'F' THEN 'last_file_backup_date' 
     ELSE '' 
  END AS backup_type 
FROM msdb.dbo.backupset WITH(NOLOCK) GROUP BY database_name, type; 

WITH cte_dbsize AS ( SELECT DB_NAME(database_id) AS dbname, SUM(size)/128 AS database_size_mb FROM sys.master_files WITH(NOLOCK) WHERE state=0 GROUP BY database_id ) 
SELECT sd.name as database_name,sd.recovery_model_desc,cte.database_size_mb,b.last_full_backup_date,b.last_log_backup_date,b.last_diff_backup_date,b.last_file_backup_date 
FROM sys.databases AS sd INNER JOIN cte_dbsize AS cte on sd.name = cte.dbname 
LEFT JOIN @tb PIVOT(MAX(last_backup_date) FOR backup_type IN (last_full_backup_date,last_diff_backup_date,last_log_backup_date,last_file_backup_date)) AS b ON sd.name = b.database_name 
WHERE sd.name <> 'tempdb' order by sd.name
  • 高权限账号：有sa权限的账号
SELECT sa.name,sa.status,sa.sysadmin,sb.is_disabled,sb.default_database_name,sb.is_policy_checked,sb.is_expiration_checked
FROM sys.syslogins sa INNER JOIN sys.sql_logins sb ON sa.name = sb.name
WHERE sa.sysadmin = 1 AND sb.is_disabled = 0
  • 查看最近30日已启用作业的最近7条执行记录
WITH cte_jobhistory AS ( 
  SELECT 
    a.name as job_name 
   ,a.enabled as is_enabled 
   ,b.run_date 
   ,STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(b.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as run_time 
   ,STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(b.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as run_duration 
   ,b.step_name 
   ,b.message 
   ,case b.run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when 3 then 'Canceled' when 4 then 'Running' end as run_status 
   ,ROW_NUMBER() OVER (PARTITION BY b.job_id ORDER BY msdb.dbo.agent_datetime(b.run_date, b.run_time) DESC) AS execution_rank
  FROM msdb.dbo.sysjobs a 
  LEFT JOIN msdb.dbo.sysjobhistory b ON a.job_id = b.job_id 
  WHERE b.run_date > convert(char(8),getdate()-30,112) AND b.step_id = 0 AND a.enabled = 1 
) 

SELECT * FROM cte_jobhistory WHERE execution_rank <= 7 ORDER BY job_name ASC, run_date DESC, run_time DESC
  • 查看 alwayson 节点信息
--.The following script is only supported in SQL Server 2012 and later versions.
SET NOCOUNT ON;

DECLARE @version SYSNAME 
DECLARE @exec NVARCHAR(1024) 
SET @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(40)) 
SET @version = SUBSTRING(@version,1,CHARINDEX('.',@version)-1) 

IF @version >= 11 
BEGIN 
  SELECT 
    ag.name AS alwayson_name,
    ar.replica_server_name,
    ar.endpoint_url,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.primary_role_allow_connections_desc,
    ar.secondary_role_allow_connections_desc 
  FROM sys.availability_groups ag 
  JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id 
END
  • 查看 alwayson 延迟信息
--.The following script is only supported in SQL Server 2012 and later versions.
SET NOCOUNT ON;

DECLARE @version SYSNAME 
DECLARE @exec NVARCHAR(1024) 
SET @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(40)) 
SET @version = SUBSTRING(@version,1,CHARINDEX('.',@version)-1) 

IF @version >= 11 
BEGIN 
  SELECT 
    ag.name AS ag_name,
    ar.replica_server_name,
    adc.database_name,
    drs.is_local,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.last_commit_time,
    CASE WHEN drs.last_commit_time IS NOT NULL AND drs.last_commit_lsn IS NOT NULL THEN DATEDIFF(ss, drs.last_commit_time, GETDATE()) ELSE NULL END AS estimated_delay_seconds,
    ISNULL(drs.redo_queue_size,0) AS redo_queue_size,  --[Redo等待队列(KB)]
    ISNULL(drs.redo_rate,0) AS redo_rate,              --[Redo速率(KB/S)]
    CASE WHEN drs.redo_rate > 0 THEN CAST(drs.redo_queue_size AS FLOAT) / drs.redo_rate ELSE 0 END AS redo_delay_time_seconds,                 --[Redo延迟(秒)]
    ISNULL(drs.log_send_queue_size,0) AS log_send_queue_size,  --[Log传送等待队列(KB)]
    ISNULL(drs.log_send_rate,0) AS log_send_rate,              --[Log传送速率(KB/S)]
    CASE WHEN drs.log_send_rate > 0 THEN CAST(drs.log_send_queue_size AS FLOAT) / drs.log_send_rate ELSE 0 END AS log_send_delay_time_seconds  --[Log传送延迟(秒)]
  FROM sys.dm_hadr_database_replica_states drs
  JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
  JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
  JOIN sys.availability_databases_cluster adc ON drs.group_database_id = adc.group_database_id
  ORDER BY ar.replica_server_name, adc.database_name
END
Copyright © www.sqlfans.cn 2021 - 2026 All Right Reserved更新时间： 2026-07-16 10:44:36

results matching ""

    No results matching ""