One of many extra perplexing issues to troubleshoot in SQL Server may be those associated to Memory Wave grants. Some queries want extra memory than others to execute, based mostly on what operations must be carried out (e.g. type, hash). SQL Server’s optimizer estimates how much memory is needed, and the query should get hold of the memory grant in order to begin executing. It holds that grant all through question execution - which suggests if the optimizer overestimates memory you may run into concurrency points. If it underestimates memory, then you can see spills in tempdb. SEMAPHORE waits. There are a number of methods to assault this challenge, and one of my new favourite strategies is to make use of Question Retailer. We’ll use a copy of WideWorldImporters that I inflated using the DataLoadSimulation.DailyProcessToCreateHistory stored procedure. The Sales.Orders desk has about 4.6 million rows, and the Sales.OrderLines desk has about 9.2 million rows. We’ll restore the backup and enable Question Retailer, and clear out any outdated Question Retailer information so we don’t alter any metrics for this demo.

Store CLEAR