the top offender represents only 0.3% CPU
A DTU is a package of CPU and IO time, so you most likely have queries performing a lot of read IO (index or table scanning most likely) or write IO.
The sudden change could be because of:
A new release of the application that has new SQL activity that hasn't been tested on data scaled/shaped like your production ...
From the ALTER INDEX documentation:
In case the base table contains LOB column(s) resumable clustered index rebuild requires a Sch-M lock in the Starting of this operation
You are doing resumable rebuild and the table contains LOB columns (nvarchar(max)). We can clearly see that the rebuild holds schema modification lock, which will block everything else. ...
In Azure, I would prefer
Create a new SQL server database and an ETL that copies
non-PHI/non-PII data from one database to the new database
PHI is serious stuff, and segregating the users in a separate database is both more obviously secure, and has other benefits. EG the database can be scaled and billed separately if you like, and have separate CPU, ...
My first choice would be to use the traditional security mechanisms, which has been in place for decades. I.e., only grant permissions on what they should see.
If they are about to write their own queries, they might be annoyed if you grant some columns but not all (1), since SELECT * will choke on that. And, yes, some will do SELECT *, whatever we say. So, (...
Let's go through this step by step. We'll create a table and all relevant indexes and then populate our table with some data.
Create Table Q275204
/****** Object: Table [dbo].[Q275204] Script Date: 10.09.2020 07:54:00 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Q275204](
The single best way to answer your question would be to run the query, measure the performance, and look at the execution plan (two steps, measure performance in one, get the execution plan in a second, capturing execution plans affects performance). See which index is used and how it's used. Then, disable that index that was used, run the query again, and ...
So DTU actually combines three different things into one (CPU, Memory and Disk) and even with Disk you get a fixed Disk Size but also a max limit of IOPS and Throughput. I would recommend to check all these metrics out and see what all of these are showing you. Also look at connection failures other connection related metrics as well. For a time trend ...
Why not update the table with a single UPDATE statement?
If your database is set to READ COMMITTED SNAPSHOT and you can afford to block other sessions from writing to the table during the update, that's the simplest option.
Otherwise update them sequentially in batches (option 2). Running lots of parallel DML statements (Option 1) is too complicated and too ...