If you want to post a query plan use something like:

https://www.brentozar.com/pastetheplan/

Would you download a unknown file from the internet?

If you have done a SQL course it will have covered how to do this; you have just not fully understood.

I reccommend you read books by Itzik Ben-Gan starting with...

• This reply was modified 2 years, 11 months ago by Ken McKelvey.

If your Windows Login is a member of the local administrator group, you can start SQL Server in Single User Mode and add your Windows user to the sysadmin server...

WITH AnyProcessed
AS
(
    SELECT [Name], FileId, recordCount
        ,MAX(CASE WHEN processed = 1 THEN 1 ELSE 0 END) OVER...

Using short aliases would make this easier to read.

To make reasonable suggestions about indexes, stats etc we would really need to see the ACTUAL query plan.

https://www.brentozar.com/pastetheplan/

At a guess, NOT EXISTS...

• This reply was modified 2 years, 12 months ago by Ken McKelvey.

The log file is an important part of the database as it is used to both roll forward and roll back on recovery. (ie When a transaction commits it is...

• This reply was modified 3 years ago by Ken McKelvey.

You might be able to get some limited retrospective information from the log. Going forward the following gives an outline and also links to some details on how to access...

As you have not posted DDL and test data we can only assume you are happy...

What I am trying to do with above Update query is that I am updating value of Individual_Id of the those records whose Ind_PK_Value_Updated is null (basically those records...

• This reply was modified 3 years ago by Ken McKelvey.

As you can tell IT had lost faith in management. We were just intrigued to see what they would swallow. It turned out to be quite a bit! I think...

Reminds me of sitting in a meeting with some of my colleagues and having to keep a straight face when it was my turn to incorporate the next phrase from:

What did you not understand about the following?

https://www.sqlservercentral.com/forums/topic/increment-max-value-by-over-a-complete-dataset

It is up to you but personally, if at all possible, I would avoid nested transactions like the plague.

I would still strip out the transactions from SubProc1 etc and rename...

• This reply was modified 3 years ago by Ken McKelvey.

I think you should check for @@TRANCOUNT and not XACT_STATE().

Also remove all TRYs, CATCHes and TRANsactions in the SubProcs. Something like the following should then work reasonably well:

This reply was modified 3 years ago by Ken McKelvey.

This reply was modified 3 years ago by Ken McKelvey.