Press "Enter" to skip to content

Category: Performance Tuning

Load Balancing in Postgres Clusters with pg_cirrus

Published 2023-08-23 by Kevin Feasel

Muhammad 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 closed

Thoughts on Parameter Sensitive Plan Optimization

Published 2023-08-15 by Kevin Feasel

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

https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver16#known-issues

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 closed

Delayed Transaction Durability in tempdb

Published 2023-08-09 by Kevin Feasel

Bob Ward does some digging:

I 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 closed

Execution Plans in Postgres

Published 2023-08-08 by Kevin Feasel

Henrietta 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 closed

Azure SQL MI and the WAF: Performance Pillar

Published 2023-07-28 by Kevin Feasel

Niko Neugebauer looks at one of the pillars of the Well-Architected Framework with respect to Azure SQL Managed Instance:

A 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 closed

Tips for Performance Testing Direct Lake Mode in Power BI

Published 2023-07-10 by Kevin Feasel

Chris 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 closed

Performance Optimizing Cosmos DB

Published 2023-07-07 by Kevin Feasel

Harshvardhan 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 closed

Impact of and Limitations to Parameter Tuning in Postgres

Published 2023-07-07 by Kevin Feasel

Henrietta 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 closed

Last Page Insert Contention in SQL Server

Published 2023-07-05 by Kevin Feasel

Eitan 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

Tools for Optimizing Azure SQL MI Performance

Published 2023-06-21 by Kevin Feasel

Rie Merritt breaks out the toolbox:

Azure SQL Managed Instance provides options within and outside Azure portal for troubleshooting and optimizing performance.  Within the portal, you can leverage automatic tuning and Intelligent Insights. Outside of the Azure Portal, you can take advantage of the capabilities that are already in the database engine, such as query store and dynamic management views (DMV). In addition, Microsoft offers several monitoring options that are in preview: Azure SQL Insights inside Azure Monitor, which requires an agent on a VM you own, Azure SQL Analytics, and Azure diagnostic telemetry. 

Automatic tuning in SQL Managed Instance supports FORCE LAST GOOD PLAN, which identifies queries using an execution plan that is slower than the previous good plan. It forces queries to use the last known good execution plan. Since the system automatically monitors the workload performance, in case of changing workloads, the system dynamically adjusts to force the best performing query execution plan. 

Many of the things Rie describes are also available on-premises, though Azure SQL Analytics is only available in Azure SQL DB and Azure SQL MI, as of the time of this post.

Comments closed