Further to this, around the time we get blocked, there is a command for alter server audit and it had a  wait type of (30651ms)PREEMPTIVE_XE_CALLBACKEXECUTE. This was collected right before we...

This logic will automatically assign the next item so you dont have to worry about being a bad planner

declare @bookings table(bookingID int, start_date date, end_date date,...

Is there a way to list all the sessions and what implicit_transactions is set to for each one? I cannot see it in sys.dm_exec_sessions

OK I think I have solved this one myself... to a degree at least. I turned on a sql trace and started up the application. I then looked at sys.dm_tran_active_snapshot_database_transactions to...

Try using CHAR(36) instead of the $ for the ones you don't want to be interpreted as a variable

@output_file_name=N'D:\Folder\Backups_' + CHAR(36) + N'(ESCAPE_SQUOTE(STRTDT))_' + CHAR(36) + N'(ESCAPE_SQUOTE(STRTTM)).txt',

Anyone have an idea on this one? Could it be that the cursor was closed but not deallocated?

Looks like there are similar posts on here and other sites around this issue. Try the resolution in this one?
https://www.sqlservercentral.com/Forums/Topic1157335-20-1.aspx

but I am with crow on this...

I don't disagree but... vendor app. If I could rewrite their tables\queries and get rid of cursors, I would.

Can you please give more information on how you did the upgrade. I assume that on the passive node, had you already run the sql installation media and done "add...

Thanks for all the replies. Problems with query store is that it fills up in minutes... (2GB query store). Anyway the vendor has provided a patch fix for their application...

Yep have tried legacy cardinality estimation. 

Unable to modify the query as its a vendor app.

I have turned on the query store and can see that its...

Have run update stats on the database. Have run update stats with fullscan on the offending table. Can see the fetch is definitely using a bad plan with a costly...

update stats weekly but I am going to dig in to this a bit deeper to see it it can be done better.

Yes have had a look at unused and...

Oh there are about 8524 clustered indexes but 43 primary keys

Myself and another junior/intermediate DBA vs 8 developers for...

Yes it is an application with separate application servers and it is my least liked system at this site for many many reasons reasons some of which are...

- 9448 tables,...