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角色名称'