Monday, April 24, 2017
In Oracle, there are 4 histogram types:
Frequency histogram: for low numbers of distinct values. If ndv < bucket count limit, then each ndv gets a histogram bucket. If ndv > limit, frequency histogram is disqualified.
Height-balanced histogram: histogram buckets with approximately the same number of rows in each bucket
Top-n Frequency: lowest value, highest value, n-2 most popular values in between as bucket end-points.
Hybrid: Top-n Frequency not qualified because N end-point values do not cross threshold to qualify.
In SQL Server 2016 (as has been the case for several major versions), one histogram type is available. Of the histogram types available in Oracle 12cR2, it is most similar to the hybrid histogram. For a nullable column, a NULL histogram bucket will be available. For non-null values, up to 200 additional buckets are available. The desired number of buckets cannot be specified. For values of particular significance, or in order to accommodate skew in data, filtered statistics can be created. For filtered statistics to reliably be consulted by the optimizer, a query filter predicate must match the predicate of the filtered statistics.
March 2017 Whitepaper (1.1 mb pdf)
Understanding Optimizer Statistics with Oracle Database 12c Release 2
Three part Oracle Database Histogram series from Jonathan Lewis
Another three part Oracle Database Histogram series from Jonathan Lewis
Two part Oracle Database Histogram series from Mohamed Houri
Friday, April 21, 2017
Requesting the actual plan for the CREATE, I find this in the plan XML. 1197064 as the SerialDesiredMemory, the RequiredMemory, the RequestedMemory.
That's a big grant for something that operates on 0 rows. When creating many CCIs concurrently, that's a problem - queries can timeout.
Might have to use Resource Governor creatively to allow high concurrency CCI creation.
Grant request is smaller if only one column in the CCI. So I guess could create a smaller CCI and add columns. That's also kinda painful. I'll ask on #sqlhelp, see if anyone has something clever...
So far the only things to come up on Twitter were the min_grant_percent and max_grant_percent query hints introduced in SQL Server 2012 SP3. But...
"Note These two new query memory grant options aren't available for index creation or rebuild."
Later I found this while looking around.
"Starting with SQL Server 2016, you can create the table as a clustered columnstore index. It is no longer necessary to first create a rowstore table and then convert it to a clustered columnstore index."
Hmmm. Here's a great post from Pedro Lopes (Twitter @sqlpto), which includes some details for using Extended Events in memory grant investigation.
So far, it looks like creating the clustered columnstore inline with table creation, as below, avoids a memory grant. (That makes sense.) Whereas creating a clustered columnstore index on an empty rowstore table requires a memory grant - at the same size as up to 1 million rows for the table! (That's a surprise.)
If you'll be creating CCIs at fairly high concurrency, you may also want to consider creating the CCI inline with table create, as below, in order to avoid a potential bottleneck in memory grants.