sp_helpfile --当前数据库文件分配情况 sp_spaceused --当前db空间大小（有时不准） sp_spaceused 'dbo.user' --指定表的空间大小（有时不准） sp_helpdb --所有数据库文件分配情况 sp_helpdb 'tempdb' --指定的数据库文件分配情况 EXEC xp_cmdshell 'wmic logicaldisk get caption,size,freespace'; exec master.dbo.xp_fixeddrives --查看操作系统逻辑磁盘可用空间 dbcc sqlperf(logspace) --各数据库日志大小及使用百分比 dbcc showfilestats --当前db各文件“区”分配（基本准确） dbcc loginfo --查看当前数据库 虚拟日志数量 --查看数据库日志记录 dbcc log(tempdb,type) /* 默认 type = 0 0 - 最少信息(operation, context, transaction id) 1 - 更多信息(plus flags, tags, row length) 2 - 非常详细的信息(plus object name, index name,page id, slot id) 3 - 每种操作的全部信息 4 - 每种操作的全部信息加上该事务的16进制信息 */ --数据库当前模式和日志可用状态 select name,compatibility_level,state_desc ,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc from sys.databases --数据库文件IO情况 select * from sys.fn_virtualfilestats(DB_ID(),null) select * from sys.dm_io_virtual_file_stats(DB_ID(),null) select * from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) --检查当前闩锁的等待累积值 Select wait_type,waiting_tasks_count,wait_time_ms ,case when waiting_tasks_count<>0 then wait_time_ms/waiting_tasks_count else 0 end AvgWaiting from sys.dm_os_wait_stats where wait_type like '%LATCH%' order by wait_type --系统主要等待类型 SELECT TOP 10 wait_type,waiting_tasks_count ,wait_time_ms,signal_wait_time_ms ,wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,100.0 * wait_time_ms / SUM (wait_time_ms ) OVER( )AS percent_total_waits ,100.0 * signal_wait_time_ms / SUM (signal_wait_time_ms) OVER( )AS percent_total_signal_waits ,100.0 * ( wait_time_ms - signal_wait_time_ms )/SUM (wait_time_ms ) OVER( ) AS percent_total_resource_waits FROM sys .dm_os_wait_stats WHERE wait_time_ms > 0 --当前DB存储情况 select DB_NAME(database_id) as dbName,file_id,(size*8/1024) as [size(mb)] ,case when is_percent_growth = 1 then '10%' else CONVERT(varchar(10),growth*8/1024)+'M' end as growth ,type_desc,physical_name from sys.master_files where state = 0 and database_id=DB_id() --数据文件读写情况（比例） SELECT [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END, [Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END, [AvgBPerRead] =CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END, [AvgBPerWrite] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END, [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END, LEFT ([mf].[physical_name], 2) AS [Drive], DB_NAME ([vfs].[database_id]) AS [DB], [mf].[physical_name] FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs] JOIN sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id] ORDER BY [WriteLatency] DESC; SELECT DB_NAME(DB_ID()) AS [Database Name],[file_id], num_of_reads, num_of_writes, io_stall_read_ms, io_stall_write_ms, CAST(100. * io_stall_read_ms/(io_stall_read_ms + io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct], CAST(100. * io_stall_write_ms/(io_stall_write_ms + io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct], (num_of_reads + num_of_writes) AS [Writes + Reads], num_of_bytes_read, num_of_bytes_written, CAST(100. * num_of_reads/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct], CAST(100. * num_of_writes/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct], CAST(100. * num_of_bytes_read/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct], CAST(100. * num_of_bytes_written/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct] FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL); SELECT DB_NAME(fs.database_id) AS [Database Name] ,SUM(io_stall_read_ms) AS io_stall_read_ms ,SUM(io_stall_write_ms) AS io_stall_write_ms ,SUM(num_of_reads) AS num_of_reads ,SUM(num_of_writes) AS num_of_writes ,SUM(num_of_reads)*1.0/SUM(io_stall_read_ms)*1000 AS [reads_per_sec] ,SUM(num_of_writes)*1.0/SUM(io_stall_write_ms)*1000 AS [writes_per_sec] ,SUM(num_of_reads)*1.0/SUM(num_of_writes) AS [read/write num] ,SUM(io_stall_read_ms)*1.0/SUM(io_stall_write_ms) AS [read/write ms] FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] WHERE fs.database_id = DB_ID() GROUP BY fs.database_id --各表索引的读写情况（比例） select DB_NAME(database_id) DBName ,object_name(s.object_id) tabletName ,i.name indexName ,sum(range_scan_count+singleton_lookup_count) as [read] ,sum(leaf_insert_count+leaf_delete_count+leaf_update_count) as [write] ,sum(range_scan_count+singleton_lookup_count)/ nullif(sum(leaf_insert_count+leaf_delete_count+leaf_update_count),0) as [read/write] from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s inner join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id where objectproperty(s.object_id,'IsUserTable') = 1 group by database_id,s.object_id,i.name order by DBName,tabletName,indexName --查看数据库增长日期及时间 DECLARE @path NVARCHAR(1000) SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('', Reverse(PATH))) +'log.trc' FROM sys.traces WHERE id = 1 SELECT databasename, e.name AS eventname, cat.name AS [CategoryName], starttime, e.category_id, loginname, loginsid, spid, hostname, applicationname, servername, textdata, objectname, eventclass, eventsubclass FROM ::fn_trace_gettable(@path, 0) INNER JOIN sys.trace_events e ON eventclass = trace_event_id INNER JOIN sys.trace_categories AS cat ON e.category_id = cat.category_id WHERE e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' ) --AND databasename='' ORDER BY databasename,starttime DESC --表在各分区文件组使用情况 SELECT ps.partition_number,f.name,p.rows,ps.reserved_page_count,ps.used_page_count FROM sys.dm_db_partition_stats ps INNER JOIN sys.partitions p ON ps.partition_id = p.partition_id AND PS.index_id = P.index_id INNER JOIN sys.filegroups f ON f.data_space_id = p.partition_number WHERE p.[object_id] = OBJECT_ID('TableName') AND PS.index_id in(0,1) AND P.index_id in(0,1) --各表分区情况 select OBJECT_NAME(object_id) as tab,COUNT(partition_number) as part from sys.partitions where index_id in(0,1) and OBJECT_NAME(object_id) not like 'conflict%' and OBJECT_NAME(object_id) not like 'sys%' group by object_id order by tab --一周内数据库备份情况 SELECT user_name AS [User] ,server_name AS [Server] ,database_name AS [Database] ,recovery_model AS RecoveryModel ,case type when 'D' then '数据库' when 'I' then '差异数据库' when 'L ' then '日志' when 'F' then '文件或文件组' when 'G' then '差异文件' when 'P' then '部分' when 'Q' then '差异部分' else type end as [backupType] ,convert(numeric(10,2),backup_size/1024/1024) as [Size(M)] ,backup_start_date AS backupStartTime ,backup_finish_date as backupFinishTime ,name ,expiration_date from msdb.dbo.backupset where backup_start_date >= DATEADD(D,-7,GETDATE()) and type <> 'L' --当前数据库各表及索引分区情况（对象多较慢） SELECT OBJECT_NAME(p.object_id) AS ObjectName, i.name AS IndexName, p.index_id AS IndexID, ds.name AS PartitionScheme, p.partition_number AS PartitionNumber, fg.name AS FileGroupName, prv_left.value AS LowerBoundaryValue, prv_right.value AS UpperBoundaryValue, CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS Range, p.rows AS Rows FROM sys.partitions AS p JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id JOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_id JOIN sys.partition_functions AS pf ON pf.function_id = ps.function_id JOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_number JOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_id LEFT JOIN sys.partition_range_values AS prv_left ON ps.function_id = prv_left.function_id AND prv_left.boundary_id = p.partition_number - 1 LEFT JOIN sys.partition_range_values AS prv_right ON ps.function_id = prv_right.function_id AND prv_right.boundary_id = p.partition_number WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0 -------------------------------------------------------------- -------------------------------------------------------------- -- 下面统计对当前数据库所有表的总计读写情况 -- drop table #table_read_write create table #table_read_write( [id] int not null identity(1,1), [dtime] datetime, [read] bigint, [write] bigint ) set nocount on declare @i int = 1 while @i <= 60 --60秒 begin insert into #table_read_write([dtime],[read],[write]) select GETDATE() ,sum(range_scan_count+singleton_lookup_count) as [read] ,sum(leaf_insert_count+leaf_delete_count+leaf_update_count) as [write] from sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s where objectproperty(s.object_id,'IsUserTable') = 1 --and s.index_id in(0,1) and exists(SELECT 1 FROM sys.tables t(nolock) WHERE s.object_id=t.object_id and is_ms_shipped=0) set @i = @i + 1 waitfor delay '00:00:01' end set nocount off select * from #table_read_write -- 每秒钟读写次数及比值 select a.[dtime] ,a.[read]-b.[read] as [read/sec] ,a.write-b.write as [write/sec] ,(a.[read]-b.[read])/(a.write-b.write) as [read/write] from #table_read_write a left join #table_read_write b on a.id=b.id+1 where (a.write-b.write) > 0 order by [read/write] desc -- 平均每秒钟读写次数及比值 select avg(a.[read]-b.[read]) as [read_avg/sec] ,avg(a.write-b.write) as [write_avg/sec] ,avg((a.[read]-b.[read])/(a.write-b.write)) as [read_avg/write_avg] from #table_read_write a left join #table_read_write b on a.id=b.id+1 where (a.write-b.write) > 0 -------------------------------------------------------------- --------------------------------------------------------------