0

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?

3
  • I think unlikely your change was related unless you restarted the server or SQL Server service by any chance? Best way to understand what did change that affected the query would be to compare the actual execution plan of when it was faster (I'm assuming you don't have it unless you have a monitoring tool or Query Store enabled) to the actual plan of it now. If you can supply at least one or both of those, we can probably explain at least the bottleneck(s) that are slowing it down currently. Btw you can upload the execution plan to Paste The Plan.
    – J.D.
    Commented Dec 9, 2022 at 2:47
  • You are correct that I do not have any execution plans for the query prior to the changes to tempdb. I did test the query on a test server and the performance was the same. I also looked at some other jobs that run on that server daily and the run times have not changed. I'm getting a bit more confident that the tempdb change didn't have any negative impact. Just needed a 2nd opinion. 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?
    – Don
    Commented Dec 9, 2022 at 16:50
  • 1
    That query to get the plan handle is super helpful too. I think that did it. Not back to the original times that the developer was claiming, but definite improvement.
    – Don
    Commented Dec 9, 2022 at 22:23

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.