Leakin’
There are some helper views and functions that I use in a few presentations, and I figured it was time to stick them on The GitHub so I don’t have to package them up and remember to keep changes in sync, etc.
It’s a nightmare. There are enough of those.
What am I dropping on you?
WhatsUpIndexes
This view gives you a quick look at index sizes and row counts. It’s not exhaustive, and it’s not meant to be.
If you want exhaustive, use sp_BlitzIndex.
Since it’s a view, all you have to do is select from it.
SELECT * FROM dbo.WhatsUpIndexes AS wui;
I know, I’m terrible for writing SELECT *, but I really do want all the columns here.
If there were columns I didn’t want, I wouldn’t have put them in the view.
You know?
Because I’m kinda forgetful, I like having the view name in the results so I know what I’m looking at, and it’s obvious to people watching where results came from without looking at the T-SQL up on the screen. The rest of the info is pretty self-explanatory. It measures indexes by size and rows.
WhatsUpMemory
These two views are designed to complement each other a bit, because often what I’m showing with them is how big indexes are compared to what’s been read into memory. Sometimes it’s the whole index, sometimes it’s part of the index. But it’s nice to be able to see right next to each other.
As far as I know, there’s nothing out there that analyzes what’s in memory, and most of the time this isn’t something I’d want to run in production.
SELECT * FROM dbo.WhatsUpMemory AS wum;
There are two reasons for that:
- The sys.dm_os_buffer_descriptors view is really slow
- It gets slower with more memory/stuff in memory
I don’t think there’s another memory view that gives me what I want back, so I’m sort of stuck with this one here.
You can see how the two help each other, and you can also probably see why it’s easy to get the results confused. If it weren’t for the buffer cache pages column here, it might look just like index info. Heh.
WhatsUpLocks
This is an inline table valued function, and it takes one parameter for a SPID.
It can be NULL if you want, but usually I want to focus in on what one things is doing.
SELECT * FROM dbo.WhatsUpLocks(@@SPID) AS wul;
This doesn’t give you nearly as much other detail as sp_WhoIsActive, but it’s good for just looking at locks taken. Note that a lot of the time, you might need to use a transaction to preserve the locks so you can see the full extent of the damage. If you’re looking at another session while it takes locks, it’ll either have to run for a bit, or you’ll have to be really fast with F5.
I use the Votes_Beater table to have a copy of the Votes table with a bunch of indexes on it that I don’t need to go and create live. They’re always there and ready for abusive demos. I like the Votes table because it’s big and narrow, with sensible data types for things (read: no MAX types).
It makes things a lot simpler.
Fine Print
I make no guarantees about which versions these’ll run on, and quite frankly I’m not interested in them being backwards compatible. They run on the versions that I do my demos on (2017 and 2019).
If they happen to work on older versions, great. If not, I’m fine with you making local changes, but won’t accept pull requests for it, and I’ll close issues about it immediately. It’s not like you can have dynamic SQL in these things, anyway.
For any questions about what they return, make sure you read the docs for the views they touch. It’s not hard to look at the queries, I promise ?
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.