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 = 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

--------------------------------------------------------------
--------------------------------------------------------------