sql server 常用运维SQL

12.定位SQL查询SQL语句实行时间和IO斲丧
SELECT s2.dbid,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count, plan_generation_num, last_execution_time,
total_worker_time, last_worker_time, min_worker_time,
max_worker_time, total_physical_reads, last_physical_reads,
min_physical_reads, max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes, max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.total_worker_time desc
13.各个数据库CPU斲丧情况
WITH DB_CPU_Stats
AS
(SELECT pa.DatabaseID, DB_Name(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N’dbid’) AS pa
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
FROM DB_CPU_Stats
WHERE DatabaseID <> 32767 – ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);
14.查询CPU占用最高的SQL语句
SELECT TOP 10
total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC
15.检察当前占用内存
IF CAST(SUBSTRING(@@VERSION,22,4) AS INT)<2008
select cast(cntr_value/(1024.0) as DECIMAL(12,1)) AS SQLServerMemoryMB
from master.sys.sysperfinfo AS s
where counter_name=‘Total Server Memory (KB)’
ELSE
SELECT CAST(physical_memory_in_use_kb/1024.0 AS DECIMAL(12,1)) AS SQLServerMemoryMB
FROM sys.dm_os_process_memory
16.检察sqlserver有效期
RECONFIGURE
GO
DECLARE @daysleft int
DECLARE @instancename sysname
SELECT @instancename = CONVERT(sysname, SERVERPROPERTY(‘instancename’))
EXEC @daysleft = xp_qv ‘2715127595’, @instancename
SELECT @daysleft ‘剩余使用天数’
GO
–例子：
RECONFIGURE
GO
DECLARE @daysleft int
DECLARE @instancename sysname
SELECT @instancename = CONVERT(sysname, SERVERPROPERTY(‘MSSQLSERVER’))
EXEC @daysleft = xp_qv ‘2715127595’, @instancename
SELECT @daysleft ‘剩余使用天数’
GO
剩余天数为0表现还未逾期

  

• 查抄数据库启动时间
  select convert(varchar(30),login_time,120) from master…sysprocesses where spid=1
  

sqlserver检察实例级别的信息，使用SERVERPROPERTY函数
select SERVERPROPERTY (‘propertyname’)
检察实例级别的某个参数XX的设置
select * from sys.configurations where name=‘XX’
更改实例级别的某个参数XX的值
sp_configure ‘XX’,‘0’
RECONFIGURE WITH OVERRIDE
sp_configure表现或更改当前服务器的全局设置设置。
RECONFIGURE表现SQL Server不消重新启动就立刻收效
使用sp_configure更改设置时，请使用RECONFIGURE语句使更改立刻收效，否则更改将在SQL Server重新启动后收效。RECONFIGURE背面加WITH OVERRIDE表现不管这个值是不是符合要求都会收效，好比recovery interval的范围值是10–60对应sys.configurations.minimum是10、sys.configurations.maximum是60，如果sp_configure ‘recovery interval’, 75设置为75，凌驾了这个10–60规范，但是要让75收效，则必须加上WITH OVERRIDE
sqlserver没有体系表可以查询全部数据库下面对象
如下只能在当前数据库下面查
select * from sys.all_objects --查询当前数据库的全部架构范围的对象
select * from sys.sysobjects --查询当前数据库的全部对象
–sys.all_objects、sys.sysobjects这种的视图，在每个数据库的体系视图下面都有
select * from sys.databases --在当前数据库下可以查询到全部数据库信息，包罗是否on状态
select * from sys.sysdatabases --在当前数据库下可以查询到全部数据库信息，不包罗是否on状态，这个体系视图会在后续的版本中删除
–sys.databases、sys.sysdatabases这种的视图，在每个数据库的体系视图下面都有
sys.processes --没有这个视图
select * from sys.sysprocesses --在当前数据库下可以查询全部正在SQL Server 实例上运行的历程的相干信息，也就是全部数据库上的线程，这个体系视图会在后续的版本中删除
全局体系视图、单个数据库体系视图
sys.database_files --每个存储在数据库本身中的数据库文件在表中占用一行。 这是一个基于每个数据库的视图。
sys.master_files --master 数据库中的每个文件对应一行。 这是一个体系范围视图。
–sys.da