A Fine Slice Of SQL Server
Welcome back to Part 3 of my SQL Server Backup Internals Series.
In Part 1, I introduced the “parts” of a BACKUP Operation and in Part 2, we delved into Backup Buffers. Today, we’re going to talk about what manipulates those Backup Buffers = CPU Threads. This’ll be a longer blog, so go refill your coffee now.
Andy did an outstanding job explaining what reader and writer threads do and how SQL Server picks the numbers of each.
Comments closedMuhammad Ali explains how load balancing works in Postgres:
Load balancing is a critical component of high availability clusters that optimises performance, scalability, and fault tolerance. By evenly distributing database connections across multiple servers, load balancing prevents bottlenecks, efficiently handles increased workloads and improves response time.
In this blog, we will explore how standby nodes contribute to efficient workload distribution and achieving optimal query execution by directing all read/select queries to these standby nodes.
Read on to see how you can use pg_cirrus to perform query load balancing.
Comments closedJared Poche shares some thoughts:
First, I wanted to point out that there are fixes to issues specific to Parameter Sensitive Plan Optimization in SQL Server 2022. The issue itself is documented at the link below.
When I heard about the issue, I heard that it could cause SQL Server 2022 to cause stack dumps every 15 minutes. The time interval made me think it was related to the default interval for Query Store, and that seems to be the case.
Jared has several thoughts on the topic as he puts together a talk on PSPO.
Comments closedI have found almost every day of my career at Microsoft, I learn something new about Microsoft SQL. It is one of the reasons I enjoy my job. In April of 2023, I was speaking at the MVP Summit in Redmond, Washington. One day I was spending time in a side conversation with MVP Rob Farley discussing some of his “wish list” items for SQL. One of these wish list items was delayed transaction durability for tempdb.
Good news for Rob on that front.
Comments closedHenrietta Dombrovskaya shows us an execution plan in Postgres:
In the last blog (When PostgreSQL Parameter Tuning is not the Answer), we compared several execution plans for a SQL statement as we made changes to parameters and indexes. Still, there was no mention of what an execution plan is, how one can obtain an execution plan for a query, and how to interpret the result. In this blog, we will take a deep dive into this topic.
Read on for the full article.
Comments closedA baseline is a known value against which later measurements and performance can be compared. Baseline helps us define what is a normal database performance and thus comparing against the baseline provides us with insights into any abnormalities. Ideally, one should take performance measurements at regular intervals over time, even when no problems occur, to establish a server performance baseline. Compare each new set of measurements with those taken earlier.
Click through for additional guidance and recommendations.
Comments closedChris Webb gives us some performance testing advice:
If you’re excited about Direct Lake mode in Fabric you’re probably going to want to test it with some of your own data, and in particular look at DAX query performance. Before you do so, though, there are a few things to know about performance testing with Direct Lake datasets that are slightly different from what you might be used to with Import mode or DirectQuery datasets.
Chris shares some great advice and takes us through a good approach for testing. This post is all about the how, not the results.
Comments closedHarshvardhan Singh has a few tips for us:
As with the other databases, indexing is the first go-to option to improve query performance. The same is the case with Cosmos DB as well. Below are a few points which you can leverage to optimize the indexing strategy for Cosmos DB.
Indexes are kind of similar to indexing in SQL Server in intent, though different enough in implementation that you’ll want to read up on them. Harshvardhan also includes some tips around data modeling and querying data.
Comments closedHenrietta Dombrovskaya wraps up a series on PostgreSQL parameters:
In this first blog, we didn’t provide any examples of the practical impact of parameters tuning on performance. Indeed, it is challenging to model such an impact on the training database.
In this blog, we will segue from discussing PostgreSQL system parameters best practices to other ways of performance tuning. Moreover, we will demonstrate that essential database performance tuning goes beyond choosing the appropriate parameters settings.
Click through for that post.
Comments closedEitan Blumin spots a wild Latch Convoy:
The “Last Page Insert Contention” in SQL Server, also known as “Latch Convoy Problem“, also known as “PageLatchEx Contention” is one of those extremely rare use cases that are very difficult to see in real-world scenarios.
Evidently, it was impactful enough that Microsoft implemented a solution for this problem back in SQL Server 2019 in the form of the new OPTIMIZE_FOR_SEQUENTIAL_KEY index option, which reportedly fixes it.
Click through to learn more about a scenario in which Eitan saw this in the wild. In fairness, I’m not sure I’d do any better at realizing that this was a last page insert contention problem.
Comments closed