A Fine Slice Of SQL Server
Andrea Gnemmi looks at a pair of extensions:
We all know the importance of monitoring our RDBMS to ensure the performance and availability. Are there any tools that provide functionality to better to monitor PostgreSQL databases? In this article we will explore pg_stat_activity and pg_locks.
Click through for an overview of these two extensions and some of the functionality available in them.
Leave a CommentUmair Shahid preps for an update:
When multiple transactions need to modify the same rows, ensuring data consistency can become tricky. A single wrong approach to locking can lead to suboptimal performance or even bring your application to a standstill as numerous transactions block one another. One tool in PostgreSQL’s arsenal to handle concurrency is SELECT FOR UPDATE. It allows you to lock specific rows before updating them, preventing other transactions from modifying those rows until your transaction completes.
In this blog, we will dive deep into SELECT FOR UPDATE in PostgreSQL. We will explore how it helps in reducing contention, avoiding deadlocks, and ultimately boosting performance when dealing with highly concurrent applications.
Click through to understand how this works and also some notes on when to use it and when not to use it.
Leave a CommentHaripriya Naidu locks the table:
TABLOCK can greatly improve insert performance under the right conditions, but it has some implications to consider. It’s important to understand where this hint cannot be used to avoid shooting yourself in the foot, specifically due to the locking behavior of TABLOCK.
In this article, we’ll explore how TABLOCK’s locking behavior makes it less suitable for tables that cannot afford significant blocking.
Click through for a demo and some takeaways.
Comments closedStephen Planck talks lock escalation:
Lock escalation is a built-in mechanism in SQL Server designed to optimize how the database engine manages locks. By reducing thousands of fine-grained locks (such as row or page locks) to a single table-level lock, SQL Server aims to lower overhead in the lock manager. In practice, lock escalation saves system resources but can also introduce challenges in high-concurrency environments. This post explores why lock escalation happens, how it works under the hood, the problems it can cause, and strategies to prevent or mitigate unwanted escalations.
Read on for the full story.
Comments closedStephen Planck talks spinlocks:
High concurrency can expose subtle performance bottlenecks in SQL Server, particularly those stemming from spinlocks and latch contention. Both mechanisms exist to synchronize access to shared data structures, yet they operate differently and require distinct troubleshooting approaches. By recognizing how they work and knowing what causes them to overload a system, DBAs can reduce CPU spikes, timeouts, and overall application slowdowns.
Read on to more about spinlocks and latch contention. My experiential bias is that spinlocks are the actual problem approximately 5% of the number of times that DBAs believe spinlocks are the actual problem.
Comments closedGulcin Yildirim Jelinek takes us through locking in PostgreSQL:
All locking, whatever their type is, will reduce the throughput, and potentially increase the latency, which means a loss of performance, as nothing is ever free. If my intention is to make sure my data does not have corruption and everyone is getting a correct result at their time of query, I have to agree that I’d have to lock access when multiple transactions are targeting the same table or same row to make sure we take some time to keep the order of things instead of showing wrong results, fast.
Read on for a quick primer on multi-version concurrency control, locking, and lock mechanisms in PostgreSQL.
Comments closedStephen Planck reads a report:
SQL Server includes an Extended Events session called system_health, which runs by default and, among other things, captures information about deadlocks as they occur. When two or more sessions block each other in such a way that no progress can be made (a deadlock), SQL Server chooses one session as the “victim,” rolls back its transaction, and frees resources so other sessions can continue. By reviewing the deadlock report in the system_health session’s XML output, you can see precisely why the deadlock happened and identify which queries or procedures were involved.
Below is a walkthrough of how to interpret a sample XML deadlock report, followed by a brief note on how to access this output.
Read on for that walkthrough.
Comments closedHaripriya Naidu gets optimistic:
In this article, we are going to take a look at how write operations block read operations under default isolation level – Read Committed and how to avoid this using the optimistic isolation level – Read Committed Snapshot.
I do wish that this were the default for new databases in SQL Server, like it is in Azure SQL Database.
H/T Brent Ozar’s newsletter for pointing me in Haripriya’s direction.
2 CommentsHubert Lubaczewski wants to make a change:
I once wrote about this problem, but given that we now have DO blocks and procedures I can make nicer, easy to use example.
Over the years there have been many improvements to how long ALTER TABLE can take. You can now (in some cases) change datatype without rewrite or add default value.
Regardless how fast the thing works, it still needs extremely heavy (though shortlived) lock: Access Exclusive.
Read on to see how you can write a SQL operation that waits for a lock and, if it does not get this lock, retries with backoff.
Comments closedAndy Brownsword has the need for speed:
When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier.
But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who weren’t aware.
Read on to see how. I rarely self-promote in other people’s blog posts (hush, person who knows all the times I’ve done it), but I do have a talk on the topic of near-zero downtime database deployment strategies which includes this and quite a few other notes on what you can do without blocking others. For these sorts of changes, what you’re looking for is asynchronous processing and a Sch-M (schema modification) lock at the very end, such as when rebuilding an index with ONLINE = ON in Enterprise Edition. Alternatively, look for a Sch-M lock only on a metadata table and not the actual data. Andy’s post is an example of the latter.
Comments closed