It’s Friday night, so I’m waiting for new CTP Releases
As soon as I got the email, I started reading the release notes. Some interesting stuff, of course.
Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more queries to run on systems that are otherwise blocking on memory.
STRING_AGG WITHIN GROUP (ORDER BY)s.
I started looking for new objects in sys.objects, comparing it to what’s in 2016.
I don’t know how this didn’t make the release notes. But there it is. My first Connect baby with Microsoft. I’m a proud papa.
What and how
So, what does it look like, and how do you use it?
It’s a pretty simple CROSS APPLY, like many other system functions.
SELECT OBJECT_NAME(dh.object_id), dh.stats_id, step_number, range_high_key, equal_rows, distinct_range_rows, average_range_rows
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) dh
WHERE s.object_id = 3 AND s.stats_id = 1
What you get out looks like a histogram. Because, well, it’s a histogram, but in rows.
It’s not exactly perfect
For instance, if you just let it loose without filters, you get a severe error. The same thing happens if you try to filter on one of the columns in the function, rather than a column in sys.stats, like this.
But I don’t even care. It’s brand new, and it’s so much better than trying to tabularize DBCC SHOW_STATISTICS output.
Thanks, whomever worked on this.
And thanks for reading!