Analyzing blocked process report XML

CREATE TABLE #BlockingDetails
(
alerttime datetime,
Nature                        VARCHAR(100),
waitresource    VARCHAR(100),
spid    VARCHAR(10),
waittime                VARCHAR(100),
transactionname         VARCHAR(100),
lockMode                VARCHAR(100),
status                        VARCHAR(100),
clientapp               VARCHAR(100),
hostname                VARCHAR(100),
loginname               VARCHAR(100),
currentdb               VARCHAR(100),
inputbuf                VARCHAR(1000),
spname              VARCHAR(1000)
)

–Blocked process details

INSERT INTO #BlockingDetails
SELECT
be.alerttime,
Nature                  = ‘Blocked’,
waitresource    = isnull(d.c.value(‘@waitresource ‘,’varchar(100)’),”),
spid = isnull(d.c.value(‘@spid ‘,’varchar(100)’),”),
waittime          = isnull(d.c.value(‘@waittime’,’varchar(100)’),”),
transactionname = isnull(d.c.value(‘@transactionname’,’varchar(100)’),”),
lockMode          = isnull(d.c.value(‘@lockMode’,’varchar(100)’),”),
status                  = isnull(d.c.value(‘@status’,’varchar(100)’),”),
clientapp         = isnull(d.c.value(‘@clientapp’,’varchar(100)’),”),
hostname          = isnull(d.c.value(‘@hostname’,’varchar(100)’),”),
loginname         = isnull(d.c.value(‘@loginname’,’varchar(100)’),”),
currentdb         = isnull(db_name(d.c.value(‘@currentdb’,’varchar(100)’)),”),
inputbuf          = isnull(d.c.value(‘inputbuf[1]’,’varchar(1000)’),”),
spname          = isnull((select object_name(objectid) from sys.dm_exec_sql_text(d.c.value(‘@sqlhandle’,’varbinary(max)’))),’Adhoc’)
FROM msdb..blockedevents be
cross apply blockedreport.nodes(‘TextData/blocked-process-report/blocked-process/process’) d(c)
–Blocking process details

INSERT INTO #BlockingDetails
SELECT
be.alerttime,
Nature                  = ‘BlockedBy’,
waitresource    = ”,
spid = isnull(d.c.value(‘@spid ‘,’varchar(100)’),”),
waittime          = ”,
transactionname = ”,
lockMode          = ”,
status                  = isnull(d.c.value(‘@status’,’varchar(100)’),”),
clientapp         = isnull(d.c.value(‘@clientapp’,’varchar(100)’),”),
hostname          = isnull(d.c.value(‘@hostname’,’varchar(100)’),”),
loginname         = isnull(d.c.value(‘@loginname’,’varchar(100)’),”),
currentdb         = isnull(db_name(d.c.value(‘@currentdb’,’varchar(100)’)),”),
inputbuf          = isnull(d.c.value(‘inputbuf[1]’,’varchar(1000)’),”),
spname          = isnull((select object_name(objectid) from sys.dm_exec_sql_text(d.c.value(‘@sqlhandle’,’varbinary(max)’))),’Adhoc’)
FROM
msdb..blockedevents be
cross apply blockedreport.nodes(‘TextData/blocked-process-report/blocking-process/process’) d(c)

select * from #BlockingDetails

Row and Page Count Influence on Estimated CPU, I/O Cost and Sort Memory Grants

From http://www.sqlskills.com/blogs/joe/exploring-row-and-page-count-impact-on-estimated-cpu-and-io-cost/

  • I/O Cost of a Clustered Index Scan is sensitive to page counts, but not row counts.
  • CPU Cost of a Clustered Index Scan is sensitive to row counts, but not data page counts

This can have effect on execution plan quality as well.

http://www.sqlskills.com/blogs/joe/data-page-count-influence-on-the-query-execution-plan/

From http://www.sqlskills.com/blogs/joe/row-and-page-count-influence-on-memory-grants/

  • The memory grant introduced by the Sort operator was sensitive to row counts, but not page counts.
  • The Sort operator’s estimated CPU and I/O costs were both sensitive to row counts, but not page counts.