Rowlock: avoid escalation to page/tablelock

More detail: https://learn.microsoft.com/en-us/answers/questions/150070/sql-server-when-to-use-rowlock-updlock-etc

Why do you COALESCE (os_user_name, '')<>'anon'? If os_user_name is null it is different than anon

Why do you make @_batchSize dynamic?

You might want to use a temporary table to store your...

You can also investigate https://github.com/spaghettidba/XESmartTarget

You can capture blocking session with extended events https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking

Have you enabled query store? Whilst 2ms probably won't show up, the 16 sec and 25 sec durations would

Single user for clustered instances: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode?view=sql-server-ver16

1. I am not aware of such setting. On shutdown
2. Using the kill command ?
3. Query sys.dm_exec_connections ? Not sure if you can measure idle time
4. Setup an extended event...

Celko, why did you choose CHAR(10) whilst an int works perfectly? Certainly in the age of anomized data

I didn't get the course "Since an identifier has to be on a...

Thanks for posting your solution

Thanks for mentioning it, wasn't aware of this kind of database

It's possible, done it a few times after testing. (without replication / availability groups ...). Can't remember needing old installation media

• Take sql server offline
• backup server (VM) (full restore...

Check

https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms

It will switch to readonly when the storage limit is reached. It will a bit of system overhead but not much. Currently one of the best ways to identify...

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...