SQLServer 维护脚本分享(07)IO

时间:2022-09-06 11:44:17
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 --------------------------------------------------------------
--------------------------------------------------------------

SQLServer 维护脚本分享(07)IO的更多相关文章

  1. SQLServer 维护脚本分享(11)部分DBCC及系统存储过程

    --DBCC命令与用法 DBCC HELP('?') DBCC HELP('useroptions') DBCC USEROPTIONS WITH NO_INFOMSGS --当前DB的区及文件 DB ...

  2. SQLServer 维护脚本分享(05)内存(Memory)

    --查看设置的最大最小每次 exec sp_configure 'max server memory (MB)' exec sp_configure 'min server memory (MB)' ...

  3. SQLServer 维护脚本分享(06)CPU

    --CPU相关视图 SELECT * FROM sys.dm_os_sys_info SELECT * FROM sys.dm_exec_sessions SELECT * FROM sys.sysp ...

  4. SQLServer 维护脚本分享(08)临时数据库(tempdb)

    dbcc sqlperf(logspace) --各数据库日志大小及使用百分比 dbcc loginfo --查看当前数据库的虚拟日志文件 --临时表'Tempdb'最近使用情况 SELECT t1. ...

  5. SQLServer 维护脚本分享(09)相关文件读取

    /********************[读取跟踪文件(trc)]********************/ --查看事件类型描述 SELECT tc.name,te.trace_event_id, ...

  6. SQLServer 维护脚本分享(04)服务器角色和数据库角色相关操作

    /*------------------------------------------------------------------------------------ [服务器级别-服务器角色] ...

  7. SQLServer 维护脚本分享(10)索引

    --可添加索引的字段 migs.user_seeks,migs.avg_total_user_cost,migs.avg_user_impact,migs.last_user_seek ,mid.st ...

  8. SQL Server定时自动抓取耗时SQL并归档数据发邮件脚本分享

    SQL Server定时自动抓取耗时SQL并归档数据发邮件脚本分享 第一步建库和建表 USE [master] GO CREATE DATABASE [MonitorElapsedHighSQL] G ...

  9. SQL Server定时自动抓取耗时SQL并归档数据脚本分享

    原文:SQL Server定时自动抓取耗时SQL并归档数据脚本分享 SQL Server定时自动抓取耗时SQL并归档数据脚本分享 第一步建库 USE [master] GO CREATE DATABA ...

随机推荐

  1. Qt——消息对话框的设计

    1.消息对话框是什么 消息对话框(MessageBox)提供了一个模态对话框,用来通知用户某些信息,或者用来询问用户一个问题并获得一个答复. 先看下面2张图—— 第一张图是网易云音乐的界面截图,在删除 ...

  2. php-建造者模式(Builder)解析

    其与抽象模式相类似,都可以创建复杂的对象,但是抽象工厂更注重多个系列的产品对象,而Builder模式则着重于一步一步的构建一个复杂的对象,在最后一步才返回产品, 使用建造者模式的好处是: 1.将构造代 ...

  3. 1&period;使用Entity Framwork框架常用的技术手段Code First 和Reverse Engineer Code First

    提示:VS版本2013,  Entity Framwork版本5.0.0,Mysql数据库  使用Entity FrameWork的好处就不多说,直接上手如何使用.两种形式:1.将代码映射到数据库实体 ...

  4. 07 JavaWeb

    软件开发的两种架构:c/s和b/s          * C/S     client/server     客户端/服务器     例子:QQ     快播     暴风影音...          ...

  5. GDI&plus; 如何将图片绘制成圆形的图片

    大概意思就是不生成新的图片,而是将图片转换为圆形图片. 实现代码如下: private Image CutEllipse(Image img, Rectangle rec, Size size) { ...

  6. ECMall模板开发文档

    ECMall 模板开发文档 前 言 欢迎阅读 ECMall 模板制作教程,通过阅读本教程可快速上手 ECMall 模板的使用和制作. ECMall 模板制 作要求用户具备 XML . XHTML 和 ...

  7. python 全栈开发,Day18&lpar;对象之间的交互&comma;类命名空间与对象&comma;实例的命名空间&comma;类的组合用法&rpar;

    一.对象之间的交互 现在我们已经有一个人类了,通过给人类一些具体的属性我们就可以拿到一个实实在在的人.现在我们要再创建一个狗类,狗就不能打人了,只能咬人,所以我们给狗一个bite方法.有了狗类,我们还 ...

  8. Java内存泄露分析和解决方案及Windows自带查看工具

    Java内存泄漏是每个Java程序员都会遇到的问题,程序在本地运行一切正常,可是布署到远端就会出现内存无限制的增长,最后系统瘫痪,那么如何最快最好的检测程序的稳定性,防止系统崩盘,作者用自已的亲身经历 ...

  9. jq对象和DOM对象的互换

    var oJq;  //JQ对象 var oDom; //dom对象 oDom = oJq[index];  // JQ对象转化为oDom对象 oJq  = $(oDom);      //DOM对象 ...

  10. springboot中maven加入本地jar

    一.今天遇到一个问题,在使用springboot打jar的时候出现了本地依赖包打不进去的情况.然后在网上试了很多方式.这里做一个记录 二.加入本地依赖包 <dependency> < ...