One of the extra perplexing issues to troubleshoot in SQL Server will be those associated to memory grants. Some queries need extra memory than others to execute, primarily based on what operations have to be carried out (e.g. sort, hash). SQL Server’s optimizer estimates how a lot memory is required, and the query should get hold of the memory grant in order to begin executing. It holds that grant for the duration of question execution - which means if the optimizer overestimates memory you can run into concurrency issues. If it underestimates memory, then you possibly can see spills in tempdb. SEMAPHORE waits. There are multiple methods to assault this situation, and one of my new favourite methods is to use Question Store. We'll use a copy of WideWorldImporters that I inflated using the DataLoadSimulation.DailyProcessToCreateHistory stored process. The Sales.Orders table has about 4.6 million rows, and the Sales.OrderLines desk has about 9.2 million rows. We will restore the backup and allow Query Store, and clear out any previous Question Retailer knowledge so we do not alter any metrics for this demo.

Store CLEAR; in opposition to your production database except you want to remove the whole lot from Question Store. The primary execution returns 1958 rows, the second returns 267,268 rows, and the last returns over 2.2 million rows. In the event you look at the date ranges, this isn’t shocking - the larger the date range the more data being returned. Because this can be a stored procedure, the input parameters used initially decide the plan, as nicely because the memory to be granted. If we look on the actual execution plan for the first execution, we see nested loops and a memory grant of 2656 KB. Subsequent executions have the same plan (as that’s what was cached) and the same memory grant, however we get a clue it’s not sufficient because there’s a sort warning. If we glance in Question Retailer for this stored procedure, we see three executions and the identical values for UsedKB Memory Wave Program, whether or not we have a look at Common, Minimal, Maximum, Final, or Customary Deviation.

Notice: memory grant data in Question Store is reported as the number of 8KB pages. If we're in search of memory grant points on this scenario - where a plan is cached and re-used - Query Store won’t assist us. However what if the precise question is compiled on execution, both due to a RECOMPILE hint or as a result of it’s ad-hoc? 5 there is only one execution, and the memory grant numbers match the initial execution - in order that plan is for the small date range. The 2 larger date ranges generated the identical plan, however there’s vital variability within the memory grants - 94,528 for minimum, and 573,568 for optimum. If we take a look at memory grant data utilizing the Question Store reviews,