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
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 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 f.(Lineage Key) between DATEDIFF(SECOND,CONVERT(DATE,GETDATE()),CURRENT_TIMESTAMP) -300 AND DATEDIFF(SECOND,CONVERT(DATE,GETDATE()),CURRENT_TIMESTAMP) ORDER BY (Transaction Key)
Actual query has inner join to almost 10 more tables.
Lineage_key is INT NOT NULL and we have NC index on this column as the only leading key
Is there something i can do with Query tuning as index look good?
Ran wait stats for 10 min interval when same query ran 8 mins and other time 22 secs. Both queries i ran from SSMS.