Hidden in SQL Server 2017 CTP v1.1: sys.dm_db_stats_histogram

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.

‘New’ string functions like CONCAT_WS, TRANSLATE, and TRIM.

STRING_AGG WITHIN GROUP (ORDER BY)s.

Cool.

Poking around

I started looking for new objects in sys.objects, comparing it to what’s in 2016.

GREAT GOOGLY MOOGLY

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.

What you get out looks like a histogram. Because, well, it’s a histogram, but in rows.

THIS IS

sysrowcols whatever GIMME

AWESOME!

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.

Oops

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!

, ,
Previous Post
[Video] Office Hours 2016/12/14 (With Transcriptions)
Next Post
Is NOLOCK Ever The Right Choice?

5 Comments. Leave new

  • The addition of STRAGG I mean STRING_AGG is all nice and well and good and such, but are we all just gonna keep acting like it’s nearly 2017 and SQL Server still doesn’t have native regex support?

    Reply
    • Well, no, but if they did implement it, it would probably just be another one of those new functions that just references the .NET class anyway. In which case you can create your own CLR function that calls the .NET class, which may or may not cause stack dumps if you use it on a large enough field.

      Which is fun.

      It’s all a lot of fun.

      Reply
  • congrats on the connect baby! spewing red ink without a filter…chip off the ole block, eh? =)

    for my part, i would kill for a t-sql analog of the .net string.format() function. for the times you need to, such a thing would make generating dynamic sql from a template string safe, readable and fun! all the plus-y plus-y and tick-y tick-y required by concatenogenedynamic SQL makes my head hurt.

    Reply
    • sp_executesql kinda does that, unless you need to pop in database/table/schema names.

      Which you probably do.

      TEE HEE

      Reply
  • Shiny!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}