Thank you, Grant, for good advises. Because of permission issues, I am using Profiler (of course in lower environment) instead of extended events. Am I missing any features?

I don't have application code. What developers sent me is a SQL query "converted" from LINQ. And it's very simple, basically select from a single table with a couple of...

Ah, thanks Sue, when I placed my reply I did not notice that you already placed yours. Thanks, that's what I wanted.

We have a stored procedure, that:

1. Deletes object_a

2. insert into object_a

select from object_b

Because step 2 takes a long time (like 10 minutes), ones it refreshed object_a, all reports select their...

Thanks, Scott.

For #3: They use as_of_month not just for display but a lot in where clauses and join conditions, like a.as_of_month = b.as_of_month

And about money data type, aren't it deprecated?

Exactly, Sue. We have an uncommitted transaction that holds this block. And the problem is that our blocking monitoring job (it is running every 30 sec) wrongfully points to another...

Thank you Sue, tried it too. It still shows getdate().

In my situation, I don't have a chain. I simply replaced one command (that holds open transaction) with...

Thank you Luis for this great stored procedure!

I downloaded, ran it and applied to my situation. But unfortunately, in its result-set, in inputbuffer column it still shows select...

Is it still open? If yes, where can I send my resume?

Thanks a lot Sreekanth for a detailed answer. The only question that I have is what is E2E?

Thanks

Ah thank you, but shrinking production database is not an option.

Thanks Thom and Scott.

Yes, I am fully aware that we can't use old-style (left or right) OUTER joins for a long time, probably since 2005.

And yes,...

Thanks, Scott.

Yes, col3 is indexed and it is unique index.

There was a great article how to solve Sudoku by Itzik Ben Gun about 12 years ago in SQL Server Magazine.

TheSQLGuru: that sounds encouraging. The only reason why our managers don't want to run trace (not Profiler but script-based tracing) is that it has greater impact than X.E. and can...