August 18, 2015 at 8:15 pm
Comments posted to this topic are about the item columnstore index maxdop
August 18, 2015 at 11:34 pm
This was removed by the editor as SPAM
August 19, 2015 at 3:28 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 19, 2015 at 3:53 am
I am happy to see questions about columnstore indexes, because I really love this feature. (I love everything that makes SQL Server go faster, actually).
But I am not happy to see errors in those questions.
I was able to pick the answer that is worth a point be process of elimination. There is no such thing as a CSMaxdop setting, so that left just the two replies with maxdop during index creation; maxdop=1 is clearly wrong because that removes any limit; and maxdop=1 does indeed limit execution to at most one processor --> serial.
But that hint only affects the creation of the columnstore index. Nothing else. The question suggests that if you create the index with maxdop=1, then any future queries that use the columnstore index will also be serial. That is simply not true.
If you want to "prevent parallel plans from being generated for my columnstore index in SQL Server 2012", then the only way (*) to do this is to add MAXDOP=1 to each and every one of those queries, or to set the instance wide "max degree of parallelism" option. The latter is not recommended because this will also prevent parallelism on all other queries in all instances on the database (unless explicltly hinted to use a higher degree of parallelism).
Also, I have no idea why you would ever want to prevent parallelism on queries that use columnstore indexes. When running in parallel, columnstore-using queries can use "batch mode" processing, which is way faster than the traditional row mode processing. On SQL Server 2012 and 2014, serial processing prevents batch mode (this limitation is lifted in the current SQL Server 2016 CTP versions, let's hope that we'll see this same improvement in RTP). So you really *WANT* your queries that use the columnstore index to run in parallel, not prevent it!
(*) I guess you could also set up a clever scheme with Resource Governor. When you find a way to ensure that all queries that use the columnstore index go into a specific resource group, you can limit that group to prevent poarallelism. But it would be quite tricky to pull this off - and again, no idea why you would even want to do this.
August 19, 2015 at 5:08 am
This was removed by the editor as SPAM
August 19, 2015 at 5:19 am
Thanks for the very clear question. I was looking for the trick. 😉
August 19, 2015 at 6:46 am
Hugo Kornelis (8/19/2015)
I am happy to see questions about columnstore indexes, because I really love this feature. (I love everything that makes SQL Server go faster, actually).But I am not happy to see errors in those questions.
I was able to pick the answer that is worth a point be process of elimination. There is no such thing as a CSMaxdop setting, so that left just the two replies with maxdop during index creation; maxdop=1 is clearly wrong because that removes any limit; and maxdop=1 does indeed limit execution to at most one processor --> serial.
But that hint only affects the creation of the columnstore index. Nothing else. The question suggests that if you create the index with maxdop=1, then any future queries that use the columnstore index will also be serial. That is simply not true.
If you want to "prevent parallel plans from being generated for my columnstore index in SQL Server 2012", then the only way (*) to do this is to add MAXDOP=1 to each and every one of those queries, or to set the instance wide "max degree of parallelism" option. The latter is not recommended because this will also prevent parallelism on all other queries in all instances on the database (unless explicltly hinted to use a higher degree of parallelism).
Also, I have no idea why you would ever want to prevent parallelism on queries that use columnstore indexes. When running in parallel, columnstore-using queries can use "batch mode" processing, which is way faster than the traditional row mode processing. On SQL Server 2012 and 2014, serial processing prevents batch mode (this limitation is lifted in the current SQL Server 2016 CTP versions, let's hope that we'll see this same improvement in RTP). So you really *WANT* your queries that use the columnstore index to run in parallel, not prevent it!
(*) I guess you could also set up a clever scheme with Resource Governor. When you find a way to ensure that all queries that use the columnstore index go into a specific resource group, you can limit that group to prevent poarallelism. But it would be quite tricky to pull this off - and again, no idea why you would even want to do this.
Agreed. Columnstore indexes should be allowed to use parallel. My only guess for people wanting to limit it (and why 2016 now allows batch in serial) is for those edge cases where the warehouse does not have more than maybe 2 processors. - :pinch:
One small correction to Hugos response. Hugo probably got typing too fast and really meant MAXDOP = 0 removes any limitation instead of the maxdop = 1.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2015 at 7:25 am
Before answering the question I read the documentation at https://msdn.microsoft.com/en-us/library/Gg492153.aspx and took the MAXDOP setting upon index creation to only affect the index creation. Steve's question seemed to ask how to prevent any queries that are using the index from going parallel. Therefore, I feel the answer should be to set MAXDOP = 1 on the instance.
August 19, 2015 at 11:36 am
Started my day with point # 1.
Thanks.
August 20, 2015 at 12:26 am
I am using this MAXDOP option occasionally 2 disable parallel plans 4 some of my long running & high CPU usage queries & SP`s. It`s useful sometimes, but never used it b4 while creating a columnstore index, thanx very much 4 the question.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
August 20, 2015 at 8:23 am
Hany Helmy (8/20/2015)
I am using this MAXDOP option occasionally 2 disable parallel plans 4 some of my long running & high CPU usage queries & SP`s. It`s useful sometimes, but never used it b4 while creating a columnstore index, thanx very much 4 the question.
Love the shorthand.
August 20, 2015 at 2:02 pm
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply