also to be kept in mind: heaps.

You should rebuild heaps too, to activate the checksum mechanism.

Alter table [yourschema].[yourtable] rebuild

for a start, maybe use sentryone plan explorer (free) to replay the exported deadlock xml

ever considered sequences ?

Also double check your backup share, I've had a case where the storage admins enabled compression for the volume holding the SQLServer backups, and it created an ugly mess for...

• This reply was modified 4 years, 10 months ago by Johan Bijnens.

I think the podcast should fit 1 hour 😉

I...

Also keep in mind, you can just update the statistics.

Have a look at Erin Stellato's advices: https://sqlperformance.com/2017/10/sql-statistics/updates-to-statistics

or even the one on how you can use Ola's solution to perform...

• This reply was modified 4 years, 11 months ago by Johan Bijnens.

And of course keep in mind you also have the transaction option "Snapshot"

Are you sure your data system really needs it all in actual columns? ( advocate of the devil, I know )

Have you considered putting those "free wheeling columns" into an...

It is indeed a concept that uses tempdb to get it things done. ( versionstore )

The difference between both:

- 'snapshot isolation level" is a transaction setting  ( which has to...

• This reply was modified 4 years, 11 months ago by Johan Bijnens.
• This reply was modified 4 years, 11 months ago by Johan Bijnens.

please read MS docs about tempdb database

This is where you want to be starting when taking tempdb into focus: Tempdb configuration survey results and advice

( and its referred...

Just keep in min , SQLServer "uniquifies" every clustered index key !

Clustered Index Uniquifier Existence and Size

oh by the way, if you know [MULE_BATCH_ID] should be unique in that table, create a unique constraint or index.

how about:

Declare @StartYear datetime = '1900-04-01' ;
Declare @StartYearNov datetime = '1900-10-01' ;
SELECT convert(date, dateadd(yy,n,@StartYear)) Dt
     , DATEPART(wk, dateadd(yy,n,@StartYear) ) AS Aprilwk
  ...

For what it's worth: Even last year, I've seen vendors still installing SQL2005 Express with their "state of the art" stuff.

I've always been "the first with who made a fuss...

Indeed.

It is by far THE biggest downside of SQL Paas / Iaas in on Azure!

on top of that, you cannot extend msdb to keep a relevant amount of job history...