SQLServer 维护脚本分享（07）IO

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 = 00 - 最少信息(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_descfrom 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 AvgWaitingfrom 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_statsWHERE 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) sinner join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_idwhere objectproperty(s.object_id,'IsUserTable') = 1group by database_id,s.object_id,i.nameorder by DBName,tabletName,indexName--查看数据库增长日期及时间DECLARE @path NVARCHAR(1000)SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +'\log.trc'FROM   sys.tracesWHERE  id = 1SELECT databasename,       e.name   AS eventname,       cat.name AS [CategoryName],       starttime,       e.category_id,       loginname,       loginsid,       spid,       hostname,       applicationname,       servername,       textdata,       objectname,       eventclass,       eventsubclassFROM   ::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_idWHERE  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_countFROM sys.dm_db_partition_stats ps INNER JOIN sys.partitions pON ps.partition_id = p.partition_id AND PS.index_id = P.index_idINNER JOIN sys.filegroups f ON f.data_space_id = p.partition_numberWHERE 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 RowsFROM sys.partitions AS p JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_idJOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_idJOIN sys.partition_schemes AS ps ON ps.data_space_id = ds.data_space_idJOIN sys.partition_functions AS pf  ON pf.function_id = ps.function_idJOIN sys.destination_data_spaces AS dds2 ON dds2.partition_scheme_id = ps.data_space_id AND dds2.destination_id = p.partition_numberJOIN sys.filegroups AS fg ON fg.data_space_id = dds2.data_space_idLEFT JOIN sys.partition_range_values AS prv_left  ON ps.function_id = prv_left.function_id  AND prv_left.boundary_id = p.partition_number - 1LEFT 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_writecreate table #table_read_write([id] int not null identity(1,1),[dtime] datetime,[read] bigint,[write] bigint)set nocount ondeclare @i int = 1while @i <= 60 --60秒begininsert 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) swhere 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 + 1waitfor delay '00:00:01'endset nocount offselect * 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+1where (a.write-b.write) > 0order 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+1where (a.write-b.write) > 0----------------------------------------------------------------------------------------------------------------------------