Simple Query Running More than 24 hours

Sampson99 2014-08-13 16:14:19

Hello –

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
FROM [DB1].[dbo].[Daily_Files]
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!

Thanks!

Kevin 2014-08-13 16:32:40
Sampson, Since you don't mind posting the SQL for the query in the stub, could you please rerun the query without anonymizing it? Also, an actual plan would be extremely helpful. Thanks!

-Kevin

Sampson99 2014-08-13 16:36:21
I can't provide an actual plan because the query will not run. I wish I could.
SQLkiwi 2014-08-14 04:35:53
Kevin's asking for a non-anonymized estimated plan (i.e. don't click the anonymize box when uploading) not an actual plan.
Chris Wilson 2014-08-13 16:30:36
Sampson99,

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'
Sampson99 2014-08-13 16:38:01
Thanks Chris
Sampson99 2014-08-14 00:45:02
I just tried this to see how it would work and it won't run either! As soon as I add Inclusion_Ind = 'Y' to the WHERE clause it hangs.
Sampson99 2014-08-14 00:52:31
It keeps getting more strange. If I change it to Inclusion_Ind = 'N' it runs in 30 seconds.
tjosh52 2014-08-25 19:18:06
What is your cost threshold for parallelism set to? If it is the default of 5 you should look at increasing it to 40 or 50 on a test system and then push to production.

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.