When and how does SQL Server allocate memory for a query

  • 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]

  • 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

  • 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

  • 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]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply