The sp_Blitz® session felt like the best session I’d ever presented in my life. The room was jam packed full of people with great energy:
And there were over 100 people in the overflow room watching me on video, too. I had no idea there was even an overflow room until halfway into the presentation. One of the in-person attendees said, “@RonDBA would like to ask a question via Twitter from the overflow room.” Me: “The what?”
I took risks on both of ‘em: sp_Blitz® was 100% demos (which are always prone to unplanned explosions), and my AlwaysOn session was all slides (which is prone to complaints about a lack of demos). To mitigate those risks, I put a hell of a lot of time into testing my demos, and I tried to bring a lot of life to my slides. For 2013, I’m thinking about new ways to take risks and bring surprises.
This makes four years in a row that I’ve been in the top 10 sessions, and I have a theory about this. I think I get good feedback because I attract the right kind of attendees – people who want to have fun while they learn.
Thanks to you, it’s just as much fun on this side of the podium.
SSMS is the standard tool for working with SQL Server databases. It does the job well. But if you’re using the default settings, you’re missing out! You can enable word wrap and number the lines. Change the settings when you script objects out. Filter through objects. Let Jes guide you through the world of hidden SSMS settings!
Watch the video to learn more about Object Explorer, Options, secret
weapons buttons, Projects, Templates, and Snippets! Have a question? Feel free to drop me a line!
Tip: this video’s got a lot of detailed demos, so try watching it full screen. While you’re watching that way, you can click the gears icon and see the video in high resolution (like 1080p).
SQL Server has two settings that kinda-sorta govern how much memory we’ll use to cache data: min server memory and max server memory. I explain the basics of how they work in my Sysadmin’s Guide to SQL Server Memory, but things are a little different under virtualization.
VMware does a great job of sharing memory between different virtual machines, but to do that, sometimes it has to steal memory from one VM to take care of another. Just because we gave 16GB of memory to our VM doesn’t mean the memory’s always there. If a host crashes and we suddenly need to boot up a bunch more guests on our existing host, we might pull some memory away from the other guests temporarily. If our company’s really cheap, we just might have never bought enough memory to begin with, and the memory might be stolen permanently.
To work around that, VMware admins can set a reservation for any guest’s memory. It works like a reservation for a table in a restaurant – we’re guaranteeing that
a corner table memory will be available whenever the virtual server needs it. By default, guests don’t have reservations – they just walk up and try to take whatever they need at the time. That works really well for most applications, but not for SQL Server. SQL Server starts at near-zero memory used, and then gradually caches more and more data as queries request it. Unlike most apps, SQL Server’s memory needs don’t go back down. It’s like that guy who keeps going to the buffet over and over and claiming all the food for himself.
When we build new virtual machines, we need to come up with three numbers:
- The guest’s memory – this is the amount of memory the guest thinks it has when it starts up. Let’s say we’re building a virtual machine with 32GB of memory.
- SQL Server’s max memory – I like to set this to leave 4GB of memory for the OS or 10%, whichever is greater. In this case, we’d set SQL’s max memory at 28GB, which would leave 4GB free for the OS.
- The VMware reservation – the lowest amount of memory the guest will have. Ideally, this is 100% of the guest’s memory, but that’s not always practical. If a host crashes, I’d rather be able to boot up all my guests with less memory than not be able to boot them up at all. For SQL Server, I generally set my reservations at 75% of the guest memory – in this case, 24GB.
So now we have an interesting problem: in the event of a disaster, VMware’s balloon driver may fire up and claim 25% of the memory, leaving just 24GB total for the guest. This will come as an ugly surprise for SQL Server because he was humming along using 28GB of memory (our max).
That’s where SQL Server’s min memory comes into play. I have to set the min memory in a way that accommodates my reservation. If my reservation is only 24GB, that means the balloon driver might pipe up and steal 8GB of my memory at any time. If I still want to leave 4GB or 10% free, that means my min memory should be 20GB.
The max memory number doesn’t change – but suddenly we need to pay more attention to our min server memory number. It’s completely okay to set that number even lower as long as you’re okay with reduced performance. For example, if this server is a value meal that also hosts SSAS/SSIS/SSRS, you’ll need to set min memory much lower to let those other apps get their jobs done.
If the VMware team refuses to set a reservation, you can’t fake your way around it by setting a high min server memory number. When things start swapping to disk, SQL Server is going to run slower – even if it’s not the one paging to disk. When the OS ain’t happy, nobody’s happy.
Wanna learn more? Check out our VMware, SANs, and Hardware training videos. It’s a 5-hour training video series explaining how to buy the right hardware, configure it, and set up SQL Server for the best performance and reliability. Here’s a preview:
A while back, Jes asked who’s taking your backups. Making sure you have good backups is important. How much thought are you giving to handling historical backups? Right now, there’s a really good chance that you’re using a solution based on tape. While tape backups work, there’s a better way.
How Are You Archiving Backups Right Now?
Sending backups to tape isn’t the easiest process. For SQL Server, the process looks something like this: SQL Server backs up the database, the backup files are copied from a central location to a tape, on a regular schedule an administrator takes tapes out of the backup machine and sends them to an off-site facility. Doesn’t that sound like fun?
In addition to requiring that tapes need to be added and remove from a tape robot, magnetic tape also has the distinct disadvantage of requiring careful storage and handling to prevent damage to the storage media. There has to be a better way.
Offloading Backup Archives to the Cloud
Durable off-site storage is a must for a lot of businesses and when you don’t have requirements for physical media, I can’t think of a better option than using Amazon S3. Many companies are already making use of Amazon S3 to house durable off-site backups of data. S3 has the advantage of being durable and relatively highly available – the S3 SLA guarantees ten 9s of durability and four 9s of availability. For this privilege, we pay a pittance (between $0.05 and $0.13 per GB per month). And, let’s face it, that’s a cheap price to pay for being able to expand your archive capabilities on demand.
Amazon Glacier is a relatively new, low cost, durable storage solution. It looks a lot like S3 but has a distinct price advantage – Glacier costs $0.01 per GB per month. Glacier is built with long term storage in mind – storage is incredibly cheap but retrieval takes longer and costs more. When you need to retrieve data from Glacier you issue a request and Amazon will notify you when the data is available to download. Typically this takes a few hours, but it’s faster than getting tapes returned from off-site storage.
Automating the Archive Lifecycle
Until recently, putting data into Glacier required that administrators or developers create a set of scripts to push data into Glacier from S3 as it aged out. While this works, it’s still a manual step – if something happens to the server driving the data movement data won’t be copied. Earlier this week, Amazon announced support for automatic archiving into Glacier through lifecycle rules.
Lifecycle rules make it easy to automatically move files into Glacier based on a prefix and a relative or absolute timestamp. It’s easy to create groups of groups of backups and archive them on a daily basis. Rules can be even use to expire the files once they’ve been in Glacier for a fixed amount of time. Some businesses are required to keep backups, source data, or even older versions of the code base for a period of time – marking files for expiration makes it easy to comply with internal and external regulations.
Data lifecycle rules sound like they’re going to be painful to create, right? Thankfully, it’s incredibly easy to put one together. There’s only one step. In this example, files with a name beginning in “archive” will be archived to Glacier after 15 days and deleted from Glacier after 180 days.
What Does AWS Glacier Mean For Your Backups?
It probably doesn’t mean anything right now if you aren’t already looking at using AWS. The combination of S3 and Glacier gives DBAs and system administrators another set of options for keeping backups for long periods of time. Automating data motion removes the fallibility of human processes and physical media from the equation. It’s worth considering how you can improve your backup retention, reliability, and recoverability by automating storage of backups using S3 and Glacier.
When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers:
- Which tables it needs to join together
- What subqueries it needs to execute
- Whether it can reverse-engineer your intent to achieve the same results faster
- What indexes exist for the tables/views you’re trying to join
- If it can do partition elimination to make things go faster
- And much, much more
Much like you, SQL Server doesn’t like doing much work. SQL Server put a lot of work into building your execution plan (which may also be a work of art itself), and wants to avoid reinventing that wheel again, so it caches the execution plan in memory. If the same query comes in again, SQL Server can just check the cache, find your beautiful snowflake, and reuse the same plan.
This is the plan cache, and it stores more than just plans. We can get metrics about how many times the query was called and how much resources it used (min/max/avg/total).
It’s not perfect – there’s a lot of things that can cause the plan cache to flush completely or in part:
- Service restarts
- Database restores
- Statistics changing on an object
- Server comes under memory pressure
- People running DBCC FREEPROCCACHE
I still love the plan cache anyway. It’s not perfect (just like many of the things I love) but it’s a fast, easy-to-access way to discover some of the queries that have been using a lot of resources lately.
Making the Plan Cache Easier to Analyze
I’ve built up a set of queries to slice and dice my way through the plan cache, but I wanted to make it easier for people tackling their first performance tuning project.
At the PASS Summit this month, I unveiled the latest version of sp_Blitz®, which makes all this easier. I’ve added a few new parameters:
@CheckProcedureCache – if 1, we grab the top 20-50 resource-intensive plans from the cache and analyze them for common design issues. We’re looking for missing indexes, implicit conversions, user-defined functions, and more. This fast scan isn’t incredibly detailed – we’re just looking for queries that might surprise you and require some performance tuning.
@OutputProcedureCache – if 1, we output a second result set that includes the queries, plans, and metrics we analyzed. You can do your own analysis on these queries too looking for more problems.
@CheckProcedureCacheFilter – can be CPU, Reads, Duration, ExecCount, or null. If you specify one, we’ll focus the analysis on those types of resource-intensive queries (like the top 20 by CPU use.) If you don’t, we analyze the top 20 for all four (CPU, logical reads, total runtime, and execution count). Typically we find that it’s not 80 different queries – it’s usually 25-40 queries that dominate all of the metrics.
To learn more about how the plan cache works and how I analyze it, here’s a 30-minute video:
Or you can take a shortcut and just grab sp_Blitz® now. Enjoy!
At PASS Summit last week, I presented “The What, Why, And How of Filegroups” to a packed room. It was great to see so many people eager to learn about something that is fundamental to every SQL Server database, but often not understood well enough.
As always, during and after the session, there were a lot of questions. One really piqued my interest: “When creating a table or index, is there a policy in Policy-Based Management to force a query to specify the filegroup instead of using the default?”
Let’s find out!
I’m going to use my DBA database on my development instance, DBAInfo. The database currently has one filegroup, PRIMARY.
USE DBAInfo; GO SELECT * FROM sys.filegroups;
ALTER DATABASE DBAInfo ADD FILEGROUP PBMTest; ALTER DATABASE DBAInfo ADD FILE (NAME = 'SecondaryData', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SecondaryData.ndf') TO FILEGROUP PBMTest; ALTER DATABASE DBAInfo MODIFY FILEGROUP PBMTest DEFAULT;
I double-check my filegroups.
SELECT * FROM sys.filegroups;
CREATE TABLE Test1 (ID INT);
I run my “What filegroup is that on?” query:
SELECT OBJ.OBJECT_ID, OBJ.name AS ObjectName, OBJ.type_desc, PA.index_id, FG.name AS FilegroupName FROM sys.filegroups FG INNER JOIN sys.allocation_units AU ON AU.data_space_id = FG.data_space_id INNER JOIN sys.partitions PA ON PA.partition_id = AU.container_id INNER JOIN sys.objects OBJ ON OBJ.object_id = PA.object_id WHERE OBJ.OBJECT_ID = (SELECT OBJECT_ID(N'dbo.Test1'));
It has been created on the default, PBMTest, as expected.
Now, I’ll add a third, non-default filegroup, PBMTest2.
ALTER DATABASE DBAInfo ADD FILEGROUP PBMTest2; ALTER DATABASE DBAInfo ADD FILE (NAME = 'TertiaryData', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TertiaryData.ndf') TO FILEGROUP PBMTest2;
I’ll check my filegroups again.
SELECT * FROM sys.filegroups;
Next, I want to create a policy that says “When a new table is created, put it on PBMTest2, even if that’s not the default.” In SSMS, I expand Management > Policy Management and right-click Policies > New Policy.
I give the policy a name, “CreateTableOnFilegroup”, and then click next to “CheckCondition” to set a condition. I name it “OnFilegroup”. The Facet is Table. I set the Field to @Filegroup, the Operator to “=”, and the Value to ‘PBMTest2’.
It’s set to every table in every database. I only want it to apply to my DBAInfo database, so I select Every > New Condition.
I give the Name DatabaseDBAInfo. I set the Facet to Database, the Field to @Name, the Operator to =, and the Value to ‘DBAInfo’. When I click OK, I’m back at the Create New Policy screen. I want to set Evaluation Mode to On demand and Server restriction to None. I click OK.
The one policy and two conditions are created.
Now, I go back to the query window and create another table, without specifying a filegroup to place it on.
CREATE TABLE PBMTestTable (ID INT);
What I want this to be created on PBMTest2. What filegroup is it on?
SELECT OBJ.OBJECT_ID, OBJ.name AS ObjectName, OBJ.type_desc, PA.index_id, FG.name AS FilegroupName FROM sys.filegroups FG INNER JOIN sys.allocation_units AU ON AU.data_space_id = FG.data_space_id INNER JOIN sys.partitions PA ON PA.partition_id = AU.container_id INNER JOIN sys.objects OBJ ON OBJ.object_id = PA.object_id WHERE OBJ.OBJECT_ID = (SELECT OBJECT_ID(N'dbo.PBMTestTable'));
This comes down to the Evaluation Mode setting of my policy. There are a total of four modes available in PBM – On Demand, On Schedule, On Change – Log only, and On Change – Prevent. What we are looking to do here is enforce On Change – Prevent – if the table isn’t going to be created on the filegroup we specify, we want it to fail. Unfortunately, not all policies can use this mode.
I had to do some research to figure this out. First, read this blog post on Facets by the PBM team. At the bottom, there is a table showing the facets and what modes are supported. Then, read this further explanation of the types by Lara Rubbelke, Policy Evaluation on a Single SQL Server 2008 Instance. Lastly, there is this blog, Policy Evaluation Modes,by the PBM team.
For On Change – Log and On Change – Prevent to exist for a facet, there must be a DDL event triggered by it. This is not the case with every operation in SQL Server. The CREATE TABLE commands in one such example. Because of this, I cannot create a policy to check what filegroup a table is being created on before it is created.
In this case, I can still create the policy and check it either on a schedule or on demand. It will then tell me which tables were not created on that filegroup.
Thank you Simon for the great question!
If you became a database administrator by rising through the ranks of software developers, you probably haven’t built your own servers from the ground up. That’s totally okay – but you’re missing some important information that will make you a better database administrator. In this 20-minute session, Brent Ozar will help you get started exploring your hardware using tools like HP System Insight Manager, Dell OpenManage, and IBM Director. He’ll explain what you’re looking at – and when you should raise an alarm because something’s not quite right.
Hello from Seattle! I’m excited to be sitting front and center of the 10th Women in Technology Luncheon at PASS Summit! This year’s topic is “Women in Technology: Where Have We Been and Where Are We Going?”
We’ll be hearing from an excellent panel! Stefanie Higgins, a Sr. DBA for Disney and former PASS board member; Kevin Kline, a past president of PASS; Denise McInerney, from Intuit, who founded the WIT virtual chapter; Jen Stirrup, consultant and now a PASSion award winner; and our very own Kendra Little!
I got talking to Geoff Hiten. He attended last year’s luncheon and had a great story for me. Last year, one of the discussions we had was that women sometimes hesitate to apply for a job or offer to speak because they don’t feel they know 100%. He used that knowledge to help a client write a DBA job description – separating out the “required” and “preferred” skills. They hired a female DBA, who noted that helped her have the confidence to apply for the position. It’s great to hear how the panel lunch has helped people in the real world!
The room is filling up fast! It’s great to see so many men and women interested in the topic!
Bill Graziano is on stage, welcoming everyone to the 10th annual luncheon! He introduces the moderator, newly elected PASS board member Wendy Pastrick!
We’ve grown from approximately 60 people at the first WIT luncheon to a room for 740. That’s amazing!
Wendy introduces Stefanie Higgins, a DBA extraordinaire, former PASS board member, and WIT lunch founder! She’s talking about her reasons for starting it. She was a computer science major in college and had trouble connecting with other women in the field. A boss once told her he had a bias against women. She wanted to connect with other female computer professionals.
Next up is Denise McInerney! Her first Summit was in 2002 – she had to work to find a woman to talk to at that event. Now, how times have changed! The message she got in 2003 when she saw that there would be a WIT lunch was, “This organization cares about my experience.” I love this sentiment! Women’s presence in the community has been increased greatly, from speakers at Summit to SQL Saturday organizers.
Women leave technology careers at twice the rate of men. How can we solve this problem?
Wendy introduces Kevin Kline. He talks about how PASS can compete with larger organizations and conferences. We have the community, and we can be the most welcoming, friendly, and hospitable environment! He’s learned that it’s not enough to say, “Welcome, come in!” We have to say, “Come in! Sit down next to me! Let me introduce you to my friends!” We want to be that organization.
Kevin is the dad of one son and six daughters. Six! He knows that men and women think differently. The challenge for men and fathers is to tell their daughters, “Come. Sit down next to me. Let me show you how I pay the bills by doing this job I love.”
Jen Stirrup, SQL Server MVP and PASSion award winner, is introduced. In Europe, only 25% of jobs in science and engineering are held by women. In IT, that’s 17% and declining. This is a global problem! The government of Scotland is making a concerted effort to make IT and data available to women, to empower them and allow them to support themselves.
Jen is talking about the growth of the PASS community in Europe, and the related growth of WIT events. It’s fantastic to hear that this is growing globally! I remember that last year, after moderating the panel, I talked to an attendee from Saudi Arabia. He told me his wife faced many of the challenges we talked about, and he was excited to take all his notes from the lunch back to her. These are truly global challenges.
Kendra Little, an entrepreneur, MCM, and MVP (and my co-worker!) is talking about how things have changed for her in the last 10 years. She was great at teaching herself things, and learning on her own. She used to think, “I’m a worker, and I’m smart, and I can do things. But I’m not an entrepreneur. I’m a worker.” Now, she’s a partner here at Brent Ozar Unlimited®. PASS helped her overcome her earlier thoughts. She was taught how to be a mentor, and a leader.
It’s Q&A time!
“I train people to be SQL DBAs. We’ve only had one. I tell them they can make great money doing this. Women aren’t interested. Why? What can I do?” Denise says, “Ask them, “What’s stopping you?”” Kevin says, “I was once told by a woman, “Money isn’t what motivates me. What I really wanted to do was help people.” It’s the wrong appeal.” Appeal to their values. Stefanie weighs in. She says it might help to have them talk to someone who is successful in the field.
“This is my third year. I run a user group. It’s so wonderful. I encourage everyone to share with their local chapters.” Wendy points us to http://sqlpass.org/wit to get more information to give to user groups and other interested people.
“I have daughter, and she is going into a science and technology field. She’s come to many SQL Saturdays with me, and I think that’s given her the courage to pursue it. Thank you, from a dad.”
“I have a question about work/life balance. With 40 or 50 hour work weeks, it can be hard to approach your boss and ask for flex time. How can we encourage our employers and workplaces to offer things like that?” Jen worked for a company once that regularly had meetings at 6:30 pm. She only worked their 8 months. Eventually, she started her own business, which means she can set her own contracts, work from home – and pick her son up from school. Kendra has a couple of techniques, from a boss perspective. Track where you spend your time – there are free online tools to help with this. Gather data, analyze it, and make proposals. If you’re spending a lot of time on repeatable tasks, ask how you can automate it. Use a webcam for meetings! People see you, and know you’re working. Denise says this is a major concern for employers. One of the top reasons women leave jobs is because of a lack of flexibility. Kevin says to make a proposal that you will be more productive at home.
“I don’t see a lot of young ladies going into this field. I’m lucky to have gained the knowledge I have. I’m looking to start a group to share some of the things I’ve learned.” Stefanie connects her with Lynn Langit, who started DigiGirlz. [EDIT: Lynn, thanks for leaving a comment! Lynn created content for DigiGirlz. She also co-founded the non-profit Teaching Kids Programming.]
“How do we bring girls into technology? They want to be rich, they want to be famous, they want to be cool. Bring Girl Scouts to these events and let them see that geek is cool.”
“Can we talk at a higher level about outreach programs at colleges? There are no database clubs, or sponsors. That’s a solid thing that we could do at a chapter level.” (Personally, I would have loved this when I was attending tech school. Now I run a user group in the same city I went to school in. I think this is An Idea.)
“How many of you got into computers because of video games? I have a four year old daughter, and I found out recently she loves video games. We can start early showing them this is cool.”
“I’ve been saving this question since last year! It’s about organizational dynamics. When I’m on a team and it’s mostly guys, team cohesion comes from hanging out together. Those things happen to be competitive in nature. These things make me feel at a disadvantage. What are your comments or suggestions around that?” Denise says to offer an alternative – she once had a chef come in a lead a cooking class! Kevin says, they probably just haven’t thought of this. Suggest other alternatives. Jen suggests something with an environmental emphasis – something positive, like helping to plant a garden.
With that, I need to go prep for my Lightning Talk! Thank you to the wonderful panelists for sharing your insights and suggestions. Thank you to everyone who attended, and those who asked questions! Thank you SQL Sentry for sponsoring the lunch. And thank you PASS for continuing this tradition!
Good morning ladies and gentlemen! The conference hall is filling up in Seattle, and it’s time to crank up another blow-by-blow commentary. I’ll be updating this blog every few minutes with what’s happening here at the PASS Summit. For a refresher, check out the liveblog of yesterday’s keynote.
8:20AM – People settled in and the room’s going dark. I’ve got a much longer zoom lens today, so I can’t really capture what’s happening in the crowd, but now I’ll be able to zoom in on the sweating faces when demos break. (No, I won’t.)
8:23AM – PASS Executive Vice President Douglas McDowell taking the stage. He’s covering financial details, and he’s excited about it.
8:24AM – On track to be an $8mm organization this year, up about 40% from last year, 80% from two years ago. Some of this is probably due to the economy gradually recovering, but wow, what a great quick growth.
8:25AM – “We’re a nonprofit. We’re not trying to capture profits – we’re reinvesting them in the community.”
8:27AM – Douglas says this conference is a bargain compared to other conferences. Well, yeah, but that’s because the speakers aren’t paid, and they have to pay their own travel, hotel, meals, etc. Other conferences don’t do that, and we’re already starting to see senior speakers pull out of the Summit. It’d be a shame if that continued.
8:33AM – Welcoming new Board of Directors members Wendy Pastrick, James Rowland-Jones, and Sri Sridharan. They’ll be serving 2-year terms from January 1, 2013 to December 31, 2014. Big thanks to outgoing board members Allen Kinsel and Kendal Van Dyke.
8:34AM – “We’re volunteers. You know what volunteers are good at? Dropping the ball.” I haven’t said this til know, but this keynote isn’t going particularly well.
8:35AM – VP of Marketing Tom LaRock taking the stage to give out the PASSion award for 2012. Two changes in the award – added an Outstanding Volunteer of the Month award, and something else.
8:40AM – The PASSion Award winner for 2012 is Jen Stirrup! Congratulations.
8:41AM – Tom explains that the Board members have little black books where they’re writing down attendee feedback to improve the conference.
8:42AM – Improved communications: forums, town halls, Twitter chats, social media, feedback site, new communications platform, and more.
8:43AM – PASS Summit 2013 registration open now with an early bird discount at $1,095 until January 4, 2013. The Summit will be October 15-18, but it’s not clear what that means because they also say there’s 3 days of in-depth training. The 15th-18th is 4 days.
8:44AM – Lots of companies have sent over 5 employees to the Summit this year, some as many as 30-40.
8:46AM – Karaoke with a live band, SQLRockeraoke, tonight at the EMP Museum party. Tom will be doing a group singalong to Careless Whisper.
8:49AM – Taking the stage, Quentin Clark, Corporate Vice President of Microsoft.
8:54AM – Hotels putting RFID chips into the hotel keys. It’s a better experience for guests – easier room access – but also lets hotels spy on which guests are using the restaurant, the gym, the pool, etc.
8:55AM – The discussion then goes into Facebook, and BI moments. This might just be our Contoso Frozen Yogurt Moment coming up.
8:59AM – The room is dead. No applause, some awkward coughing. Twitter’s lighting up that this stuff just isn’t interesting to DBAs and it’s not telling a good story. Zzzz.
9:02AM – Julie onstage to do a demo of PASS Cinemas.
9:10AM – Showing various pieces of technology but not actually doing any work. We got Hadoop, PDW, Azure, and enterprise data warehouses in about 3 minutes.
9:13AM – Apparently big data means small fonts. Seriously, what the hell, Microsoft?!? We complain about this every year. Demos are tedious enough in a room this big, but gimme a break. If we can’t read the screen, we get really pissed off. Value our time.
9:18AM – Body language onstage says it all. Demos are failing, bad patter, even the presenters seem bored. The room is just dead.
9:22AM – Demo is still just absolutely crawling. I’m cashing out mentally here. If I was watching from home, I’d have bailed fifteen minutes ago.
9:36AM – There’s a vibrant discussion going on in Twitter about how bad this keynote sucks. Is it as bad as the fake Tina Turner that sang Simply the Best a few years ago? What about the year when a vendor speaker kept saying, “Yadda yadda yadda”? Tough call, but it’s definitely in the bottom. The material may be vaguely useful, but it’s presented in tiny fonts on a big screen, totally unusable, and no cohesive story. There’s bugs and missteps all through the demo. The air’s totally dead, and the audience is quiet. It’s horrendous.
With that, I’m signing off and leaving. I don’t do this often – it’s the first time I can remember leaving a keynote – but my time is more valuable than this. I’m heading out for coffee.
Good morning, folks! Lights, camera, action – it’s time for the first keynote presentation at the Professional Association for SQL Server Summit. It’s the annual international conference for Microsoft database folks. This year it’s in Seattle, Washington again.
Over the next two hours, I’ll be expanding this post with minute-by-minute notes of what’s being covered by Microsoft and the PASS executives. You can refresh this page and see the latest notes. Enjoy!
8:13AM – People filing in, lots of folks surrounding the blogger table. Tough to ignore all the cool people while I’m getting set up. Ah, dear reader, the sacrifices I make for you.
8:15AM – PASS’s Kathy Blomstrom has informed us that “As of this morning, PASS Summit 2012 had 3,894 delegates – up 13% from last year’s previous record attendance – and 1,717 pre-conference registrations across 57 countries for a total of 5,611 registrations.”
8:19AM – PASS President Bill Graziano is taking the stage to talk about the numbers and what makes PASS successful – grassroots community involvement around the world.
8:22AM – Bill: “We are 12,000 – excuse me, make that 120,000 people strong.” That’s what I’m talkin’ about.
8:23AM – The PASS Board will be holding another open session this year for Q&A. It’s important for the community to continue to be open and accept the tough questions, and I’m glad they continue to open the kimono. Wait, maybe I don’t want to see inside that kimono, heh.
8:28AM – SQLRally Nordic will hold their third event in November 2013. No SQLRally in the US mentioned yet – it was put on hold earlier this year.
8:29AM – Over 543,000 hours of training delivered by the community for the community this year. (This is calculated with attendee numbers – if one person leads a one-hour sess
8:31AM – The first PASS Business Analytics Conference will be held in Chicago April 10-12, 2013.
8:31AM – Microsoft announcing their new in-memory database technology, Project Hekaton. This didn’t come from the stage – @JamieT caught it. Excerpt:
Furthering Microsoft’s commitment to deliver in-memory solutions as part of our data platform, today we are introducing Project codenamed “Hekaton,” available in the next major release of SQL Server. Currently in private technology preview with a small set of customers, “Hekaton” will complete Microsoft’s portfolio of in-memory capabilities across analytics and transactional scenarios. It will provide breakthrough performance gains of up to 50 times, and because it will be built into SQL Server, customers won’t need to buy specialized hardware or software and will be able to easily migrate existing applications to benefit from the dramatic gains in performance.
8:36AM – SQL Server 2012 SP1 out today.
8:39AM - Ted Kummert taking the stage. He’s Microsoft Corporate Vice President of the Data Platform Group. He usually leads the Microsoft part of the Day 1 keynote and hands demos off to individual Microsofties.
8:40AM – Ted: “I continue to be impressed by how this community invests in itself.” That’s a great way of saying it – we’re all trying to improve our skills and improve those around us to increase our overall value.
8:42AM – Officially announcing SQL Server 2012 SP1 available today. (I caught this earlier in the press release.) Interesting that there’s absolutely no applause for this.
8:43AM – Showing a video of attendees talking about the change they’ve seen in their career and what they’re excited about for the future. Looks like it was taken at a feedback group in the last couple of days with a combination of customers, consultants, and MVPs.
8:46AM – Starting to talk about big data. “Approaching the tipping point.” Talking about how we need to reason over large amounts of data every time we serve people a page. This is where I start to get a little twitchy – the other way to think about big data is sloppy programming, but I digress. There *is* legitimate big data, but if you reason over large datasets for each web page you serve, you’re doing it wrong.
8:49AM – Microsoft Research worked with hospitals to conquer the re-admittance problem: patients that had to come back to the hospital to get their problems solved. They used machine learning to give patients better care to reduce return visits. Or as I like to call it, “euthanasia.”
8:50AM – “If the full dataset fits in memory, amazing transformations are possible.” Ayup. This should not be news to any database professional, let alone any database manufacturer, and it’s a little frustrating that we’d be reacting to this in the next version of SQL Server rather than, say, 2008. I don’t envy Microsoft’s challenges in predicting the future, but this one seems a little obvious.
8:56AM – Demoing SQL Server Classic up against Hekaton. Classic is running 2,000 transactions per second, but running into latching problems. (Latching often means a lack of indexes.) This already sounds like a cooked demo specially created to show how fitting stuff in memory AND applying the right indexes makes things faster. Moving it to Hekaton got a 10x improvement at around 20,000 transactions per second.
8:59AM – By modifying the stored proc, we’re up over 60,0000 transactions per second. They didn’t cover what the modifications are, and I’ll leave that to you to think about.
9:01AM – Demoing column store indexes as a way to show performance improvements by keeping data in memory. This works in SQL Server 2012, but coming in the next major release, they’ve added two new improvements: it’s updatable, and it can be the clustered index. It’ll be interesting to see how they describe the differences between these to end users, and how the licensing will work. These scream Enterprise Edition only.
9:04AM – Rick from online gaming company BWin talking about using Project Hekaton to improve their session state database. They were maxed out at around 15k transactions per second – and yes, these guys really optimize the bejeezus out of their stuff. They’re been able to hit over 250k transactions per second with Hekaton.
9:06AM – Over 1.5mm units of the in-memory database in customers’ hands. They’re referring to the in-memory columnar analytics stuff, which includes Excel, so that’s a little tricky – but it’s such an awesome time in technology when this kind of technology is available to end users on their laptops.
9:08AM – Ted says they’re building a lot around the Apache Hadoop infrastructure and they want you to be able to leverage everything that the Hadoop ecosystem provides. “This may not be technology that you’re familiar with, but I’d encourage you to discover them and use the samples.” Microsoft needs you, dear reader, to keep your skills current so Microsoft can bring you new tools and you can adopt ‘em. No pressure – I’m just sayin’.
9:10AM – SQL Server 2012 Parallel Data Warehouse will be coming in H1 2013, and it lowers costs by using Windows 2012 Storage Spaces. Christian Kleinerman onstage to demo it.
9:16AM - Christian Kleinerman demoing a 1PB data warehouse query finishing in under two seconds. It’s tough to do justice to this kind of thing in a 5-minute demo. Like Kummert says, it’s a heck of a tough audience, and we take a lot of this with grains of salt. There’s no mention of the hardware performance, storage performance, number of columns in the table that we’re not selecting, etc.
9:20AM – Microsoft went to Dr. David DeWitt with a question – how should the query processor change? The answer was PolyBase, a new breakthrough in data processing for queries over relational and Hadoop data, in place.
9:28AM – A few very awkward “BI moment” phrases which bombed. Now showing a Great Western Bank customer video talking about how quickly they were able to recoup their BI investment in a 30TB data warehouse. “BI makes heroes, and there’s not a lot of tools that can do that.” I think that’s a great quote for executives, but not in a room full of developers. Visual Studio makes heroes too.
9:30AM – Ted: “Excel is now the complete end user BI tool.” Die, Access. Die in a fire.
9:32AM – Amir Netz onstage! He’s the wild card of the demo crew.
9:33AM – Demoing data visualization with Bing maps inside Excel. Looks absolutely gorgeous – except for the freakin’ CAPS LOCK MENUS. Jeebus, these things bother me. But yeah, this is a great visualization tool, and if I was an SSRS person, I’d be worried. The same guys who love handling data in Access will love bypassing the BI crew by building their own tools in Excel.
9:37AM – Visualizing data by combining the movie award data with 11mm tweets to see when people were tweeting about movies. Counts terms extraction by actors and actresses – Brad Pitt versus George Clooney, etc. “Imagine that you’re a brand manager – actor names are brand names.”
9:42AM – Not much going on here. Just showing Excel moving charts. While Amir Netz is upbeat and fun, he isn’t really communicating anything technical here.
9:49AM – Audience clapping wildly, biggest applause so far, for transparent images of Angelina Jolie and Jennifer Aniston. This is a little…awkward? The PASS folks unveiled a new code of conduct aimed at avoiding harassment, and I wouldn’t be surprised if someone complained about the appropriateness of these.
9:51AM – And we’re done! Off to a day of learnin’.