04 July 2012

Hash warnings (last hour)

by Fabiano Amorim

16,897 0 Memory, Performance

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 Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate 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.

Metric definition

Name

Hash warnings (last hour)

Description

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


		
			
			
Transact-SQL
			
			DECLARE @Str NVARCHAR(MAX);

SELECT @Str = CONVERT(NVARCHAR(MAX), Value)
  FROM :: FN_TRACE_GETINFO(DEFAULT) AS Tab
  WHERE Tab.Property = 2
    AND Traceid = 1;
 
SELECT COUNT(*) AS Cnt
  FROM FN_TRACE_GETTABLE(@Str, DEFAULT) AS trace 
  INNER JOIN sys.trace_events te
  ON te.trace_event_id = trace.EventClass
  WHERE te.name LIKE 'Hash Warning'
    AND trace.StartTime >= DATEADD(hh, -1, GETDATE());
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
				
						DECLARE @Str NVARCHAR(MAX);
 
SELECT @Str = CONVERT(NVARCHAR(MAX), Value)
  FROM :: FN_TRACE_GETINFO(DEFAULT) AS Tab
  WHERE Tab.Property = 2
    AND Traceid = 1;
 
SELECT COUNT(*) AS Cnt
  FROM FN_TRACE_GETTABLE(@Str, DEFAULT) AS trace 
  INNER JOIN sys.trace_events te
  ON te.trace_event_id = trace.EventClass
  WHERE te.name LIKE 'Hash Warning'
    AND trace.StartTime >= DATEADD(hh, -1, GETDATE());

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

16,897 Rate

Click to rate this post!

[Total: 3 Average: 3]

Hash warnings (last hour)

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics