When and how does SQL Server allocate memory for a query

• okbangas

  SSChampion

  Points: 11773

  More actions

  October 3, 2011 at 3:03 am

  #258166

  I know this is a generic question, and it is because I want to understand how memory is allocated for a query. If we for instance have a clustered index scan which is estimated to return 10000 rows of 8kB each, will SQL Server allocate memory for all these estimated rows before the clustered index scan is starting, or will the memory be allocated dynamically during the execution. Is this behaviour the same for all operators, or does some preallocate and some allocate dynamically?

  I've tried to search for information, but I only seem to get tons of information about how SQL Server allocates memory from the OS, and not how SQLOS is allocating memory to queries.

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  October 3, 2011 at 3:13 am

  #1389362

  There's a good article on the memory grant process here:

  http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  October 3, 2011 at 3:17 am

  #1389366

  You can also take a look at the below DMV for more information on how memory is granted for a particular query in your environment:

  sys.dm_exec_query_memory_grants

• okbangas

  SSChampion

  Points: 11773

  More actions

  October 3, 2011 at 4:51 am

  #1389398

  Thanks a lot, that was exactly what I needed.

  ---

  
  Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

  Concatenating Row Values in Transact-SQL[/url]