4

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 ?
3
  • What was the reason the DBA gave for the server level setting?
    – gbn
    Dec 5 '11 at 14:08
  • Well he said he saw lots of cxpacket and that sql server best performs if only 1 core is active. And that if I use maxdop I should only use 50% of the available core.
    – nojetlag
    Dec 5 '11 at 15:05
  • 4
    Your DBA made the wrong choice. Setting MAXDOP serverwide may help certain processes, but punishes all others that would benefit from multiple CPU's being available.
    – datagod
    Dec 5 '11 at 18:31
10

You can't.

A view is a stored sub-query. MAXDOP is a query hint.

2
  • to bad I touhgt there might be a work around, like for the "order by" restriction. For this export szenario (via SSIS) it is very helpful.
    – nojetlag
    Dec 5 '11 at 15:06
  • 2
    That work around for ORDER BY doesn't work though. There's never a guarantee that you'll get a result set in a particular order unless you use ORDER BY on the outer query.
    – Rob Farley
    Dec 5 '11 at 22:21
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.