Warning – "The query had to wait 5 seconds for MemoryGrant during execution"

Background

Most of today I spent reviewing  one of our vendors Scheduled Jobs and their corresponding Stored Procedures.

BTW, the jobs are SQL Server based and scheduled through SQL Server Agent.

 

Query Plan Warnings

As I reviewed each SQL Statement noticed a big warning that reads “The query had to wait 5 seconds for MemoryGrant during execution”.

Image

TheQueryHadToWait5Seconds (cropped)

 

Query Statement

Query Text

Here is the query text


UPDATE	a
SET	a.HasMajor = CASE 
			WHEN am.ArticulationId is NOT NULL THEN 1
			ELSE 0
		    END 

FROM		tArticulation a

LEFT JOIN tArticulationMajor am

	ON a.CourseArticulationID_Oltp = am.ArticulationId

;

Explanation

What does the query do

  1. It performs a left outer join on tArticulation and tArticulationMajor
  2. Each time an ArticulationMajor exists for an Articulation we stamp the HasMajor column with 1
  3. On the other hand, for Articulations missing tArticulationMajor we stamp them with 0

 

Rewrite – Using Alternate Joins

The query statement appears ripe for revision

 

Rewrite as Inner Join


UPDATE	a

SET	a.HasMajor = 1
					
FROM	tArticulation a

INNER JOIN tArticulationMajor am

	on a.CourseArticulationID_Oltp = am.ArticulationId

	;

 

Rewrite as Correlation Join / Where Exists


UPDATE	a

SET		a.HasMajor = 1
						
FROM		tArticulation a

where exists
	(
	   select 1

	   from   tArticulationMajor am

	   where a.CourseArticulationID_Oltp = am.ArticulationId

       )

;

 

Compare Original Against Alternate Joins

Compare Query Plans

Image

compareQueryPlans (cropped)

 

Explanation

  1. While the original left outer join comparatively comes in at 100%, our two revisions come in at 0%

 

Compare Statistics IO

Image

compareStatisticsIO

 

 

Number of Records Affected

Image

NumberofReordsAffected

 

 

 

Rewrite – Using Merge Statement

Using Merge Statement, we can compare and merge data from two different sources, as well

 

Rewrite Using Merged / When Matched

Explanation

In the sample code we set HasMajor to 1 when we have records in the source table

Code



merge tArticulation tblA

USING tArticulationMajor tblAM

	on tblA.[CourseArticulationID_Oltp] = tblAM.[ArticulationId]

WHEN MATCHED THEN
	UPDATE
	   SET	tblA.HasMajor = 1
	;

 

Rewrite Using Merged / When Matched and Not Matched

Explanation

In the sample code we set HasMajor to 1 when we have records in the source table.

And, when not matched we set to 0.


merge tArticulation tblA

USING tArticulationMajor tblAM

	on tblA.[CourseArticulationID_Oltp] = tblAM.[ArticulationId]

WHEN MATCHED THEN
	UPDATE
		SET	tblA.HasMajor = 1
	
	WHEN NOT MATCHED BY SOURCE THEN
	UPDATE
		SET	tblA.HasMajor = 0
	
	;

 

Compare Original Against Alternate Merge Statements

Compare Query Plans

Image

queryPlans-mergeStatements

 

Explanation

In comparison

  1. Weigh
    • The original query is at 49%
    • The Merge Statement with Matched is at 0%
    • On the other hand the Merge Statement with Matched and Not Matched is at 51%
  2. Parallelism
    • Both the original query with left outer join and the Merge\Not Matched query relies on Parallism
    • While the Merge and Matched does not use Parallelism

 

Compare Statistics IO

Image

StatisticsIO-mergeStatement

 

Explanation

  1. The Original query and the Merge Statement with matched and not matched experience similar IO
  2. The Merge with Update and Match alone experience limited IO

 

Merge Rewrite – Number of Records Affected

Image

NumberofRecordsAffected - MergeStatement

 

Metrics

Query Cost

Image

Left Outer Join

Summary-LeftOuterJoin (Cropped)

 

Inner Join

Summary-InnerJoin (Cropped)

Merged When Matched / Alone

Summary-MergedWhenMatchedAlone (Cropped)

 

Correlation Join / Using Exists

Summary-Exists ( Cropped )

Merged When Matched / Alone

Summary-MergedWhenMatchedAndNotMatched (cropped)

 

Detailed

    • Left Outer Join
      • Cached plan size = 40KB
      • Degree of Parallelism = 4
      • Estimated Subtree Cost = 33.4372
      • Memory Grant = 21856
      • Estimated Number of Rows = 328914
    • Inner Join
      • Cached plan size = 24KB
      • Degree of Parallelism = 1
      • Estimated Subtree Cost = 0.0631069
      • Memory Grant = 1024
      • Estimated Number of Rows = 32.5183
    • Exists
      • Cached plan size = 24KB
      • Degree of Parallelism = 1
      • Estimated Subtree Cost = 0.0515091
      • Memory Grant = None
      • Estimated Number of Rows = 32.5183
    • Merged when matched alone
      • Cached plan size = 32KB
      • Degree of Parallelism = 1
      • Estimated Subtree Cost = 0.063129
      • Memory Grant = 1024
      • Estimated Number of Rows = 32.5183
    • Merged when matched and not matched
        • Cached plan size = 48KB
        • Degree of Parallelism = 4
        • Estimated Subtree Cost = 34.7932
        • Memory Grant = 22152
        • Estimated Number of Rows = 328914

Tabulated

 

Property Left Outer Join Inner Join Correlated Not Exists Merge Join – When Matched Merge Join – When Matched & Not Matched
Cached Plan Size 40KB 24KB  24 KB  32KB  48KB
Degree of Parallelism  4  1  1  1  4
Memory Grant  21856  1024  1024  22152
Estimated Subtree Cost  33.4372  0.0631069  0.0515091  0.063129  34.7932
Estimated Number of Rows  328914  32.5183  32.5183  32.5183  328914

 

 

Statistics I/O

Image

Left Outer Join

summary-statisticsio-leftouterjoin

Inner Join

summary-statisticsio-innerjoin

Correlated Join / Not Exists

summary-statisticsio-correlatedjoin-exists

Merge – When Matched / Update

summary-statisticsio-merge-whenmatchedupdate_20170203_0816am

Merge – When Matched – Update  & When Not Matched – Update

summary-statisticsio-merge-whennotmatchedupdate_20170203_0816am

 

Tabulated

 

Property Left Outer Join Inner Join Correlated Not Exists Merge Join – When Matched Merge Join – When Matched & Not Matched
tArticulationMajor Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0
workfile  Scan count 0, logical reads 0, physical reads 0  Scan count 0, logical reads 0, physical reads 0
worktable  Scan count 0, logical reads 0, physical reads 0 Scan count 0, logical reads 0, physical reads 0 Scan count 0, logical reads 0, physical reads 0  Scan count 0, logical reads 0, physical reads 0
tArticulation  Scan count 5, logical reads 1007936 Scan count 15, logical reads 145, physical reads 0 Scan count 15, logical reads 145, physical reads 0 Scan count 15, logical reads 145, physical reads 0 Scan count 5, logical reads 1007936, physical reads 0
worktable scan count 0, logical reads, physical reads scan count 0, logical reads 0, physical reads 0

 

 

Explanation

  1. Workfiles
    • Left Outer Join and Merge Join
      • Queries that include and have a marker for records that do not match
  2. WorkTable
    • Inclusive
      • Left Outer Join, Inner Join, Merge Join when matched, Merge Join when matched and not matched
      • Where?
        • Left Outer Join
          • Hash Match
          • Sort
            • Estimated Rows = 328914
        • Inner Join
          • Nested Loop
          • And, then Sort
            • Estimated Rows = 32
        • Merge When Matched Update / Alone
          • Nested Loop
          • Sort
            • Estimated Rows = 32
        • Merge When Matched – Update / When not matched – Update
          • Sort
            • Estimated Rows = 328914
    • Excluded
      • Correlated Not Exists

 

Terminology

 

Term Meaning Link
Worktables The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed. Link
workfile Work files are similar to work tables but are created strictly by hashing operations. Work files are used to store temporary results for hash joins and hash aggregates.  Link

 

 

Operators

WorkFile

Let us quickly and look at the Queries that references Workfile in their Query Plan

Occurrences

Left Outer Join

summary-hashmatch-leftouterjoin

Merge – When Not matched

summary-hashmatch-mergenotmatched

 

Findings

  • We find that each time Hash Matches are found in the Query Plan, we see corresponding workfile entries

Optimizations

Work Table

  1. Worktable caching is improved.
    Link

    • Version 2005 and Higher
      • When a query execution plan is cached, the work tables needed by the plan are not dropped across multiple executions of the plan but merely truncated. In addition, the first nine pages for the work table are kept.
    • version 2000
      • In SQL Server 2000, the work tables used during query plan execution are dropped.
    • Because the work table is cached, the next execution of the query is faster.
    • When the system is low on memory, the execution plan may be removed from the cache and the associated work tables dropped as well.
    • Both SQL Server 2000 and SQL Server 2005 use a small global pool of pre-allocated pages and extents that make the initial creation of work tables faster.

 

Summary

Honestly, have to come back and add in more metrics, and generally tie things up.

There are some things that will later help our understanding of Workfiles and Worktables.

WorkFiles occur when we need records that are not included within our Join Clause.

Whereas, worktables are hallmarks of the Sort Operator.

But, again how true this is, will come later.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s