Thanks Sebastian.  I am looking at it but in not sure what to look at.  What would be some other key facts to look at in this execution plan?  In the past would look for the largest Cost and the one that I found was at 38%

MANY-TO-MANY MERGE:([dnn_TabModules].[ModuleID])=([dnn_Modules].[ModuleID]), RESIDUAL:([dnn_Modules].[ModuleID]=[dnn_TabModules].[ModuleID])

 

Thanks Chris

Found another good post about Recompile because of Plan Caching Issues in SQL.  Not sure how often this query is used in DNN if it will hurt to change how it is setup? 

/****** Object:  StoredProcedure [dotnet].[dnn_GetModuleByDefinition]    Script Date: 06/16/2009 15:52:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dotnet].[dnn_GetModuleByDefinition]
      @PortalId int,
      @FriendlyName nvarchar(128)
AS
    SELECT dotnet.dnn_vw_Modules.*  
    FROM dotnet.dnn_vw_Modules
        INNER JOIN dotnet.dnn_ModuleDefinitions as MD ON dotnet.dnn_vw_Modules.ModuleDefID = MD.ModuleDefID
    WHERE ((PortalId = @PortalId) or (PortalId is null and @PortalID is null))
          AND MD.FriendlyName = @FriendlyName
          AND IsDeleted = 0

Best Practice: "CREATE PROCEDURE ... WITH RECOMPILE" can be used to mark stored procedures that are called with widely varying parameters, and for which best query plans are highly dependent on parameter values supplied during calls. From http://technet.microsoft.com/en-us/library/cc966425.aspx

Was thinking if we could add the recompile to help remove this problem I am having. I am still not sure if I am looking at the plan excuation correct but some of the post make sense just grab for any help would be great.

Page State Persistence: Select the mode to use to persist a page's state. This can either be a hidden field on the Page (Default) or in Memory (Cache).	Page Memory
Module Caching Method: Memory caching provides the most flexibility and is the highest performance method however it also consumes the most system resources. Disk caching is the least expensive on resources but does not offer any flexibility.	Memory Disk
Performance Setting: Select how to optimize performance.	Heavy Caching
Authenticated Cacheability: Sets the Cache-Control HTTP header value for authenticated users.	ServerandNoCache
Compression Setting: Select the type of Http Compression to use.	No Compression
Use Whitespace Filter: Select this to remove excess white space from the response sent to the client - this will result in a smaller download (10-15%) but it will make the html less readable.

Above are my setting.  Sorry for all the other post just trying to see if I can get comments out of people or if other have had this issue and how they corrected it.  I am agree that does not make much sense why that one store proc is taking up all the time.  Like I mentioned before it only an issue when the server is on a live domain that outside world is hitting.  It just does not come back during heavy load times.  If I discount the server from the outside world it comes back up.  I don't under stand why the Store Proc is getting called so many times back to back when we do a recyle of the IIS.  SiteSettings I would think only needs to be called once on intail setup.