It’s here. Today’s the first day of SQLskills training.
I will not be live-tweeting or live-blogging the training (both impossible) but I am going to try and provide one update during lunch, and another at the end of the day. We’ll see how that goes! I have no idea what that will do in my RSS feed if you’re following my posts that way…we’ll just figure that out as we go along.
I arrived yesterday and the wonderful Yanni Robel ( b | t ) picked me up from the airport and we spent a wonderful afternoon at her house, and John made us dinner (thank you John, it was fantastic!). When I arrived at the hotel around 8:30, I pretty much unpacked and went to bed. This morning I got up early and went for a run. The weather was awesome. Thank you Microsoft for the large building near the hotel which prevented me from getting too lost.
As I type, I’m in our classroom for the week, waiting for breakfast. Here is what greets each attendee:
My desk for the week
I am relaxed and ready. Time for breakfast and some socializing with the other attendees before we start. Bring it.
Note: The Diet Coke comes out at 9:15. Waiting…patiently… 🙂
10:00 AM
Kimberly, Paul and Jonathan spent the first hour or so of the first day covering logistics. They’re luring us in. We are all ready to get started, but honestly, if they came out guns blazing talking about storage, we might cry. Actually, I might cry anyway because even though we haven’t started the first module, I feel stupid. Stupid as in I know nothing about SQL Server. Intellectually I know this is a phase, but it is really hard to not compare one’s knowledge level to theirs when they’re talking.
As an aside, of the 37 attendees, only 5 are women (note: I orignally posted that there were 4 women…I wasn’t counting me…classic). What’s cool is the number of attendees from outside the US (somewhere between 5 and 10, I lost count).
Ah, I have my Diet Coke. Now I’m really ready.
12:30 PM
We just finished Module 1. It was a lot of information, but a lot of it was review so I’m feeling pretty good (although very hungry!). I took a lot of notes, and here two new things I learned this morning:
- There is a hard limit of 32 outstanding asynchronous I/Os against a database’s transaction log. You cannot do anything to change this number, check out the Diagnosing Transaction Log Performance Issues and Limits of the Log Manager post from the SQLCAT team for more info.
- When you begin a transaction, then do an insert, then do an update and then do a delete, and then rollback, the rollback occurs in backwards order. The transactions are chained backward, so the first transaction that is rolled back is the delete (and a log record written that the delete was rolled back), then the update is rolled back (with log record written) and then the insert (with log record written). Why does this matter? Paul was discussing sequential versus random IO and most of the time, IO against the log is sequential, but there are cases when random writes occur. A rollback is one of them. Paul’s comment was that this is not optimal in terms of performance, but hopefully it doesn’t happen often.
All right, time to start Module 2…Waits and Queues.
10:30 PM
Most of my knowledge about Waits and Queues comes from the reading I have done, and I have seen a fair bit of data from customer environments. However, it’s always nice to hear someone (especially someone like Paul) talk through concepts and provide demos, and this module was no exception.
A few basics about Waits and Queues that are always good to review:
Any time a thread has to wait for something to occur, SQL Server tracks that a wait occurred, how long it had to wait, and the resource on which it was waiting. There are three states that a thread can be in:
- Running – the thread is actively executing something on a scheduler; a scheduler can only have one thread that is actively running.
- Suspended – When a thread needs a resource that is not available, it moves off the scheduler and goes into a suspended state until access to the resource is granted.
- Runnable – Once the resource for a thread becomes available, the thread moves to the runnable queue which is a FIFO (First In First Out) queue*. This wait time is different than the time when the thread is waiting for the resource, and thus is referred to as the Signal wait. The thread has the resource, but it has to wait its turn to hop back on a scheduler.
*There is an exception to the queue being truly FIFO, which involves the Resource Governor and making use of Workload Priorities.
The sys.dm_os_wait_stats DMV only gives you total wait time (suspended + signal) and signal time, so you have to do some fun math to get the suspended time – or the time you’re truly waiting on a resource. Paul has a post which includes a query that does all the calculating for you.
Paul also talked about spin locks, and there are still a few things I want to think over before I write anything in a post, but I wrote this in my notes as he was explaining them, “Man he describes things well.”
Jonathan finished the day talking about some IO basics and he covered disks, SSDs, RAID and DAS. Something new I learned about SSDs: the newer models use wear leveling, which are algorithms that ensure that the controller doesn’t write to the same flash cells all the time, so that no one cell dies to fast.
Other random things I learned today:
- Kimberly, Paul and Jonathan’s laptops are ridiculously fast
- There’s a very useful filestats query from Jimmy May ( b | t ) that I need to download
- Redhook WIT may look like diluted orange juice, but it’s really pretty good
