Solved

hash warning in sql2005 profiler

Posted on 2010-08-27
1
961 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
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

930 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

15 Experts available now in Live!

Get 1:1 Help Now