SQL Server generate execution plan based on available table column statistics. It’s important to have more accurate statistics for SQL to come up with a better execution plan.
Memory grant is memory SQL Server thinks is required to hold the estimated data based on the statistics. If SQL Server estimated a large amount of data will be processed but actually only small amount is process, more memory grant will be required than actually required. This will cause several issues performance wise:
-
Memory resource is wasted, as it’s reserved but not used, causing reduced available memory resource for others
-
The query might encounter RESOURCE_SEMAPHORE wait as it’s waiting for SQL Server to allocate the estimated large amount of resource before executing. SQL by default will wait for 25 times the cost of the query (in seconds), and up to 24hrs, before executing.
-
If there is not enough available memory to full fill the memory grant requirment, the query will spill to tempdb which is a much slower operation than process in memory.
SQL Server 2019+ and Azure SQL Database and Azure Managed Instance has lightweight profiling enabled by default. It introduced LIGHTWEIGHT_QUERY_PROFILING database option to enable/disable at database level. A new DMV sys.dm_exec_query_plan_stats was introduced to get the last actual query execution plan stats for a given plan handle. To see this stats through this DMV function, you need to enable trace flag 2451 server-wide. Alternatively you can enable the database scope configuration option LAST_QUERY_PLAN_STATS.
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON
To get the last execution plan for all cached queries
SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;