Skip to content

Roundup of T-SQL Tuesday #66: Monitoring

T-SQL TuesdayT-SQL Tuesday #66 was hosted by me on May 12th. The topic of the month was Monitoring. We all monitor something while working with SQL Server, and this month’s blog posts covers all kinds of topics by DBAs, developers and BI professionals.

T-SQL Tuesday #66 blog posts:
• Aaron Bertrand (@AaronBertrand) shows how you can use trace flags to babysit slow backups or restores.

• Andy Yun (@SQLBek) reminds us that monitoring in development is important too! Deal with problems before they reach your production environment.

• Angela Henry (@SQLSwimmer) tells how she became a better DBA by creating her own monitoring solution, and how having a monitoring solution in your toolbox can save you from having to look for a new job!

• Ben Miller (@DBAduck) shares his PowerShell script for monitoring tables to watch trends of growth and activity in a database.

• Boris Hristov (@brshristov) encourages IT managers and decision makers to ensure that teams also show how to monitor solutions, and not just implement the latest cutting-edge technologies and expect it to work immediately.

• Cathrine Wilhelmsen (@cathrinew) shows an example of a Real Time Monitoring solution developed in her company.

• Chris Sommer (@cjsommer) points out some of the most important things a DBA must monitor, and also links to his recent post about Monitoring SQL Server Agent Jobs by using PowerShell.

• Daniel Mellor ‏(@sqlsanctum) shows you step-by-step how to use the Utility Explorer to create a dashboard for monitoring and baselining SQL Server CPU and storage resources.

• Ed Leighton-Dick ‏(@eleightondick) explains how to use tables in the msdb database to monitor your backups.

• Jason Brimhall (@sqlrnnr) discusses different monitoring approaches and methodologies.

• Jason Hall (@SQLSaurus) reminds us to monitor the monitoring systems, they are also important parts of your environment.

• Jeffrey Verheul (@DevJef) writes about different solutions you can use to monitor your environments.

• Kenneth Fisher (@sqlstudent144) writes about a three tiered approach to monitoring when your company is unable to invest in monitoring solution licenses for hundreds of instances.

• Lori Edwards (@loriedwards) reminds us that effective monitoring is more than just a high number of alerts.

• Malathi Mahadevan (@sqlmal) uses a combination of third-party tools, out-of-the-box tools and custom tools to monitor environments.

• Michael Bourgon ‏(@Mbourgon) shares a PowerShell script for capturing multiple servers’ Event Logs to a database.

• Mickey Stuewe (@SQLMickey) created a custom solution to monitor all reports and shares the scripts used.

• Nicky van Vroenhoven (@NickyvV) joins the T-SQL Tuesday blog party for the first time and shares what is important for him to monitor as a SQL / BI developer.

• Richard Douglas (‏@SQLRich) writes about the sneaky Error 825 that disguises itself as just an informational message and why this should be explicitly monitored.

• Rob Farley (@rob_farley) shows how to monitor skew in PDW and how it should be tracked over time when your data changes.

• Robert L Davis (@SQLSoldier) explains how to make sense of errors in the Replication Monitor.

• Robert Pearl (@PearlKnows) writes about how important it is to monitor your systems for a healthy SQL environment.

• Robert Verell ‏(@SQLCowbell) shares how to use WMI and WQL with the native alert system to monitor security.

• Steve Jones (@way0utwest) takes a more philosophical approach to monitoring and talks about why it is important to capture and monitor specific information that is critical to you.

• Steve Thompson (@Steve_TSQL) shows how to identify potential bottlenecks by using Performance Monitor.

• Tim Peters (@tpet1433) uses the Legend of Zelda Triforce to illustrate how solutions can be both easy, helpful and cheap.

• Volker Bachmann (@VolkerBachmann) shares his step-by-step approach to implementing base monitoring.

• And finally Björn Peters (@SQL_aus_HH) joined T-SQL Tuesday with a German post: Self-Repairing-Monitoring Solution oder was einem noch so versprochen wird.

Key takeaways
Monitoring is a very wide, but also very important topic for all of us. Be proactive and monitor your databases, servers and environments (even test and development), and make it a goal to discover issues before your end users alert you. Invest in vendor solutions, use the tools available in SQL Server or write your own custom solutions, just make sure you monitor what is critical in your company. Capture data to see trends over time and use PowerShell to automate tasks. Also don’t forget to monitor the monitoring solutions!

Want to participate or read more?
Monitor the #tsql2sday hastag on Twitter the first week each month for the invitations. Steve Jones (@way0utwest) keeps an updated list of all previous T-SQL Tuesday Topics. If you ever need inspiration to write a blog post, just pick a topic and write away.

Thank you again to everyone who participated in T-SQL Tuesday #66! :)

Custom Real Time Monitoring Solution (T-SQL Tuesday #66)

T-SQL TuesdayT-SQL Tuesday #66 is hosted by me (yay, fun!) and is all about monitoring. We all monitor something while working with SQL Server, and there are so many topics to choose from. As a Data Warehouse developer I use Adam Machanic’s sp_WhoIsActive all the time, I look at the Integration Services Dashboard in SSMS to monitor SSIS package executions and I check the Job Activity Monitor for a quick overview of what’s currently running on our servers.

However, I decided to write about a custom Real Time Monitoring solution we use in my company (Storebrand) that my coworkers in Lithuania created. I couldn’t resist this opportunity to show one of our cool solutions that I get to use and to brag about how talented my coworkers are :)

Storebrand Real Time Monitoring
There are many great monitoring solutions available out there. In my department we actually use several solutions from vendors to monitor our SQL Servers and Business Intelligence environments, but we also had some very specific monitoring requirements. None of the vendor solutions available at the time were able to provide all the functionality we needed, so we built a custom real time monitoring solution:

  • Easily create and edit personal projects, or collaborate on shared projects
  • Web-based interface to access projects from any computer in the network
  • Pick and choose specific files, databases, cubes, jobs and even job steps to monitor in each project
  • Create status and quality checks, and subscribe to get alerts via e-mail or SMS if the checks fail

Continue reading →

Invitation to T-SQL Tuesday #66: Monitoring

T-SQL TuesdayWelcome to T-SQL Tuesday #66! My name is Cathrine Wilhelmsen (@cathrinew) and I’m very happy to be your host for the T-SQL Tuesday coming up on May 12th.

Much Monitoring in the Month of May
The topic of the month is monitoring. We all monitor something while working with SQL Server: Maybe you’re a DBA who monitors database performance, an SSIS developer who monitors job and package execution, an application developer who monitors queries or a BI analyst who monitors report usage? Do you use T-SQL or PowerShell scripts? Have you created your own monitoring reports or applications? Have you invested in a complete monitoring solution from a vendor? What is monitored manually and what is automated with alerts? If a brand new SQL Server professional in your role asks you for advice, what do you say is the number one most important thing to monitor? Have you ever stayed awake for a week solving a problem that could have been avoided with proper monitoring? Did you implement a monitoring solution that saved your company?

Be creative! There are so many topics to choose from, and you can of course write about anything you want as long as it is related to SQL Server and monitoring. It doesn’t matter if you’re new to SQL Server or have decades of experience, whether your blog post is short or long, an introduction or a deep dive, if you include scripts or screen shots… Just join the party and have fun while you share your knowledge. (And if you joined the #SQLNewBlogger challenge in April, this is a great way to keep blogging!)

What is this T-SQL Tuesday thing?
T-SQL Tuesday is a monthly blog party started by Adam Machanic (@AdamMachanic) back in 2009. The concept is simple: The host of the month picks a SQL-related topic and writes an invitation on the first Tuesday of the month. All bloggers are welcome to join in, they have one week to write a blog post about the chosen topic and publish their post on the second Tuesday of the month. The host then writes a summary of all the participating blog posts.

How do I participate?

  1. Write a blog post about Monitoring
  2. Include the T-SQL Tuesday logo and link it back to this invitation blog post
  3. Publish your blog post on Tuesday, May 12th between 00:00 and 23:59 GMT
  4. Leave a reply below with the URL to your blog post (if you don’t see a pingback to it)
  5. Tweet about your blog post using the #tsql2sday hashtag (and if you really want to make sure I see your post you can also add my Twitter handle @cathrinew)

That’s it! I can’t wait to read your blog posts and learn something new :) Ready… set… blog!

Using a Numbers Table in SQL Server to insert test data (T-SQL Tuesday #65)

T-SQL Tuesday

T-SQL Tuesday #65 is hosted by Mike Donnelly (@SQLMD). There is no specific topic to write about this month, Mike simply wants us to learn something new and then write a blog post to teach it to others. I want to share something that I only recently learned, something I wish I had known about years ago, something that became part of my toolbox as soon as I discovered it: the Numbers Table. It is so simple and solves so many problems that everyone should know about it :)

A Numbers Table (perhaps most known as a Tally Table, sometimes called an Auxiliary Table of Numbers and even referred to as the Swiss Army Knife of SQL Server) is a one-column helper table that contains the numbers 1, 2, 3, 4, 5 and so on all the way up to the-highest-number-you-could-possibly-need.

It can be used to replace slower loops and row-by-row operations with faster set-based operations, generate dates, split strings, find gaps in data sets, expand data sets, insert test data and probably hundreds of other things. There are so many great and detailed articles already published about this topic, so I will stick to the T-SQL Tuesday topic of “Teach Something New” and share the two most recent things I learned: different ways to create a numbers table, and different ways to quickly insert test data by using a numbers table.

Continue reading →

Giving back to the SQL Community in 2015 (T-SQL Tuesday #61)

T-SQL TuesdayWayne Sheffield (@DBAWayne) is hosting T-SQL Tuesday #61, the last one of the year, and he wants to know how you plan on giving back to the SQL community in 2015.

My short answer is to continue to do everything I did in 2014 – and some more.

In 2014 we restarted our local PASS chapter, SQL Server User Group Norway, and in 2015 I will continue to be a board member and help organize meetings. It has been great to see our chapter grow this year. We have more members, but more importantly, we are slowly becoming a community. Members have volunteered to help out with events, they want to start speaking, and it’s great to see former coworkers catch up with each other at meetings.

We also organized the first SQLSaturday in Norway in 2014, and we have already started planning our next SQLSaturday. (We’re aiming for August 29th, so save the date!) It was a lot of hard work organizing the event this year, but it was such an amazing learning experience that I can’t wait to start working on the next event. I’m so happy that we can give the Norwegian SQL community a day of high-quality free training!

Volunteering is fun and I’ve become slightly addicted to it. I’m planning to be a helper at SQLBits XIV and I have also told the SQLRally Nordic organizers that I’m happy to volunteer if needed. The same goes for all the other PASS events. I hope I can help out again at PASS Summit 2015 and 24 Hours of PASS, and I also want to join the Program Committee and learn from that.

Next year I want to challenge myself to speak and blog more. I love to do both, but they terrify me because they push me out of my comfort zone. There will always be someone who knows more than me, who has more experience than me, who has presented something before me, but I don’t want that to stop me. I wouldn’t be where I am today if others hadn’t shared their knowledge, and hopefully I will be able to help someone by sharing my knowledge. Also, I can’t think of a better way to learn something than to figure out how to teach it to others, so I’m really just being selfish.

And lastly, I will be hosting a T-SQL Tuesday next year! I thought it was fun to share the news in a T-SQL Tuesday post about giving back to the SQL community :)