Diagnostics – Page 3 – LincolnBlog.net

DBCC (Transact-SQL) Overview

Posted on 01/22/201401/21/2019 by Lincoln Bovee'

SQL Server 2012 Posted from TechNet The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. Database…

A DBCC PAGE Example with Output

Posted on 01/21/201403/27/2014 by Lincoln Bovee'

This post will demonstrate how to run the undocumented DBCC PAGE command to view the properties of a page. First we execute the…

The the most blocked queries

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT 
    OBJECT_NAME(objectid) 
    ,BlockTime = total_elapsed_time - total_worker_time
    ,execution_count 
    ,total_logical_reads 
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) 
ORDER BY total_elapsed_time - total_worker_time DESC

SELECT

OBJECT_NAME(objectid)

,BlockTime = total_elapsed_time - total_worker_time

,execution_count

,total_logical_reads

FROM sys.dm_exec_query_stats qs

CROSS apply sys.dm_exec_sql_text(qs.sql_handle)

ORDER BY total_elapsed_time - total_worker_time DESC

Views – 2184

Find resource currently involved in blocking

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT DISTINCT objname = object_name(p.object_id)
FROM sys.partitions p
JOIN sys.dm_tran_locks t1
ON p.hobt_id = t1.resource_associated_entity_id

SELECT DISTINCT objname = object_name(p.object_id)

FROM sys.partitions p

JOIN sys.dm_tran_locks t1

ON p.hobt_id = t1.resource_associated_entity_id

Views – 1915

Identifying blocking processes (lead blocker)

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

Posted on June 17, 2009 by Derek Dieter Finding the lead blocker in a chain of blocks is a difficult challenge when using…

T-SQL for identifying slow-running queries on a database

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT sqltext.TEXT,
    req.session_id AS 'SPID',
    req.status AS 'Status',
    req.command AS 'Command',
    req.cpu_time AS 'CPU Time',
    req.total_elapsed_time/1000 AS 'Total Elapsed Time in Seconds'
FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY [CPU Time] DESC