Thank you Lisa Griffin for the invitation to December month of T-SQL Tuesday.
After I started presenting, I learned many technical concepts but here I would like to quickly mention couple of them:
- Memory grant connections are stored in a memory component in the SQL Server which is called as Fixed memory. As the fixed memory cannot be expanded or shrinked when there is a memory pressure, SQL server requests the other components of memory like for example, evict the plans out of the plan cache and dirty pages out of the buffer pool to release the memory as memory grants for executing the queries. These memory grants are allocated before the query goes to the execution phase. Memory grants for any query are allocated based on the hashes, sorts in the execution plan and Cardinality estimates. When there are many queries requesting more memory and when the Fixed memory is already full, these other memory components releases the memory as there is an internal pressure with in the SQL Server. Once the memory grant is allocated to the query, the memory will only be released once the query execution completes. Resource Semaphore is responsible for satisfying memory grant requests while keeping overall memory grant usages within the server limit.
- Redundant joins between the tables will be only removed by the SQL Server automatically on the background only if the referential integrity is maintained by the foreign key constraint defined between both the tables. If there is no Constraint defined, then as SQL Server doesn’t know about the relationship between two tables, SQL Server will perform a join scanning one table to find matching values from other table.
- SQL Server needs a NOEXPAND hint to create a query plan that uses an indexed view. When NOEXPAND is specified, automatic stats are created on index view same as it happens with the ordinary tables.
I would also like to hear from other community members of what else they have learned from presenting!
Deepthi Goguri's SQL Server Blog 