Understanding your Wait Statistics

Download PDF

SQL Server Performance Tuning Tips – Wait Statistics

Wait statistics are one of commonly overlooked ways to quickly find out what is causing your SQL Server to be slow. One of the reasons that they are commonly overlooked is that without a monitoring tool, or your own custom monitoring tools, it is difficult to see how they are trending over time.

When someone reports that the database was slow yesterday at 2:00pm, do you know how to determine what was causing it to be slow? You can check the logs, you can look at the history of running jobs, and you might even ask around to see if anyone was doing anything unusual at that time. If you are tracking wait statistics, it is quick and easy to zoom in on a point in time and see exactly what queries were slow and why they were slow.

You might be thinking the following:

  • Did someone run a slow ad-hoc query?
  • Was there something wrong with the network?
  • Did someone run an unscheduled SSIS ETL package?
  • Was an index being rebuilt?
  • Did someone change application code?
  • Was there a hardware failure of some kind?
  • Was DBCC CheckDB running?

What are Wait Statistics

Whenever SQL Server is waiting for something to happen it logs that information as a WAIT. If your SQL Server is waiting on I/O, that gets logged. If it’s waiting on network traffic to another SQL Server, that gets logged. If your database is waiting on a transaction to complete, that gets logged as well. Most everything that SQL Server waits on gets logged. However this information doesn’t stay around for long.

Read more ›

Posted in Database Health, Performance, Server Health Tagged with: , , , ,

System Outage… How do you communicate with your customers?

Download PDF

While watching a movie tonight, the Amazon Prime video system had an outage, no Amazon videos would play on my Amazon Fire TV Device. Netflix and Hulu worked fine, other internet traffic worked fine, just no Amazon Prime videos.

After going to Twitter, I did some looking around and could find not status update on the outage on the official Amazon twitter account. Twenty minutes passed and still no update. There were many tweets calling out the issues from all over the world to @Amazon, and #amazonPrime, however no official word. Many were complaining about shopping cart issues beyond the watching of videos, so this appeared to be more than just a video playback issue.

It made me wonder the following:

  • Do they know about the outage?
  • Are they working on it?
  • Is this a short term outage, or something more serious?
  • Do they care about their customers?
  • When will I be able to see the end of the movie?

As far as disaster recovery and system outages, the way that you communicate with your customers is one of the most important factors during the outage and the recovery. This is one of the key items that I teach during Disaster Recovery Training.

So far on this outage, Amazon has failed. No communication and nothing but promotions on their official Twitter feed. What a waste of a valuable resource that they could use to inform their customers. Is Amazon to big to communicate with their customers?

System Outage

As a customer or a consumer, what type of communication do you expect during a system outage?  Please post your response below.

Update: 30 minutes have passed, Amazon Prime videos still down, lots of people tweeting about the problem. No official word from Amazon. Some have posted that they phoned Amazon, and they heard that the service would be restored in an hour.

Update 45 minutes, my service is restored, still nothing on Twitter from Amazon. Many people still complaining about Prime video issues, some claiming it is fixed.

 

The lesson to be learned is that things happen, systems have outages, and I think that most people understand that. The important thing is how you communicate with your customers to let them know what is going on. Twitter, Facebook and other social media should be used as a tool to communicate with customers, not solely as a channel to push out marketing material. Even just a statement that says something like “we know about the problem and are working on it” would be enough to let people know the problem is being worked on.

How will you communicate with your customers in an outage scenario?

Posted in Uncategorized Tagged with: , ,

DBA on Vacation… Share your stories

Download PDF

Being the middle of the summer in the northern hemisphere, and with the Database Corruption Challenge over, I thought I would cover something a bit lighter, and not as hard core as database corruption.

Share Your DBA on Vacation Stories

If you have been a DBA for more than a year or two you probably have some story about going on vacation, and getting the call from the office with some database problem. It might have been that someone forgot their password, or something more serious like a corrupt database. Either way, the simple or more complex issues are not why you went on vacation.

 

Here is a picture of a DBA on vacation in Hawaii. Notice the empty chair… They should be sitting in the sun sipping a tropical drink, but instead they are back in the hotel room with a VPN connection to the office dealing with some emergency (or perceived emergency) SQL Server issue instead of enjoying the beach.

DBA on Vacation

 

DBA on Vacation (photo by Steve Stedman)

Read more ›

Posted in DBA Tagged with: , , , ,

Solution to Week 10 Part 1 Database Corruption Challenge

Download PDF

If you are looking for more info on the corrupt database with Week 10 of the Database Corruption Challenge, you can take a look at the week 10 Challenge Details.

With this being the final competition of the Database Corruption Challenge, I had to make it more difficult than the others, so I added 3 different types of corruption. This challenge was about the equivalent of any 3 of the other challenges.

I have split the solution to the Week 10 Challenge into 3 different posts, with each post explaining how to fix one of the three specific corrupt areas.

Corruption in the Orders Table

WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.

Read more ›

Posted in Corruption Tagged with: ,

Patrick Flynn Interview – Database Corruption Challenge

Download PDF

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants. This interview is Patrick Flynn.

Patrick placed in third with a 3 way tie for third overall in the the Database Corruption Challenge scoring 21 points.

Here are the overall statistics for Patrick in the Database Corruption Challenge

Read more ›

Posted in Corruption Tagged with: , ,

Neil Abrahams Interview – Database Corruption Challenge

Download PDF

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants. This interview is Neil Abrahams.NeilAbrahams

Neil placed in third place with a 3 way tie for third overall in the the Database Corruption Challenge scoring 21 points. He was one of only 3 participants to complete all 10 challenges in the contest, the other two were André Kamman and Rob Farley.

Here are the overall statistics for Neil in the Database Corruption Challenge

Read more ›

Posted in Corruption Tagged with: , ,

André Kamman Interview – Database Corruption Challenge

Download PDF

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge, finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants. This interview is André Kamman.

André placed second overall in the the Database Corruption Challenge scoring 22 points which pushed hiAndreKammanm above a 3 way tie for third place. He was one of only 3 participants to complete all 10 challenges in the contest, the other two were Rob Farley and Neil Abrahams.

Here are the overall statistics for André in the Database Corruption Challenge

Read more ›

Posted in Corruption Tagged with:

Raul Gonzalez Interview – Database Corruption Challenge

Download PDF

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge, finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants. This interview is with Raul Gonzalez.

Raul placed third (in a tie) overall in the Database Corruption Challenge behind Rob Farley, and André Kamman. Raul Gonzalez Interview

Here are the statistics for Raul in the Database Corruption Challenge

Raul Gonzalez Interview

Read more ›

Posted in Corruption Tagged with: ,

Rob Farley Interview – Database Corruption Challenge

Download PDF

After 10 weeks (or almost weekly) of corrupt databases, missing data and a challenging competition the Database Corruption Challenge, finally comes to an end. As part of the last week of the challenge I created a short blog interview for the participants.Rob Farley Interview

Rob Farley is the overall winner, the champion of the Database Corruption Challenge scoring more points than anyone else.  Congratulations Rob!

Rob was one of only 3 participants to complete all 10 challenges in the contest, the other two were André Kamman and Neil Abrahams.

Here are Robs statistics for the Database Corruption Challenge

Read more ›

Posted in Corruption Tagged with: , ,

Corruption Challenge – Your Opinion Counts

Download PDF

Now that the Database Corruption Challenge has come to an end, I am looking for some feedback from the participants to see what we can do next (if anything) and to learn from the experience.

I have created a survey, it is not too long, so please take a minute or two to provide some feedback. I would really appreciate it.

Please fill out the survey at Google Docs

One of the questions that I would love to hear from people on is:

  • What would you like to do next
  • Another 10 week Corruption Challenge
  • Take a break for a few weeks
  • 10 week Database Performance Challenge
  • Nothing more – I have had enough

So please take a minute to fill out the survey, your email and name is optional.

Thank you!

Posted in Corruption Tagged with: , ,

SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the SteveStedman.com blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA