Other thing is, SQL optimizer has not suggested any missing indexes in the actual execution plan.

We are on SQL Server 2017 EE.

Your query can be simplified to this:

SELECT TOP(11) 
       c.rowid_object,
       c.creator,
   ...

Can we use lastbatchtime and get the difference between lastbatch and getdate() for this ?

repro

======

use testdb

go

CREATE TABLE [dbo].[DEPT](

[DEPTNO] [int] NOT NULL primary key,

[DNAME] [varchar](14) NULL,

[LOC] [varchar](13) NULL,

)

INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (10, N'ACCOUNTING',N'NEW YORK')

INSERT [dbo].[DEPT] ([DEPTNO], [DNAME], [LOC]) VALUES (20, N'RESEARCH',N'DALLAS')

INSERT [dbo].[DEPT] ([DEPTNO],...

PFA bpr sample report.

It does. It's just not obvious when looking at the XML. You can open the blocked process report as a report which will resolve the values for you. However,...

A bunch to unpack here. Let's start with the nerdier stuff.

You list a bunch of events for capturing query metrics, but, you're missing a few, sp_statement_completed for example, since...

You don't want any one session to do too much. It makes it harder to distinguish the information, search for what you want, etc.. Plus, you put more of...

Sir, is that a good thing to have 2 separate XE's , one for collect sql stmts and another one for collecting blocked processes on two different drives or one...

I will be scripting it out as server side trace . Make changes (filename, maxfilesize,rollover etc) and then convert into an Extended Event using Jonathan's script

https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/

Sir, one thing I want...

Is the blocked process report running as part of query collection? It shouldn't separate those out. One as one session. One as another. And, yeah, capturing query metrics, especially...

Just to prove Grant's statement a little more, here's a quick demo.

So you can see that the first SELECT, which wants rows on or after the...

Education.

Get extended events out and set up data collection. Show them how long queries take. Show them how blocking works, and how sometimes, you simply can't guarantee 2 seconds...

Thank you Ken for the wise advise. As of now, it got escalated.

I am already using it but not helpful in my case it is giving only the current sql stmt.