Simple Query Running More than 24 hours
I have attached an anonymized plan for a query off a view that is taking > 24 hours to run. The underlying table has about 65M rows. The server storage contains 20 internal SAS drives in Raid 10 with two 8 core processors and 96 GB RAM.
The command text is as follows:
SELECT COUNT(*) AS TRX
WHERE Inclusion_Ind = 'Y' AND
YearMonth BETWEEN 201401 AND 201403
If I remove the "Inclusion_Ind = 'Y'" from the query it runs in 30 seconds. If I group by Inclusion_Ind it runs in 30 seconds. But the second I add the Inclusion_Ind into the WHERE clause it will not run.
Looking at the wait types I am seeing CXPacket with a wait time of 327,806 and a total elapsed time of 81,327,700
Any guidance would be greatly appreciated!
I am not sure why the query is doing what it is doing. Hopefully someone can come in and answer that question and you might get a better query than mine. The following though should work to get the data filtered out first and then filter on the remaining.
SELECT TRX FROM ( SELECT Inclusion_Ind, COUNT(*) AS TRX FROM [DB1].[dbo].[Daily_Files] WHERE YearMonth BETWEEN 201401 AND 201403 GROUP BY Inclusion_Ind ) AS InclusionCnt WHERE Inclusion_Ind = 'Y'
Check your maxdop. MAXDOP should be set to the size of one NUMA node which for you would be 8.
Update stats on the table.