SQLServerCentral.com

Always Striving to Improve

K. Brian Kelley — Sat, 07 Nov 2009 14:51:00 GMT

I'm enjoying a relaxing Saturday morning and I'm doing a bit of reading on ESPN. I see the article about Kobe reaching another scoring milestone and decide to give it a read. I'm admittedly a Lakers fan; I have been since I first watched Byron Scott knocking down outside shots. So naturally, I enjoy reading up on the Lakers and what their players are doing. It's a normal sports article talking about Kobe's scoring, and I begin to yawn, and then I see this:

Bryant visited Olajuwon over the summer to learn more about low-post play, and the Houston Rockets' famed center provided Kobe with even more skills to keep knocking down scoring marks.

Now that caught my attention. Here is a guy who just won the NBA championship... again. He's won a scoring title. He's considered the greatest closer in the game. Yes, even over Bron Bron. Don't believe me? go back and watch the end of the gold medal game against China. We were going to lose. And Kobe took over. Everybody, including Lebron, deferred to Kobe. And USA won. USA won because when Kobe gets that glint in his eye, nobody can stop him. He has too many offensive options. And if he happens to get hot as he's taking over a game? It's over and over in a hurry. He can beat you inside. He can beat you outside. He can beat in the low post or driving the paint. He can beat you when you're sleeping in your bed and he's two zones away watching Lionel Messi light it up on the pitch. So if anyone doesn't need to worry about his offensive game, it's Kobe. But Kobe obviously didn't rest on his laurels. He went and visited Hakeen "The Dream" Olajuwon to work on low-post play. Olajuwon had that post-up fade away jump shot no one could stop. He could up and under on a spin move that was equally unstoppable. It was all part of his "Dream Shake" package. And that's who Kobe went to learn from.

And that reminded me that I can never stop learning. There's always more to do. There's always more to understand. If Kobe can go to Olajuwon, I can go to the top folks in whatever field or endeavor I'm working on, whether it be SQL Server, the Bible, flute, cooking, chess, or something else. Speaking of chess, I'll end on another guy who hasn't stopped, Victor Korchnoi. At the age of 78 he won the Swiss Chess Championship. He continues to play well at grandmaster levels, despite his advanced age. That's an inspiration to keep pushing hard to grow and do better if there ever was one.

Thursday at the PASS Community Summit

Brad M. McGehee — Sat, 07 Nov 2009 14:40:09 GMT

The last full day of the 2009 PASS Community Summit was the first day I have had at the conference to attend sessions all day long. My day started with two keynotes. After the obligatory vendor keynote, Dr. David DeWitt presented a keynote on future trends in data and storage technology in SQL Server, which was well-received by the audience. See Steve Jones’ blog for more information on the keynote.

After the keynote, I attended these sessions:

Capturing and Analyzing File & Wait Stats by Andrew Kelly
Troubleshooting Applications Accessing SQL Server by Microsoft CSS Experts
Solid State Disks and SQL 2008: High Availability and Performance by Jamon Bowen
Lies, Damned Lies and Statistics by Gail Shaw

While I have been attended anywhere from 3 to 6 conferences for the past 4 years, I always find out something in a session that I never knew before. I like to select sessions on topics I know (always looking for some new tidbit), sessions on topics that are completely new to me, or sessions from people I know, like Andrew Kelly and Gail Shaw.

After the day was over, I attended the Friends of Red Gate Software Dinner with about 45 people, including many SQL Server MVPs, and many people who write for SQLServerCentral.com and Simple-Talk.com.

I was unable to stay for the post-con sessions because I had to attend a Red Gate Publishing Department meeting Friday and Saturday. On Sunday, I will be flying to Las Vegas to make three presentations at SQL Server Connections.

Tampa Code Camp

Adam Jorgensen — Sat, 07 Nov 2009 01:09:09 GMT

Looking forward to a great Tamp Code Camp tomorrow. My first code camp. I’ve had some great experiences at the one day conferences, but am really looking forward to my first code camp. The session list looks great! Can’t wait to see everyone there!

Check it out at - www.tampacodecamp.com

Identity Issues

Adam Jorgensen — Fri, 06 Nov 2009 18:43:00 GMT

Have you ever received the following Error when developing your ETL or working with data cleanup?

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table 'tblDemo' when IDENTITY_INSERT is set to OFF.

This is because the table has a column marked for identity. The best way around this is to use a table option called IDENTITY_INSERT. This will allow you to insert a row into a table including an identity value for troubleshooting, issue resolution, or reseeding the table.

In order to do this, you need to enable IDENTITY_INSERT as demonstrated below:

SET IDENTITY_INSERT tblDemo ON

INSERT tblDemo(Table1PK, ColumnDescription)

VALUES (3, 'First Row')

SET IDENTITY_INSERT tblDemo OFF

Couple of things to point out about using IDENTITY_ INSERT:

- It can only be enabled for one particular table at a time.

- The user issuing the query must own the object in question.

- You must specify the identity column value when this option is enabled.

- If you are inserting a value greater than the current maximum value for that identity, the identity column will be reseeded with that new value.

This is a VERY powerful option and can wreck havoc is used improperly. Be very careful if you are ever using this in production.

Great Post on Parallel vs.. Series SSIS

Adam Jorgensen — Fri, 06 Nov 2009 14:34:11 GMT

Mike Davis, Author and consultant extraordinaire recently posted a great new take on parallel vs. series SSIS packages. Some interesting results and Myths dispelled here :

http://blogs.pragmaticworks.com/mike_davis/2009/11/ssis-parallel-vs-series-data-flows.html

Check it out !

Geek of the Week

Grant Fritchey — Fri, 06 Nov 2009 14:05:05 GMT

I made geek of the week. I’m sorry, but I’m excited by that. I’m very proud to be a geek. Thanks guys.

PASS Summit 2009 Day 3

Grant Fritchey — Fri, 06 Nov 2009 13:47:51 GMT

The day started off with a mixed bag. First we had an honestly tearful farewell with Wayne Snyder saying goodbye to Kevin Kline, leaving the board for the first time since PASS was founded. This was followed by a painfully dull session with Dell all about their commitment to bread & butter DBA concerns. That was followed by Dr. DeWitt doing a deep dive into the history and the future of computing, showing and teaching in ways that only the very best can achieve. It was a fantastic performance, entertaining, enlightening, amazing… Just flat out incredible. It’s the kind of understanding that you wish you could get about most things, most of the time. Unfortunately, it came to an end.

Today I finally got to hit a lot of sessions. First I saw Andrew Kelly give a session on “Capturing and Analyzing File & Wait Stats.” It was great. Andrew Kelly is a good presenter and he knows this topic forwards and backwards. That makes it very easy to sit and learn from him. It’s the kind of useful information you can really take advantage of in your job. For lunch I went to a book signing to find out that both my books were sold out. A few people, including @sqlbelle, stopped by to get books signed. It was a real honor and privilege for them to do that. After that I went to two Buck Woody sessions, back-to-back. After the session yesterday I couldn’t have missed them. The first session was on “SQL Server Automation on Steroids.” The slide deck was laid out to look like a Zune. It was great stuff on fundamentals like how to configure SQL Agent, and drill downs on mechanisms for working with PowerShell, or POSH as Buck calls it. He showed several different scripts and I’m pretty jazzed to continue my pursuit of POSH skills after his session and Allen White’s earlier in the week. Yes, this sort of reinforcement of session on session with different people giving different views of the same tools used in varying ways is something you can only get at the PASS Summit. His second session was on “Performance Tuning with SQL Server 2008.” While I didn’t find it as technically useful as the previous two sessions I’d seen him do, it was every bit as entertaining and enlightening. He made my list of must see presenters. I finished out the day, and the PASS Summit, at Gail Shaw’s “Lies, damned lies and statistics.” Gail presented fantastic information in her clear, informative style. If you needed to know something about statistics, she laid them out for you in this session. Things were a bit subdued, this being the end of the Summit (not counting the post-conference) but Gail got the audience up and awake with some great demo’s and explanations of how statistics works inside SQL Server.

After hours it was off to the Friends of Red Gate party. I’m a friend of Red Gate because I sing the praises of their products, which are absolutely praiseworthy. But, I’ll tell you, I might be inspired to sing at least one praise more because of the meal we had. Nice food at a nice resteraunt with great, impassioned people, excited about what they do. It’s hard to enjoy things more.

So that’s the end of the Summit proper for me. I’ll be staying in Seattle through Friday because of a series of events that Microsoft is holding, but I won’t be blogging about them here. This has been one of the best PASS conferences I’ve been to, out of the five that I’ve attended.

OFF-TOPIC: Surviving, Missing Folks, Glad I'm Home

K. Brian Kelley — Fri, 06 Nov 2009 07:16:00 GMT

I certainly wish circumstances were different and I would have been able to attend the PASS Summit this week. I miss being part of the active community and seeing old friends and meeting new ones, but I'm glad I was here at home. Being home means I've been able to tend to my wife. That's my job, more so than any regular employment or professional commitment. She's recovering, still in pain, requiring the Motrin that was prescribed. She's also still very tired as her body heals up from the pregnancy. Once we found out the twins had passed, they went through a process to induce similar to a normal pregnancy and birth, so her body is recovering just as it would from a regular pregnancy. And that means she gets tired more easily. I've seen her regain more of her strength each day, but she still gets more tired than she does normally. Being here means I can take on kid wrangling, fix the meals (although this has been made easier since our church family has, with grace and generosity, provided all of her dinners and some of our lunches since Sunday night), and take care of other things that she might not feel up to do. So we're surviving, we're not over the physical part yet, and I know it'll be a while for us to recover emotionally and mentally.

Speaking of which, I went back to some advice my dad gave me after we found out the news. He made a point of telling me to ensure that our four year-old wasn't neglected. He had been through this when his mother lost her twins, and as the baby of the family, he felt like he was kind of shoved to the side. This is something he still remembers in a very raw and painful way. While he knows that it wasn't intentional, that doesn't erase the memory of the pain he felt. Now my father is a retired Marine Gunnery Sergeant (like fellow Kelley/Kelly, Andy Kelly) and he's an old-school Marine among old-school Marines. So he typically keeps his emotions close to his vest. I know for him to share that meant it was a pretty significant memory and a time of great pain in his life. As a result, we've purposely have looked to ensure all three kids have received a good deal of attention. This includes my oldest, who will soon be turning 12, because of his age and his tenderness I think he took the hit almost as hard as Kimberly and I did. He has always had a tender and loving heart. It's one of the things about him I can't take any credit for but I'm extremely proud of him over. But it also means in times like this he hurts and hurts a lot.

In order to cope I've turned more to reading and to music, both playing and listening. Hymns and jazz music are certainly a salve for my soul. I've also delved back into poetry, specifically poems of faith. Thankfully, I've got a great library here in Columbia, SC, and it had a book entitled The Poetry of Piety. I don't see it available for order anywhere any longer, or I would link to it, but it's been a good read thus far. I'm barely into chapter two, and I've enjoyed what I've read.

(Editorial Warning: If you're not interested in hearing about matters of faith because that's not why you follow this blog, then what follows is exactly that. Just wanted to give you fair warning.)

For instance, chapter one covered Sir Walter Raleigh's epitaph, which was:

Even such is Time, which takes in trust
Our youth, our joys, and all we have,
And pays us but with age and dust;
Who in the dark and silent grave,
When we have wandered all our ways,
Shuts up the story of our days.
But from which earth and grave and dust
The Lord shall raise me up, I trust.

The message of this epitaph is that time will eventually get us. The joys and energy we have in our youth will succumb to old age and then eventually death. It's an inevitable marching in that direction. However, Sir Walter Raleigh was putting his trust in the promise that God would raise Him up again in the future. This is a central message of Christianity, and it was one he was holding tight to. The book points out that this epitaph was reportedly written the night before his execution, and if that's the case, is a reflection of the final thoughts of a man who had done and seen much in life. He didn't reflect upon his accomplishments or his family or anything else except a promise from his faith. This was a welcome reminder that I believe in something more than this present life. And that I believe that there is hope beyond what I can touch and see. I know some would think I'm naive, silly, foolish, an idiot, or even a bit unstable because I cling to such faith. But in a time such as the present, that faith has steered me through. It has always steered me through, both in rough times and in good ones. And I hope to end life with a similar sentiment and conviction as Sir Walter Raleigh did.

Thanks to PASS Board Members Finishing Up Their Terms

Andy Warren — Fri, 06 Nov 2009 06:45:00 GMT

We’re losing three good people as of the end of the year, but I thought now would be a good time to mention their names and add a comment or two from me:

Pat Wright – Pat is quiet, thoughtful, and worked hard to make PASS good for volunteers, especially at the Summit. He’s dropping back to a less demanding level of volunteering, but expect to see him in and around PASS events going forward
Greg Low – Greg did a great job on building chapters, and did a lot to help me see the challenges/values that come from taking an international view of PASS. Like Pat he plans to stay engaged at a reduced effort level and may run for the board again in a year or two.
Kevin Kline – Kevin has spent 10 years building PASS, doing about every job there is. He’s been a good mentor to me as well (though I don’t always listen!). Kevin will be taking a break I think, available for conversation but enjoying some time to just focus on job and family for a while.

I appreciate all that each of you did and the things you did for PASS. I know it was hard work and not everyone saw all that work, but you’ve moved things forward and I hope the rest of us can continue that work. Thanks!

PASS Summit Day 2 – What More Can I Learn

Jack Corbett — Fri, 06 Nov 2009 03:15:00 GMT

I actually slept in on Wednesday until 6am. This was a change from being wide awake at 4am. I made my way over to the convention center and was ~~roped into~~ invited to an informal meeting of bloggers with Andy Warren (@sqlandy) and a member of the PASS marketing team. It was a discussion of how PASS and SQL Server bloggers can work together to get the message of PASS out to the community. It was more of a brain storming session than a meeting.

When the meeting closed I spent some time with Gail Shaw and Bob Hovious before the opening remarks and keynote. PASS recognized some of the key volunteers, Tim Ford (@sqlagentman), Grant Fritchey (@gfritchey), and a few others. Then it was time for the PASSion awards. The international PASSion award went to Charlie Hanania while the U.S. award when to Allen Kinsel (@sqlinsaneo), both of whom did yeoman's work for PASS and certainly earned their awards. After the awards were announced I took a break and worked on blogging my experiences.

Paul Randal – Logging and Recovery

This was my first session of the day. Because I read Paul’s blog I did know a lot of the material, but he definitely expanded and explained more. One of his main points is that the Transaction Log is the most important component of the database as ALL changes must be written to the transaction log BEFORE they can be written to the data files. Some takeaways were:

Each data page has the last LSN (Log Sequence Number) stamped on the page for recovery purposes.
While there are minimally logged operations there are NO non-logged operations.
You can enable trace flag 3502 to see checkpoint operations.
A checkpoint writes ALL dirty pages from memory to the log file, even pages that are part of an uncommitted transaction.
The log always reserves enough space to rollback open transactions.
The log cannot be “cleared” (have virtual log files marked as available) while a Full or Differential backup is occurring, even if there is a simultaneous log backup taken.
Only log backups “clear” the transaction log, Full and Differential do not.

Very interesting material on the details of the transaction log and how it is used in recovery.

Lunch

I enjoyed lunch with some newly made Christian friends again, Mike Walsh, Brain Moran, Peter Schott, and Erik Veerman. Always good to spend time with men of faith.

Louis Davidson – Database Design

Louis has an interesting presenting style where he uses a lot of humor to make his point. His main premise is that taking the time to have a well designed database at the start, saves much time later in the process.

Gail Shaw – Insight into Indexes

Gail’s session was about how to find out information about your indexes using the Dynamic Management Views and Functions. I did know a bit about this, but I did not know about sys.dm_index_operational_stats which can help you see what is happening to indexes (inserts, updates, deletes) and how they are affecting locking, i.e. the number of locks taken on the resource by type (page, row). Page Latch (waiting for access to page in memory) and Page IO Latch (waiting for pages to be written to disk or read from disk) wait information is also available in this DMV. Gail did a great job using a book to demonstrate how indexes work, a very good visual example.

Panel Discussion with the PASS Board of Directors

For the first time the BoD made themselves available for a question and answer session, moderated by former board member Joe Webb. This was a late addition to the schedule, which contributed to the light turnout, about 15-20 people, not counting the board members. One of the key points to come out is that there needs to be better communication between the board and other parties (members, chapter leaders, and sponsors/partners). Chapter leaders would like more support from PASS and PASS would like more information from the chapter leaders.

Steve Jones (@way0utwest) asked for more transparency and publication of the goals and accomplishments for each board member. His point is that, to be honest, the community really doesn’t know what they are doing/have done, and they should be publishing their accomplishments.

Another interesting issue, raised by Jessica Moss, was that we, the community, need to know who to contact and how to go about presenting ideas we have for the organization. Kevin Kline (past president) answered that the board was working on a “process” for this. Jessica followed up by asking “When will this process be available?” and, unfortunately, there was not a concrete answer given, probably the low point of the session.

Lastly I asked about the recent BoD election controversy and if that caused them to consider changing the election process. There was clear, “Yes”, and that there may need to be changes made to the by-laws to make the election process better. I also commented that I’d like to see the BoD aim higher for the number of voters than the 1100 or so they are looking for next year. Granted that would be double the votes cast this year, but still a small percentage of membership (30000). I took some grief from Andy Warren as I would not/could not provide a realistic number to shoot for for next year’s election. I’ll be thinking about this later.

It was, I think, a positive experience for both the BoD and those from the community who attended and I applaud the efforts being made by the BoD to be more transparent and make themselves more easily available to the membership. As a relative newcomer to PASS, I can’t speak for how available they have been in the past, but I do believe that they are doing the best they can.

I certainly haven’t covered everything that was discussed, but I covered what I remembered.

I also want to thank the board for making themselves available for this session, it was definitely a step in the right direction. I also want to thank Bill Graziano (@billgraziano) for being willing to hang around a while longer to continue the discussion.

Please take the time to email or talk to the board members and bring them your ideas and goals for PASS. I believe that they truly want to serve the needs of the community, but without feedback they can’t know if they are focusing on the areas we, as a community, are most concerned about.

In the evening

I went over to the party provided by Microsoft at Gameworks, the biggest arcade/game room, I have ever been in. There was good food and unlimited game play made available. I grabbed some food and visited with some of my new friends while there and then went out with old friends before heading in for an early evening (10:00pm).

Trying to save up energy for my last day at the Summit where I will get to “host” the OPASS table at the Chapter Leader lunch.

Thoughts on a PASSing Scene...

Robert Pearl — Fri, 06 Nov 2009 02:06:00 GMT

Well it certainly has been a busy and exciting week at PASS. I couldn't go this year, but through the magic of SSC.com, the blogosphere and personal email from Seattle, I was there in spirit. You might say I was virtually there.

Speaking of virtual, one of the sessions I've followed was the Virtualization VC Breakfast Session, sponsored by VMWare, highlighting real-life experiences and challenges with SQL Server in virtualized environments. The debate to virtualize SQL or not continues.There's been a lot on this in the news and events this week, and I'll add my two-cents next week.

I even got pinged by one of my seasoned BI colleagues, Chuck Kelley manning one of the booths at PASS, hoping to hook up with Brian Knight. I left a message, but there's always a next time :-) So, let's hear some feedback on Brian's politically incorrect presentation.

My new buddies from India, Jacob Sebastian and Pinal Dave were there, as well Greg Larsen, who was given honorable mention in Andy Warren's blog as one of those to watch. And as if Steve Jones didn't have enough to think about on the way out the door to take off for Seattle, I pinged him about when my next article's going to be published. (Sorry man, I hope it wasn't my fault on the luggage mishap)

Ok, I dropped enough names, I feel like a gossip columnist. No offense to the ones I didn't. During this time, I made some new friends and connections in the SQL World. I hope to collaborate with them and bring some new and great contributions to the SQL community.

My blog has been appropriately light this week, making room for all the PASS MVPs, attendees and presenters, bringing us up-to-date coverage from Seattle. I didn't want to compete with all this, so I'll pick up the pace in the week to come.

So, in absence of technical content, and to keep those who didn't attend PASS in a good mood, I launched my first DB Audit challenge #1 (still one more day to play, before I announce the winner(s).) I will mention and profile those who participated.

Check out the challenge at http://www.sqlservercentral.com/blogs/pearlknows/archive/2009/11/01/db-audit-challenge-1.aspx
I've received very positive feedback and thank you's on this, so I will do my best to bring a new challenge soon.

Finally, maybe I'll have an MVP Profile of the week again, but this week at PASS, all our MVPs and presenters there deserve to be named MVP of the week for their extraordinary, outstanding contributions and ongoing service to the SQL Server community!

Great job ALL! On behalf of the sql community at large, we thank you!

PASS Summit 2009 Key Note 3

Grant Fritchey — Fri, 06 Nov 2009 01:24:09 GMT

Dr. David DeWitt

“I’m not a doctor.” This is going to be good. “From 1 to 1000 MIPS” He’s doing great. He was a fantastic last year and I knew this was going to be good. 32 years in academia and only in MS for 1.5 years. He’s the blue sky guy, and beleive me when I tell you, you get smarter from being in the room with him. And a huge ovation because he told us that he’s going technical and not covering a marketing pitch.

Right. This one is going to be hard to blog. He’s going through information quick. It’s all good. He’s giving an academic talk about the 30 years of technology trends in databases. He’s going over how the trends have affected OLTP and why the trends are forcing DBMS to evolve and some future trends to keep an eye on.

Back in 1980 we had a Digital VAX with a 1MIP CPU, 8mb of memory, 80mb disk drive… all for $250K. Yeah, the “good old days” Not. More or less 1000 times better for Cache & memory, 2000 times better for CPU and 10000 times for disks. But transfer rates have only increased by 65X. And for seeks there has only been a 10x improvement. 1985, 100TPS & 400 I/O/second. Today, 25,000 TPS & 100,000 I/O/second. His key point is that it took 14 drives to keep a single processor busy in 1985 but it takes 300 drives to keep a cpu busy today. He believes that SSD and phase change memory are the only things that are going to make a difference here.

His comparison of the problem is that today we’re in the situation where we have a gigantic water tank to feed water to the hole town, but only a garden hose to drain it. That’s scary to think about. When thinking about it, he compared the disk to cpu ratio and came to the conclusion that drives are 150x SLOWER today than they were 30 years ago.

So his basic conclusion is that we need to avoid random I/O in our database applications…. uh, yeah, good advice, I guess, but how the heck do we do that? Obviously we don’t and that’s not what he’s saying. He’s making a great point. This is just flat out fantastic.

In the old days it was 6 cycles to access memory. Now it’s 200 cycles to access memory. Despite all these improvements, we’re really messing up our scalability on the hardware because of limitations in the design. Hard to believe when you consider that everything is, in fact, faster, but it’s just not faster the right way. It’s scaling too hard.

He’s showing how the queries work within the system, what happens, literally, what is waiting on what, all the way down at the lowest level of the process. Scan queries vs. join queries. So why so many stalls? It’s the L2 data cache stalls, an artifact of the last 30 years of design within systems. Why? Well, you’d need to be here to really understand. I’m not going to repeat it correctly. For each record, there’s an L2 & L1 cache miss from each page as it reads from the disk, row-by-flipping-row. So basically, we’re getting RBAR within the CPU when it has to read from the disk.

There is a new memory configuration called a column store where the columns are broken down instead of rows. The columns are stored on files/pages. He’s showing how ID would be one page, Name on 2, City on 12, etc. because otherwise the rows are stored across pages. Without compression, it changes the amount of data on a page. If they also add compression. It’s going to make a huge difference. BUT, it’s not updateable. They’re going to make the DB a perfect decision support engine. Showing some drill down on the details, he showed about a 7x improvement in speed, without compression.

Then, you put compression on top of it and stuff really takes off. But remember, this is a read win. It’s write lose.

Really, I’m going to stop trying to keep up. I’m starting to lose track of everything anyway. I think this might be available online or on the DVD. I sure hope it is. If you were at the Summit and you didn’t attend this… you messed up, big time.

The key to compression is to remember that you need to get more data onto the disk because the CPU is 1000x faster but the disk is only 65x faster. That’s why you compress. Not because you’re saving space, but because you’re saving READS. I’m going back to the office to turn on compression on everything (after testing & verification).

I can’t describe it all, but the early/late materialization performance differences are amazing.

Key points for the quiz. yes, I’m giving out the quiz answers, call Bluto and D-Day:

At first glance hardware folks would appear to be our friends
Huge inexpensive disks have enabled us to cod-effectively store vast quantities of data
On the other hand ONLY a 10x improvement in random disk access and 65x improvement in through point
Two pronged solution for “read” intesnsive data waroehouse workloads – parallel db technology & column stores
Column stores, minimize transfer, facilitate compression, minimize memory stalls
But column stores don’t work for OLTP, AT ALL
Hardware trends and demands are forcing DB systems to evolve through specialization

But, I missed to two bullets because I couldn’t type fast enough. So you might still fail the quiz.

This will be on the DVD. Thank you PASS. Thank you Dr. DeWitt.

PASS Day 3 – PASS Summit 2009

Andy Warren — Fri, 06 Nov 2009 00:37:55 GMT

Started the day at 6:30 at Top Pot with a meeting about SQLSaturday over coffee and it really ended up being a discussion about chapters as much as anything. Jack Corbett, Kendal Van Dyke, Rob Hatton, Pam Shaw, Patrick Leblanc, Tim Mitchell, Greg Larsen, and Stuart Ainsworth joined me. I love their passion for community! I see them as the next generation of leaders – get to know them!

8:40 am

Bill Graziano doing the keynote, started by thanking Greg Low and Pat Wright for their contributions to PASS for serving on the Board for the past two years. Then they did a great slide presentation with music showing pictures of Kevin Kline, then brought him up on stage to say thanks, brought Wayne Synder out to give him a thank you award. Very emotional Wayne starts talking about a picture of Kevin of sleeping – hard to describe, Wayne crying with emotion – kudos to Wayne for showing what he feels about how much Kevin has done over the years (Kevin served on the original board back in 1999 and continuously since then). Then reviewed existing board members and the new ones, then the new Executive Committee (Rushabh, Bill, Rick H).

European Conference April 21-23, 2020 in Neuss Germany, PASS Summit in Seattle next year, Nov 8-11, early bird rate is $995 through Dec 2009. Talked about the location of 2011 conference not set yet. Showed a picture of PASS HQ staff.

8:50 am

Patrick Ortiz from Dell. Rough start, presentation about SQL in the Enterprise, DR, etc. Honestly not very good so stopped taking notes. Sorry Dell.

9:18 am

David DeWitt, Technical Fellow, Data and Storage.32 years as Computer Sci professor, joined MS in March 2008, runs Jim Gray Systems Lab. Very engaging, joked about the server fans spinning up yesterday. Talking about growth/speed trends, seek times not getting much faster compared to other factors, need a lot more drives to keep CPU’s busy, SSD’s the big hope for changing that. This is really a presentation that’s worthy of a keynote – interesting, technical, no sales pitch, recognizes we get the base concepts already. Right now we try to avoid random IO, expensive. Memory is in Peoria, 200 cycles to access compared to 20 for L2 cache. Green stuff is bad (gotta be here, it’s the stall on the chart he is showing). Talking about column store vs row store, advantages to column store are only retrieving data needed to put into cache. Stopped writing to pay attention!

Missed a planned meeting due to bad scheduling, keynote went later than I thought it did. My fault, annoying!

Went back to the hotel to drop off my bag, back to the convention center for more networking, then off to a lunch meeting, then back for some more chat before a 2 pm meeting and another at 2:30, then spent some time wandering, looked in on Buck Woody’s session which seemed to go very well, then started working on preliminary dinner plans, meeting people at 6 pm and go from there. Finally headed back to the hotel, it’s funny that the conference just quietly ends, seems like there should be a closing ceremony!

So it’s dinner tonight, then a board meeting in the morning, one more meeting after that, then some time to relax before heading to the airport about 7p for a 10:30 flight that puts me back in Orlando at 9am Saturday – doesn’t seem like it should take long.

It’s been a good week, think I hit my goal of meeting 50 new people but it started to blur, definitely did better than in previous years. Looking forward to getting the event DVD’s so I can enjoy the technical side of the content back home. Things went very smoothly, PASS HQ did a great job this year!

Let the Learning Begin

Jack Corbett — Fri, 06 Nov 2009 00:11:00 GMT

Finally time for the sessions to begin after a day and a half in Seattle being overwhelmed by the people that I’ve had the opportunity to meet.

I started the day meeting with Mike Walsh (@mike_walsh) and Joe Webb (@joewebb) in the Sheraton living area (lobby) where we had a short time of prayer to start the day. We then went on to meet with some other men of like faith to discuss Brian Moran’s idea about reaching others. It was great to meet Brian and encouraging to know others in the SQL Server community that share my faith. Then it was on to the keynote.

Keynote

I attended the keynote by Bob Muglia and Ted Kummert from Microsoft. Mostly stuff about the new technologies available in SQL Server 2008 R2, Windows 2008 R2, and Visual Studio 2010/.NET 4. The coolest thing was the LIVE migration of VM’s using Hyper-V. Move a running VM from one VM server to another without interrupting the virtual server! Wow!

Session 1

My first session was titled Data Access Layers: A road map to smarter, efficient, and effective queries. It was not exactly what I anticipated as it was a session mainly about using Inline Table Valued functions to replace views and direct table access. I’m not a big fan of UDF’s because they are often mis-used and have to admit I tuned out a little bit and spent time following Twitter so I’d know where the people I wanted to meet were.

Lunch

Today’s lunch was a Birds of a Feather lunch with MVP’s hosting topic-focused tables. I went to Paul Randal’s (@PaulRandal) table about Corruption and High Availability. It was very interesting to hear him tell stories and hear the stories of other folks at the table. It was also great that he was able to give me reason why a restored database with no activity would have transaction log growth. It was AutoShrink! AutoShrink is evil in more ways than one. It was also cool that when I introduced myself, after everyone else had left because I was late to lunch, he recognized my name immediately from the SQLServerCentral forums and Twitter and asked why I hadn’t introduced myself earlier! I really try not to put people on a pedestal, but he and Kimberly Tripp (his wife) are pretty much universally recognized as SQL Server royalty, so it’s definitely cool to meet them and then be recognized as well! Both are very nice and desire to help people out. That’s the great thing about the SQL Server community, it seems that the more well-known you are, the more helpful you are.

After Lunch

Because of my discussion with Paul, Kim, and Gail Shaw I didn’t make the first session after lunch so I spent some time in the vendor exhibit hall looking around and talking with some of the vendors.

Next I went to Andy Leonard’s session, A Tale of Careers and User Groups, where Andy explained how he became involved in user groups and the lessons he has learned. He gave some good tips about how to get people involved in a user group. A key way is to ~~recruit~~ encourage those who come early or stay late to be a part of the user group leadership team.

Finally I checked into Kalen Delaney’s session, Indexing Internals. It was a good session on the structure of indexes. The interesting thing was that, having read Kalen’s book and some other resources on indexes, I actually knew the majority of the information. It was interesting to see what I actually know.

After the session I had a great discussion with Lorie Edwards (@lorieedwards) about what to learn in SQL Server and how the size of your environment definitely affects the features you need.

Dinner

I went out to dinner with Andy Warren, Steve Jones, Pam Shaw, Rob, Tim Mitchell and Kendal Van Dyke. We didn’t talk much SQL Server, but more general conversation. Always a good time with this crew.

After dinner I went to a party sponsored by SQL Sentry and met a ton of new people. The guys from SQL Sentry (Greg, Peter Shire), Adam Machanic, Peter Ward, Charlie Hanania, Chuck Heinzelman, and others.

Finally I went back to the hotel about midnight and crashed.

PASS Summit 2009 Key Note 2

Grant Fritchey — Fri, 06 Nov 2009 00:10:28 GMT

Patrick Ortiz from Dell, the platinum sponsor for the PASS Summit.

The goal will be to discuss the Dell Microsoft Practice, Configuration Management, Disaster Recovery and Consolidation. The MS Practice team has server tools, messaging & communications and collaboration & databases. Lots of work going on there.The see the CM, DR & Consolodation as linking cogs, if you trust the slide. They want to start implementing CM management systems. He’s driving the data suggested with Asset Data and physical server information. Then you drill down and collect SQL Server information followed by drilling down further to collect DB info. He’s gone on to show some of the benefits of CM. The idea behind CM is so that, once you’ve defined your servers, you can define disaster recovery and start looking into server consolidation. I hate to say this, but this is really the equivalent of eating your vegetables. This is pretty dull stuff. A key note needs to be a little more exciting than this. Show us some hardware, show us some flashing lights, some screens, something. It’s early in the morning. More.

PASS Summit 2009 Key Note 1

Grant Fritchey — Thu, 05 Nov 2009 23:50:49 GMT

Wow, the bloggers table is empty today!

Nice intro! Good photo’s. I love the Summit!

Bill Graziano is introducing Day 3. He acknowledged Twitter and the bloggers. We rock! Outgoing board members are Greg Low and Pat Wright. These are great guys who’ve busted their butts for the community. Kevin Kline is completely off the board now, finishing his time as the immediate past-president. I’m pretty sure that’s the first time he won’t be on the board. Yep, I’m right, he’s never been off the board since PASS was a organization. He really has done a lot for the organization. Thanks for your time Kevin. A review of all the other board members including the new president Rushabh Mehta and Wayne Snyder as the immediate past president.

April 21-23,2010 the European Conference will be in Neuss Germany. Next years conference will be in Seattle (again…. blech!… I understand why, I do, but it’s a ROYAL pain to fly across the whole continent every year). A thanks to the headquarters staff too. These guys are great and they work some odd hours. You’d think they were DBA’s. Nice work guys.

PASS Summit 2009 Day 3 Keynote Live Blog

kendal.vandyke — Thu, 05 Nov 2009 23:00:00 GMT

10:25 AM – And that's it for the week! Thanks for tuning in. Be sure to order the Summit DVDs. David's keynote alone was worth it.

10:20 AM – Time's up for David. Mentioning key points. Waaaaay too much information on the slides to type before he's moved on to the next one. Other bloggers doing live keynotes were smart enough to grab photos of the screen. Hopefully they'll post the contents.

10:05 AM – David's comparing different compression techniques. Run Length Encoding, Bit-Vector Encoding, and Dictionary Encoding.

10:00 AM – Now talking about compression in column stores. Basically it trades I/O cycles for CPU cycles (remember that CPUs have gotten 1000x faster while disks have only gotten 65x faster).

9:51 AM – Boiling down the row store vs. column store discussion, basically we're looking at a 7X performance improvement in column store architecture for certain types of queries. "Select * from …" will never be faster though. Best bet: Store some data row wise and some data column wise.

9:45 AM – L2 cache misses are due to row store architecture. Now David's talking about column store architecture.

9:43 AM – David talking about why we have L2 cache misses in CPU when fetching data off disk. Highly technical, and my head is beginning to hurt!

9:36 AM – OK, lots of technicals on CPUs now. Too hard to write about what he's talking about. It's deep. Really deep.

9:35 AM – Now talking CPU trends. This is awesome stuff, really. Best keynote so far in my opinion.

9:34 AM – 1980, sequential/random 5:1; 2009 sequential/random 33:1. Takeaway: RDBMS must avoid random disk I/O when possible.

9:32 AM – Incredibly inexpensive drives & processors have made it possible to collect, store, and analyze huge quantities of data. But considering the metric transfer bandwidth/byte, when relative capacities are factored in, drives are 150X SLOWER today.

9:30 AM – After 30 years of CPU and memory improvements, relative performance of CPUs and disk are totally out of whack in terms of performance. The benefits from a 1,000x improvement in CPU performance and memory sizes are almost negated by the 10x in disk accesses/second. David thinks that SSDs will change our lives in this area. (Side note, I've got some GOOD stuff coming on SSDs soon. Stay tuned!)

9:27 AM – Drilling down into disk trends now. 10,000 x capacity, 65x transfer rates, but the BIG change is in seek times.

9:24 AM – Going back to 1980, comparing hardware then to hardware today. A LOT has changed: improvements of 2,000x CPU, 1,000x CPU Caches, 1,000X memory, & 10,000x disk.

9:21 AM – This is going to be a really deep dive into technical stuff: trends in CPU, memory, disk, and how they impact design. Disclaimer: This is an academic talk – no marketing speak (yay!)

9:20 AM – No product announcements, no motorcycles, but already a lot of humor. This is going to be interesting.

9:18 AM – Bill back on stage, introducing Dr. David DeWitt, Technical Fellow at Microsoft. He's going to present "From 1 to 1000 MIPS"

9:18 AM – Dell Keynote over…not a lot to mention, honestly.

9:05 AM – More talk about disaster recovery…(BTW, we're a room full of DBAs at PASS. I sure hope we all understand disaster recovery)

8:59 AM – Benefits of configuration management:

Provides clear picture of the SQL Server Environment
Improves operational efficiency
Drives priority for SQL Server Disaster Recovery and Consolidation

8:55 AM – Patrick talking about configuration management…types of data to collect (e.g. Name of server, Server Properties, CPU, Location Information, OS Version, SQL Server Version, etc). Basically stuff that anybody in IT should already know and be collecting.

8:50 AM – PASS business out of the way, now Patrick Ortiz from Dell's Infrastructure Consulting Services coming onstage to talk about "Managing SQL Server In The Enterprise"

8:49 AM – Thursday's To-Do List:

SQL Server Clinic
Hands-On Labs
Eat lunch with regional and local Chapter Leaders
Sign up for one of tomorrow's Post-Cons
Order Summit 2009 DVDs for $125
Submit your session and Summit evals
Drop comments & questions in the Suggestion Box

8:48 AM – Announcing 2010 PASS Summit, November 8-10 in Seattle again. Register now for $995. No contract signed for 2011 yet; feedback on location will be solicited.

8:47 AM – And now the new executive board: Rushabh Mehta (President), Bill Graziano (Executive VP), Rick Heiges (VP, Marketing), and Wayne Snyder (Immediate Past President)

8:46 AM – Bill introducing new board members Brian Moran, Jeremiah Peschka, and Tom LaRock.

8:45 AM – Wayne is tearing up telling stories about Kevin…Kevin receiving a standing ovation as he receives his award for 10 years of service.

8:44 AM – Kevin taking the stage now. Wayne Snyder joining in to present Kevin with a special award for his service.

8:43 AM – BIll thanking outgoing PASS board members Pat Wright, Greg Lowe, and Kevin Kline. Special video tribute playing for Kevin's years of service to PASS. Big applause for Kevin.

8:40 AM – Bill Graziano, PASS VP of Marketing, taking the stage now. Promises this will be the shortest keynote of the week. Big applause.

8:38 AM – I'm back for the final day of the 2009 PASS Summit. Today is the Dell sponsored keynote. Journey's "Don't Stop Believin'" is blaring through the speakers as videos from the conference play on the big screens.

PASS Summit 2009 – Day 2

Grant Fritchey — Thu, 05 Nov 2009 20:53:18 GMT

Day 2 of the Summit was extremely busy. I missed a breakfast on DMV’s that evidently was one of the hits of the show. Day 2 was kilt day. I wore mine and there were two others, Steve Jones and Bill Fellows. They were a hit. Next year I’d like to see more. There are pictures all over the place. Track one down on your own.

I set up at the bloggers table and blogged & tweeted my way through the key note. Except for the hyper-sexy Windows 7 touch screen computer, it wasn’t the most exciting key note I’ve ever seen. The new technologies coming out for data manipulation on the client machines in Office 2010 are impressive, no doubt, but something seemed lacking and I’m not sure what it was. During the key note they gave out awards to the outstanding volunteers of the year and they gave the PASSion award to best of the volunteers. I won one of the outstanding volunteer awards for the work I did with the Editorial Committee & SQL Server Standard. Allen Kinsel was the winner of the PASSion award. I wanted Allen to win. I had put in a nomination for him and I knew others that did as well. Congrats Allen. You earned it.

My first session was completely personal for me, and I doubt most people would be as excited about it as I was, but I was very excited to see Michael Rys of Microsoft talk about spatial indexes. It was a very useful session. I took a lot of notes, but I’m going to have to go back and watch it again on the DVD because there was stuff I missed. If you are working with spatial data, you should check this one out.

Lunch today was just lunch, but during it, many of the MVP’s that took part in the MVP book project, SQL Server MVP Deep Dives, all proceeds of which go to War Child International, were signing their chapters in the book. I bought the book, but only went over afterwards to get it signed by the last four or five that were still there. I told Paul Randal how disappointed I was that he didn’t wear a kilt. But, during his session he did put up a picture of himself in a kilt. He doesn’t own one. I hit on MVP’s for the rest of the day to get their signature. I’ll be carrying it with me today (and breaking my back, it’s huge) to get some more. Buy the book. You’ll get good information and help an important charity.

After lunch I went to Ben Nevarez’s session on How the Query Optimizer Works. It was very good. I took some notes and got some ideas for future research. I really like the way he did simple, direct examples that illustrated his point. He also reminded me of a DMV that I should have included in my session (and will next week). This is useful stuff and it’s definitely worth checking out on the DVD.

I skipped the next session to go and do a video interview with the Midnight DBA. For those that don’t know, the Midnight DBA is actually two people Jenn and Sean McCown. It was a blast. We talked about books, TSQL, Kenpo and sexual harassment of men by men. Yeah, it was pretty fun and not entirely appropriate (although, probably only PG in rating and completely work safe).

After that I did a thing with Microsoft, completely unrelated to being an MVP, but still NDA. Done.

To finish out my time at the Summit proper I went to Buck Woody’s session on using Policy Based Management, Data Collector and Central Management Servers. I’d met Buck in the hall and he asked me to come down. I’ve never seen Buck Woody speak before. For those who know, why didn’t anyone tell me? All I can say is, what have I been doing with my time at these conferences? I should have been going to see everything the man says. Not only was it useful information, but it was hilarious. We were laughing our butts off through the whole thing. He picked on people in the audience (especially poor Aaron Bertrand, although I took some hits when I showed up late, wearing a kilt), cracked jokes, and gave out useful information in a clear, engaging manner. I’d suggest you watch the DVD, but you’re not going to get the full kick in the britches that being there will give you. However, you will still see some good stuff. I’ve already sent off a message to my team lead to suggest we get started with some of this right away.

After the show was over it was party time. I had two to go to, but I only made one, the Microsoft hosted party at GameWorks. Good food, free alcohol, video games. I rode the Hummer with Wendy, @wendy_dancer, driving. She drove better than I would have. It was fun, but I went home early because I’d had a nasty cough and runny nose all day and my voice was completely shot. I needed rest.

Another great day at the PASS Summit 2009.

EDIT: Updated Bill Fellows name.

SQL Server 2008 R2 Running on “Big Iron”

Glenn Berry — Thu, 05 Nov 2009 20:43:23 GMT

During the opening keynote on Tuesday at the PASS Summit in Seattle, Microsoft’s Bob Muglia showed a demonstration of a pre-release build of SQL Server 2008 R2 Datacenter Edition on Windows Server 2008 R2.

The demo started out with 64 processor cores running at about 90%. Bob pushed a button, and went to 128 processor cores, which changed the processor utilization to about 50% (see the big plunge in CPU utilization in the middle of the graph). Then, he increased the load on the server to take processor utilization back up to 90%. Finally, he went to 192 processor cores, and the CPU utilization went down to about 65-70% (see the dip at the end).

The server (which filled up a 42U rack) was on stage with Bob. My blurry photo shows it identified as an IBM x3950 M2.

Microsoft also announced a new OLTP world record for TPC-E, of 2012 tpsE, and a new record for TPC-H (on Windows) of 102,778 QphH.

This is even more evidence that SQL Server can and is playing in the “big leagues”.

PASS Conference 2009 Oddities and Curiosities

Bill Nicolich — Thu, 05 Nov 2009 19:38:00 GMT

Down on the docks in Seattle there's a shop of oddities and curiosities where one can find things like a chapter of the Bible written on a grain of rice, an aligator boy, a gobblet of glass eyes - you know - stuff like that. So too one can find oddities and curiosities at the 2009 PASS Conference.

One presentor who discussed the new hierarchy data type and the bill of materials problem used a 3D Lego modeler tool called the Lego Digital Designer to explain in a visual way the hierarchical relationship between the parts of a model. How cool is that? The download is free at ldd.lego.com. Also an insider note is that several good sources have suggested that the new HIERARCHYID is not the best way to solve the bill of materials problem. Rather, other techniques are preferred like Joe Celko's Advanced Nested Sets model or the more simple adjacency list model (for scenarios where data doesn't change much).

An interesting note that was made is that Microsoft developed the HIERARCHYID to make some of the native XML features work efficiently. Hmm. Then the decision was made to expose it to users so they can benefit from it in various scenarios.

TSQL developers are familiar with the GO directive which sets up an implicit transaction. Kalen Delaney pointed out that one can run a block of TSQL code multiple times simply by doing something like GO 320 - and the code will run three hundred and twenty times. Imagine using this to test constraints for insert statements. Pretty neat.

Listening in on a Microsoft presentation on database connectivity, the presenter mentioned "fuzzy testing." Is that when Elmo takes a hammer to your laptop? No. Apparently it's a bug-finding technique that commonly that enters random data to controls and systems. The presenter mentioned using a tool called "Slammer." I hope on the box there's a picture of a professional wrestler in the air about to deliver a flying elbow or something.

David J. DeWitt, keynote speaker on Thursday overviewed trends in database technology over the past 30 years. He points out that since SQL Server is row-based, and entire rows in a table are sent through the CPU cache, a large amount of CPU usage is wasted due to cache misses. That being the case, then there's another reason why narrow tables - or tables with fewer columns and smaller data types are preferable due to the CPU cache bottleneck. Another neat point is that Dr. DeWitt mentioned that future versions of SQL Server like SQL Server 10.5 and 11 may do some really nifty things like using in-memory column-based tables to take advantage of high compression opportunities and to drastically reduce cache misses, getting operations through to CPU much more efficiently. That's cool.

I had an in-depth question about the full-text features of SQL Server. I asked around and was told by Microsoft personnel that I needed to talk to Fernando. I played a game similar to "Where's Waldo?" that I call "Where's Fernando?" I wanted to learn more about how full text correlates words like "light," "lights", "lighter" and "lighting" as belonging to the same English root word. I talked to some other very knowledgeable people like Robert Cain, a SQL Server MVP who is also a fantastic presenter.

I learned that Robert Cain has recently changed employers - and is working on some really cool projects for NASA. One really interesting and odd note is that NASA uses Microsoft Project extensively - and perhaps in ways it was never intended. They have the larges Project database on planet earth - and Robert is helping them manage that odd data store. It's like the bill of materials problem for complex engineering projects mixed with project management, mashed together into one solution slash problem! Wow. Wierd.

Thursday at lunch yielded a curiosity. I sat next to a guy who swims with Alistair Cockburn, one of the founders of the Agile Movement. Where does he swim? Stein Erickson or something like that. Also, I learned that Microsoft has recently added a research lab at Thanksgiving Point in Salt Lake City. That makes sense. There's always a lot of interesting research going on over there at the University of Utah. After all, it's one of the original four nodes of the Arpanet - the precursor to the internet.

It turns out that the room where the session "Storage for the DBA" is actually not where all the DBAs are stored.

The official commedian of the conference is Buck Woody. Speaking of users, Buck says "The Internet is down! have you ever heard that? Despite what Al Gore told you, [the internet] isn't all on your laptop."

PASS Summit - David DeWitt Keynote

Steve Jones — Thu, 05 Nov 2009 18:29:54 GMT

Microsoft sent Dr. David DeWitt to do the last keynote of the PASS Summit. He's a technical fellow in the data and storage platform. He's looking forward to the future of SQL Server and is talking to future of technology and trends.

From 1 to 1000 MIPS is his talk.

His background, 32 years at the University of Wisconsin. He runs the Jim Gray Systems lab in Madison, with the idea of taking technology out of UW into the Microsoft product suite. It's not part of Microsoft Research, and has 3 faculty and 8 grad students.

Working on releases 1 and 2 of SQL Server Parallel Database Warehouse. Last year he talked about parallel database technology. The idea is to look at trends in hardware and then database system specialization in the next decade. I tend to agree with this and we might need to specialize with new database technology.

Let's go back to 1980. The VAX 11/780, 1MIPS, 1kb cache, 8MB memory, 80MB disk drives, 1MB/sec xfer all for $250k! This was the first 32 bit relational database. INGRES and Oracle were the main relational database vendors and the basic DBMS architecture is the same thing we use today.

However hardware is different. We now run around 2GIPS, 1MB caches on chip, 2GB/CPU RAM, and 800GB drives. Everything is 1,000x greater or more.

Disk Drives

If we look at transfer rates on disks, we're only about 65x improvement, and seek times are only about a 10x improvement. Quite a disparity.

1985, early benchmarks. 100TPS, 400 disk IOs/sec, with 14 drives. Roughly 30I IOs/sec per drive.

Go to 2009, we have 25,000TPS, 100,000 disk IOs/Sec, 330 drives with 300 IOs/sec equivalent!

So the 1000x improvement in CPUs has been almost negated by the 10X disk access/sec changes. That is amazing. And sobering.

Transfer Bandwidth/byte, a new metric. Dividing transfer rate by capacity, then you get .015 in 1980 drives, .0001 in today's drives. So the Dr. DeWitt sees drives as slower relatively. Hard to reconcile that in my little mind.

CPU Trends

1980 the VAXC had a single CPU, 8kb L1 cache, 6 cycles ot access memory. 10 cycles /instruction.

today we have multiple CPUs, 64kb private L! caches, 2-8MB shared L2 cache, 1 cycle/instruction. 2 cycles to access L1, 20 to access L2

What is the impact on DBMS performance. Looking at a DB2 on Linux, TPC-H queries on 10GB database and a 1GB buffer pool.

In looking at a CPU and where time is spent. Lots of time waiting for things, about 10% useful computation time. 50% of the time waiting on memory. Why? The L2 data cache is waiting on transfers. That is amazing.

Read 3 pages from disk, up to 9 L1 and L2 cache misses. An L2 cache miss can stall the CPU for up to 200 cycles.

An alternative physical layout is a column store. What does this mean? I wasn't sure this was a great idea when I read it last year, but I would recommend you read to understand. Now if we look at the cache misses, there's a difference. Same query we saw before now has many less cache misses. Less I/O is wasted.

In an example he gave, for a 10M row table. In a row store, for a simple query, 3 columns, you return 2GB of data and it takes 25 sec. A row stored only scans 280MB at 3.5 sec. That's amazing, though I'm not sure how useful this is for most of us. It's definitely built for data warehousing, and might not apply for OLTP loads.

Physical Representation

We have our row store, a column store, and hybrids. We could include an ID with the column as well. I know it's confusing, and I'm not giving enough detail, but it's confusing to me as well. This is probably in a paper, or series of papers somewhere. I'll try to find references.

Compression starts to work better because CPU is 1000x faster, but disks are 65x faster. So you spend the time doing decompression and it still works better. Some types that Dr. DeWitt talked about.

Run Length encoding, a good way to compress data. works well with sorted data.

Bit Vector encoding, another way to compress. Use bits to represent values if there are relatively few values. Combine this with RLE and increase compression.

Dictionary encoding - Create a dictionary for the values.

With a row store, you have different issues. You cannot run length compress in that store. Or not easily or well.

A column store scanner, to satisfy a query, is more complex. A much different path of working through a query. This is very interesting stuff, and very well explained.

However updates are an issue. No free lunch. The updates are hard and creates lots of work. Research is being done on how to make these more efficient, but it's a tough problem to solve.

There is more interesting stuff, but it's hard to relate. And I spent more time listening than typing. I'll write on this more, but it is interesting. There is work being done for SQL Server 10.5, and SQL Server 11 in this area. Some of this, the Vertipaq engine, is in SQL Server 10.5, which is SQL Server 2008 R2, I believe. Dr. DeWitt hinted at other things, but I'm not sure what will be used in future products.

Daniel Abadi (Yale) has a great technology blog. David DeWitt recommends him.

PASS Summit - DELL Keynote

Steve Jones — Thu, 05 Nov 2009 17:04:29 GMT

Note might be sparse here as it's not a great keynote. There's a lack of excitement, and enthusiasm from the speakers this week for keynotes. I've typically dreaded the vendor keynote because they work too hard to try and sell you something.

DELL is here to talk about SQL Server in the enterprise and specifically consolidation and configuration.

We're getting some ideas about configuration management, showing us types of data to track. This isn't worthy of a keynote. There is a good list of information, but it's not something that we need talked about in the morning session, and we certainly don't need it read to us.

Why do configuration management? You can learn how to classify systems for SLAs and support resources. It also gives you priority for DR.

Ay yi yi, this is almost painful. I do like technical information and not marketing, and this isn't a lot of marketing, but it is boring and uninspiring.

I quit, this just sucks. It's not worth reporting on, unless we go Mystery Science Theater 3000-style.

PASS Summit - Opening Remarks with Bill Graziano

Steve Jones — Thu, 05 Nov 2009 16:53:09 GMT

Bill, the Vice President of Marketing, opened the day with a few remarks about how to keep up with PASS. Then a tribute to Kevin Kline.

Kevin has been a part of PASS for 10 years, and it was great to see people recognizing his efforts. Wayne Snyder came up to say good bye to Kevin, the past President of PASS, after 10 years. An emotional moment, and Wayne broke up a little. I can relate to that and I'm not sure I'd be any better.

An award to Kevin for his service to PASS and he received a well deserved, standing ovation.

Our new directors, Thomas LaRock, Brian Moran, and Jerimaih Peschka, were noted on the stage. Our new executive committee is:

President Rushabh Mehta
Vice President of Finance Bill Graziano
Vice President of Marketing Rick Heiges
Past President Wayne Snyder

The European conference is scheduled for April in Neuss, Germany. It will be the 21-23rd, April, 2010.

The North American Summit is back in Seattle next year, November 8-11, 2010. If you're coming back to the Summit, the lowest price is available now, so if you can book it now, you'll save around $1000. http://www.sqlpass.org/summit/na2010.

The headquarters staff was recognized as well. They are a great group of people up in Vancouver that keep PASS running all year around. I certainly rely on them for a few things, and I think they're great. The most visible is Blythe Morrow (@blythemorrow) on Twitter, so if you have something to suggest or know, send them an email, or tweet Blythe.

Feedback

The way you change things at the Summit is with feedback. PASS is here for you as a user, so if there is something you want them to do, let them know. If there's something you liked, or disliked, about the Summit, fill out the surveys.

If you want to get the Summit on the East Coast, TELL PASS.

The speakers are also chosen partially based on the reviews that you give them. So if you enjoyed a session, be sure to let them know. If you didn't enjoy a session, give constructive criticisms.

New Pricing and Editions For SQL Server 2008 R2

Glenn Berry — Thu, 05 Nov 2009 13:21:26 GMT

Mary-Jo Foley recently posted about some announcements about SQL Server 2008 R2 out of the PASS Summit. There will be two new, high-end SKUs of SQL Server 2008 R2, and the prices of Standard and Enterprise Edition are going to be increased somewhat over SQL Server 2008.

Standard: $7,500 (Per Processor), or $100/Server + $162/CAL (a $1,500 increase over SQL 2008 Standard)
Enterprise $28,800 (Per Processor), or $9.900/Server + $162/CAL (a $3,800 increase over SQL 2008 Enterprise)
Datacenter $57,500 (Per Processor), Not offered via Server/CAL (no previous version available)
Parallel Data Warehouse: $57,500 (Per Processor), Not offered via Server/CAL (no previous version available)

These prices are still quite a bargain compared to Oracle, especially since the SQL Server processor licensees are by physical socket, not by core. One theory I have heard (and tend to believe), is that having a lower price actually hurts the perceived value of Microsoft vs. Oracle in some cases. In other words, since Oracle is so much more expensive than SQL Server, that means it must be that much “better”, right?

Wednesday at the PASS Community Summit

Brad M. McGehee — Thu, 05 Nov 2009 12:28:12 GMT

The second day of the full PASS Summit was packed with sessions. If fact, it was hard to select from the 56 sessions that were available.

The day started with the Rushabh Mehta announcing the 2009 PASSion Awards, an annual award given by PASS to those volunteers that have given a lot of their time volunteering. This year, two awards were made. For the North American award, it was given to Allen Kinsel , who gave a lot of time volunteering for the PASS Program Committee.

The international award was given to Charlie Hanania, for his volunteer work putting together the European PASS Conference. I meet Charlie last spring at the European PASS Conference, and I have to say that he really deserved the award. He is a great guy who helped make the 2008 European PASS Conference a great success.

After the PASSion Awards, and a keynote by Tom Casey, I gave a presentation on “Identifying SQL Server Performance Problems Using SQL Trace.”

Immediately after my presentation, I headed to the SQL Server MVP Deep Dives autograph party, where about 30 SQL Server MVPs were on hand to sign copies of this new book. The royalties for the book will be donated to the War Child International charity. Myself, and the other MVPs, signed over 200 books.

I spent the rest of the afternoon in meetings before heading back to the hotel to catch up on work. Tomorrow will be my first fully free day at the PASS Summit, and I hope to take in a lot of sessions.