Solved

hash warning in sql2005 profiler

Posted on 2010-08-27
1
968 Views
Last Modified: 2012-05-10
I see in my profiler, there was many "hash warning" and  google tell me for a following info about hash warning, can I says from explainaton below our sql server running low memory ?? what it measn by hashing operation and what is hash recursion ?

The HashWarning event class can be used to monitor when a hash recursion or hash bail has occurred during a hashing operation. Hash recursion occurs when the build input does not fit into memory, resulting in input split into multiple partitions, which are processed separately. If any of these partitions still do not fit into memory, they are split further into sub-partitions, which then are processed separately. This process continues until each partition fits into memory or the maximum recursion level is reached (displayed in the Integer Data data column), thus causing hash bail.
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 25

Accepted Solution

by:
jrb1 earned 500 total points
ID: 33544997
http://support.microsoft.com/kb/197297
---------
Hash Joins
In a hash match, a hash table is created in memory for one of the inputs (build input) using a repeatable randomizing function and this table is searched for matching values from the other input (probe input). The hash table performs like an index. The data from the build input is stored in memory. If the build input does not fit in memory it is partitioned recursively until it fits in memory. The probe input is then hashed and used to search for matches. Unlike with nested loops, the presence or absence of indexes is not particularly a concern in this case. Hash joins are CPU-intensive in comparison to nested loops and are affected by available memory. Hash joins are better when there is a significant difference in the sizes of the tables being joined.

In a hash join the build input determines the recursion depth because it can stop partitioning when the input fits in memory. A single hash join can perform both grouping and join at the same time when the grouping attribute is also the join attribute. The result of this join is not in any particular order. Inequality conditions cannot be satisfied by this type of join.
---------

http://technet.microsoft.com/en-us/library/ms190736.aspx
---------
Hash recursion occurs when the build input does not fit into available memory, resulting in the split of input into multiple partitions that are processed separately. If any of these partitions still do not fit into available memory, it is split into subpartitions, which are also processed separately. This splitting process continues until each partition fits into available memory or until the maximum recursion level is reached (displayed in the IntegerData data column).

Hash bailout occurs when a hashing operation reaches its maximum recursion level and shifts to an alternate plan to process the remaining partitioned data. Hash bailout usually occurs because of skewed data.

Hash recursion and hash bailout cause reduced performance in your server. To eliminate or reduce the frequency of hash recursion 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 on the computer. 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.
---------
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question