This months T-SQL Tuesday is number 159 and is hosted by Deepthi Goguri - you can read the original invitation here or by clicking the image below.
The topic of this month is two fold:
- Favorite feature from SQL Server 2022 or SQL server in Azure
- What is my new year resolution and how to keep the discipline
My favorite new feature in SQL server.
This must be the new Optimized Locking feature released to Azure SQL Database.
This feature is an entire new way of handling locking inside the SQL database when doing transactions. It removes the exclusive lock on rows by implementing to components in the locking scheme: The Transaction ID (TID) locking and Lock After Qualification (LAQ).
The Transaction ID lock is locking each row with the Transaction ID of the current executing transaction. This is done by labeling each row with the latest TID that modified it. This removes the potential of many key or row ID locks.
The Lock After Qualification (LAQ) is improving concurrency with an approach to evaluate predicates of a query on the latest committed version of row without aquiring a lock.
What’s the benefit
The SQL server can now lock only the rows that are actually being used instead of a bunch of exclusive locks on all rows untill the transaction is done. This releases each row very early in the transaction for other transactions to use it.
I can really see huge benefits with both large and small ERP transactions and how to use it when loading the data warehouse every night.
When updating ex. 500.000 rows, each row is only locked untill it has been updated and then immediately released for other processes to access it. I think that this will remove a lot of headackes for both ERP consultants and data warehouse technicians.
On writing this blog post the new feature is available in the following regions:
- West Europe
- UK South
- Canada Central
- Brazil South
- West Central US
My new year resolution
Quite often I tend to give myself a small pile of New Year solutions - but this year, I’ve agreed with myself, that only 1 or 2 gives more sense as I’m then more committed (I know I can do it).
This year I’ve given myself these two:
- Keep the current level of daily physical exercise, level up the meditation to 2 x 20 mins a day and physically walk slower
- Be more on top with the news from Microsoft on the Data Platform
How to keep the discipline
I’ve invested in a subscription for the app Headspace which reminds me every day - twice a day, to do my meditation. I then either use the app for “Todays meditation” or just go with the flow and do my own. My exercise at the gym has become a daily routine and I need to keep up with the pace. I don’t think this will be a struggle, but I still need to focus on gettin my a.. to the gym. The reason why I’ve also included “walk slower” is inspired by a blog post I’ve read, saying that the mind is as troubled and thoughtfull as the pace you are walking. The slower you walk, the more grounded the mind will be in the moment and give you a better mindfull every day
My commitment with the news from Microsoft Data Platform - I’ve re-found the app Feedly which gives me a daily overview of my self-configured RSS feeds. With a subtle notification from the app, I can then read blog-posts and news from all my RSS feeds every morning. Giving me the news I’ve configured to be interested in.