Problem
Indexes are very useful in SQL Server when it comes to row level sorting, there is also an option in SQL Server known as columnstore indexes which can be used for column level sorting. We will see how to implement column level sorting in this tip. Also, when we Insert, Update and Delete records, SQL Server indexes tend to get fragmented which will impact the efficiency of SQL Server queries. We will also see how to rebuild indexes to overcome fragmentation issues.
Solution
We will create a columnstore index as a clustered columnstore index. The index can be created during the creation of the table. Unlike row level indexes, columnstore indexes do not require the column names in the indexes of the corresponding table. Clustered columnstore indexes operate on the entire table which is at the data page level. In general clustering is arranging data in a specific order, so when using row level indexes, we will sort the rows of the indexed column or column(s) specified in the particular index. But when it comes to a columnstore index, the clustering is done on the column followed by each column.
Create SQL Server Columnstore Clustered Index
We will see how to define a columnstore clustered index for kids1 table. The below code can be used to create the table “Kids1”. This code will create 4 columns for our example.
CREATE TABLE [dbo].[Kids1](
[RoomNo] [numeric](8, 0) NOT NULL,
[RoomName] [varchar](20) NOT NULL,
[RoomLink] [numeric](8, 0) NULL,
[Sex] [nchar](6) NULL
) ON [PRIMARY]
GO
In SQL Server Management Studio (SSMS), expand the Kids1 table. Under Indexes, right click and select New Index and then Clustered Columnstore Index as shown below.
After clicking Clustered Columnstore Index, we will see the below screen. If you notice there are no columns displayed on the page. A Columnstore index does not accept column names. I just gave the index a name of CCS-Kids1 and clicked OK.
Once clicking OK, we will see the new Clustered Columnstore index is created as shown below:
Below is the code that is generated by the script generator in SSMS for your reference. The code will create a clustered columnstore index for the table Kids1. You will notice that there are no columns from the Kids1 table that are specified.
CREATE CLUSTERED COLUMNSTORE INDEX [CCS-Kids1] ON [dbo].[Kids1]
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO
That’s how we create a Clustered Columnstore Index.
Create SQL Server Columnstore Non-Clustered Index
Let’s first drop the Clustered Columnstore index that we created above using the below command.
DROP INDEX KIDS1.[CCS-KIDS1]
GO
In SSMS, expand the Kids1 table and right click on Indexes, select New Index and click on Non-Clustered Columnstore Index as shown below.
After clicking Non-Clustered Columnstore Index, we will get this screen as shown below:
Click the Add button and we will get a screen as shown below. Just like a normal Non-Clustered Index, choose the column that will be used in the index. I selected RoomNo as the column. Then click OK.
Below is the code that is generated by the script generator in SSMS for your reference. The code will create a nonclustered columnstore index for the column RoomNo for the Kids1 table. If you notice RoomNo is the column selected for the index, this is different than the clustered columnstore index.
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCS-Kids1] ON [dbo].[Kids1]
(
[RoomNo]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)
GO
Defragmentation of SQL Server Columnstore Indexes
Indexes tend to get fragmented when data is modified in the tables which may result in a performance impact during data retrieval. SQL Server offers defragmentation of indexes to overcome this issue. Let us see how this works.
We will first drop the existing table that was created above and re-create it. After that we will create a Clustered Columnstore Index and then add some sample data.
The below code will be used to create the table and index:
DROP TABLE dbo.Kids1
GO
CREATE TABLE [dbo].[Kids1](
[RoomNo] [numeric](8, 0) NOT NULL,
[RoomName] [varchar](20) NOT NULL,
[RoomLink] [numeric](8, 0) NULL,
[Sex] [nchar](6) NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [NCCS-Kids1] ON [dbo].[Kids1] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)
GO
Now that the table is ready, let’s insert data into the table with the below code. The below code will insert 1,278,576 records for our example.
DECLARE @i int = 0
WHILE @i < 1278576
BEGIN
SET @i = @i + 1
INSERT INTO [dbo].[Kids1]
([RoomNo]
,[RoomName]
,[RoomLink]
,[Sex])
VALUES
(1,'temp',1,'Male')
END
Now that the rows are inserted into the table, let’s explore the current RowGroup Level information of the columnstore index that was created. Since I have only one table with an index in my database, we can simply query sys.dm_db_column_store_row_group_physical_stats table to get the data.
SELECT * FROM sys.dm_db_column_store_row_group_physical_stats
- Delta Rowgroups work with columnstore indexes. They are useful to improve columnstore compression and performance by storing records based on a threshold number. When the maximum number is reached in a Delta Rowgroup, it closes that group and compresses the rowgroups and stores in a Columstore.
- A set of delta rowgroups are collectively called the Deltastore.
- A Deltastore is temporary storage for a clustered index.
- Deltastore improves performance and will reduce fragmentation of column segments.
- A columnstore index uses Deltastore to retrieve correct query requests.
- A columnstore index can have more than one delta rowgroup.
Let me explain our example:
For every 1,048,576 rows, a delta rowgoup is created and the rows are COMPRESSED, hence the State_Desc column shows as COMPRESSED for rows that are equal to 1048576. The left-over rows of 69,000 rows are stored in a new row with a DELTA_STORE_HOBOT_ID generated and the state is OPEN.
When SQL Server database inserts, updates or delete data to tables, the table with indexes get fragmented. The fragmentation can cause a performance issue when a query is submitted to the table. To solve the issue of fragmentation, we have an option to Reorganize/Rebuild indexes.
Let’s run the below query to get the details of fragmentation by using the system function sys.dm_db_index_physical_stats which returns the fragmentation information for the given table.
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('MyDB'), OBJECT_ID('KIDS1'), NULL, NULL , 'DETAILED')
The below screen is from the above query. This shows us as that the above insert statement create fragmentation by looking at the avg_fragmentation_in_percent column.
As we stated, any table change will cause fragmentation, so let’s delete some records and see how fragmentation is affected, by executing the below code.
DELETE TOP (1500) FROM KIDS1
After deleting rows, we can see the percentage increase in the avg_fragmentation_in_percent column.
Rebuilding an index is a solution to remove fragmentation for an index. When we use the rebuild option, the index is dropped and re-created. This will result in removing fragmentation, disk space usage is reset by compacting the pages and it reorders the index rows.
In the command below, we used the ALL keyword which will drop and rebuild all indexes in a single transaction.
ALTER INDEX ALL ON kids1 REBUILD
Now we see fragmentation is set to 0 after the rebuild.
SQL Server Index Fragmentation data using Transact-SQL
The sys.indexes and sys.dm_db_index_physical_stats views contain index details within SQL Server. The below query can be used to get more details on the indexes assigned to tables. We will see how the Kids1 table indexes are available in the tables. These two queries can be run in a query window to get information related to indexes.
SELECT * FROM sys.indexes WHERE object_id = object_id('kids1')
SELECT * FROM sys.dm_db_index_physical_stats (db_id ('mydb') ,object_id ('kids1'), null, null, null)
- fragment_count: number of fragments (physically consecutive leaf pages) in the index.
- avg_fragmentation_in_percent: logical percentage of fragmentation information will be in this column.
- avg_fragment_size_in_pages: average number of pages in one fragment in an index.
Index Fragmentation Information via SQL Server Management Studio
SSMS offers graphical details of the index fragmentation for users. Let’s see the details of the Kids1 table index that was created above. In SSMS, expand Indexes under Kids1 table as shown below.
Clicking on Properties and you will see this screen.
Click on the Fragmentation page on the left. Below are the fragmentation details for the index NCCS-Kids1. Since we rebuilt the index above, we can see that fragmentation is 0%.
Reorganize a SQL Server Columnstore Index using SSMS
Reorganizing indexes can be performed in SSMS. Expand the Kids1 table in SSMS, expand Indexes and right click on the Index name. The below screen will show us the Reorganize and Rebuild options.
Below is what we see if we select the Reorganize option for the index.
Next Steps
- Learn more about Columnstore indexes with these tips:
