SQL SERVER – Script: Finding queries without JOIN Predicates

I have been in developer’s shoes for close to 5+ years before reinventing myself to do things that made me even better in coding. It is a tough challenge these days being a developer. The life around you and the technology is changing so rapidly that we seldom get time to take a pause from our busy schedule and learn something that has come new. This constant struggle to build new competencies, read documentations of newly released products is something I do religiously.

This blog can never come this far if I had not been doing this till date. The more I read and learn, the opportunities to share become better. To carry a baggage of legacy from time to time is inevitable and I see folks in the industry hold onto some of the practices even without knowing why they are doing so.

I am writing this code blog below because during a code review at one of the software companies, I saw a code that was not adhering to ANSI standards. I quickly jumped onto it and asked – why? The developer gave me the usual long answer of no time, release pressures and lastly – what is wrong, it works right?

I have respect for such pressures as you build your product, but writing a code block that performs and is easier to maintain is one of the critical step. After I explaining, I showed the warning in the execution plan that shows we have done something that can cause poor performance.

I quickly came up with this script to help the team identify all the queries that were written in this fashion and requested them to change the same at the earliest.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT st.TEXT,
qp.query_plan
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes( '//p:RelOp/p:Warnings[(@NoJoinPredicate[.="1"])]' ) AS q(n) ;

The warning is because, poorly performing queries are throwing warnings that there are operators without join predicates. That means somewhere in your query, you’ve logically written a CROSS JOIN without thinking about it. Maybe you are using an old style join syntax (SELECT * FROM a, b, c) or maybe you really did intend to write this code. Either way, a lot of rows can be produced as a result of this operation. It’s best to make sure this was your intent.

The only way out here is to re-write your query so you are always applying a join predicate. This is a script blog. If you run this in your environment, do you get anything? Find out and keep me posted.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Database Mirroring login attempt failed – Connection handshake failed
Next Post
SQL SERVER – Are Power Options Slowing You Down? – Notes from the Field #095

Related Posts

4 Comments. Leave new

  • Srinivasan Prasnna
    September 9, 2015 10:07 am

    When I executed this query I got nothing. So I fictiously created a procedure without the JOIN predicate and my proc is as follows:
    CREATE PROCEDURE [dbo].[dummy]
    AS SELECT TOP 10 itm.*
    FROM dbo.tableA A WITH (NOLOCK), dbo.tableB B WITH (NOLOCK)
    WHERE A.wh_id = B.wh_id

    After I created this proc, I tried to execute the above query and still got nothing. Can you please tell me what am I missing?

    Reply
    • have a look at query plan for your query. Do you see warning of “No Join Predicate” ?

      Here is what you can use to know what I mean

      SELECT top 10 *
      FROM Sales.SalesOrderHeader AS soh
      ,Sales.SalesOrderDetail AS sod
      ,Production.Product AS p
      WHERE soh.SalesOrderID = 43659

      Reply
  • Pinal,
    I have one view and its involved 3 tables ..first two tables are left joined and the third one is with cross apply …in my execution plan …i am getting the warning no join predicate..could you please suggest on this

    Reply
  • forgot to mentioned …the view is inner joined with some other table

    Reply

Leave a Reply

Menu