database.md

database.md

ms sql server

blocking queries

SELECT TOP 10 r.session_id, r.plan_handle,      r.sql_handle, r.request_id,      r.start_time, r.status,      r.command, r.database_id,      
r.user_id, r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, r.transaction_isolation_level,      
r.row_count, st.text  
FROM sys.dm_exec_requests r  
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st  
    WHERE r.blocking_session_id = 0       and r.session_id 
        in       (SELECT distinct(blocking_session_id) FROM sys.dm_exec_requests)  
GROUP BY r.session_id, r.plan_handle,      r.sql_handle, r.request_id,      r.start_time, r.status,      r.command, r.database_id,      
r.user_id, r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, 
r.transaction_isolation_level,      r.row_count, st.text  
ORDER BY r.total_elapsed_time desc

finding the query hash and plan

SELECT qsq.query_id,qsq.query_hash,qsqt.query_sql_text,qsp.plan_id,qsp.query_plan_hash,qsp.query_plan 
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query qsq ON  qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
-- WHERE qsq.query_hash = 0XACF854B6470CA064

forcing a plan on a query

EXEC sp_query_store_force_plan @query_id = 156960, @plan_id = 95954;

finding locks and sessions

 SELECT  L.request_session_id AS SPID, ES.last_request_end_time ,
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName,
        P.object_id AS LockedObjectId,
        L.resource_type AS LockedResource,
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

finding constraints

select t.name [table], c.name [column], d.name [constraint]
from sys.default_constraints d
join sys.tables t
on t.object_id = d.parent_object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id