Can pregrowing tempdb hurt performance?

Question

Late yesterday afternoon I added 4 tempdb data files to the existing 4 for a total of 8 (16 processors on the SQL server). I also pre-grew them to near 100% of the available space.

Today I'm contacted by one of our developers stating that yesterday she was running a query that would return an initial set of results to the ssms display within 3-5 seconds and the entire query would complete in 2-3 minutes. Today the query takes 5 minutes to complete and no results are displayed in ssms for about 2.5 minutes.

There were no other queries running at the time. CPU usage was low. I bounced the server and there was no improvement. So now I'm wondering if my tempdb change caused the decrease in performance. I can't see how, but the paranoia is setting in. I ran the query and monitored tempdb while it was executing and it did not appear to use tempdb at all which I find odd since it has a GROUP BY statement which I believe uses tempdb. The table contains about 22 million rows and the query returns about 7.8 million.

    select
      DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0) as Quarter
      ,[PartnerCD]
      ,[PartnerNM]
      ,[PartnerGRP]
      ,[BizMemberID]
      ,[MemberID]
      ,[BizType]
      ,[RateCD]
      ,[Rate]
      ,sum([MemberMonth]) as MemberQuarter
      ,[CurrentAge]
      ,[AgeAtTime] = CASE WHEN dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') < 0 THEN NULL ELSE dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') END
      ,AgeCategory = dbo.fn_CalculateAgeCatYrsOrdered(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)))
      ,[BirthDT]
      ,[ZipCD]
FROM [Partner].[dbo].[Membership]  
group by DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)
      ,[PartnerCD]
      ,[PartnerNM]
      ,[PartnerGRP]
      ,[BizMemberID]
      ,[MemberID]
      ,[BizType]
      ,[RateCD]
      ,[Rate]
      ,[CurrentAge]
      ,[BirthDT]
      ,[ZipCD]

Is it likely that my change caused the decrease in performance or is it probably coincidental?

J.D. · Accepted Answer · 2022-12-09T17:43:16.800

I did however restart SQL services after making the change. I thought that was the proper action to take after though. I guess maybe it could cause the query to make a new, less efficient execution plan?

Bingo. That's why I specifically added that clause in my comment.

When you restart the SQL Server service (or the server itself which obviously restarts the service too) the query plan cache gets cleared, as mentioned in Aaron Bertrand's answer to my question on the subject. That means, the query plan that was previously cached for your query in question was now wiped, and when your SQL Server instance went to generate a new plan (based on a multitude of factors with the tables at play, how they're indexed, and how the statistics of the data has changed since the previous plan) it ended up with a different plan that appears to be less efficient.

You can clear the problematic query plan from the plan cache by using DBCC FREEPROCCACHE (the_plan_handle_of_the_bad_plan). Please see this example from the docs, on how to obtain the plan handle for that specific query plan. Re-running the query will regenerate a new plan which may or may not end up being the same plan. If it does end up being the same, then your best course of action is actually troubleshooting the performance bottlenecks which should be evident in the actual execution plan itself.

Can pregrowing tempdb hurt performance?

1 Answers1