Thursday, 11 July 2013

Question: When is a columnstore index not a columnstore index?


When its a rowstore index.


When its not been rebuilt.



The answer is on MSDN – “The updateable columnstore first inserts new data into an OPEN rowgroup, which is in rowstore format” –> Yes, rowstore, not column store.

MSDN says re-organising the index will put it in columnstore format, but in CTP1 this doesn’t work. You need to rebuild the index, not a problem because we are using developer edition (or enterprise edition).

I’ve blogged here what happens when you insert into a column store index here

Tuesday, 9 July 2013

Column Store Index Life Cycle

In my series on the Column Store Index, I thought I would blog on what happens when you insert/ delete into a column store index.

1) Lets insert some rows into a table with a column store index.


2) Rebuild the Index. The state of index changes to Compressed from Open and size_in_bytes is populated.


3) Insert some more rows. We now have two rows, one ‘OPEN’ and second ‘COMPRESSED’


4) Delete some rows. The deleted rows appear to have come from the ‘COMPRESSED’ row.


5) Rebuild the index again.



6) When I updated 1 row in the COMPRESSED table, the engine deleted the row and added a new row in the OPEN / delta table.



The page on MSDN about the sys.column_store_row_groups system table , explains why the 2nd table / delta table or delta row set is used , how and when data is moved from open of the threee states; ‘OPEN’,’COMPRESSED' and ‘CLOSED’ – I’ve not seen ‘CLOSED’ yet.

Clustered Column store index recap

Last week I blogged on the Clustered Column store index, I’ve been playing with it – its very early days, as its a feature of SQL2014 CTP1.

Just to recap.  I noticed that something wasn’t working as I expected.


The number of rows returned from the report ‘Disk usage by table’ is incorrect, shown by the top results set, the correct number is shown from select count (*) query and the select from sys.partitions.

Also notice on sys.partitions, the compression description.


So how do I get the standard report to show the right data?

Well, restarting the instance doesn’t help.

But what about rebuilding / re-organising the index? Re-organising the index does nothing, but rebuilding the index works!


All the counts are returning the expected number of rows, now everything is alright with the world.

Wednesday, 26 June 2013

Very interesting things about updateable column store indexes

After more digging, I’ve found a link on msdn to information about the new system table sys.column_store_row_groups.

This bit “The hobt_id for delta row groups, or NULL if the row group type is not delta. A delta row group is a read/write row group that is accepting new records. A delta row group has the OPEN status. A delta row group is still in rowstore format and has not been compressed to columnstore format.”

So this sounds like there are still two tables [or row groups], the delta row group which you can read and write into and a 2nd row group which is compressed.

This is the work-around people have been using with the non-updateable column store indexes to make to work like they are updateable, but its happening behind the scenes. 


Very very nice.

More interesting things about the Clustered Column store index..

I’ve previously blogged on some interesting things about the clustered column store index or CCSI for short.

I’ve found my CCSI in an internal table.  sys.Column_store_row_groups


Noticed the size_in_byte…NULL

Just for fun, I tried to create a 2nd column store index on the same table. (Non-clustered of course)


So we can’t add a non-clustered column store index (NCCSI) to a table that already has a CCSI.

But can we add two NCCSI to a table? The answer is no, but the UI doesn’t let you get that far.  Its non-selectable.. interesting..


And more digging….

Interesting thing about updateable Clustered Column Store Index

I downloaded SQL Server 2014 CPT1 last night and I’ve started playing with it.

I’m really interested in the updateable clustered column store index, so I thought I would see if it works in the CTP1 (not all features are released in the CTPs)

I created two identical tables, added a Clustered Column store index to one of them, and inserted 15000 rows into both of them.

Guess what, it only bloody worked! Excellent! Happy bunny.

So the next step, how does it work? While looking at the IO for the insert, I stumbled on this..


TestTB_1 has 15,000 rows and TestTB_2 has 0 rows… Wait a second, that’s not right.


No – There are 15,000 records in both tables.


Wow – check out the logical reads,physical reads…etc…etc..

All coming back as 0 – I guess the column store engine isnt reporting back in the same way the normal engine is.

This will get fixed at some point, no doubt, but the fact that the column store index was updated is so cool…

I’m going to do some more digging now…

Monday, 10 June 2013

Going to SQL Relay?

Going to SQL Relay? Show some support and impress your friends and colleagues with these two badges.  Not going to SQL Relay? Why not! Go to the SQL Relay web site and find out where your nearest event is.

“I’m speaking at SQL Relay”

<a href=""><img src=""></a>

“I’m going to SQL Relay”

<a href=""><img src=""></a>

Wednesday, 15 May 2013

How many rows in that table?

How many of us have typed ‘select count(*) from TABLE’ when we want to know how many rows in a table?

There is another way, by querying sys.partitions. 

I’ve compared the performance and IO.

   7:  SELECT COUNT(1)
  10:  DECLARE @objid AS BIGINT;
  12:  SELECT @objid = object_id
  13:  FROM   sys.tables
  14:  WHERE  name = 'MYBIGTABLE';
  16:  SELECT SUM(rows)
  17:  FROM   sys.partitions
  18:  WHERE  index_id IN (0, 1)
  19:         AND [object_id] = @objid;

The results from STATISTICS IO and TIME are as follows;

Table 'MYBIGTABLE'. Scan count 1, logical reads 115328, physical reads 0, read-ahead reads 115328, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 27628 ms,  elapsed time = 61186 ms.

So over a minute.

For the query on sys.partitions;

Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 140 ms.

(1 row(s) affected)
Table 'sysrowsets'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 35 ms.

So 140+35=175ms, so less than a quarter of a second compared with 61 seconds.

Much much much much much much faster!

Monday, 8 April 2013

Technology: Why I Hate and Love you so…

I word in IT, I love and hate technology with equal passion…

Technology makes our lives better, wireless networks, cheap computers, mobile phones that can make calls / play games and make a terrible cup of coffee… We can do more..cheaper.. 

But its the most frustrating thing in the world, when the iPad doesn’t find the airport printer…What do you do? Reboot everything….if that doesn’t work, setup everything from scratch again…if that doesn’t work? Pray to the God of Calculators… if that doesn’t work??? ? ? ? Hours later… still doesn’t work, even after selling my soul to the God of Calculators….

But wait a second, the iPhone can see the airport printer… so its a problem with the iPad..

At that point I give up and print from a PC {wirelessly}– several hours of my life I will never get back….

Viva technology!!

More Blogging…

This is my second post where I state my intention to blog more…’this time is personal’ (tag line from Jaws: The Revenge’ )…

Just like everyone else I have blog ideas that fade into nothingness, either I forget it and move on or it takes so long to write something witty and informative that it just gets binned…

So I will try and keep then short and sweet, amusing if possible, but unlikely. But most of all, actually blog something.