![]() ![]() , TotalLogicalWrites = SUM(qs.total_logical_writes) , TotalLogicalReads = SUM(qs.total_logical_reads) , TotalPhysicalReads = SUM(qs.total_physical_reads) ![]() , TotalElapsedTime = SUM(qs.total_elapsed_time) , TotalWorkerTime = SUM(qs.total_worker_time) , TotalExecutionCount = SUM(qs.execution_count) SELECT TOP (10) qs.query_plan_hash, qs.query_hash You can copy-and-paste the contents of this column for each query hash and run it in another query window, and it will output the top 10 different query plans and query texts based on their usecount, including their corresponding execution stats. The column “ MoreDetailsCmd” will be available for each query hash. But you can still set the parameter to 1 if you want it to count based on query plan handles instead. I found that this results in far fewer “false positives”. Unlike the similar script provided by Brent, my script counts the number of query plan hashes rather than the query plan handles per each query hash. Running the script with the parameter set to 1 will output the same list, with additional columns that will help you do deeper root cause analysis. Running the above script with the parameter set to 0 will output a list of the top query hashes (sorted based on their size in bytes so that you’d see the most impactful queries first) that have multiple different query plan hashes cached for them. To easily detect and troubleshoot this use case, I wrote this T-SQL script available in our Madeira Toolbox: Source: Brent Ozar Monitoring for the issue this can add up to more CPU time, more memory used for caching plans, and less memory that can be used for caching data. Each plan could be different – in cases where the different plans have a different number of estimated rows, SQL Server might choose to use (or avoid) an index.Each execution plan is cached separately – meaning it takes up more memory.Each separate execution plan means a separate compilation, which means that the query took a little longer to finish. ![]() So, when the plan cache is bloated with too many compiled execution plans, that means less memory space for data.īrent Ozar wrote an excellent blog post about this back in 2018, which lists additional problems that this can cause: The important thing to remember, though, is that this plan cache shares the same memory space as the buffer cache (which is where SQL Server saves table data pages for fast retrieval). The database engine automatically evicts “outdated” execution plans based on various rules (for example, when relevant database schema objects are changed, or when an execution plan is no longer being used). plan cache – SQL Server stores a cache of compiled execution plans as part of its memory buffer. ![]() Each separate execution plan has a different query plan handle, no matter how little the difference.
0 Comments
Leave a Reply. |