SQLServer運(yùn)維實(shí)用的幾個腳本
當(dāng)前位置：點(diǎn)晴教程→知識管理交流
→『 技術(shù)文檔交流 』
|
● sys.dm_exec_query_stats AS s1：SQL Server中的系統(tǒng)視圖，提供了關(guān)于查詢統(tǒng)計(jì)信息，例如CPU時(shí)間、讀取次數(shù)和寫入次數(shù)等。
查詢指定數(shù)據(jù)庫的記錄數(shù)、總工占用空間KB、總共占用空間MB、已使用空間KB、已使用空間MB、未使用空間KB、未使用空間MB，這對于分析磁盤占用非常有用。
400 186 1886
SELECT
t.NAME AS 表名,
s.Name AS 架構(gòu),
p.rows AS 記錄數(shù),
SUM ( a.total_pages ) * 8 AS 總工占用空間KB,
CAST ( ROUND( ( ( SUM ( a.total_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 總共占用空間MB,
SUM ( a.used_pages ) * 8 AS 已使用空間KB,
CAST ( ROUND( ( ( SUM ( a.used_pages ) * 8 ) / 1024.00 ), 2 ) AS NUMERIC ( 36, 2 ) ) AS 已使用空間MB,
( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 AS 未使用空間KB,
CAST (
ROUND( ( ( SUM ( a.total_pages ) - SUM ( a.used_pages ) ) * 8 ) / 1024.00, 2 ) AS NUMERIC ( 36, 2 )
) AS 未使用空間MB
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.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 0
GROUP BY t.Name,s.Name,p.Rows
ORDER BY 總共占用空間MB DESC;
該語句適合排查生產(chǎn)環(huán)境實(shí)時(shí)SQL語句慢的情況。
SELECT TOP 10
r.session_id,
r.request_id,
r.start_time AS '開始時(shí)間',
r.status AS '狀態(tài)',
r.command AS '命令',
t.text AS 'sql語句',
DB_NAME(r.database_id) AS '數(shù)據(jù)庫名',
r.blocking_session_id AS '正在阻塞其他會話的會話ID',
w.wait_type AS '等待資源類型',
r.wait_time AS '等待時(shí)間',
r.wait_resource AS '等待的資源',
w.waiting_tasks_count AS '當(dāng)前正在進(jìn)行等待的任務(wù)數(shù)',
r.reads AS '物理讀次數(shù)',
r.writes AS '寫次數(shù)',
r.logical_reads AS '邏輯讀次數(shù)',
r.row_count AS '返回結(jié)果行數(shù)'
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
LEFT JOIN sys.dm_os_wait_stats AS w ON r.wait_type = w.wait_type
WHERE r.session_id > 50 -- 排除系統(tǒng)進(jìn)程
ORDER BY r.cpu_time DESC;
-- 第一步查詢被鎖表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT';
--參數(shù)說明 spid 鎖表進(jìn)程 ；tableName 被鎖表名
-- 第二步解鎖語句 需要拿到spid然后殺掉縮表進(jìn)程
declare @spid int
Set @spid = 57 --鎖表進(jìn)程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)
USE master
ALTER DATABASE DB SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY SIMPLE --調(diào)整為簡單模式
USE DB
DBCC SHRINKFILE (N'DB_log' , 2, TRUNCATEONLY) --設(shè)置壓縮后的日志大小為2M，可以自行指定
USE master
ALTER DATABASE DB SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE DB SET RECOVERY FULL --還原為完全模式
