SQL Server Memory Grants - My Notes No 01

In order to run queries, SQL server need memory to store data. There fore when query is getting estimated, memory grant (amount of Memory SQL Server going to request) also get estimated.

Memory grant estimates are based on following (as per Erik Darlings this article):

• Number of rows -> How many rows query will read/write (again this is also an estimate at this stage)
• Size of the row -> how much memory one raw required
• Number of concurrent memory consuming operators -> how many operators will run at a time when query get executed (not all operators run same time, some need finishing other to start)

Variable Size Column Effect

Size of the row calculation get interesting with variable size (varchar/nvarchar like) columns. SQL server use half of the size of the variable size column. 

For example if column in varhchar(50) -> SQL server will assume column is half full and take 25 characters.

Memory Grant Info

If you run a query, which require a memory grant (some queries doesn't require it as SQL server can output the data as it reads), Memory grant information is shown on the execution plan.

To see this right click the Select operator and select properties:

Description of these properties can be found in this article by Pinal Dave.