• Eirikur Eiriksson - Monday, May 29, 2017 3:38 AM

    One just has to love this community!
    😎

    Jeff, if you are running this regularly, why not collect the plan_handles separately and only dig into those which are new? That way you wouldn't be repeating large portion of the work every time.

    Something like
    SELECT
      CP.plan_handle
    INTO dbo.TBL_PLAN_HANDLES
    FROM sys.dm_exec_cached_plans CP;
     

    and then

    SELECT
      qp.query_plan
    FROM dbo.TBL_PLAN_HANDLES PH
    CROSS APPLY sys.dm_exec_query_plan(PH.plan_handle) qp
    -- some filter returning new plans

    You get my drift 😉

    Would this be better using a CROSS APPLY and then filtering out the ones you already have or using an EXCEPT on the sets?  I'm more than a bit out of my element here with XML.  I know it's usually better with normal columns, but I don't know about XML.  Have you already been down this road?