2

I know this sounds a bit odd... the thing is we have a piece of critical code in production which issues a query with a Hash Join hint which is causing high CPU usage on SQL Server.

Owing to some lack of change control in the past, we are not confident of being able to deploy a new version of this code without the HASH without potentially breaking other (much more critical) stuff.

I'd like to ignore this HASH (and even all hints, as I am confident none of them are required and I'd like to allow SQL Server to do its thing without interference!)

Is there a server-wide option to do this?

Thanks!

7
  • No there is no server wide option, but you said you just don't need any hints and you are confident the code will work acceptable even without the hint. Would it not be possible for you to comment these hints in the code ?. This may seem like lame question but just though of confirming.
    – Shanky
    Commented Jan 10, 2018 at 10:46
  • Hi @shanky - yes, commenting out is easy... being sure that the rest of the code is the same as the compiled code in production is not as easy - if we'd not had people deploying builds from their local machines, been deploying versioned builds from the build server (as we are now), then we'd not have this dilema! :)
    – Mark
    Commented Jan 10, 2018 at 11:04
  • As I see here you don't have much option, I would not dictate things here but everyone putting there own code is really a bad process and sooner or later things are going to go south. As I see you have to either change things manually and ask nobody else to put there own crafty things or let it work with HASH joint till you figure out correct process to refine things.
    – Shanky
    Commented Jan 10, 2018 at 11:17
  • @shanky. You're preaching to the choir here :) I do know that which is why I've been instrumental in putting in a proper change management process... however, I can't accommodate for previous practices, sadly. I guess I was once one of those people who just put it live myself to be honest... age has changed me!
    – Mark
    Commented Jan 10, 2018 at 11:28
  • 2
    You can use a plan guide to remove some hints (like index hints), but I don't think you can remove join hints.
    – Brent Ozar
    Commented Jan 10, 2018 at 11:38

1 Answer 1

2

I spent a while messing with this. And while there's not a way to get SQL to ignore a forced join type at the table level (see Kendra Little's excellent video and related links), there is something kinda sneaky you might be able to do to get them to change the code.

In SQL Server 2012 (SP3+), there are hints to toggle Memory Grants. Since Hash Joins are memory consuming operators, you could use a Plan Guide to severely limit the memory your query acquires, slowing it down immensely.

Take this simple example query:

SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;

The plan looks pretty normal.

NUTS

The Hash Join stays in memory, because the query acquires a ~2.1GB memory grant.

NUTS

Creating a bad plan:

If we create a plan guide and use the MAX_GRANT_PERCENT hint to cap the memory at 0...

EXEC sp_create_plan_guide 
@name = N'brimful of hash joins',
@stmt =  N'SELECT TOP 100 u.DisplayName
FROM dbo.Users AS u
INNER HASH JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
ORDER BY p.Id;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(MAX_GRANT_PERCENT = 0)'
GO

Our query gets a similar looking plan...

NUTS

But now the Hash Join spills dismally to disk.

NUTS

Because it got a nonsense memory grant of around ~8mb.

NUTS

Further evidence of how awful this query got is in SET STATISTICS TIME, IO ON

No Guide:
Table 'Users'. Scan count 7, logical reads 113953, 
Table 'Posts'. Scan count 7, logical reads 9777604, 
Table 'Workfile'. Scan count 0, logical reads 0, 
Table 'Worktable'. Scan count 0, logical reads 0, 

 SQL Server Execution Times:
   CPU time = 40001 ms,  elapsed time = 6992 ms.

Guided By Voices:
Table 'Users'. Scan count 7, logical reads 113953, 
Table 'Posts'. Scan count 7, logical reads 9776298, 
Table 'Workfile'. Scan count 3312, logical reads 362880, physical reads 56144, read-ahead reads 446000, 
Table 'Worktable'. Scan count 0, logical reads 0, 


 SQL Server Execution Times:
   CPU time = 66593 ms,  elapsed time = 34270 ms.

The query with the rotten plan guide is in decidedly worse shape.

Is this a good idea?

Well, no, but it doesn't seem like that join hint is so hot, either.

For what it's worth, I tried some other stuff to get the hint to ignored, but they didn't seem to work (no plan change):

@hints = N'OPTION(LOOP JOIN, MERGE JOIN, HASH JOIN, TABLE HINT(p, FORCESEEK))'

Or resulted in an error:

@hints = N'OPTION(LOOP JOIN, MERGE JOIN, TABLE HINT(p, FORCESEEK))'

Msg 1042, Level 16, State 1, Line 64
Conflicting JOIN optimizer hints specified.

Msg 10516, Level 16, State 1, Procedure sp_create_plan_guide, Line 20 [Batch Start Line 59]
Cannot create plan guide 'brimful of hash joins' because @module_or_batch can not be compiled.

I don't expect you to do this, but it was an interesting enough question for me to want to come up with some response (note: not answer!) to.

And, you know, I thought the results were funny.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.