Pain: Performance tuning
SQL DBA’s email to SQL Developer “Your query is too slow and eating up server resources. Please tune it”
Senior SQL DBA’s email to SQL Developer “Your query is too fast. Slow it down :P”
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