Post reply

Columnstore Index metadata

  • August 27, 2015 at 12:13 am

    #320886

    Comments posted to this topic are about the item Columnstore Index metadata

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • August 27, 2015 at 1:43 am

    #1822668

    Thank you for this interesting question Hugo.

    😎

    Slightly taken aback seeing the overall results though

    Correct answers:6%

    Incorrect answers:94%

  • August 27, 2015 at 2:28 am

    This was removed by the editor as SPAM

  • August 27, 2015 at 2:35 am

    #1822676

    Eirikur Eiriksson (8/27/2015)

    Thank you for this interesting question Hugo.

    😎

    Slightly taken aback seeing the overall results though

    Correct answers:6%

    Incorrect answers:94%

    I suspect that's got something to do with how people have researched this. If you don't specifically ensure you're checking the 2012 documentation it's very easy to come away with the wrong answer. A very cunning question Hugo; it's been a double learning exercise, thank you. I also have to say there are absolutely no tricks here, I got it wrong because I didn't check something I should have.

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • August 27, 2015 at 5:19 am

    #1822710

    Thanks for a very good question. I'm still on SQL 2008, so I had to do research to find the answer. Of course, I love starting the day by learning something new.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 27, 2015 at 7:53 am

    #1822765

    Lost the point on a technicality - LOL! sys.column_store_row_groups not supported in SQL 2012.

  • August 27, 2015 at 8:39 am

    #1822785

    Got me good.

    and i thought there were no DMV's in 2012 for column index info.

    Alex S

  • August 27, 2015 at 12:03 pm

    #1822879

    Burned! I don't have a SS 2012 instance so I ran some tests on 2014 and hoped the answer would be the same. I got the correct answer for 2014, though.

  • August 27, 2015 at 1:31 pm

    #1822905

    I was so sure I was going to get this right. Should have made sure things were the same in 2012 and 2014.

  • August 27, 2015 at 2:30 pm

    #1822927

    Thanks, all, for the kind words!

    And while I realize that this may be perceived as a trick question, that was not my intention - I submitted this question to point out an inconsistency that can catch you off guarad if you are not aware of it. This can especially burn people working in a mixed environment, or consultants who constantly move between versions as they move from client to client.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • August 28, 2015 at 8:51 am

    #1823070

    Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • August 30, 2015 at 9:45 am

    #1823263

    BWFC (8/27/2015)

    Eirikur Eiriksson (8/27/2015)

    Thank you for this interesting question Hugo.

    😎

    Slightly taken aback seeing the overall results though

    Correct answers:6%

    Incorrect answers:94%

    I suspect that's got something to do with how people have researched this. If you don't specifically ensure you're checking the 2012 documentation it's very easy to come away with the wrong answer. A very cunning question Hugo; it's been a double learning exercise, thank you. I also have to say there are absolutely no tricks here, I got it wrong because I didn't check something I should have.

    Actually it's very easy to come up with the wrong answer if you look at 2012 documentation - since the right answer is listed in 2012 BOL as a "Catalog View" (specifically an Object Catalog View) and there is indeed no mechanism for getting the information using anything listed as a Dynamic Management View.

    So if we believe BOL the correct answer is Option 1, "None, this information is not exposed through DMVs" and Option 4 is wrong because that system view is NOT a DMV. (The BOL classification of system views can be seen at System Views (Transact-SQL).)

    So SQL 2012 BOL clearly says that this can't be done with a DMV. Anyone who tried to look this up in SQL 2012 BOL was guaranteed to get a "wrong" answer. People would be far more likely (1 chance in 8 instead of no chance at all) to pick an answer at random rather than look in 2012 BOL.

    But I don't really complain about the question - if I'd known the answer I wouldn't have had to look it up, and if the question had been consistent with BOL I wouldn't have noticed that "DMV" is yet another ill-defined SQL Server term.

    edit (2015/09/08): Steve has corrected the question to say "system view" insteaad of "DMV".

    Tom

  • August 31, 2015 at 2:40 pm

    #1823474

    I've got a lot to learn about a columnstore index.

  • September 6, 2015 at 4:33 am

    #1824850

    Thanks for this question.

    I have been able to give the good answer only because I remembered a SQL Server Days session about différences between columnstore indexes in SQL Server 2014 and in SQL Server 2012. The speaker learnt me that there were 2 DMV giving the repartition in row groups in 2014 instead only one on 2012. So the answer was evident.

    I must admit that I have not deeply studied columnstore indexes in the 2012 version and not all in the 2014 one.

Viewing 14 posts - 1 through 13 (of 13 total)

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