hash joins

Posted on 2009-12-21
Last Modified: 2012-05-08
Is it possible to see a recent history of hash joins that were dun through the SQL engine, through t-SQL?

Question by:anushahanna
    LVL 60

    Accepted Solution

    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    not sure, but you can try this

    , [cp].[objtype]
    , [st].[dbid]
    , [st].[objectid]
    , [st].[text]
    , [qp].[query_plan]
    FROM sys.dm_exec_cached_plans cp
    CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
    CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp
    where cast([qp].[query_plan] as varchar(max) ) like '%hash join %'
    LVL 14

    Expert Comment

    I think you age getting mixed up with oracle where that can be done to some point
    LVL 6

    Author Comment

    cast([qp].[query_plan] as varchar(max) ) is truncating at 256 characters.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    you have to chnge the settings to a bigger value

    tools -> options-> query results ->results to text  -> maximum no of characters displayed in each column -> to 8091
    LVL 60

    Expert Comment

    No, your editor is truncating at 256 characters.  go to tools, options, query results, results to text and change it to 8000.

    as a note...what you're returning is just what SQL has in the cache...with a default max of 5, by no means what it has ran recently.
    LVL 6

    Author Comment

    When I change it to 8000, it says

    Msg 6355, Level 16, State 1, Line 1
    Conversion of one or more characters from XML to target collation impossible

    The collation in use is SQL_Latin1_General_CP850_BIN

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 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