Columnstore index – SQL Performance

Pain: Performance tuning

SQL DBA’s email to SQL Developer “Your query is too slow and eating up server resources. Please tune it”

Dream:

Senior SQL DBA’s email to SQL Developer “Your query is too fast. Slow it down :P”

Fix:

Well there are thousands or should I say tens of thousands of ways to do performance tuning of SQL queries. Almost every SQL developer has to do it.

I will just cover one out of the box feature called ColumnStore Index  introduced by Microsoft SQL Server 2012.

As we all know, Indexes are used to help SQL engine to create efficient query plans thus giving optimal result. So I wont go into basics of indices here as its a big subject as a whole.

To understand columnstore index, first we need to understand how index works.

Prior to SQL Server 2012. whenever a table was applied with index (for particular column or set of column), the data was stored in rows, but indexed (bit confusing O.o). In over-simplified terms, before SQL Server 2012, data was stored in pages and looked similar to the one we see in grid format in management studio. So if say ID column has clustered index, the data for ID column will span across pages as there will be other columns too who will eat up space on that page. But columnstore index will save all the data, for specific column only, on whom the index is applied, on a single/consequent page. This will, drastically help the SQL Server Database Engine to scan the data effectively and build optimal plans around it.

Needless to say this is much helpful in DW as data wont be updated frequently.

I hope that I have created some craving about this feature

Recommended further reads MSDN and Simple-talk

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s