Solved

hash warning in sql2005 profiler

Posted on 2010-08-27
1
959 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
1 Comment
 
LVL 25

Accepted Solution

by:
jrb1 earned 500 total points
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now