A Fine Slice Of SQL Server
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 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 closedRie 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 closedJoseph 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 closedIain 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
SQLAdvancedThreatProtectionTraffichad 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