Share via

Hash Warning SQL Profiler Event

  • Article

One of the less well-known warning events that is logged to SQL Profiler trace is the Hash Warning event. Hash Warning events are fired when a hash recursion or hash bailout has occurred during a hashing operation. Both of these situations are less than desirable, as they mean that a Hash Join or Hash Aggregate has run out of memory and been forced to spill information to disk during query execution. When a hashing operation spills to disk, this almost always results in slower query performance and can cause space consumption increase in tempdb.

 

Note that the Hash Warning event needs to be explicitly enabled within SQL Profiler. It is not one of the “default” set of events. More info on SQL Profiler can be found here

 

What can be done if you see a lot of Hash Warning events? The recommended actions are:

 

· Make sure that statistics exist on the columns that are involved in the hashing operation. Without statistics, the hashing operation has no way to know how much memory to pre-allocate.

· Even if statistics do exist, try updating them, as this can give more current information to the hashing operation when it decides how much memory to allocate.

· Try using a different type of join (this can be done by hinting OPTION(MERGE JOIN) or OPTION(LOOP JOIN)). Note that forcing a join type does not necessarily guarantee a better execution plan.

· If all of these fail, you can increase the available memory on the computer.

 

A sample of what you will see in the profiler would look something like the following. Note the batch starting, followed by a number of Hash Warning events prior to batch completion. Also, the SPID that is causing the events will be recorded

 

EventClass

StartTime

SPID

SQL:BatchStarting                           

2007-02-01 18:53:34.703

51

Hash Warning

2007-02-01 18:53:48.267

51

Hash Warning

2007-02-01 18:53:48.283

51

Hash Warning

2007-02-01 18:53:50.097

51

Hash Warning

2007-02-01 18:54:05.300

51

SQL:BatchCompleted

2007-02-01 18:54:19.130

51

 

- Steve Herbert

SQL Server Query Execution

Comments

  • Anonymous
    August 05, 2008
    The comment has been removed
  • Anonymous
    August 23, 2008
    I'm sure many of you are aware of the wonderful RML Tools (description here and x86 download here ) and