SQLbits: A day with Brent Ozar and his talk about performance tuning when you can’t fix queries
*Beep beep*: my alarm clock. Whoat!? 8 AM already? I thought I had it set to 7:45. Bollocks. Now I have to hurry for the one class I did not want to miss this weekend. My alarm clock was set too late and although I streamlined my routine for this morning – had my bag packed, ready to go – I still had to shower. Anyway, I thought showering would be the civilized way to go.
When I entered the convention center I found “her”, the only pretty girl about my age, already there, sitting in one of the best spots. I was left with a spot next to an indian guy who had a cold so couldn’s stop coughing through the presentation. If you stop hearing from me after this, it is because I have got his thing as well…
Anyway, Brent is already on stage. The first thing I thought was: “Damn! He’s much taller in real life!” I am 178 (cm) so not very short. Brent is like.. Ten centimeters taller than me. I console myself with the fact that he has a belly (he didn’t have that when he did the mankini presentation) and I have not. We were given a “little” handout. I put quotes in there because it’s an understatement here. This thing looks like it’s the collected works of Brent Ozar, soon to be on a shelf next to Shakespeare’s combined works. This initial thought proved to be true, because there is so much in this book that Brent had to skip over complete presentations as he was doing his talk.
Let’s talk about the first session: I came here, because I am an enterprise DBA and I want to know what I can do to help my business succeed, without my developers being able to change the code. Let’s say, you are in a company, and your managers have bought this shiny new product, without consulting you (of course, why would they, you are only the one who keeps things running 😉 ). And you will lose support if you ever modify the product. Hmmm. Tough question. His short answer: change indexes in the database, but be sure you mark these indexes. For instance by preceding the index names with your company’s name, so when the guy from MS* support comes in to fix something, you can change things quickly to how they were out of the box. Same goes for server settings like MAXDOP. If you see MAXDOP causing a problem (either SOS_SCHEDULER_YIELD or CXPACKET), sure… Change your SQL server’s settings, but be sure to change it back when the support guys come rolling in. Damn, I love this guy.
Coffee break. Brent asked us to leave him alone so he could get his coffee too, just like the rest of us. After that, we were allowed to ask questions.
I went to get a quick coffee, and decided I would go straight towards my ultimate goal for this week: I wanted a selfie with Brent Ozar. Back in Holland, I had been bragging about this, so now, even though I am a naturally shy person, I couldn’t back out anymore. So there I was, standing in a group of guys, who were asking all kinds of sql related questions. I was next. Brent pointed at me and said: “Ok, I have to continue this presentation, so you are the last one for now…. I looked at the guy behind me and felt a bit ashamed and told Brent: “Well… I don’t have a SQL question, I just want to make my coworkers jealous… Will you be in a selfie with me?” “Sure”, he said, smiling his broad Brent Ozar smile. So I got my Samsung S3 out, hands shaking, and tried to make a selfie, pushed the wrong button and turned my phone off. “Don’t worry”, Brent said, “this happens to me all the time”. Anyway, I got the camera app running again, and here is the result:
Anyway, back to the contents of Brent’s presentation.
Brent started off by asking how much of us have gotten questions like: “My SQL Server is slow, can you take a look at it?”. 200 arms went up, duh :). “Users are the enemy” is a well known adage between IT people, but we know they can’t help it. They just want to be able to do their jobs, and we as DBA’s are here to help them do it.
Brent introduced a couple of basic stats, while comparing SQL Server to different types of cars. Have you got a Volkswagen Beetle, or do you own a Ferrari. So, these were the questions: (I won’t break into elaborate explanations of these as you can find these on MS Books Online, and I consider these to be very basic. If you think otherwise, please let me know in the comments)
1. How fast are we going? –> Batch requests per second
2. How hard are we working to achieve that speed? –> sys.dm_os_wait_stats: what is your server waiting on. You should store this metric periodically so you can see when things change for your server. Check wait time per core per second, as percentages don’t mean anything. Your server could be virtually idle and display a lot of IO waits that amount to fairly nothing when it comes to query durations.
3. How much weight are we carrying? –> Total data size and/or number of DB’s.
For all the above, Brent put his sp_AskBrent stored procedure online for free. Try it out.
Baseline, baseline, baseline! Store your baselines based on month, day of month, weekday vs weekend and hour of day because this will tell you about your normal workload. Your server can be very busy, but that doesn’t mean anything if it’s the last day of the month and it is busy doing calculations on the previous month, or year or whatever. Your baseline can help you determine what workload is to be expected today.
Your business wants to go faster. Brent introduced a matrix, which we can use to ask questions to management and application owners to determine what things we are allowed to change in our database (for instance, MS Sharepoint doesn’t allow you to change anything in the DB. If you do, you will lose support). Brent’s advice: prefix all your custom indexes with say, your company name, so when you have a support call and your supplier comes in, you can quickly drop your custom indexes and be supported again.
Right now it’s 6:33 PM, so I have to get cooking. Of course, there were many more topics that Brent discussed, maybe I will blog about those later..