SQL Server 2012 introduced non-clustered columnstore indexes, and SQL Server 2014 gave us clustered columnstore indexes. Both share the same technology for performance boosts, and they both share the same algorithms for compression. However, the compression will depend on the data you are storing. SQL Server uses a mechanism of row groups, segments, encoding and compression to store the data for columnstore indexes. First the data is horizontally divided into row groups, where each group contains approximately 1 million rows. Next, the row groups are vertically partitioned into segments, where each column of the table is its own segment. Those...
Continue reading...What is a Non-Clustered Columnstore Index?
First introduced in SQL Server 2012, the Columnstore index is a new in-memory feature that allows for the creation of indexes that are stored in a column-wise fashion. It is targeted for data warehouses and can improve query performance by 10 to 100x. A columnstore index stores data in a different type of index page as well as heavily compressing the data. It also introduces a new batch execution mode that greatly increases processing speed and reduces CPU utilization. The combination of the new storage type, the compressed data, and batch mode processing allows SQL Server to read less data...
Continue reading...The system_health Extended Event Session
When I first started poking around in SQL Server 2012, I noticed an extended event session called “system_health” was created by default. It took me a few months before I really dug into the session details to see what it was capturing. But once I did, I was pretty amazed. The system_health session starts up by default and collects various performance points that can be used to help troubleshoot issues on the server. To look at the details of it, just right click on the event session and choose properties. This will open the Extended Events UI. Select the events...
Continue reading...How Long is that SQL Command Going to Take?
Have you ever needed to restore a large database while someone is standing over your shoulder asking “How long is that going to take”? If that hasn’t happened to you yet, then it’s only a matter of time. Let’s throw out all the reasons why you need to do the restore and just discuss the technical part. Obviously the easiest way to know how long the restore will take is to use the “WITH STATS” option in the restore database command. But let’s say in the heat of the moment you forgot that little piece of the statement. Now what?...
Continue reading...My Experience Aboard SQL Cruise 2014
Where do I begin? First let me say, WOW what an experience! How it All Began When I first heard about SQL Cruise way back in 2012, I thought the idea of hosting training sessions aboard a cruise ship was a swell idea. However, talking my wife into going with me on the cruise or even letting me go on my own was next to impossible. Don’t get me wrong, my wife and I love cruising and we even took a cruise to our destination wedding in Bermuda. But no matter how I argued, my wife would not budge. We...
Continue reading...
