Performance Tuning Batch Mode HTDELETE and HTBUILD Waits In SQL Server

Performance Tuning Batch Mode HTDELETE and HTBUILD Waits In SQL Server

Thanks for watching!

Here’s the demo query, in case you’re interested in following along.

SELECT
    u.Id,
    u.DisplayName,
    u.Reputation,
    TopQuestionScore = 
        MAX(CASE WHEN p.PostTypeId = 1 THEN p.Score ELSE 0 END),
    TopAnswerScore = 
        MAX(CASE WHEN p.PostTypeId = 2 THEN p.Score ELSE 0 END),
    TopCommentScore = 
        MAX(c.Score)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
  ON u.Id = p.OwnerUserId
JOIN dbo.Comments AS c
  ON u.Id = c.UserId
WHERE EXISTS
(
    SELECT
        1/0
    FROM dbo.Votes AS v
    WHERE v.PostId = p.Id
    AND   v.VoteTypeId IN (1, 2, 3)
)
AND  u.Reputation > 10000
AND  p.Score > 0
AND  c.Score > 0
GROUP BY 
    u.Id,
    u.DisplayName,
    u.Reputation
ORDER BY
    TopAnswerScore DESC
OPTION
(
    RECOMPILE, 
    USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'), 
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140')
);

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.