Hash warnings (last hour)

This metric measures the number of hash warnings events in the last hour. A hash warning event means that part of the data processed for a hash operation was written to tempdb.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

This metric measures the number of hash warnings events in the last hour. A hash warning event means that part of the data processed for a hash operation was written to tempdb. This means that a hash join or hash aggregate has run out of memory and been forced to spill information to disk (tempdb) during query execution, which can degrade the SQL Server performance. If you have performance problems, this metric may help you to find out what they are. It is also a good idea to run this metric on servers suffering from tempdb contention, because data is spilled to tempdb.

Use this metric to find out when a hash recursion or hash bailout (cessation of hashing) has occurred on your server during a hash operation. A hash recursion (event 0) happens when the input of the query does not fit entirely into available memory, forcing SQL Server to split the input into multiple partitions that are then processed seperately. A hash bailout (event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion level and shifts to an alternative query plan to process the remaining partitioned data. This usually occurs because the data is skewed.

To eliminate or reduce the frequency of hash recursions and bailouts, do one of the following:

  • Make sure that statistics exist on the columns that are being joined or grouped.
  • If statistics exist on the columns, update them.
  • Use a different type of join. For example, use a MERGE or LOOP join instead, if appropriate.
  • Increase available memory. Hash recursion or bailout occurs when there is not enough memory to process queries in place and they need to spill to disk.

Creating or updating the statistics on the column involved in the join is the most effective way to reduce the number of hash recursion or bailouts that occur.

It is also important to monitor which query caused the hash warning and when it happened, because the query is probably taking more time to run than expected.

For more information, see http://msdn.microsoft.com/en-us/library/ms190736.aspx.

Hash warnings (last hour), 3.0 out of 5 based on 3 ratings

Metric definition

Name

Hash warnings (last hour)

Description

This metric measures the number of hash warnings events in the last hour. A hash warning event means that part of the data processed for a hash operation was written to tempdb. This means that a hash join or hash aggregate has run out of memory and been forced to spill information to disk (tempdb) during query execution, which can degrade the SQL Server performance. If you have performance problems, this metric may help you to find out what they are. It is also a good idea to run this metric on servers suffering from tempdb contention, because data is spilled to tempdb.

Use this metric to find out when a hash recursion or hash bailout (cessation of hashing) has occurred on your server during a hash operation. A hash recursion (event 0) happens when the input of the query does not fit entirely into available memory, forcing SQL Server to split the input into multiple partitions that are then processed seperately. A hash bailout (event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion level and shifts to an alternative query plan to process the remaining partitioned data. This usually occurs because the data is skewed.

To eliminate or reduce the frequency of hash recursions and bailouts, do one of the following:

  • Make sure that statistics exist on the columns that are being joined or grouped.
  • If statistics exist on the columns, update them.
  • Use a different type of join. For example, use a MERGE or LOOP join instead, if appropriate.
  • Increase available memory. Hash recursion or bailout occurs when there is not enough memory to process queries in place and they need to spill to disk.

Creating or updating the statistics on the column involved in the join is the most effective way to reduce the number of hash recursion or bailouts that occur.

It is also important to monitor which query caused the hash warning and when it happened, because the query is probably taking more time to run than expected.

For more information, see http://msdn.microsoft.com/en-us/library/ms190736.aspx.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

master

Collection frequency

3600

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Hash warnings

Description

This alert is raised when the number of hash warnings events in the last hour goes above a specified threshold. A hash warning event means that part of the data processed for a hash operation was written to tempdb. This means that a hash join or hash aggregate has run out of memory and been forced to spill information to disk (tempdb) during query execution, which can degrade the SQL Server performance. If you have performance problems, the metric on which this alert is based may help you to find out what they are. It is also a good idea to run the metric on servers suffering from tempdb contention, because data is spilled to tempdb.

Use the metric to find out when a hash recursion or hash bailout (cessation of hashing) has occurred on your server during a hash operation. A hash recursion (event 0) happens when the input of the query does not fit entirely into available memory, forcing SQL Server to split the input into multiple partitions that are then processed seperately. A hash bailout (event 1) is even worse for performance. It occurs when a hashing operation reaches its maximum recursion level and shifts to an alternative query plan to process the remaining partitioned data. This usually occurs because the data is skewed.

To eliminate or reduce the frequency of hash recursions and bailouts, do one of the following:

  • Make sure that statistics exist on the columns that are being joined or grouped.
  • If statistics exist on the columns, update them.
  • Use a different type of join. For example, use a MERGE or LOOP join instead, if appropriate.
  • Increase available memory. Hash recursion or bailout occurs when there is not enough memory to process queries in place and they need to spill to disk.

Creating or updating the statistics on the column involved in the join is the most effective way to reduce the number of hash recursion or bailouts that occur.

It is also important to monitor which query caused the hash warning and when it happened, because the query is probably taking more time to run than expected.

For more information, see http://msdn.microsoft.com/en-us/library/ms190736.aspx.

Supported SQL Server versions: SQL Server 2005 or later

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:50
Medium:10
Low:0

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled