Essential Skills for SQL Server DBAs: Session 3 Resources

For those of you that attended my 3 part eLearning Series on “Essential Skills for SQL Server DBAs” with WindowsIT/SQLMag, here’s a list of additional resources and links for things that I mentioned, alluded to, or wanted to provide additional information on from within session 3.

The SQLOS

A key component of some of the things we talked about in this session in terms of performance (as well as in session 1 where we looked at a rough overview of how SQL Server works) was the way in which SQL Server has its own internal scheduler and resource management capabilities – commonly referred to as the SQLOS.

And, if this was something that you want to learn more about, then I recommend taking a peek at Brent Ozar’s free SQL MCM video that goes into the SQLOS in more depth.

Likewise, in terms of being able to interact with the SQLOS via Dynamic Management Objects, I also wanted to provide a link to Books Online which provides documentation of Execution Related Dynamic Management Views and Functions.

Statistics and Indexes

Otherwise, another key thing we addressed in this session was Indexes and Statistics – as these are both critical concerns to addressing SQL Server performance. To that end I wanted to provide a link to a great overview of how SQL Server 2005 uses statistics (even though this information is a BIT dated it’s still very valuable because it’s a good overview of how statistics work – and how you can interact with them).

I also wanted to provide a link to a great series of articles on indexes – that will walk you through the basics and on up into more advanced, how-to, topics for dealing with, interacting with, and managing indexes: SQLServerCentral.com’s Stairway to SQL Server Indexes. (And make sure to check out other stairways, these are FANTASTIC resources.)

Then, along the lines of indexes, I also wanted to provide a link to Books Online: Reorganizing and Rebuilding Indexes – as this provides great info about fragmentation and index management/maintenance.

Similarly, I also wanted to provide a link to Michelle Ufford’s spectacular Index Defrag scripts (which, you’ll remember, that I compared to ‘bacon-soaked' marshallows’ – by means of a metaphor comparing index management to fishing/lures).

Statistics Maintenance
In my presentation, I mentioned using sp_updatestats and covered that it does a decent job of updating most (not all) stats with very little effort. (Though just be careful if it’s never been run, as it will take a while). I also included a sample script (using the undocumented special procedure, sp_msForEachDb) that you can use to update stats across ALL of your databases on a single server. And, here’s that script again:

EXEC sp_msForEachDb @command1 = ‘EXEC ?..sp_update_stats’

Additional Performance Resources

One thing I didn't cover in this eLearning event was the role of vendors when it comes to SQL Server. And I skipped over that simply due to time constraints. That said, there are GOBS of great SQL Server tools and services available - from major SQL Server Tools vendors. And, as I opined a long time ago, one of the great things about these vendors is that they're all very keen to be integral parts of the SQL Server community - meaning that they all do a great job of trying to help educate SQL Server users and DBAs on various, core, concerns - instead of focusing solely on just selling products.

So, to that end, I wanted to do something a bit different and provide links to three additional resources that you can use to learn more about SQL Server Performance tuning - from three different vendors of 3rd party SQL Server tools/solutions.

Quest: SQL Server Perfmon Counters Poster
This poster is a great resource - one of the best aggregations of information, documentation, and guidance on how to interpret Windows and SQL Server Performance Counters.

Redgate: Mastering SQL Server Profiler (free eBook)
This free eBook is also a great resource - that provides outstanding insights and details on how to use SQL Server Profiler. (Note the link on this page to the free download, or you can purchase a 'hard' version of this book if you're interested.)

Idera: Essential Performance Tools for SQL Server DBAs
Hmmm. The title of this free whitepaper sounds a bit like the title for this session. And that's probably because both resources had the same author. So, to that end, this free whitepaper does rehash a number of the same things that we covered in this session. It does, however, provide a better overview of some of the tools and resources we looked at today as well.

What’s Next?

Finally, I wanted to close out this event (or my notes for it) by pointing to a link for a great blog post that I bumped into the other day:

The Power of T-SQL’s APPLY Operator

The content in this blog post isn’t what I’d consider being on par with most beginning or newer DBAs. BUT, this post is a fantastic example of just how fun and fulfilling it can be as a DBA to dig into SQL Server performance. That, and this blog post is also a great example of a seasoned veteran of SQL Server showcasing (indirectly) just how fun and exciting it can be to continue learning, discovering, and finding new challenges and insights when working with SQL Server.

Translation: This post is a fantastic example of how being a DBA can continue to be a fulfilling career choice over the years, because in this case, there’s no doubt that Rob Farley (who is a genuine bad-ass) is thoroughly in love with what he’s doing. And I can say the exact same thing about my career choice in terms of SQL Server – it’s been a blast, it’s still a blast, and I anticipate more challenges and opportunities in the future.