ALL CAPS EXCITEMENT
In rather fun news, a Connect Item of mine about replacing DBCC SHOW_STATISTICS with a DMV or function got updated as ‘Resolved as Fixed’. It was only slightly odd, because I intentionally funneled votes to Greg Low’s item after realizing I had duplicated it. So, Greg, if you’re out there… Uh, hi!
Root cause: Microsoft employees also have a hard time searching Connect Items.
So what does it look like?
I have no idea. I don’t know if it’s a DMV or a function, I don’t know what it’s called, and I don’t know what information it exposes. I also don’t know how it will get joined to other DMVs. There were no details offered up when the status changed. And I’m fine with that! I’m pretty psyched that it got enough traction to get a fix to begin with. If anyone from MS feels like shooting me an email with details, I won’t complain.
But since we don’t know, we’re free to speculate. Like all those History Channel shows about aliens and fake animals and where the Templars secretly buried Jesus’ gold teeth in Arizona. It’ll be fun!
A Function!
It could be a lot like sys.dm_db_index_physical_stats! Maybe sys.dm_db_stats_stats! Heh. No, don’t do that. I think sys.dm_db_statistics would work. It could take inputs for database id, object id, and optionally index id and partition number to retrieve more specific stats. One input that would be killer here is for column name, so you could see any statistics objects that reference a particular column. Why? Because system generated statistics on _WA_sys_iuwghaiuhareiugh982375 aren’t helpful. In the words of Doug Lane: “that’s crap”.
This would be cool, because it would be easy to CROSS APPLY with existing queries to gather stats info. No learning new JOINs and all that hocus pocus relational database nonsense! It’s basically NoSQL with less curly brackets and reliable results.
A DMV!
Now, this could go two ways. It could be additional data tacked on to sys.stats, or it could be something like sys.stats_details (yeah, not exactly inspired), and join pretty easily to sys.indexes, sys.tables, sys.partitions, etc. These are probably the most common DMVs that you’d use along with this one.
What should it tell you?
Well, most statistics information queries I’ve written return a good bit of information. Not everything is easily accessible, so this could be expanded.
- Table name
- Index name (if not system generated, or a stats object without an index)
- Statistics object name
- Name of the column used for the histogram
- Last time the stats were updated
- Rows in table/index (different for filtered indexes, etc.)
- Rows sampled by the last update
- Modifications since last update
- Modifications before auto update
- Histogram steps
- If the statistics are marked as ascending
- Use count
Use Count?
HECK YES USE COUNT!
No, not because I want to start dropping statistics. But this would be AWESOME for maintenance scripts like Ola Hallengren’s when deciding which stats to update. Right now, you can skip statistics that haven’t been modified, and that’s great. But why bother updating statistics that aren’t even used? Building off other information exposed, you could also choose between full or sampled updates based on how many rows are in there.
But what about the Histogram?
What is it? Why is is capitalized here and nowhere else? The histogram is where the data for the leftmost column in your [object] gets described. There can be up to 200 steps in a histogram, and there are several columns of information in there. If this is also exposed, you may need something like sys.stats_histogram, or a function that accepts a stats ID and returns the histogram as well.
What would you want to see?
Leave a comment below if you think I missed something! Who knows, there may still be time for some lucky developer to make changes before this gets released!
Thanks for reading!
17 Comments. Leave new
Would it help to have a Power BI ‘content pack’ on top of the data? Just a thought. I can help you with that, and publish it up to powerbi.com. Let me know if that’s helpful.
Hi Jen! Er… Yes? But maybe this was meant for another post?
Please excuse my dumb thoughts (Brent does it all the time!). I was wondering how you’d evangelise the results of the statistics to the end user / person paying the bill. So, you start to do work and get results X, and then you do magical things with sql, and you get results Y. Customers like to ‘see’ how things have improved. Don’t worry if I don’t make sense 🙂 I am still turning it over in my head.
So we’re on the same page, this is just about the internal statistics that SQL keeps on table data for cardinality estimation. I’m not sure how that would fit in to the type of evangelization project you’re considering (maybe, I could be missing something totally obvious). The type of stuff a customer would want to see is probably faster queries 🙂
OH! Yes, that’s brilliant, actually. It’d be showing end users what the database looks like to SQL Server. SQL Server thinks in terms of table statistics, so it’d be kinda neat to visualize that. Plus, if it’s available as DMV data, then it would be super-fast to query (as opposed to querying the actual data to guess histograms.)
Yes! Thank you for articulating what I meant to say, Brent 🙂 My thinking is that power bi would pull data through and it would be much better visualised in power bi than the histogram. Power BI has ‘content packs’ which can get published up to the site, so you’d have it accessible there. I wonder if they same could be done with the sp packs that you produce, Brent, so you’d have a Brent Ozar Unlimited pack on their gallery for the benefit of the community.
Now I am not going to pretend I have DBA magic skills. I slog through the exam so I know what I’m talking about, and then I ask people far more smart than me to do DBA stuff whilst I go and do BI because that’s my thing. If it helps, I can see you at Summit and maybe we could go through some of this stuff as a community effort. I know folks make a lot of the sp_Blitz material you provide and it might be icing on the cake to have a content pack too.
You’re both too smart for me. I was sitting here thinking about something totally different.
Yeah, I was working with Patrick LeBlanc on one for sp_BlitzFirst. I know literally nothing about PowerBI, heh, but he was doing the legwork on it. Maybe one of these days…
Kurtosis (it isn’t a disease)
You’re a… that… thing.
Personally, I’d like to see ALL DBCC commands be replaced with DMVs or DMFs.
I don’t think that’s too personal. We’d all like to see that, aside from a few of the more dangerous commands.
Ohhhh… please, come to SQL 2012 & 2014… not just 2016… PLEEEAAAASEEEEEE
It’ll probably come to 2012 first, the way things have been going.
Eric-
I’m wondering if they’ve expanded sys.dm_db_stats_properties… I didn’t see that mentioned in your post, I have a variety of queries that use it, along with sys.stats, sys.objects, etc. I find it extremely useful for quickly seeing when stats were last updated and how much data has changed.
Erin
Because I like that one as-is and don’t want them messing with it 😉
Hahahaha! Good luck with that 🙂