• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

hash joins

Is it possible to see a recent history of hash joins that were dun through the SQL engine, through t-SQL?

Thanks
0
anushahanna
Asked:
anushahanna
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
chapmandewCommented:
no
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
not sure, but you can try this

SELECT
 [cp].[usecounts]
, [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 %'
0
 
EmesCommented:
I think you age getting mixed up with oracle where that can be done to some point
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
anushahannaAuthor Commented:
cast([qp].[query_plan] as varchar(max) ) is truncating at 256 characters.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
chapmandewCommented:
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 minutes...so, by no means what it has ran recently.
0
 
anushahannaAuthor Commented:
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
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now