23-系统函数的使用

1.给字符串添加中括号[]

select QUOTENAME('aa')

2.根据对象名称查询对应的object_id

select object_id('dbo.T3')

3、根据对应的object_id查询对应的object_name

select object_name('1525580473')

 

4、查看某个session的详细信息，包括IP

select s.session_id,s.login_time,s.login_name,s.host_name,c.client_net_address,c.local_net_address,s.program_name,s.client_interface_name,
s.status,s.cpu_time,c.connect_time ,s.last_request_start_time,s.last_request_end_time
from sys.dm_exec_sessions as s
inner join sys.dm_exec_connections as c
on s.session_id = c.session_id
where s.session_id=61

 

 5、查看当前正在执行的请求

select r.session_id,r.start_time,r.status,r.command,db_name(r.database_id) as dbname,r.cpu_time ,qt.text as Tsql
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt

 

  6、查看当前正在执行的请求的session的详细信息

select r.session_id,r.start_time,r.status,r.command,db_name(r.database_id) as dbname,r.cpu_time ,qt.text as Tsql,
s.session_id,s.login_time,s.login_name,s.host_name,c.client_net_address,c.local_net_address,s.program_name,s.client_interface_name,
s.status,s.cpu_time,c.connect_time ,s.last_request_start_time,s.last_request_end_time
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt
inner join sys.dm_exec_sessions as s on s.session_id = r.session_id
inner join sys.dm_exec_connections as c on s.session_id = c.session_id
where r.session_id >50
order by r.cpu_time desc

7、查看正在执行的SQL的执行计划

select r.session_id,r.start_time,r.status,r.command,db_name(r.database_id) as dbname,r.cpu_time ,qt.text as Tsql,qp.query_plan
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) qt
cross apply sys.dm_exec_query_plan(r.plan_handle) qp
where session_id > 50
order by cpu_time desc