I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes.

This is not a store proc , he is doing it manually from his machine to get data and later after extraction just push it to other application for data visualization

Below is the Query i tried to create which is similar to my user query, logic wise. But since i am still figuring out how to run ETL package on WideWorldImporter database to get it up to date, cant really repro the run time.

For my env: Fact table is around 6 TB. What i have noticed is around 10K rows fetched is varying between 10-30secs while anything over 60K rows goes over 10 mins

Query below

Select * from  [Fact].[Transaction] f
INNER JOIN  [Dimension].[Customer] dc  on dc.[Customer Key] =f.[Customer Key]
INNER JOIN  [Dimension].[Date] dd on dd.[Date] =f.[Date Key]
INNER JOIN  [Dimension].[Supplier] ds  on ds.[Supplier Key] =f.[Supplier Key]
INNER JOIN  [Dimension].[Transaction Type] dt  on dt.[Transaction Type Key] =f.[Transaction Type Key]
INNER JOIN [Dimension].[Payment Method] pm on pm.[Payment Method Key] =f.[Payment Method Key]
WHERE f.[Date Key]>= convert(varchar,getdate(),112) and 
ORDER BY [Transaction Key]

Actual query has inner join to almost 10 more tables.

enter image description here

Lineage_key is INT NOT NULL and we have NC index on this column as the only leading key

Index:- enter image description here

Is there something i can do with Query tuning as index look good?

Plan:- I can paste the part of plan as captured, rest it cant fit due to 30 odd inner joins.

enter image description here


I tried to look at the plan which is too complicated chain as there are 30 odd inner joins. So would try my best to paste. But i found something interesting. I am seeing that compile time is around 8 secs in total of 14 sec run. So i think problem seems more on very high compile time. Is there a way we can avoid compilation time when query has joins with so many tables?

Ran wait stats for 10 min interval when same query ran 8 mins and other time 22 secs. Both queries i ran from SSMS.

enter image description here

  • 1
  • Also while or after running the query check it's session's wait stats: docs.microsoft.com/en-us/sql/relational-databases/… – David Browne - Microsoft Feb 11 at 20:04
  • @ErikDarling: I do have an exec plan from prod but due to PI compliance cant show here. I am trying to replicate my problem on wideworldimporter database since schema wise they look bit same. However data loading in this sample database i am still wandering to get it up to date as default one seems loaded with 2013 only. – Newbie-DBA Feb 11 at 20:23
  • @DavidBrowne-Microsoft, Sure let me update question once i have handy – Newbie-DBA Feb 11 at 20:23
  • @Newbie-DBA You can anonymize the execution plan with SentryOne Plan Explorer and then post it here since it is not the same as the original plan and won't have any identifiable information. – J.D. Feb 11 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.