Blog

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.

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:

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.

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.

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.

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.

Another thing that really rocks about SQL Server is that it allows the end users to write their own Custom Reports which can be executed in SSMS.
I recently wrote a set of reports which are used for gathering performance, config and security information from the Report Server database.
This way, anywhere I go, I can always execute my SSRS Performance Dashboard reports and quickly start debugging performance issues with any Reporting Server. Here is a link, if you are interested: http://sqlconcept.com/tools/ssrs-performance-dashboard/

There are other great custom reports, of course – the Microsoft’s Performance Dashboard, for example.

I love the tip about scripting everything out. It allows you to reverse it, repeat it or replicate it to other servers or environments. For those things that don’t have script out option, you can just use SQL Profiler to get a re-usable script. If you don’t know how, just check out this step by step video on how to generate T-SQL scripts using SQL Profiler.http://sqltidbits.com/videos/2011/02/producing-t-sql-using-sql-server-profiler

The SQL Server community is mindblowing. To say there’s a wealth of knowledge out there is an understatement.Not only the online documentation, but the free events (SQLSaturdays, 24HOP, MCM recordings, Quest videos, Idera videos, etc) and all the tweets (#sqlhelp, #sqlserver, #msbi), and all blogs.

It is utterly amazing to me how tight knit the community is. It feels like there’s always somebody – someone out there – who will help you. Not to be obviously biased, but I don’t think this is something you get with other products.