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 --每个存