Capture sql statement from missing join predicate event

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  January 27, 2015 at 3:27 am

  #296940

  Hi All,

  After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.

  The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.

  I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run

  select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt

  where spid=169

  it gets me the SQL being run at that time not the one that causing the event.

  Appreciate any help on that.

  Best Regards

  Nader

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  January 27, 2015 at 1:26 pm

  #1773495

  You can try this query from sp_BlitzCache at http://www.brentozar.com/blitzcache/no-join-predicate/. It will look through your plan cache to find all statements that have nojoinpredicate warning.

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  January 27, 2015 at 11:32 pm

  #1773575

  Thank you very much.

• nadersam

  SSCertifiable

  Points: 6542

  More actions

  January 29, 2015 at 1:52 am

  #1773907

  Hi Guys,

  I tried getting the different types of warnings or errors available in SQL server to use them in same SQL Robert posted to detect any other problems.

  I couldn't find it in SQL server documentation, if anyone could help me with that please.

  Thanks

  Nader