If your tables don't have a clustered index (aka heap-table), the space might not be released rightaway

https://www.sqlskills.com/blogs/paul/sqlskills-sql101-why-does-my-heap-have-a-bunch-of-empty-pages/

For efficient filtering it should be the first column of an index. (so there are statistics about time distribution). As Thom A mentioned, the clustered index won't help much for...

Before changing the clustered index, you should know what other queries are impacted and their importance

Is a non-clustered index on [Time] an option?

Is [Time] a string datatype? Since you compare...

If the program is multiple connections and still only uses one core (12%) it could also be due locking or the way it is programmed.

Consider contacting the vendor about the...

That's bad. The script seanachim script posted is viable.

From SQL Server Configuration manager:

The server is up and running?

TCP is enabled? (might be disabled)

I agree with Jeff, the applications seems to use a single connection sequentially.

Also, there seem to be a lot of databases on the C-drive which is usually reserved for OS....

Strange that you would need to modify the clients firewall for outgoing traffic

Does the SQL Server use TCP fixed ports or dynamic?

About limited to 12%. Looks like single-core processing. SQL Server is pretty good at detecting available cores.

MaxDop accidentally set to 1 at server/database/query level ?

VM limited to 12% : are...

Is it possible to provide an easy digestable test-case?

Table definition

Inserts for testcase

Wanted results

COALESCE vs ISNULL https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

I prefer COALESCE because it is known by other SQL-dialects and it doesn't truncate.

In where-clauses I would use: where X is NULL or X=1

instead of COALESCE (unless...

COALESCE(TABLE1.COL2,1) ? equals if COL2 is null, treat it as value "1"

Better apply the latest version (after testing)

SQL 2022 is now supported https://github.com/olahallengren/sql-server-maintenance-solution/blob/master/README.md

If you want to alter the database to enable query store you need ALTER permission on the database.

Can be through the dbowner or grant a specific user alter database rights

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=sql-server-ver16&tabs=sqlpool

Who created the database "Logger_Groups_CI"? Who is the owner

From https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16

dbmanager

Can create and delete databases. A member of the dbmanager role that creates a database, becomes the owner of that database,...

An alternative for STRING_AGG is SELECT ... FOR XML_PATH

https://www.itprotoday.com/sql-server/ordered-set-functions-whats-new-and-missing-sql-server-vnext

• This reply was modified 3 years, 8 months ago by Jo Pattyn.