SQL Server 运维常用sql语句（三）-阿里云开发者社区

查询返回当前配置的内存值和当前使用的值的相关信息

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

修改内存的大小

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'max server memory (MB)', 4096; -- 设置最大内存限制为4GB
RECONFIGURE;

启用对表的压缩

EXEC sp_estimate_data_compression_savings 'Production', 'TransactionHistory', NULL, NULL, 'ROW';
ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW);
GO

启用索引压缩

SELECT name, index_id
FROM sys.indexes
WHERE OBJECT_NAME (object_id) = N'TransactionHistory';
EXEC sp_estimate_data_compression_savings
@schema_name = 'Production',
@object_name = 'TransactionHistory',
@index_id = 2,
@partition_number = NULL,
@data_compression = 'PAGE';
ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
GO

读取错误日志

EXEC sp_readerrorlog 0, 1, 'database', 'start'

限制错误日志大小

USE [master];
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

CHECKPOINT检查点

CHECKPOINT [ checkpoint_duration ]

查询是否有死锁

WITH CTE_SID ( BSID, SID, sql_handle )
AS ( SELECT blocking_session_id ,
session_id ,
sql_handle
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
UNION ALL
SELECT A.blocking_session_id ,
A.session_id ,
A.sql_handle
FROM sys.dm_exec_requests A
JOIN CTE_SID B ON A.SESSION_ID = B.BSID
)
SELECT C.BSID , C.SID , S.login_name , S.host_name , S.status ,S.cpu_time ,
S.memory_usage ,S.last_request_start_time , S.last_request_end_time ,S.logical_reads ,
S.row_count ,
q.text
FROM CTE_SID C
JOIN sys.dm_exec_sessions S ON C.sid = s.session_id
CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
ORDER BY sid

查看阻塞超时设置

SELECT @@LOCK_TIMEOUT

查看数据库的负载

SELECT substring (a.name,0,20) as [数据库名],
[连接数] = (SELECT COUNT(*) FROM master..sysprocesses b WHERE a.dbid = b.dbid), [阻塞进程] = (SELECT COUNT(*)
FROM master..sysprocesses b
WHERE a.dbid = b.dbid AND blocked <> 0),
[总内存] = ISNULL((SELECT SUM(memusage) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
[总IO] = ISNULL((SELECT SUM(physical_io) FROM master..sysprocesses b WHERE a.dbid = b.dbid),0),
[总CPU] = ISNULL((SELECT SUM(cpu) FROM master..sysprocesses b
WHERE a.dbid = b.dbid),0), [总等待时间] = ISNULL((SELECT SUM(waittime)
FROM master..sysprocesses b
WHERE a.dbid = b.dbid),0) FROM master.dbo.sysdatabases a WITH (nolock)
WHERE DatabasePropertyEx(a.name,'Status') = 'ONLINE'
ORDER BY [数据库名]

整理索引碎片

第一步：查询表的索引碎片情况
DBCC SHOWCONTIG WITH ALL_INDEXES；
第二步：删除并重建索引
第三步：使用DROP_EXISTING子句重建索引
第四步：执行DBCC DBREINDEX
第五步：执行DBCC INDEXDEFRAG
Select 'DBCC INDEXDEFRAG ('+DB_Name()+','+Object_Name(ID)+','+Cast(INDID As Varchar)+')'+Char(10)
From SysIndexes
Where ID Not IN (Select ID From SYSObjects Where xType='S')

查询索引

SELECT CASE WHEN t.[type] = 'U' THEN'表'
WHEN t.[type] = 'V' THEN '视图' END AS '类型',
SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS '(表/视图)名称',
i.[name] AS 索引名称,
SUBSTRING(column_names, 1, LEN(column_names) - 1) AS '列名',
CASE WHEN i.[type] = 1 THEN '聚集索引'
WHEN i.[type] = 2 THEN '非聚集索引'
WHEN i.[type] = 3 THEN 'XML索引'
WHEN i.[type] = 4 THEN '空间索引'
WHEN i.[type] = 5 THEN '聚簇列存储索引'
WHEN i.[type] = 6 THEN '非聚集列存储索引'
WHEN i.[type] = 7 THEN'非聚集哈希索引'
END AS '索引类型',
CASE WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS '索引是否唯一'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY
(
SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
AND index_id > 0
ORDER BY i.[name];

整理索引

select 'drop index ' + index_name + ' on ' + tab_name + ';' ,a.tab_name, a.index_Type,a.index_colum FROM (
SELECT CASE WHEN t.[type] = 'U' THEN'表'
WHEN t.[type] = 'V' THEN '视图' END AS '类型',
SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
i.[name] AS index_name,
SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
CASE WHEN i.[type] = 1 THEN '聚集索引'
WHEN i.[type] = 2 THEN '非聚集索引'
WHEN i.[type] = 3 THEN 'XML索引'
WHEN i.[type] = 4 THEN '空间索引'
WHEN i.[type] = 5 THEN '聚簇列存储索引'
WHEN i.[type] = 6 THEN '非聚集列存储索引'
WHEN i.[type] = 7 THEN'非聚集哈希索引'
END AS index_Type,
CASE WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY
(
SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
AND index_id > 0
) a
where a.index_TYpe = '非聚集索引' and a.index_only = '不唯一';
select 'create index ' + 'idx_' + tab_name + '_' + a.index_colum + ' on ' + tab_name + '(' + a.index_colum + ');',
a.tab_name, a.index_Type,a.index_colum FROM (
SELECT CASE WHEN t.[type] = 'U' THEN'表'
WHEN t.[type] = 'V' THEN '视图' END AS '类型',
SCHEMA_NAME(t.schema_id) + '.' + t.[name] AS 'tab_name',
i.[name] AS index_name,
SUBSTRING(column_names, 1, LEN(column_names) - 1) AS 'index_colum',
CASE WHEN i.[type] = 1 THEN '聚集索引'
WHEN i.[type] = 2 THEN '非聚集索引'
WHEN i.[type] = 3 THEN 'XML索引'
WHEN i.[type] = 4 THEN '空间索引'
WHEN i.[type] = 5 THEN '聚簇列存储索引'
WHEN i.[type] = 6 THEN '非聚集列存储索引'
WHEN i.[type] = 7 THEN'非聚集哈希索引'
END AS index_Type,
CASE WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS 'index_only'
FROM sys.objects t
INNER JOIN sys.indexes i
ON t.object_id = i.object_id
CROSS APPLY
(
SELECT col.[name] + ', '
FROM sys.index_columns ic
INNER JOIN sys.columns col
ON ic.object_id = col.object_id
AND ic.column_id = col.column_id
WHERE ic.object_id = t.object_id
AND ic.index_id = i.index_id
ORDER BY col.column_id
FOR XML PATH('')
) D(column_names)
WHERE t.is_ms_shipped <> 1
AND index_id > 0
) a
where a.index_TYpe = '非聚集索引' and index_only = '不唯一'

查看哪些表占用了比较大的磁盘空间

select o.name, SUM(p.reserved_page_count) as reserved_page_count,
SUM(p.used_page_count) as used_page_count,
SUM( case when(p.index_id<2) then (p.in_row_data_page_count+ p.lob_used_page_count+p.row_overflow_used_page_count) else p.lob_used_page_count+p.row_overflow_used_page_count end ) as DataPages,
SUM( case when (p.index_id<2) then row_count else 0 end ) as rowCounts
from sys.dm_db_partition_stats p
inner join sys.objects o on p.object_id=o.object_id group by o.name order by rowCounts desc

查看表的占用情况

SELECT
name '表名',
convert (char(11), row_Count) as '数据条数',
(reservedpages * 8) '已用空间(KB)',
(pages * 8) '数据占用空间(KB)',
(CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8 '索引占用空间(KB)',
(CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8 '未用空间(KB)',
LTRIM (STR (reservedpages * 8/1024/1024, 15, 0) + ' GB') as '已用空间(GB)'
from(
SELECT name,
SUM (reserved_page_count) as reservedpages ,
SUM (used_page_count) as usedpages ,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
) as pages,
SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
) as row_Count
FROM sys.dm_db_partition_stats
inner join sys.objects on sys.dm_db_partition_stats.object_id=sys.objects.object_id
where type='U'
group by sys.objects.name
union
SELECT sys.objects.name,
sum(reserved_page_count) as reservedpages,
sum(used_page_count) as usedpages,
0 as pages,
0 as row_count
from sys.objects inner join sys.internal_tables on
sys.objects.object_id = sys.internal_tables.parent_id
inner join sys.dm_db_partition_stats on sys.dm_db_partition_stats.object_id=sys.internal_tables.object_id
where sys.internal_tables.internal_type IN (202,204,211,212,213,214,215,216)
group by sys.objects.name) t
order by '已用空间(KB)' desc

查看物理读高的100条SQL

SELECT TOP 100
a.session_id,a.client_net_address,
qs.total_physical_reads,qs.execution_count,
qs.total_physical_reads /qs.execution_count as avg_io,
qt.text, db_name(qt.dbid) as dbname, qt.objectid
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) as qt
Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address client_net_address
From sys. dm_exec_requests a, sys.dm_exec_connections b
where a.session_id = b.session_id
) a on qs.sql_handle = a. sql_handle
ORDER BY qs.total_physical_reads desc

查看逻辑读高的100条SQL

SELECT TOP 100
a.session_id,a.client_net_address,
qs.total_logical_reads,qs.execution_count,
qs.total_logical_reads /qs.execution_count as avg_io,
qt.text, db_name(qt.dbid) as dbname
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address client_net_address
From sys. dm_exec_requests a, sys.dm_exec_connections b
where a.session_id = b.session_id
) a on qs.sql_handle = a. sql_handle
ORDER BY qs.total_logical_reads desc

查看CPU高的100条SQL

SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC

查看内存消耗高的SQL

SELECT a.session_id,a.client_net_address,SS.SUM_EXECUTION_COUNT,
T.TEXT,
SS.SUM_TOTAL_ELAPSED_TIME,
SS.SUM_TOTAL_WORKER_TIME,
SS.SUM_TOTAL_LOGICAL_READS,
SS.SUM_TOTAL_LOGICAL_WRITES
FROM (SELECT S.PLAN_HANDLE,
SUM(S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
SUM(S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
SUM(S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
SUM(S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
SUM(S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
FROM SYS.DM_EXEC_QUERY_STATS S
GROUP BY S.PLAN_HANDLE
) AS SS
CROSS APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
Left join (select a.session_id, a.plan_handle plan_handle,b.client_net_address client_net_address
From sys. dm_exec_requests a, sys.dm_exec_connections b
where a.session_id = b.session_id
) a on ss.plan_handle = a. plan_handle
ORDER BY SUM_TOTAL_LOGICAL_READS DESC

查找执行慢的SQL

SELECT a.session_id,a.client_net_address,
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句'
,st.text
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
Left join (select a.session_id, a.sql_handle sql_handle,b.client_net_address client_net_address
From sys. dm_exec_requests a, sys.dm_exec_connections b
where a.session_id = b.session_id
) a on qs.sql_handle = a. sql_handle
ORDER BY total_elapsed_time / execution_count DESC

查看正在执行的SQL语句

SELECT spid,
blocked,
DB_NAME(sp.dbid) AS DBName,
program_name,
waitresource,
lastwaittype,
sp.loginame,
sp.hostname,
a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE spid > 50

日志收缩注意问题

select * from sys.databases
当 log_reuse_wait_desc 为nothing装态，直接收缩无限制。为log_backup时，再作一次备份，然后进行收缩。
SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring
为active_transaction 表示有大事务，为database_mirroring 镜像状态异常，需要resume。

检查日志空间占用及不能截断原因

DBCC SQLPERF(LOGSPACE)
GO
SELECT name,recovery_model_desc,log_reuse_wait,log_reuse_wait_desc
FROM sys.databases
GO
log_reuse_wait_desc 为REPLICATION时，多为cdc相关，use Xxx; 重新开启cdc再关闭

查询是否开启CDC

SELECT IS_CDC_ENABLED
,CASE WHEN IS_CDC_ENABLED = 0
THEN 'CDC功能禁用'
ELSE 'CDC功能启用'END 描述
FROM SYS.DATABASES
WHERE NAME = 'XXXX'

开启CDC和关闭CDC

对当前数据库启用CDC
USE xxx
GO
EXECUTE sys.sp_cdc_enable_db
GO
禁用CDC
USE xxx
GO
EXECUTE sys.sp_cdc_disable_db
GO

开启表的异常捕获

exec sys.sp_cdc_enable_table @source_schema='模式名称 ',
@source_name='表名称',
@role_name= 'CDC角色名称'

SQL Server 运维常用sql语句（三）

查询返回当前配置的内存值和当前使用的值的相关信息

修改内存的大小

启用对表的压缩

启用索引压缩

读取错误日志

限制错误日志大小

CHECKPOINT检查点

查询是否有死锁

查看阻塞超时设置

查看数据库的负载

整理索引碎片

查询索引

整理索引

查看哪些表占用了比较大的磁盘空间

查看表的占用情况

查看物理读高的100条SQL

查看逻辑读高的100条SQL

查看CPU高的100条SQL

查看内存消耗高的SQL

查找执行慢的SQL

查看正在执行的SQL语句

日志收缩注意问题

检查日志空间占用及不能截断原因

查询是否开启CDC

开启CDC和关闭CDC

开启表的异常捕获

热门文章

最新文章

相关课程

相关电子书

相关实验场景