Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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 ?
share|improve this question
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
3  
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

2 Answers

up vote 8 down vote accepted

You can't.

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

share|improve this answer
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
up vote 4 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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