Dynamic Management Views & Functions (DMVs & DMFs)
What are these? It is said that one’s eyes are the gateway to the soul, I say that DMVs/DMFs are the gateway into SQL Server! Formally speaking, the DMVs/DMFs expose internal information about your SQL Server. This information can be used to learn about its current state & health, and provides valuable insight for troubleshooting and tuning purposes.
Much earlier in my SQL Server career, I never knew much about how SQL Server operated under the hood. I only had knowledge of the things I could do directly, but I never questioned what was really happening behind the scenes. Then I attended SQLskills IE1 training, which was a turning point in my career. Among other things, it was my first exposure & deep dive into SQL Server Internals. I became enamored with learning how things really worked under the hood and the DMVs/DMFs became one of my best friends.
Since their introduction in SQL Server 2005, the number of available DMVs/DMFs has more than doubled. How can you see what’s available to you on a given SQL Server? One quick & easy way is to query sys.system_objects:
WHERE system_objects.schema_id = 4
AND system_objects.type IN (‘IF’, ‘V’)
AND system_objects.name LIKE ‘dm_%’
Of these, what are some of my favorites?
Combining these two, gives me very useful insight into both the structure of a table & any indexes it may have, but to how my workload is actively making use of each of these structures. Data found here is extremely useful when it comes to performance tuning.
I like to use this DMV to get row count & page count information about a database. Its output gives me greater insight into the contents of a table, particularly without the overhead of a SELECT(1) for a quick row count or sp_spaceused execution. Personally I find knowing size by page count to be far more valuable anyway.
Mixing & matching these three, you can obtain information regarding current connections and activity on your SQL Server.
I can’t tell you how many times I’ve had a “eureka” moment, discovering that a DMV/DMF exposed another interesting piece of information that I wasn’t aware of before. To say that there’s a wealth of information is an understatement. So whether you’re new or not to DMVs/DMFs, take another look – you too may find a new and useful gem hidden inside them!
And if you want to read all about all available DMVs/DMFs, be sure to check out Books Online: Dynamic Management Views and Functions (Transact-SQL)
A couple of weeks ago, I posted an Invitation to T-SQL Tuesday #68: Just Say No to Defaults. Last week, #SQLFamily responded in force with 22 blog posts! There’s a fantastic wealth of information across all of these blog posts and I was blown away!
Without further ado, here’s the round-up!
- Aaron Bertrand (@AaronBertrand) discusses SQL Server Setup & “Installing All The Things!” (Should I add a trademark notation to that phrase for you?)
- Andy Yun (@SQLBek) explored SSMS & Keyboard Query Shortcuts.
- Angela Henry (@SQLSwimmer) explores her two two quick wins to improve performance.
- Björn Peters offers us a post in German, discussing Min & Max Server Memory. (Thank goodness for Google Translate!)
- Bob Pusateri (@SQLBob) shares some of his Defaults to Change, not only in SQL Server, but in Windows as well.
- Cathrine Wilhelmsen (@cathrinew) explores using Batch Update change defaults in SSIS packages.
- Chris Sommer (@cjsommer) discusses using Powershell & SMO and explores an example.
- Daniel Mellor (@sqlsanctum) writes about his top six defaults that he makes changes to.
- Jason Brimhall (@sqlrnnr ) takes an interesting twist, to talk about the one default we all share – ourselves. He also wrote a preview post the week prior, showing us how to track changes via Extended Events.
- Jens Vestergaard (@vestergaardj) shares his exploration of Powershell & Desired State Configuration and discusses how it can be useful for changing Defaults.
- Jim Dorame (@DBAJD) gives us an excellent checklist of six items that he always explores, including Model DB!
- Justin Randall (@jh_randall) takes a turn by exploring SQL Server Agent.
- Kenneth Fisher (@sqlstudent144) reminds us of the importance of documenting our changes, and explores what can be automated to aid that.
- Koen Verbeeck (@Ko_Ver) gives us a SSIS-centric post, talking about changes he recommends.
- Lori Edwards (@loriedwards) shares three of her Defaults to change.
- Martin Catherall (@MartyCatherall) writes an informative post about Backup Checksum.
- Mike Fal (@Mike_Fal) flexes his PowerShell with a detailed exploration of Desired State Configuration.
- Nancy Hidy Wilson (@NancyHidyWilson) writes about SQL Agent, and a setting she addresses after installation.
- Rob Farley (@rob_farley) writes about something I am guilty of rarely ever thinking about – Default Collation.
- Robert Pearl (@PearlKnows) took the time to expand on the topic from a higher level, offering a wide variety of topics & solutions for further reading.
- Steve Jones (@way0utwest) explores one of the default settings on his must-change list.
I want to thank everyone for participating and sharing your knowledge. And I hope that those of you who are reading, but not yet blogging, will join us, start your own blog, and begin sharing your knowledge and your stories too!
Until next time!
After being a long time read and occasional participant, I am honored and humbled to be your host for this month’s T-SQL Tuesday! In my Invitation to T-SQL Tuesday, I asked everyone to write a blog having something to do with “Defaults.”
For my contribution, I decided to blog a bit about SQL Server Management Studio. As a SQL Server Developer, I live my life within SSMS. As much as I like it, there are a variety of defaults that I like to change. And for today’s blog, I’m going to talk about Keyboard Query Shortcuts.
If you navigate to Tools -> Options -> Environment -> Keyboard -> Query Shortcuts, you will find that SSMS comes with a handful of defaults.
These three starting shortcuts are indeed very handy, but SSMS gives us a number of other blank entries to add our own. Wouldn’t it be a waste if we did not take advantage of them, to add in some of our own?
As you can see above, here are ALL of the new Keyboard Query Shortcuts that I add. And here they are in full:
- Ctrl+3 = sp_help
- Ctrl+4 = sp_helptext
- Ctrl+5 = sp_SQLskills_SQL2008_helpindex
- Ctrl+6 = sp_SQLskills_SQL2012_helpindex
- Ctrl+7 = EXEC sp_whoisactive @get_plans = 2, @get_transaction_info = 1, @get_task_info = 2, @get_avg_time = 1, @get_outer_command= 1
- Ctrl+8 = EXEC tempdb.dbo.sp_help
- Ctrl+9 = EXEC sp_helpExpandView @ShowObjectCount = 1, @ViewName =
- Ctrl+10 = EXEC sp_whoisactive @get_plans = 2, @get_transaction_info = 1, @get_task_info = 2, @get_avg_time = 1, @get_outer_command= 1, @delta_interval = 10
Let’s explore how I make use of each of these!
I use these two SQL Server built-in stored procedures on an almost daily basis. They’re extremely useful while in the midst of code, to check aspects of objects. How do you make use of it? Simple! All you have to do is highlight the object in question, then hit your Keyboard Shortcut!
Using sp_help, I can quickly peruse all available columns in a given object, double-check their datatypes, or take a quick glance at what foreign keys and constraints are in place.
With sp_helptext, I can quickly scan the contents of a view or stored procedure, when I don’t want the hassle of finding it & script it out via the Object Explorer.
SQL Server comes with a built-in stored procedure: sp_helpindex. sp_help also returns index information about a table. However, they don’t return everything. When I’m query tuning and checking underlying indexes, I need to know about INCLUDE columns. That’s where Kimberly Tripp’s sp_helpindex rewrite comes in.
Used in the same fashion as sp_help & sp_helptext, I now have in-depth index information available at my fingertips!
“Hey, why’s the server running slow right now?” Every time I hear that question, Adam Machanic’s sp_whoisactive is the very first thing I always run. If you don’t use this tool already, read more about it on Adam’s blog and install it NOW!
While you can run it without parameters, I like having some additional data points available to me, which require parameters. As you can see from the above command list, that’s a heck of a lot to memorize and re-type, so I’ve mapped that to Control-7.
If you’re like me and work with a temporary tables regularly, you’ll notice that the usual sp_help shortcut does not work. Not having sp_help information conveniently available for temporary tables is rather obnoxious, hence this particular Query Shortcut.
Now I can have the same sp_help functionality and convenience with my temporary tables!
My final Query Shortcut executes my custom tool sp_helpExpandView. Used in the same way as sp_help, I can quickly reference underlying objects of a view. If you have to deal with untangling nested views, I would encourage you to check it out!
That’s it for my SQL Server Management Studio Keyboard Shortcuts. I hope you all found this insightful. If there are any Defaults that you like to change in SSMS, please feel free to share in the comments. Thanks for reading – until next time!
I am honored to be your host this month. This month, I’d like to ask everyone to blog about SQL Server Defaults. While SQL Server CAN be run with just the default settings, doing so is far from ideal. As we progress through our careers, many of us build lists of things we change. Let’s blog about those! And don’t think this month’s topic is constrained to just the SQL Server engine. SSIS & SSAS are absolutely fair game as well. SQL Server developer tools – sure! Want to take a different spin on “defaults,” go right ahead and be creative!
Do you have a checklist of “always change” settings? Blog about that!
Do you have a preferred method for mass-deploying your changes? Blog about that!
Do you have a specific default setting you ALWAYS change? Deep dive & blog about that!
Want to Participate?
- Write a blog post about the topic. Don’t have a blog? Start one!
- Include the T-SQL Tuesday Logo and link it back to this post.
- Publish your blog post Tuesday, July 14, between 00:00 GMT & 23:59 GMT.
- Leave a reply below with a link to your blog post (for the round-up).
- Share you post with the community! Tweet it out using the #tsql2sday hashtag!
Let’s have some fun! I’m looking forward to everyone’s contributions & learning something new!
24 Hours of PASS
Last week, I had the thrill and honor of presenting for 24 Hours of PASS. I presented a shortened version of my successful Every Byte Counts: Why Your Datatype Choices Matter session and had a blast.
While I’ve presented this session in person a number of times, I faced some new challenges this time. First, I was presenting a new, shortened version (the original has grown to 70 minutes of content!). The second challenge was presenting online – a much bigger challenge than one might think. I had no crowd to feed off of. Additionally, I had to consciously remember to make eye contact with my webcam, instead of looking at my slides. Finally, I had picked up a head cold the week prior. 30 minutes before my start, my nose decided to start running, so I had to excuse myself a few times to tend to that. A little embarrassing & irritating, but I suppose it’s no different than presenting in person. After all, the show must go on!
Despite the challenges, it was an amazing experience. I was honored to have been given the opportunity and want to thank everyone who worked hard to put another successful 24 Hours of PASS together.
Here is Matt Penny’s (@salisbury_matt) Sketchnote from my session.
On another note, I also received notice last week that I was not accepted to speak at PASS Summit. I wasn’t upset about this. After all, almost 900 abstracts were submitted, of which only a 15% were accepted? While I know that my Every Byte Counts session is useful information, and COULD be Summit quality, it is also a niche topic that given the volume of other submissions, would probably lose out. And it did, but I’m okay with that. I will not be dissuaded from speaking or submitting, and am still proud to be part of this community!
So what’s next? Last week, I was also accepted to speak at SQL Saturday Indianapolis! I’m thrilled to be making my return to Indianapolis & joining a great group of presenters. Additionally, I will be presenting remotely to the Las Vegas User Group. This will be the first time I’ve done a remote User Group session, but I’m hoping that this will be the first of many! And finally, I just submitted for SQL Saturday Minnesota!
I’m happy to share that my SQL Presentation spree for 2015 is going strong! I wanted to highlight when/where I’ll be presenting again, in the next month.
After several years of conflicts, I’m thrilled to finally be attending SQL Saturday Iowa! I will be debuting a revised version of my Every Byte Counts presentation. The original version of the session had grown to 70 minutes of content and I needed to compress it to fit 60 minute speaking slots.
My good friend Jes Borland (b|t) runs FoxPASS and I’ll be making the trek up there to present! This will be the second time I am presenting my other session: Cleaning House: The Indexing Edition. I had a lot of fun with it at MadPASS, so am looking forward to giving it again.
You can watch online too! Lync info is available on the FoxPASS website.
I am without words to describe how I feel about 24 Hours of PASS. I’ve been watching these semi-annual webinar events for years now, and now I get to present in one! I’m thrilled to be joining a fantastic line up of speakers. I will be presenting my Every Byte Counts session
Three presentations over the course of the next month – wow, that’s a lot! Nevertheless, I’m jazzed to share what I’ve learned. Hope to see you all soon!
When the topic of monitoring arises, the focus almost universally shifts to Production environments. But as a developer, I would like to give Development environments some monitoring love as well.
Dev: “Monitor Dev? HAHAHA! Why do we care what’s happening in Dev?”
How many times have you been working on something and its performance is less than stellar? Perhaps you’re refactoring a legacy chunk of code and contrary to your expectations, your re-write is running dog slow. Or you’re working away in a shared Dev environment and suddenly queries that had taken mere seconds are now taking their own lunch break before coming back with resultsets?
Dev: “Okay, what should we watch for?”
Well, some of the metrics that are mission-critical to a Production environment may not have such a high priority in an Development environment. Perhaps you don’t care as much about about CPU spiking to 100%, because your Dev environment has fewer & less powerful CPUs than your Production hardware? Of course that hardware will be stressed more easily.
But here’s some things that you may still want to keep an eye on. How about blocking, especially in a shared environment? I’d really like to know that Fred’s new routine was written in a way that it unexpectedly takes exclusive table locks all over my database, and interfering with the rest of the development team. Better to find it causing trouble in Dev than Prod, right?
How about wait stats? If you’re working on a poorly performing chunk of code, and your Dev SQL Server is waiting on something, chances are good that your code will cause your Prod SQL Server to suffer some similar waits.
Dev: “Yeah, I can see how that kind of information can be useful. But how should we monitor?”
There are a wide variety of tools out there, both DIY and 3rd party vendor. Do you already have a product that you use to watch Production, that you could also use to watch Development? Talk to your DBAs about making use of it.
Want to roll your own solution? That’s easy too! Read up on how to baseline your SQL Server – Erin Stellato’s Baselines Series is a fantastic place to start! Leverage SQL Agent Jobs in conjuction with different diagnostic scripts like Glenn Berry’s Diagnostic Scripts, and save that data off to a table periodically for analysis.
Dev: “Huh, there’s some cool stuff in there!”
Absolutely! Be creative and do what makes sense for your enterprise and application. Remember, the sooner you learn about a problem, the sooner you can deal with it before it becomes a fire – even in Development!