When something remarkable is around for a while, it becomes easy to take it for granted.

Today, I’ll share three awesome things about SQL Server with tips to give you an edge when using each one.

#1. The Free Online Documentation is Mind Blowing

SQL Server is a huge product. We’ve got the database engine, Analysis Services for cubes, Integration Services to process and transform data, and an increasing set of other complex products for working with data documented in Books Online.

Tip: When using Books Online, check the Community Content at the bottom of the page first for any errors or questions others have raised— this can save you time and trouble if an important detail is missing or in error. Check other versions of the same page using the link at the head of the topic, and always read critically. Even encyclopedias can’t be perfect!

Microsoft’s documentation doesn’t stop with Books Online. Microsoft also publishes in-depth whitepapers for SQL Server. I recommend you periodically review the list to make sure you know what’s available, then set aside time to read the topics which are most useful to you.

• Microsoft Whitepapers for SQL Server

We also have sites where community members publish high quality documentation for SQL Server. The SQL Server community is so vast that it’s impossible to mention them all. Three of my favorite sites for technical documentation are:

• Erland Sommarskog’s texts on SQL;
• SQL Server Stairways on SQLServerCentral;
• The SQL Server Customer Advisory Team Blog and SQL CAT articles.

Tip:  When you see unusual behavior in SQL Server, search Microsoft Connect. This is where users report bugs and suggestions for the product. Make sure you log in to search: external search engines typically won’t lead you to what you need to find.

#2. Lots of Ways to Find Out “What’s Happening Now?”

We have many ways to check out what’s currently happening in SQL Server. The instrumentation for the product is very well developed and gives a lot of options to see what’s processing at any given time.

The top four ways I check to see what’s happening are by using:

1. Dynamic Management Objects – We can find out an awful lot with queries— and more with each version of the product;
2. SQL Trace – Our old friend, sometimes used with SQL Server Profiler;
3. Extended Events – The new, leaner and meaner hotness for tracing: SQL 2008 and higher;
4. Perfmon Counters – Windows and SQL Server specific counters.

Each of these techniques has its own strengths, and Extended Events is becoming increasingly powerful. Together, all of these methods provide a vast array of information about what’s happening in and around SQL Server.

Gathering data with each technique has its own cost. It takes experimenting and research to know what you can get away with and where you need to hold back.

Tip: When it comes to finding out what’s going on in production, treat your investigation like a database change. That means you should test your method against another environment, even if you can’t reproduce the issue there. Always think about how your method of investigating may be impacted by increased load, and make sure you have a way to monitor its impact.

#3. Tools, Tools, Tools

SQL Server ships with rich, user-friendly tools.

SQL Server Management Studio covers a lot of ground with different features. It helps us navigate a given installation and also develop Transact SQL. It has built-in reports to help describe what’s going on at instance and database levels.

It has sqlcmd mode if you prefer a different kind of scripting and want to interact more with Windows. You can use Object Explorer Details to select multiple items like Windows Explorer. We can view Graphical Execution Plans. Or, we can just run Transact SQL statements in multiple windows.

Tip: SSMS lets you choose to generate a script for almost every action you want to do rather than just executing it through the GUI. This feature is just plain awesome. Even if you choose to execute a change through the GUI, I recommend you always script out the command and save it off as a record of your change.

Want more SSMS tips? Check out Jes Schultz Borland’s blog post Tips and Tricks to Make SQL Server Management Studio Awesome.

What’s Cool About These Things Together

SQL Server may be complicated, but we have a lot of ways to work with it and to learn more about it.

As the SQL Server product grows, the tools and increasingly improved instrumentation allow us to understand the product more deeply. Books Online is just a launch board– the tools and the methods we have to see what’s going on in SQL Server help us take off from there.

Together with community members who want to share knowledge, this creates an interested, invested group of technologists who write about what they’re learning. And that’s something we really shouldn’t take for granted.