SQL Server Quickie #40 – ColumnStore Indexes

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I have uploaded SQL Server Quickie #40 to YouTube. This time I’m talking about ColumnStore Indexes in SQL Server.

Like or share to get the source code.

Thanks for your time,

-Klaus

8 Comments

  • Nice Quickie Sir πŸ™‚

    I have a question which is not related to this but hope you will help me out.

    What is xp_userlock does and its running so frequently on one of my SQL server 2008R2.

  • Klaus Aschenbrenner

    Hello Vimal,

    You can find here more information about it: http://lmgtfy.com/?q=xp_userlock πŸ˜‰

    -Klaus

    • Thank You so much for your quick response. πŸ™‚ I already did google but not able to relate it with my scenario. I am getting blocking on my server because of this πŸ™

  • Thanks Klaue.

    Nice and simple presentation and good to know what these different indexes can do. Is there any downside to these sorts of indexes or do they perhaps use more data space ?

    • Klaus Aschenbrenner

      Hello Andrew,

      Thanks for your comment.
      There are some performance implications that you have to verify, but normally the ColumnStore Index should give you a great performance in reporting workloads.

      -Klaus

  • Qaiser's

    Can we Create normal clistered index along with column store clustered index?

    • Klaus Aschenbrenner

      No, because there can be only 1 Clustered Index per table.

  • Tomasz

    Hi Klaus,

    I’me testing the clustered columnstore index right now.
    I’m using the SSDT from VisualStudio 2013.5 for the development.

    When I tried to add a columnstore index:

    CREATE CLUSTERED COLUMNSTORE INDEX ccsidx_yyy ON [dbo].[yyy];
    GO

    I get an error message that:

    Filegroup [XXX] cannot be specified on Table: [dbo].[yyy] when there is the clustered Column Store Index.

    Whats stange that when I ran this command from SSMS the clustered columnstore index has been added to the dbo.yyy table which is using FileGroup.

    Is there any way to solve this issue without moving a table do a different FileGroup (PRIMARY) or to place clustered columnstore index on the whold FileGroup (I’ve found such proposition as well) ?

    Tomasz

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top