MS SQL 常用SQL查询-CSDN博客

1.查找被block的SESSION
SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

2.SP3(SP_WHO2和DBCC INPUTBUFFER的结合，既能找到对应的被block的session id，也能找出该session执行的sql text;最大的作用则是找出当前最消耗性能的语句)

CREATE PROCEDURE [dbo].[sp_who3]

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    SPID                = er.session_id
    ,BlkBy              = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,Executions         = ec.execution_count
    ,CommandType        = er.command
    ,LastWaitType       = er.last_wait_type
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid,

qt.dbid)
    ,SQLStatement       =
        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
        )
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
    ,DatetimeSnapshot   = GETDATE()
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
OUTER APPLY
(
    SELECT
        lead_blocker = 1
    FROM master.dbo.sysprocesses sp
    WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
    AND sp.blocked = 0
    AND sp.spid = er.session_id
) lb
WHERE er.sql_handle IS NOT NULL
AND er.session_id != @@SPID
ORDER BY
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id

END

3.找出消耗tempdb最多空间的对象

SELECT TOP 10
tsu.session_id, tsu.request_id, tsu.task_alloc, tsu.task_dealloc,
erq.command, erq.database_id, DB_NAME(erq.database_id) AS [database_name],
(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(erq.[sql_handle])) AS query_text,
qp.query_plan
FROM
(SELECT session_id, request_id,
SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc,
SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS tsu
INNER JOIN sys.dm_exec_requests AS erq ON tsu.session_id = erq.session_id AND tsu.request_id =

erq.request_id
OUTER APPLY sys.dm_exec_query_plan(erq.[plan_handle]) AS qp
WHERE tsu.session_id > 50 AND database_id >= 5
ORDER BY tsu.task_alloc DESC
GO
---08/29/2014 02:55

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27799221/viewspace-1259844/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27799221/viewspace-1259844/