My DBA Adventure

In an earlier blog SQL Server Performance: Troubleshooting Memory Grant Overestimation using SQL Diagnostic Manager and SQL Server execution plan (mydbadventure.blogspot.com) , I introduced how SQL Server miscalculated the required memory for processing a complex query, leading to memory contention on the server and impacting other concurrent queries. In this blog, we will explore and evaluate different approaches to resolve the issue ✨. Are you excited to follow along with me? If so, let’s dive in!!! 🤗 Introduction When troubleshooting a SQL Server system issue, I found it effective to first identify the problem, and then weigh pros and cons, and exceptions of different solutions. For example, in this case, some solutions might not work, others work but take more time, but one solution stands out as the best solution. Don’t be easily fooled by any single approach 😉. Let’s focus on understanding the problem and researching different ways to solve it. As I did a lot of res...

Have you ever wondered how SQL Server uses memory? Have you ever encountered a memory issue in SQL Server? Did you know that SQL Server isn't smart sometimes and it can overestimate and grant a significant amount of memory just for a single query to run —up to one-fifth of the server’s total memory 😱? If you are curious to learn more, read on 🤓. Introduction In SQL Server, before executing a query, a compilation phase may be triggered to generate an execution plan. An execution plan determines 3 key aspects: the order in which source tables are accessed, the operations used to retrieve data (e.g. seek, index/table scan), and the methods for joining, filtering, sorting, or aggregating data ( Execution plan overview - SQL Server | Microsoft Learn ). SQL Server can bypass the compilation phase if it finds a reusable cached plan for the query. During the compilation phase, SQL Server estimates how much data each operation (e.g. read, join, sort, aggregate) will handle and...