【SQL】一些或许将来能用到的sql 语句

查询blocked 的session及所占用资源

USE [master]
GO
SELECT  session_id
,blocking_session_id
,wait_time
,wait_type
,last_wait_type
,wait_resource
,transaction_isolation_level
,lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

--Also have a look at:

exec sp_who2 
exec sp_who
exec sp_lock

查询XML信息 

declare @CrmCustomerId xml = '<Root><CustId>c9ccebda-683f-e211-85be-00155db7bf1b</CustId></Root>'

SELECT CRMCustomers.[CrmCustId].value('.', 'nvarchar(50)')
FROM @CrmCustomerId.nodes('/Root/CustId') CRMCustomers([CrmCustId])

 Missing Index

SELECT S.name AS schemaName, T.name AS tableName, 
    ROW_NUMBER() OVER (PARTITION BY S.name, T.name
                        ORDER BY MIGS.avg_total_user_cost * MIGS.avg_user_impact * (MIGS.user_seeks + MIGS.user_scans) DESC
                    ) AS benefitRank,
       MIGS.user_seeks, MIGS.user_scans, MIGS.avg_total_user_cost, MIGS.avg_total_system_cost, MIGS.avg_user_impact,
       MID.equality_columns, MID.inequality_columns, MID.included_columns, S.name, T.name, T.object_id AS tableObjectId
FROM sys.dm_db_missing_index_group_stats MIGS
    INNER JOIN sys.dm_db_missing_index_groups MIG ON MIG.index_group_handle = MIGS.group_handle
    INNER JOIN sys.dm_db_missing_index_details MID ON MID.index_handle = MIG.index_handle
    INNER JOIN sys.objects T ON T.object_id = MID.object_id  
    INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
WHERE MID.database_id = DB_ID()

查询正在执行的语句的隔离级别

SELECT db_name(sp.dbid) as DBName, CASE se.transaction_isolation_level 
            WHEN 0 THEN 'Unspecified' 
            WHEN 1 THEN 'ReadUncomitted' 
            WHEN 2 THEN 'Readcomitted' 
            WHEN 3 THEN 'Repeatable' 
            WHEN 4 THEN 'Serializable' 
            WHEN 5 THEN 'Snapshot' END as Isolation_level,
            qt.text SqlText,sp.Waittime
FROM sys.dm_exec_sessions se
inner join sys.sysprocesses sp on sp.spid=se.session_id
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) as qt
where sp.dbid = db_id() 

查询被blocked 的sql以及blocker

SELECT sp.spid as Spid,
      sp.blocked as BlockerSpid,
      sp.Status,
      --sp.waittype as WaitType,
      sp.waittime as WaitTime,
      sp.waitresource as WaitResource,
      db_name(sp.dbid) as DBName,
      sp.loginame as LoginName,
      sp.login_time as LoginTime,   
      sp.last_batch as LastBatch,
      sp.hostname as HostName,
      sp.program_name as ProgramName,
      --er.start_time as StartTime,
      --DATEDIFF(SS,er.start_time,GETDATE()) as [TotalBlockedTime(s)],
      
      SUBSTRING(qt.text,sp.stmt_start / 2+1 , 
      ((CASE WHEN sp.stmt_end = -1 
       THEN (LEN(CONVERT(nvarchar(max),qt.text)) * 2) 
       ELSE sp.stmt_end END)  - sp.stmt_start) / 2+1) AS [BlockedStmt], 
         
      qt.text as [BlockedStmtText],
      
      --SUBSTRING(qt1.text,sp1.stmt_start / 2+1 , 
      --((CASE WHEN sp1.stmt_end = -1 
      -- THEN (LEN(CONVERT(nvarchar(max),qt1.text)) * 2) 
      -- ELSE sp1.stmt_end END)  - sp1.stmt_start) / 2+1) AS [BlockerStmt], 
               
      qt1.text AS [BlockerStmtText],
      sp1.stmt_start,sp1.stmt_end   ,sp1.spid
FROM sys.sysprocesses sp 
LEFT JOIN sys.sysprocesses sp1 ON sp1.spid = sp.blocked
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) as qt
CROSS APPLY sys.dm_exec_sql_text(sp1.sql_handle) as qt1
WHERE sp.blocked!=0 AND sp.spid NOT IN (@@SPID) and db_name(sp.dbid)='Test'
ORDER BY sp.spid

查询索引碎片 

SELECT a.index_id, name,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(N'POSDBNAME'),OBJECT_ID(N'dbo.RBOTRANSACTIONTABLE'),NULL,NULL, NULL) AS a 

    JOIN sys.indexesAS b ON a.object_id= b.object_idAND a.index_id = b.index_id ORDERBY avg_fragmentation_in_percent DESC;