Sql-server – SQL Server single query takes longer by end of week

sql-server-2008

SQL Server 2008 R2. We are reporting off a DB-Amp backup of our SalesForce instance.
I have a SQL query that takes about 8 seconds to run on Monday. By Friday the duration is 31 seconds and my app is giving up a second too early. By the next Monday, the query is performing quickly again.
Here is the SQL
I don't have any Jobs running weekly that do any cleanup. Rebuilding indexes and sp_syspolicy_purge_history are nightly jobs.
I checked Profiler and nothing else is happening while the query is running slowly.
I don't see any User Temp tables that were created before today (Friday).
I have another similar query that never slows down.
Any ideas?!

Best Answer

I didn't see the speedup this weekend?! However, I did find the AccountTeamRollup CTE was a huge drain.
I was able to remove the CTE and replace the referring 'Team' column with the below. It went from 30sec to 4sec. 

,STUFF(
    (SELECT ',' + SUB.UserN AS [text()]
    FROM (
        Select distinct atm.AccountId, u.Name UserN
        From AccountTeamMember atm (nolock) 
            INNER JOIN [User] u  (nolock) 
                ON atm.UserId = u.Id) SUB
    WHERE SUB.AccountId = acct.Id
    FOR XML PATH('') ), 1, 1, '' ) Team
Related Question