Press "Enter" to skip to content

Category: Performance Tuning

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

Troubleshooting Slow Performance in SQL Server

Published 2023-06-19 by Kevin Feasel

Joseph Pilov puts together a compendium:

In the past 1.5 years we in the SQL Server CSS and content teams have been working on creating new documentation to assist with troubleshooting SQL Server slow performance issues.

Here is a list of articles that were produced over this period. Hope you find these useful. Please share with others and don’t hesitate to provide feedback at the bottom of each article page by clicking on “Was this page helpful?”.

The following individuals contributed with reviews, ideas, project management and content creation: Shaun Beasley, Pradeep Madheshiya, Stephanie Okhagbuzo, Hemin Shah, Liwei Yin, Seven Dong, Haiying Yu

Read on for 14 separate articles covering a variety of issues.

Comments closed

Microsoft Defender for Azure SQL Slowdown

Published 2023-06-19 by Kevin Feasel

Iain Evans finds a hidden cost:

To find the root cause, we conducted a configuration comparison of the servers against an Aireforge configuration snapshot to check for any changes. We immediately noticed that an extended event session called SQLAdvancedThreatProtectionTraffic had recently been deployed. This session is deployed by default with Azure Defender for SQL Server. More info: https://learn.microsoft.com/en-us/azure/defender-for-cloud/defender-for-sql-introduction#advanced-threat-protection

Read on to see what the symptoms were and what kind of impact this had.

Comments closed

Speeding up a Power BI Report via Constant Line

Published 2023-06-08 by Kevin Feasel

Nikola Ilic tries something different:

Essentially, the idea here is, since these four lines are not changing based on the numbers in the visual itself (they have constant value based on the slicer selection), to leverage the Constant line feature from the Analytics panel. Since no Constant line is available with Line and clustered column chart visual, let’s duplicate our visual and change its type to a regular Clustered column chart.

This is a good reminder that the best outcome isn’t always the most straightforward one.

Comments closed

Troubleshooting an Occasionally Poorly Performing Query in Oracle

Published 2023-05-31 by Kevin Feasel

Jura Bratina looks at the numbers:

A client asked us to verify why an INSERT AS SELECT statement, which was part of a scheduled ETL job executing on a single instance V11.2 database running as a data warehouse, suddenly started to take longer than usual. The database doesn’t have the Diagnostic and Tuning license, so Statspack and an external 3rd party application are being used to monitor the performance.

Okay, so I like making fun of Oracle licensing as much as (well, more than) the next guy, but seriously, a Diagnostic and Tuning license? Anyhow, the post isn’t about Oracle’s quest to find a more efficient way to vacuum money out of your bank account, but instead a performance troubleshooting approach when performance is intermittently bad. Spoilers: noisy neighbors. It’s usually noisy neighbors.

Comments closed

Databricks SQL Performance Tuning

Published 2023-05-30 by Kevin Feasel

Katie Cummiskey provides some tips for us:

We previously discussed how to use Power BI on top of Databricks Lakehouse efficiently. However, the well-designed and efficient Lakehouse itself is the basement for overall performance and good user experience.  We will discuss recommendations for physical layout of Delta tables, data modeling, as well as recommendations for Databricks SQL Warehouses.

These tips and techniques proved to be efficient based on our field experience. We hope you will find them relevant for your Lakehouse implementations too.

Read on for these tips.

Comments closed