10-常用SQL

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

select r.session_id,r.blocking_session_id,r.wait_type,r.wait_time,r.wait_resource,
r.start_time,r.status,r.command,db_name(r.database_id) as dbname,r.cpu_time ,r.total_elapsed_time,qt.text as Tsql,
r.reads,r.logical_reads,r.writes,
s.login_time,s.login_name,s.host_name,s.program_name,s.client_interface_name,
s.last_request_start_time,s.last_request_end_time,
c.client_net_address,c.local_net_address,c.connect_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  --and r.session_id <> @@SPID
order by r.cpu_time desc

2、查看有索引的表有多少条数据需要更新统计信息

select
ss.name AS SchemaName
,st.name AS TableName
,si.name AS IndexName
,ssi.rowcnt
,ssi.rowmodctr
FROM sys.indexes si
INNER JOIN sys.sysindexes ssi ON si.object_id = ssi.id
AND si.name = ssi.name
INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE st.is_ms_shipped = 0
AND si.index_id != 0 --0代表的是堆表
AND ssi.rowcnt > 10

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

select 
    s.session_id,s.login_time,s.login_name,s.host_name,s.program_name,s.client_interface_name,s.status,s.cpu_time,
    s.last_request_start_time,s.last_request_end_time,
    c.client_net_address,c.local_net_address,c.connect_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

4、查看执行的命令的进度（百分比）

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

5、查看数据库对文件的IO操作

SELECT 
DB_NAME(fs.database_id),
fs.num_of_reads,  --对文件的读取次数
fs.num_of_writes, --对文件的写入次数
fs.io_stall_write_ms, --用户等待在该文件中完成写入所用的总时间（毫秒）
fs.io_stall,        --用户等待在该文件中完成I/O操作所用的总时间（毫秒）
fs.io_stall_queued_read_ms,  --针对读的IO资源调控所引起的总IO延迟
fs.io_stall_queued_write_ms  --针对写的IO资源调控所引起的总的IO延迟
FROM sys.dm_io_virtual_file_stats(NULL,NULL)  fs

6、显示阻塞信息，锁

SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;

7、查询正在等待的等待事件

select * from sys.dm_os_waiting_tasks  where session_id>50

select * from sys.dm_os_waiting_tasks  （查看所有的等待任务）

select * from sys.dm_os_wait_stats  （查看正在等待的事件）

select * from sys.dm_exec_session_wait_stats  where session_id=56（SQL2016以上能用）

8、查看死锁信息

select * from sys.dm_os_performance_counters p
where  RTRIM(p.counter_name) = 'Number of Deadlocks/sec'

9、IO检查

--读检测
SELECT DISTINCT
      '慢速磁盘' AS name,
       '磁盘:' + UPPER(LEFT(mf.physical_name, 1)) + ' 读非常慢(响应时间大于100毫秒)' AS Details
FROM  sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf 
ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
WHERE ( io_stall_read_ms / ( 1.0 + num_of_reads ) ) > 100;      --num_of_reads加1是怕遇到分母为0的情况

--写检测
 SELECT DISTINCT
      '慢速磁盘' AS name,
       '磁盘:' + UPPER(LEFT(mf.physical_name, 1)) + ' 写非常慢(响应时间大于50毫秒)' AS Details
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
WHERE   ( io_stall_write_ms / ( 1.0 + num_of_writes ) ) > 50;

10、查看每个文件的IO等待时间

SELECT 
    DB_NAME(database_id) AS [Database Name] , 
    file_id , 
    io_stall_read_ms as '等待读取所用的总时间', 
    num_of_reads   as '读取次数',
     CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] ,--- '平均每次读等待时间'
     io_stall_write_ms  as '等待完成写入所用的总时间', 
     num_of_writes as '写入次数' ,
    CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] ,  --- '平均每次写等待时间'
    io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,   --总的IO等待时间
    num_of_reads + num_of_writes AS [total_io] ,    --总的io次数
    CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]  ---平均io等待时间
FROM sys.dm_io_virtual_file_stats(NULL, NULL) ORDER BY avg_io_stall_ms DESC ;

11、查看平均读写延迟

SELECT
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] =   --平均每次读的字节
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =   --平均每次写的字节
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

12、查看表的大小

select 
    t.name as tableName,
    s.name as schemaName,
    p.rows as rowCounts,
    sum(a.total_pages) * 8 as totalSpaceKB,
    cast(round(((sum(a.total_pages) * 8) / 1024.00),2) as numeric(36,2)) as totalSpaceMB,
    SUM(a.used_pages) * 8 as usedSpaceKB,
    cast(round(((sum(a.used_pages) * 8) / 1024.00),2) as numeric(36,2)) as usedSpaceMB,
    (sum(a.total_pages) - sum(a.used_pages)) * 8 as unusedSpaceKB,
    cast(round(((sum(a.total_pages) - sum(a.used_pages)) * 8) / 1024.00, 2) as numeric(36,2)) as unusedSpaceMB
from sys.tables t
inner join 
   sys.indexes i on t.OBJECT_ID = i.OBJECT_ID
inner join
   sys.partitions p on i.object_id = p.object_id and i.index_id =p.index_id
inner join 
   sys.allocation_units a on p.partition_id = a.container_id
left outer join
   sys.schemas s on t.schema_id = s .schema_id
where 
   t.is_ms_shipped = 0
   and i.object_id > 255
group by 
   t.name,s.name,p.rows
order by rowCounts desc

13、查询存储过程执行到哪个SQL了

SELECT     [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),
 [User] = nt_username, [Status] = er.status, 
 [Wait] = wait_type, 
 [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) 
                      * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),
                       [Parent Query] = qt.text, 
                       Program = program_name, Hostname, 
                       nt_domain, start_time
FROM  sys.dm_exec_requests er INNER JOIN  sys.sysprocesses sp ON er.session_id = sp.spid 
     CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE     session_Id > 50 /* Ignore system spids.*/ AND session_Id NOT IN (@@SPID) 
--and DB_NAME(sp.dbid)= 'NAV' 
and qt.text like '%存储过程名字%'

14、Ad Hoc Distributed Queries组件

1.开启Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
2.关闭Ad Hoc Distributed Queries组件，在sql查询编辑器中执行如下语句：

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

15、查看tempdb的文件使用情况

use tempdb
SELECT name AS '文件名称',round(size*1.0/128,2) AS '总大小(MB)',
round(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0,2) AS '可用空间（MB）',
convert(varchar(10),(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 ) /(size*1.0/128)*100) + ' %' as '剩余百分比'
FROM sys.database_files  
where type_desc='ROWS' and  ((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 ) /(size*1.0/128)*100)<30

16、查看AlwaysOn的延迟情况

select GETDATE() as '时间',DB_NAME(database_id) as '数据库名称', redo_queue_size / redo_rate as '延迟时间'     
from sys.dm_hadr_database_replica_states 
where is_primary_replica=0  
and redo_queue_size / redo_rate >0

17、查看表级别的权限

--1、查看表级别资源的权限
select c.name as 用户名,b.name as 对象名,
CASE b.type
WHEN 'U' THEN 'Table'
WHEN 'P' THEN 'Procedure'
WHEN 'FN' THEN 'Scalar function'
WHEN 'TF' THEN 'Table function'
ELSE 'OTHER'
END AS 对象类型,
CASE WHEN a.ACTION = 26 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'REFERENCES',
CASE WHEN a.ACTION = 193 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'SELECT',
CASE WHEN a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'INSERT',
CASE WHEN a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'UPDATE',
CASE WHEN a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'DELETE',
CASE WHEN a.ACTION = 224 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'EXECUTE',
CASE a.PROTECTTYPE
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
ELSE 'OTHER'
END AS PROTECTTYPE
from sysprotects a 
inner join sysobjects b on a.id = b.id
inner join sysusers c on a.uid = c.uid


-- 2、查看建表权限等隐藏权限
select
'HKTRADE_TEST' as DBName,
c.name as 用户名,
CASE WHEN a.ACTION = 178 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'CREATE FUNCTION',
CASE WHEN a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'INSERT',
CASE WHEN a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'DELETE',
CASE WHEN a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'UPDATE',
CASE WHEN a.ACTION = 198 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'CREATE TABLE',
CASE WHEN a.ACTION = 203 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'CREATE DATABASE',
CASE WHEN a.ACTION = 207 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'CREATE VIEW',
CASE WHEN a.ACTION = 222 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'CREATE PROCEDURE',
CASE WHEN a.ACTION = 228 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'BACKUP DATABASE',
CASE WHEN a.ACTION = 233 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'CREATE DEFAULT',
CASE WHEN a.ACTION = 235 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'BACKUP LOG',
CASE WHEN a.ACTION = 236 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'CREATE RULE',
CASE a.PROTECTTYPE
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
ELSE 'OTHER'
END AS PROTECTTYPE
from sysprotects a 
inner join sysusers c on a.uid = c.uid
where  a.action  in  (178,195,196,197,198,203,207,222,228,233,235,236)

--3、查询实例下的所有库
declare @sql nvarchar(max)
declare @DBname nvarchar(max)
declare  @t1 table(DBName varchar(50),UserName varchar(50),[CREATE FUNCTION] varchar(50),[INSERT] varchar(50),
[DELETE]  varchar(50),[UPDATE]  varchar(50),[CREATE TABLE]  varchar(50),[CREATE DATABASE]  varchar(50),[CREATE VIEW]  varchar(50),
[CREATE PROCEDUR]  varchar(50),[BACKUP DATABASE]  varchar(50),[CREATE DEFAULT]  varchar(50),[BACKUP LOG]  varchar(50),[CREATE RULE]  varchar(50),[PROTECTTYPE]  varchar(50))

declare cur CURSOR for
select  name  from  sys.databases  where  name  not  in  ('master','tempdb','model','msdb')  and  name not  like  '%202%' -- and  name='HKTRADE_TEST'
open cur
fetch next from cur into @DBname
while @@FETCH_STATUS = 0
begin
    set @sql =''
    set @sql=@sql+ 'USE '+QUOTENAME(@DBName)+';'
    set @sql = @sql++CHAR(13)+CHAR(10)+
    'select '''+@DBName+''' as DBName,
        c.name as 用户名,
        CASE WHEN a.ACTION = 178 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''CREATE FUNCTION'',
        CASE WHEN a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''INSERT'',
        CASE WHEN a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''DELETE'',
        CASE WHEN a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''UPDATE'',
        CASE WHEN a.ACTION = 198 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''CREATE TABLE'',
        CASE WHEN a.ACTION = 203 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''CREATE DATABASE'',
        CASE WHEN a.ACTION = 207 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''CREATE VIEW'',
        CASE WHEN a.ACTION = 222 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''CREATE PROCEDURE'',
        CASE WHEN a.ACTION = 228 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''BACKUP DATABASE'',
        CASE WHEN a.ACTION = 233 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''CREATE DEFAULT'',
        CASE WHEN a.ACTION = 235 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''BACKUP LOG'',
        CASE WHEN a.ACTION = 236 AND a.PROTECTTYPE = 205 THEN ''√'' ELSE '''' END AS ''CREATE RULE'',
        CASE a.PROTECTTYPE
        WHEN 204 THEN ''GRANT_W_GRANT''
        WHEN 205 THEN ''GRANT''
        WHEN 206 THEN ''DENY''
        ELSE ''OTHER''
        END AS PROTECTTYPE
    from sysprotects a 
    inner join sysusers c on a.uid = c.uid
    where  a.action  in  (178,195,196,197,198,203,207,222,228,233,235,236)
    '
    insert into @t1 exec sp_executesql @sql
    --print(@sql)
    fetch next from cur into @DBname
end
close cur
deallocate cur
select   *  from  @t1

18、查看数据库大小

--1.查看数据库的大小
exec sp_spaceused 

--2、 查看session是否还开启事物
select  session_id,status,open_transaction_count, *  from   sys.dm_exec_sessions where  session_id=431

--3、查看日志的使用情况
dbcc SQLPERF(LOGSPACE)

--4、清空动态管理视图的内容
dbcc SQLPERF('sys.dm_os_wait_stats','CLEAR')