31-SQLServer运维常用脚本

1、查看数据库的大小
命令：exec sp_spaceused

2、查看日志的使用情况

命令：dbcc SQLPERF(LOGSPACE)

3、查看某个session的SQL脚本

命令：dbcc inputbuffer(session_id)

4、查看表有多少条数据需要更新统计信息（一般指有索引的表）
命令：

select
ss.name AS SchemaName
,st.name AS TableName
,si.name AS IndexName
,ssi.rowcnt
,ssi.rowmodctr
FROM sys.indexes si
INNER JOIN sys.sysindexes ssi ON si.object_id = ssi.id
AND si.name = ssi.name
INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE st.is_ms_shipped = 0
AND si.index_id != 0 --0代表的是堆表
AND ssi.rowcnt > 10

注：rowcnt：表的总行数或者索引的总行数

       rowmodctr：未更新统计信息的行数

5、查看某个session的详细信息，包括IP
命令：

select 
s.session_id,s.login_time,s.login_name,s.host_name,s.program_name,s.client_interface_name,s.status,s.cpu_time,
s.last_request_start_time,s.last_request_end_time,
c.client_net_address,c.local_net_address,c.connect_time 
from sys.dm_exec_sessions as s
inner join sys.dm_exec_connections as c
on s.session_id = c.session_id
where s.session_id=61

6、查看当前正在执行的请求的session的详细信息

命令：

select r.session_id,r.blocking_session_id,r.wait_type,r.wait_time,r.wait_resource,
r.start_time,r.status,r.command,db_name(r.database_id) as dbname,r.cpu_time ,r.total_elapsed_time,qt.text as Tsql,
r.reads,r.logical_reads,r.writes,
s.login_time,s.login_name,s.host_name,s.program_name,s.client_interface_name,
s.last_request_start_time,s.last_request_end_time,
c.client_net_address,c.local_net_address,c.connect_time 
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt
inner join sys.dm_exec_sessions as s on s.session_id = r.session_id
inner join sys.dm_exec_connections as c on s.session_id = c.session_id
where r.session_id >50 --and r.session_id <> @@SPID
order by r.cpu_time desc

7、查看正在执行的SQL的执行计划

命令：

select r.session_id,r.start_time,r.status,r.command,db_name(r.database_id) as dbname,r.cpu_time ,qt.text as Tsql,qp.query_plan
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt
cross apply sys.dm_exec_query_plan(r.plan_handle) qp
where session_id > 50
order by cpu_time desc

8、查看执行的命令的进度（百分比）

命令：

select r.start_time,r.status,r.command,db_name(r.database_id) as dbname,r.cpu_time,r.percent_complete,qt.text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt

注：不是所有的命令都能看到进度，比如备份、回滚是可以看到的。

9、显示阻塞信息，锁
命令：

SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;

10、IO读响应时间

命令：

SET NOCOUNT ON
select 
distinct
UPPER(LEFT(mf.physical_name,1)) as '盘符 ',
DB_NAME(fs.database_id) as '数据库名称',
mf.name as '文件名称',
fs.num_of_reads as '读取的总次数',
convert(dec(18,2),(fs.io_stall_read_ms / (1.0 + num_of_reads))) as '平均每次读的响应时间(ms)'
from sys.dm_io_virtual_file_stats(NULL,NULL) as fs
inner join sys.master_files mf on fs.database_id = mf.database_id and fs.file_id = mf.file_id
where (fs.io_stall_read_ms / (1.0 + num_of_reads)) > 100

go

11、IO写响应时间

命令：

SET NOCOUNT ON
select 
distinct
UPPER(LEFT(mf.physical_name,1)) as '盘符 ',
DB_NAME(fs.database_id) as '数据库名称',
mf.name as '文件名称',
fs.num_of_writes as '写入的总次数',
convert(dec(18,2),(fs.io_stall_write_ms / (1.0 + num_of_writes))) as '平均每次写的响应时间(ms)'
from sys.dm_io_virtual_file_stats(NULL,NULL) as fs
inner join sys.master_files mf on fs.database_id = mf.database_id and fs.file_id = mf.file_id
where (fs.io_stall_write_ms / (1.0 + num_of_writes)) > 50

go

12、查看数据库对文件的IO操作

命令：

SELECT 
DB_NAME(fs.database_id),
fs.num_of_reads, --对文件的读取次数
fs.num_of_writes, --对文件的写入次数
fs.io_stall_write_ms, --用户等待在该文件中完成写入所用的总时间（毫秒）
fs.io_stall, --用户等待在该文件中完成I/O操作所用的总时间（毫秒）
fs.io_stall_queued_read_ms, --针对读的IO资源调控所引起的总IO延迟
fs.io_stall_queued_write_ms --针对写的IO资源调控所引起的总的IO延迟
FROM sys.dm_io_virtual_file_stats(NULL,NULL) fs

13、每个挂起的IO返回一行

命令：

SELECT 
DB_NAME(mf.database_id) AS [Database] ,
mf.physical_name ,
r.io_pending , 
r.io_pending_ms_ticks , 
r.io_type as '挂起的IO类型', 
fs.num_of_reads , 
fs.num_of_writes
FROM sys.dm_io_pending_io_requests AS r 
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs 
ON r.io_handle = fs.file_handle 
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id 
ORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;

14、查看每个文件的IO等待时间

命令：

SELECT 
DB_NAME(database_id) AS [Database Name] , 
file_id , 
io_stall_read_ms as '等待读取所用的总时间', 
num_of_reads as '读取次数',
CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] ,--- '平均每次读等待时间'
io_stall_write_ms as '等待完成写入所用的总时间', 
num_of_writes as '写入次数' ,
CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , --- '平均每次写等待时间'
io_stall_read_ms + io_stall_write_ms AS [io_stalls] , --总的IO等待时间
num_of_reads + num_of_writes AS [total_io] , --总的io次数
CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
+ num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] ---平均io等待时间
FROM sys.dm_io_virtual_file_stats(NULL, NULL) ORDER BY avg_io_stall_ms DESC ;

15、查看平均读写延迟

命令：

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;
GO

16、查看磁盘的大小和使用率

命令：

SET NOCOUNT ON
IF @@VERSION LIKE 'Microsoft SQL Server 2012%'
OR @@VERSION LIKE 'Microsoft SQL Server 2014%' 
OR @@VERSION LIKE 'Microsoft SQL Server 2016%' 
OR @@VERSION LIKE 'Microsoft SQL Server 2017%'
OR @@VERSION LIKE 'Microsoft SQL Server 2019%' 
BEGIN 
SELECT DISTINCT 
LEFT(vs.volume_mount_point,1) as '盘符',
convert(varchar(30),CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0))+'GB ' as ' 总大小',
convert(varchar(30),CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0)) +'GB' as '可用大小',
convert(varchar(30),CAST((CAST(vs.total_bytes AS FLOAT) - CAST(vs.available_bytes AS FLOAT))/CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2))*100)+'%' as '磁盘使用率'
FROM sys.master_files AS f WITH (NOLOCK) 
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs order by LEFT(vs.volume_mount_point,1) OPTION (RECOMPILE)
END
ELSE IF @@VERSION LIKE '%2005%' OR @@VERSION LIKE '%2008%'
BEGIN 
create table #server_disk_usage (
disk_num nvarchar(10),
total_size_mb nvarchar(100) DEFAULT (''),
free_siez_mb nvarchar(100)DEFAULT ('')
)
create table #tempDisks( id int IDENTITY(1,1),DiskSpace nvarchar(100))

--1.收集磁盘剩余空间信息到临时表中
insert into #server_disk_usage(disk_num,free_siez_mb) exec xp_fixeddrives

--2.收集磁盘总空间信息
--==========================================
--开启CMDShell
EXEC sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell',1;

RECONFIGURE WITH OVERRIDE

--========================================

--将需要检查的磁盘放入临时表#checkDisks
SELECT 
ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,
[disk_num]
INTO #checkDisks
FROM #server_disk_usage
WHERE [total_size_mb] = '';


--循环临时表#checkDisks检查每个磁盘的总量
DECLARE @disk_num NVARCHAR(20)
DECLARE @total_size_mb INT
DECLARE @sql NVARCHAR(200)
DECLARE @max INT
DECLARE @min INT
SELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisks

WHILE(@min<=@max)
BEGIN
SELECT @disk_num=disk_num 
FROM #checkDisks WHERE RID=@min

SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '+@disk_num+':'+''''

INSERT INTO #tempDisks
EXEC sys.sp_executesql @sql

SELECT @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)
-CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024
FROM #tempDisks WHERE id = 2


UPDATE #server_disk_usage
SET total_size_mb=@total_size_mb
WHERE disk_num=@disk_num

--SELECT * FROM #tempDisks

TRUNCATE TABLE #tempDisks

SET @min=@min+1

END

--禁用CMDShell
EXEC sp_configure 'xp_cmdshell',0;

EXEC sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;


--3.查询最后的结果
select 
sdu.disk_num as '盘符',
convert(varchar(20),cast(convert(bigint,total_size_mb)/1024.0 as NUMERIC(18,2))) + 'GB' as '磁盘总大小',
convert(varchar(20),cast(convert(bigint,free_siez_mb)/1024.0 as NUMERIC(18,2))) + 'GB' as '磁盘可用空间',
convert(varchar(20),convert(dec(18,2),(convert(bigint,total_size_mb) - convert(bigint,free_siez_mb))*100 / convert(bigint,total_size_mb)))+'%' as '磁盘使用率'
from #server_disk_usage sdu

drop table #server_disk_usage
drop table #tempDisks
drop table #checkDisks
END

go

17、查看表的大小

命令：

select 
t.name as tableName,
s.name as schemaName,
p.rows as rowCounts,
sum(a.total_pages) * 8 as totalSpaceKB,
cast(round(((sum(a.total_pages) * 8) / 1024.00),2) as numeric(36,2)) as totalSpaceMB,
SUM(a.used_pages) * 8 as usedSpaceKB,
cast(round(((sum(a.used_pages) * 8) / 1024.00),2) as numeric(36,2)) as usedSpaceMB,
(sum(a.total_pages) - sum(a.used_pages)) * 8 as unusedSpaceKB,
cast(round(((sum(a.total_pages) - sum(a.used_pages)) * 8) / 1024.00, 2) as numeric(36,2)) as unusedSpaceMB
from sys.tables t
inner join 
sys.indexes i on t.OBJECT_ID = i.OBJECT_ID
inner join
sys.partitions p on i.object_id = p.object_id and i.index_id =p.index_id
inner join 
sys.allocation_units a on p.partition_id = a.container_id
left outer join
sys.schemas s on t.schema_id = s .schema_id
where 
t.is_ms_shipped = 0
and i.object_id > 255
group by 
t.name,s.name,p.rows
order by rowCounts desc

18、查看死锁情况

命令：

SET NOCOUNT ON
SELECT 
CAST(p.cntr_value AS NVARCHAR(100)) as '死锁总共发生次数',
convert(varchar(10),CONVERT(decimal(18,2),( 1.0 * p.cntr_value / (DATEDIFF(DD, d.create_date, CURRENT_TIMESTAMP)+1) ))) as '平均每天发生次数'
FROM sys.dm_os_performance_counters p
INNER JOIN sys.databases d ON d.name = 'tempdb'
WHERE RTRIM(p.counter_name) = 'Number of Deadlocks/sec' --锁里的死锁
AND RTRIM(p.instance_name) = '_Total' --所有的锁类型
AND p.cntr_value > 0

go

 

 

 

***************************************************

如下是个人开发系统，欢迎大家体验，纯属个人爱好，想一块玩的，私信。

易本浪账：www.jialany.com  

***************************************************