understanding SQL server 2012 columnstore index

In the past few months, I spent some time playing with SQL server 2012 ColumnStore (CS) index. One of the tests was to apply CS index on tables with 50 million and 500 million rows. On average, query execution time reduced from 10 - 15 minutes to less than 10 seconds. Realized later that even with CS, you might still need to apply tranditional row-based indices to maximize query optimization. One simple way is to let the query optimizer decide when to use which. In general, CS improves performance on large tables using joins and aggregations. For look-ups, the row-based index doing seeks still works faster in many cases.

CS works so fast due to its in-memory technology. It compresses tables on columns and puts them in-memory as a whole. For columns with many repeating values, if they are integers, date-times and numerics with precisions less than 18, they are compressed the most. If you inlcude many text fields with high cardinality, the CS compression won't help much. These texts would also increase storage spaces and consume lot of memories. Had one interesting observation that when I was opening a tabular project in visual studio 2010, the tabular was loading into memory instantly. It consumed about 2 GB of memory for about 1.5 GB of data.

SQL server 2012 SSAS tabular and PowerPivot have built-in CS indices. Tabular runs on a server, while PowerPivot runs on a client PC. If you import millions and millions rows to a PowerPivot into your Excel, you need minimum 4 GB RAM based on Microsoft, 6 or 8GB based on my testing. Your PC must have 64-bit OS.

Some other basics learned about CS -

- CS cannot be applied to views

- After you apply CS to a table, the table becomes read only.

- CS cannot be combined with page / row compression.

reference - http://msdn.microsoft.com/en-us/library/gg492088.aspx

Customize SharePoint top navigation bar by working with Master page, CSS and JQuery

Worked on SharePoint 2010 top navigation drop-down menu for a project lately. It was quite straightforward to use SharePoint designer to edit master page. However, don't overwrite the original v4.Master page without making a copy for roll-back. Enabling the drop-down menu turned out to be easier than I thought. All you need to do is to update SharePoint:AspMenu - ID = "TopNavigationMenuV4" ' s attribute "MaximumDynamicDisplayLevels" from "1" to "2". Done. You can then update CSS combined with JQuery to apply some visual effects and themes. Make sure to register your new CSS file in the master page and provide a correct order. Usually you would order your custom CSS file running after the coreV4.CSS file. The class Dynamic is used for UL and LI to apply drop-down menu themes.

One thing got me is that after I completed the master page, I didn't realize that I needed to publish it as a major version at least once to the master page document library for approval. Once it is approved, this new master page then becomes available for site collection, sites and sub-sites.

It's nice to make a face lifting for your entire site by re-using the traditional .net master page technology. :)

SharePoint 2010 meta-data service

Spent some time to understand SharePoint 2010 meta-data service and the steps to activate it. The meta data service works as the rest of the SharePoint services by creating a service proxy in your farm. Besides, in order to make this available for your site collection, you need to grant site app pool service account permission to the newly created meta-data service. Otherwise, you would get an error message telling you that the service proxy is unavailable.

Then, you can start adding users to be term store administrators for managing and approving document keywords for tagging. They can be built in hierarchical structure to mimic your organizational chart. This tree structure can be used as site menu for navigation. Moreover, these keywords become available instantly in search box. Nice!

SharePoint 2007 does not have this feature. In my opinion, it is hard to claim your site as a content or document management system without a customizable meta-data search-engine. ;)