未加星标

Columnstore Indexes part 87 (“Indexed Views”)

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

Continuation from the previous 87 parts, the whole series can be found at http://www.nikoport.com/columnstore/ .

A very long time desired feature from my side was something that I have described in the Connect Item allowing calculation measures on the row group level does not seem to be getting close to the reality, or at least until the next major release, but 1 day before SQLSaturday in Munich, I have accidentally found something in the engine of the SQL Server 2016 RTM that made me very happy.

A long time requested feature of the indexed views supporting columnstore indexes was rather silently inserted into the engine and I am very excited about blogging about it.

My favourite free database ContosoRetailDW comes to the rescue once again (with the original backup being stored in C:\Install\ folder:

USE master; alterdatabaseContosoRetailDW setSINGLE_USERWITHROLLBACKIMMEDIATE; RESTOREDATABASE [ContosoRetailDW] FROMDISK = N'C:\Install\ContosoRetailDW.bak' WITHFILE = 1, MOVE N'ContosoRetailDW2.0' TO N'C:\Data\ContosoRetailDW.mdf', MOVE N'ContosoRetailDW2.0_log' TO N'C:\Data\ContosoRetailDW.ldf', NOUNLOAD,STATS = 5; alterdatabaseContosoRetailDW setMULTI_USER; GO Use ContosoRetailDW; GO ALTERDATABASE [ContosoRetailDW] SETCOMPATIBILITY_LEVEL = 130 GO ALTERDATABASE [ContosoRetailDW] MODIFYFILE ( NAME = N'ContosoRetailDW2.0', SIZE = 2000000KB , FILEGROWTH = 128000KB ) GO ALTERDATABASE [ContosoRetailDW] MODIFYFILE ( NAME = N'ContosoRetailDW2.0_log', SIZE = 400000KB , FILEGROWTH = 256000KB ) GO

Now, let us consider the following query, which is calculating the Monthly sales for the FactOnlineSales:

selectMonth(Datekey), Sum(SalesAmount) fromdbo.FactOnlineSales groupbyMonth(Datekey) orderbySum(SalesAmount) desc;

which is producing the following execution plan:


Columnstore Indexes   part 87 (“Indexed Views”)

On my test Virtual Machine, the above query makes lob logical reads 3988 and it takes 1750 ms of the CPU time , while lasting 604 ms . This is a pretty good result, but we can certainly improve it by creating an indexed view.

Let’s create the indexed view on our test table:

createviewdbo.vFactOnlineSalesWITHSCHEMABINDING AS selectDateKey, SUM(SalesAmount) as SumTotal, COUNT_BIG(*) as CountBig fromdbo.FactOnlineSalessales innerjoindbo.DimProductprod onsales.ProductKey = prod.ProductKey groupbyDateKey

This view will calculate the totals for each of the distinct dates that we have in our FactOnlineSales table.

The next step for the improvement would be to create the unique clustered rowstore index on our test view:

createuniqueclusteredindexpk_vFactOnlineSales ondbo.vFactOnlineSales (DateKey)

And now we are ready to implement the updatable nonclustered colummnstore index on this view

createnonclusteredcolumnstoreindexcci_vFactOnlineSales ondbo.vFactOnlineSales (DateKey,SumTotal)

which is working perfectly without any troubles in all (tested on SQL Server 2016 CU 1).

Let’s run the sample aggregation query against our new indexed view:

selectMonth(Datekey), Sum(SumTotal) fromdbo.vFactOnlineSales groupbyMonth(Datekey) orderbySum(SumTotal) desc

With 166 ms spent on the query execution, 248 ms of the CPU Time and the 3976 Lob Logical Reads , the query has definitely got some small boost, but its far from the performance that an indexed view can provide and as a matter of a fact, the execution plan shows that the Query Optimiser has decided not to use our indexed view:


Columnstore Indexes   part 87 (“Indexed Views”)

To solve this situation, one will have to use the NOEXPAND hint that will force the Query Optimiser (the need for it arises most probably because the total estimated cost of my query is around 0.0096…, while minimum threshold for parallelism is set to 50 :))

selectMonth(Datekey), Sum(SumTotal) fromdbo.vFactOnlineSaleswith (noexpand) groupbyMonth(Datekey) orderbySum(SumTotal) desc;

Take a look at the execution plan:


Columnstore Indexes   part 87 (“Indexed Views”)

The single threaded performance is not a problem here, since we get 36 ms for the total execution time , with 0 ms of the CPU time , and … just 14 lob logical reads .

Here are the images with the achieved results for the better visualisation:


Columnstore Indexes   part 87 (“Indexed Views”)
Think about this primitive example as a framework for achieving different goals, such as improving complex aggregation calculations or the problem solving of the 130 compatibility level that removed Batch Execution Mode from the processing if no columnstore indexes are involved in the query.
Columnstore Indexes   part 87 (“Indexed Views”)

From the perspective of the disk access, this is where you will definitely win at least a couple of times with the amount of the disk access while processing the information, amount of memory that you will need to store and process (think hashing and sorting for the late materialisation phases), and you will pay less for the occupied storage.

Another noticeable thing was that the memory grants for the Indexed Views query was smaller compared to the query that was processing the original columnstore table FactOnlineSales.

Clustered Columnstore Indexes

With Nonclustered Columnstore Indexes supported for the purpose of the Indexed Views, the next logical question here is what about the Clustered Columnstore Indexes ?

Let’s try them out on a new view, that I will call vFactOnlineSales2:

createviewdbo.vFactOnlineSales2WITHSCHEMABINDING AS selectDateKey, SUM(SalesAmount) as SumTotal, COUNT_BIG(*) as CountBig fromdbo.FactOnlineSalessales innerjoindbo.DimProductprod onsales.ProductKey = prod.ProductKey groupbyDateKey;

Let’s try to build the clustered columnstore index on it:

createclusteredcolumnstoreindexcci_vFactOnlineSales ondbo.vFactOnlineSales2 (DateKey,SumTotal);

The SQL Server returns an error message for trying to execute the above operation:

Msg 35305, Level 16, State 1, Line 14

Unfortunately the Clustered Columnstore Index creation is not supported. I guess that the DEV team was waiting on finalising the Updatable Nonclustered Columnstore Index in SQL Server 2016 so that they can include support for the indexed views.

I consider the error message to be quite wrong, since it gives the wrong impression over the current state of the indexed views and their support for the Columnstore Indexes.

I could write a full blog post on the error messages in SQL Server, and who knows, maybe one day I will.

Final Thoughts

The existence of the updatable Columnstore Indexes (Nonclustered) in SQL Server 2016 is an incredibly pleasant surprise. I can see a lot of workloads taking advantage of this feature, and I am definitely looking forward to implement it at some of our clients.

I have digged into the in

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLCPURESTSQL ServerSUTIMPACUEDI
分页:12
转载请注明
本文标题:Columnstore Indexes part 87 (“Indexed Views”)
本站链接:http://www.codesec.net/view/481604.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(31)