« February 2011 | Main | May 2011 »

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.



Essential Skills for SQL Server DBAs: Session 2 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 2.

Sizing Databases

During the first part of Session 2 we focused on Server Management (checking logs/etc) and on database management – where we focused on the perils of both shrinking databases and on letting them grow fragmented by letting them grow with the lame, default, sizing options found by default within SQL Server.

To that end, I wanted to share a link to Books Online that covers more details on: Managing Databases.

For more information on database sizing ideas and guidance, you may also want to check out this SQL Server Magazine Article: A Practical Approach to Managing Database Size.

Likewise, I also mentioned that the proper care and feeding of log files can play a role in performance concerns as well. And, to that end, I wrote about that a while back in my article: Maximize Storage Performance.

Additional Resources

During this session I also mentioned some specific resources that I would make available.

Specifically, I called out that I would provide a link to the 2.5 hours of free SQL Server Videos covering backups.

Likewise, I also mentioned that I’d provide a link to an overview of the benefits of RPOs and RTOs – as they apply to SQL Server.

And, I also mentioned that I’d provide a link to my upcoming, free, webcast: SQL Server Disaster Recovery and Prevention (where I’ll be covering problems and concerns around database corruption).



Essential Skills for SQL Server DBAs: Session 1 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 1.

It’s Good to Be the DBA

During this session, I mentioned some of the benefits of being the DBA, and quoted job and salary surveys from CNN/Money, and Indeed.com – showing that being a DBA can be a rewarding career, has a great outlook in terms of looking like DBAs are going to be around for a while, and salaries aren’t bad either.

I also called out that there were different KINDS of DBAs. Gobs of them. And quoted a list defined by Craig S. Mullins and Brad McGehee. (Brad’s list of different types of DBAs can be found in his applicable chapter in SQL Server MVP Deep Dives – a great book.)

Technical Details / Basics / Fundamentals

During the second part of this session, we took a look at core, or basic, SQL Server concepts that all DBAs should know about. And, after providing a VERY high-level overview of how SQL Server works as a proxy to manage access to your databases/files, we then touched upon how SQL Server uses, well, SQL Server databases, to meet its own needs. And, in that regard, we touched upon SQL Server’s system databases. So, to that end, I wanted to make sure that I provided a bit more depth or detail on some of those databases, and therefore have a couple of links to Books Online:

SQL Server’s System Databases

The Resource Database (I could tell you more, but I’d have to kill you… )

Likewise, the following two links can be great resources to check out if my overview of how SQL Server works was new or something you’re not terribly familiar with:

The SQL Server Database Engine

Managing SQL Server

And, finally, I also wanted to drop off a link basic documentation within SQL Server’s Books Online for Security as well:

SQL Server Security

And, as always, feel free to ping me if you have any questions that weren’t answered during the Q&A portion of this session.