option (maxdop 1)

Question

Post reply

option (maxdop 1)

email-935809

Old Hand

Points: 329
More actions
August 10, 2010 at 3:27 am

#222409

I've noticed some blocking sessions caused by the following SQL but it's nowhere to be found in my SP's and Exe's. Anyone know what this is? Is it stats / indexes being rebuilt?
insert [dbo].[CLIENT] select * from [dbo].[CLIENT] option (maxdop 1)

Viewing 8 posts - 1 through 7 (of 7 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

MAXDOP is an option with which you can control the Maximum Degree of Parallelism. Details on the query hint is here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

You've got a query that is selecting everything from the client table to insert into the client table... I'd figure out who was doing that right away if it were my system.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

email-935809 Old Hand Points: 329 More actions · Answer 3

It's seems to be a network service that is running it from the server itself. I've installed ideara diagnotic manager recently but other than that no SP's, any batch SQL that is going it.

Would be the index being rebuilt? occurred around the index rebuild time last night.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

email-935809 (8/10/2010)
It's seems to be a network service that is running it from the server itself. I've installed ideara diagnotic manager recently but other than that no SP's, any batch SQL that is going it.
Would be the index being rebuilt? occurred around the index rebuild time last night.

No, I'm pretty sure that wouldn't be part of a normal index rebuild, I don't think. I sure don't recall ever seeing that before.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

bugg SSCertifiable Points: 7567 More actions · Answer 5

email-935809 (8/10/2010)
I've noticed some blocking sessions caused by the following SQL but it's nowhere to be found in my SP's and Exe's. Anyone know what this is? Is it stats / indexes being rebuilt?
insert [dbo].[CLIENT] select * from [dbo].[CLIENT] option (maxdop 1)

I know this is a very old post, I just had the same query execute on my server as wondering what it is? Is this Index rebuilding or mirroring? There aren't any duplicate records in the table and it is being called by the 'sa' user.

bugg SSCertifiable Points: 7567 More actions · Answer 6

Looks like this happened at same time as the index rebuild under the same spid.

Has anyone else seen this before, that can perhaps provide a little more insight?

Thanks

robert.walker 79049 SSC Rookie Points: 45 More actions · Answer 7

This is normal for index rebuilds - or at least it is on our systems. I always found it odd that it doesn't show the actual SQL DDL query that was executed.

It will of course block unless you use the ONLINE = ON option, and that's only available in the Enterprise edition. If you're not worried about the performance impact, you can probably speed it up by changing the query to use MAXDOP = 0. That will of course be at the expense of higher CPU and IO usage.