Last modified by Billie D on 2021/02/12 07:30
From version 36.1
edited by Billie D
on 2021/02/12 10:26
To version 37.1
edited by Billie D
on 2021/02/12 07:30
Change comment: There is no comment for this version

Summary

Details

Icon Page properties
Content
... ... @@ -86,8 +86,8 @@
86 86  **Spills** are really ugly because this is not data placed into a buffer pool page associated **to tempdb**. Tempdb data files become the paging file for memory grants for hash joins (these are not tempdb pages for temporary tables. This is yet another reason why I often call tempdb the garbage dump of SQL Server).
87 87  
88 88  
89 -Even with the feedback system, in some cases, the actual needed memory grant can be very large. Large enough that concurrent users will encounter RESOURCE_SEMAPHORE waits to cause memory pressure within SQL Server. In these cases, you can use resource governor to limit the amount of memory for grants. See the documentation [[here>>https://docs.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql||target="_blank"]] on how to change this. In SQL Server 2019, this value can now be a float value so values < 1% are valid. This could be important with systems with a large
90 -amount of memory. In addition, you can set these values at the query level. See the documentation [[here>>https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15#arguments||target="_blank"]].
89 +Even with the feedback system, in some cases, the actual needed memory grant can be very large. Large enough that concurrent users will encounter RESOURCE_SEMAPHORE waits to cause memory pressure within SQL Server. In these cases, you can use resource governor to limit the amount of memory for grants. See the documentation [[here>>https://docs.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql||target="_blank" rel="noopener noreferrer"]] on how to change this. In SQL Server 2019, this value can now be a float value so values < 1% are valid. This could be important with systems with a large
90 +amount of memory. In addition, you can set these values at the query level. See the documentation [[here>>https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15#arguments||target="_blank" rel="noopener noreferrer"]].
91 91  
92 92  This system is well designed and could really help save you time on expensive tuning for workloads requiring memory grants. There are a few scenarios where memory grant feedback will not be enabled or will not take effect:
93 93  
Icon SQL Server.Code.SQL ServerClass[0]
databaseList1
... ... @@ -1,0 +1,1 @@
1 +Blog.BlogIntroduction