How do you use MAXDOP in a View?

I have several views that are used to export data from SQL-Server into .csv files. They are all executed via SSIS packages. Our DBA has decided to set the "Max Degree of Parallelism" to 1 and told me to use MAXDOP where I think it makes sense.

I have now seen many times that MAXDOP really helps to pull big data amounts (especially when it results in a table scan over all partitions).

Questions

• How can I use MAXDOP in a view ?

• Is there a way around this restriction ?

An indirect answer... based on comments to the question

This sounds like "multi core" is being confused with "logical processors" in some way

A "core" is a discrete processor in it's own right and has it's own (more or less) resources. A "socket" consists or 1 or more cores. So you can have 2 sockets with 12 cores between them

Each core may be hyperthreaded to give an extra "logical" processor: this shares resources on that core. So the 12 cores above may be 24 "processors" or "CPUs" to the OS.

SQL Server doesn't differentiate: it sees all "CPUs" presented by the OS as equal. A parallel query on 2 "CPUs" or 2 concurrent queries each using a single "CPU" may actually be running on the same core but using HT. This is where you'll get resource contention on the caches and pipelines.

I would consider disabling HT (and have done) before restricting parallelism (never done it at the instance level). But only with testing.

Just see the top few answers from Google for example

http://www.google.co.uk/search?q=sql+server+hyper+threading

StackExchange Database Administrators Q#8774, answer score: 4