Max DOP > 13 get stuck on one env and works fine on another env

Question

Max DOP > 13 get stuck on one env and works fine on another env

y.loewidt

SSC Rookie

Points: 37
More actions
December 7, 2010 at 9:56 am

#242172

Hi Guys,
I have a bizzare issue.
Lately one of our ETL Engines started getting stuck, after some exploration we found that it is due to MAX DOP option which is set to 0 in one operation. some more exploration revealed that any use of MAX DOP over 13 CPU gets stuck. which is wierd because when we run the same exact command on our bench server, which has the same specifications and configuration as our Test server, every thing runs fine.
Any one has a clue ?
Thanks
Yonatan.
For example:
The following code runs fine on both Test\Bench
select count(1)
from HRDWreportstemp.dbo.DIM_Person A
join HRDWreportstemp.dbo.DIM_Person B on a.emplid = b.Emplid
option(maxdop 13)
The following code get stuck on test and works fine on Bench:
select count(1)
from HRDWreportstemp.dbo.DIM_Person A
join HRDWreportstemp.dbo.DIM_Person B on a.emplid = b.Emplid
option(maxdop 14)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

same windows and sql server hot fixes?

I guess this could be affected by something like fragmentation or location of files or buffer space available/what is already cached.

Think I'm saying I don't know.

I assume the query plans are the same.

Try looking at the disk io + cpu usage when they work.

Cursors never.
DTS - only when needed and never to control.

y.loewidt SSC Rookie Points: 37 More actions · Answer 2

Yes, every thing is the same, plans are the same.. Acctually i have a new lead, i found that for some reason the following query :

SELECT CASE WHEN '1' IN(SELECT '1') THEN '1 IN 1' ELSE '1 NOT IN 1' END, CASE WHEN '1' IN(SELECT NULL) THEN '1 IN NULL' ELSE '1 NOT IN NULL' END

when ran create a query that cannot be killed and i guess captures a core.

when i ran it again(for the fourth time) my working max dop went from 13 to 12...

Still havent resolved this issue..

Does any one know how can i kill this process ? "Kill SPID" merely makes the SP status KILLED/ROLLBACK but doesnt kill it + ROLLBACK Completion stuck on 0%..

Thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

y.loewidt (12/7/2010)
"Kill SPID" merely makes the SP status KILLED/ROLLBACK but doesnt kill it

"Kill" always results the data in consistent state;either commit or rollback

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)