Yearly Archives: 2012

Wanna Get Your FreeCon This Friday?

Written on May 15, 2012 by in Upcoming Events, Writing and Presenting

When I first started going to conferences, I thought all the value was up on the screen. I took copious amounts of notes in every session. I asked questions. I paid close attention. When the sessions were over, I packed up and went home, eager to implement what I’d learned. Of course, reality set in when I got back home. I had a gazillion emails and help desk tickets, and by the time I dug my way back out, I’d forgotten a lot of what I’d learned. My notes sat dormant on my hard drive for weeks or months.

Punch harder.

Free-Con Seattle 2010 - Homework Time

I’ve since learned that the most valuable things at conferences are peers, not PowerPoints.

I started the Free-Con to connect and inspire people. It’s a free one-day event before conferences, and it completely ignores the conference itself. We focus on improving our content, our people skills, and our sense of community. I only invite a limited number of community content creators, but they’re from all walks of life – new bloggers, MVPs, and corporate spokespeople.

The next one is this Friday, May 18th in downtown Chicago, lining up with SQLSaturday 119. We’ve had a few last-minute cancellations, so now’s your chance to sneak in.

What to Expect

Free-Con feels like a panel discussion without an audience – just one big panel. We’ve got a great list of attendees with all kinds of different experience, and everybody wants to share and help each other. To promote discussion, I’ve got a list of topics and short presentations ready, but expect to spend most of the time interacting with each other.

FreeCon Chicago’s agenda includes:

  • Your Resume is Backwards – Your resume is organized by company, then position, then skill. What if we organized it the other way and looked for gaps?
  • Lean Methodology – “Lean” is part of a manufacturing strategy for minimizing waste. I’ll explain why it’s relevant to your career and your day-to-day work.
  • Zen and V12 Maintenance – Can your work bring you closer to people who inspired you?
  • Tufte’s Supergraphics in Action - Edward Tufte wrote about how to reinvent presentations using large printed handouts. Software manufacturers are starting to do a surprisingly good job of it. We’ll examine one and see how to turn it into a full-blown presentation.
  • And more – like group discussions around where we find inspiration and how we can improve SQLSaturday.
This Could Be You

This could be you. Only older, and without the mouse ears.

The event will start at 9AM and last until 4PM. With this many bloggers in one room, we’ve got an incredible opportunity to build relationships and blog posts. We’ll cover three ways to get other bloggers involved in your blog, and then we’ll actually do it live. (Wait, not “do it” – although, I mean, if you find one of the other bloggers attractive, I suppose you could. Just don’t blog about that.)

Doors will open at 8, and if you make the list, you’re welcome to show up early. A continental breakfast will be served at 9am, and you can’t come to Chicago without having Giordano’s pizza for lunch, so that’s catered in too.

Who To Expect

This event is about you meeting your peers – not just SQL Server peers, but other people who want to build their brand online to get better jobs. I picked people I enjoy spending time with, and if I like hanging out with you, I’m pretty sure you’re going to like the other attendees. You have a lot in common, and you’ll do a better job at it if you’ve got friends you can ask for advice and help.

The attendees for this Free-Con are:

  1. Argenis Fernandez (Blog – @DBArgenis)
  2. Bill Lescher (@BLescher)
  3. Bob Pusateri (Blog – @SQLBob)
  4. Christina Leo (Blog – @ChristinaLeo)
  5. Eric Harrison (LinkedIn)
  6. Garima Sharma (LinkedIn)
  7. Hope Foley (Blog – @Hope_Foley)
  8. Jason Fey (Blog – @JFay_DBA)
  9. John Mazzolini (LinkedIn – @JMazzolini)
  10. Josh Fennessy (Blog – @JoshuaFennessy)
  11. Louis Fritz (LinkedIn)
  12. Noel McKinney (Blog – @NoelMcKinney)
  13. Norman Kelm (Blog – @NormanKelm)
  14. Scott Ellis (LinkedIn)
  15. Tim Ford (Blog – @SQLAgentMan)
  16. Tim Radney (Blog – @TRadney)
  17. Tom Norman (LinkedIn – @tjnorman57)

Former Free-Con attendees include:

What Past Attendees Said

Jes Borland wrote:

“I got to pick the brains of some really smart, really talented, really motivated, really funny, really smart people. For someone who is relatively new to the database world, and really new to speaking and blogging, this was like a year’s worth of lessons crammed into a day. I got to sit next to people that I never thought I could and ask them anything about their career, their experiences, for advice, and listen to their stories. I cannot tell you how cool that was.”

Grant Fritchey wrote:

“For me, FreeCon defined some things that have been running around in my brain for the last 18 months or so. It also inspired me to pump up my blogging, to try to improve my game and my brand. I’ve done a very large number of actions in the two weeks since FreeCon went down. Many of them are already bearing fruit, for example, I’m now hosted on my own domain. Others may bear fruit in the near term, and I have EXTREMELY high hopes for these. Still more are the high level goals that I’ve started to define that will likely take me years to deliver.”

Jason Strate wrote:

“It was a terrific event and I learned a lot.  In actuality, there’ll be a free PDF up on my blog fairly soon that is a direct result of attending.  And there’ll likely be a second PDF download available before the end of April.”

Update – Filled Up!

Thanks for your emails – we’re now filled, and the attendees above are the final list.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

3 Comments - Leave a comment!

Who’s Going to Hire You? Seriously.

Written on May 14, 2012 by in Interviewing, SQL Server

I got my start in the hospitality business by working at distressed properties.  Distressed is the polite term that hotel management companies use when a hotel is in financial trouble, has health violations, and nobody in their right mind would stay there.  Thankfully, there are a lot of tourists who are not in their right mind.  Think about that the next time you pull into a hotel after a long day of travel and say, “Just give me a room.”  Here’s an example of a distressed hotel’s pool (and yes, I worked at this hotel):

The guests were distressed, too.

That particular hotel had been hit by a tornado, and the owners were fighting with the insurance company over who was going to pick up the tab for the damage.  After the tornado struck, the first management company simply closed the restaurant and shut the power – without emptying or cleaning the freezers.  I had the joyous task of getting maintenance men to clean out hundreds of pounds of rotting steak.

When I say “distressed hotel,” it’s not just the building that’s distressed.

At distressed properties, a pattern emerged pretty quickly:

  • The hotel owners would hire the management company because things weren’t going well
  • The management company brought in their own General Manager
  • The GM would bring in a bunch of his friends in for management positions
  • Things would get better – for a while
  • The hotel owners would fire the old management company and go back to the first step
  • The GM would leave the property, take their friends along, and go to another property

Each time the management company changed and the GM left, I noticed that the GMs were bringing a lot of friends along for the ride.  When the third new GM (Tom) came in, I made it a point to be buddy-buddy with him.  He was thankful for the help, and he taught me a ton about the hotel business in a matter of months.

When the owners fired this new management company, Tom headed off to Georgia to manage yet another property, but this time something different happened.  The phone rang, and Tom was on the other end.

Tom: “I’ve just taken over this hotel in Savannah.  You wanna come down here and manage the front desk staff?

I said, “I have no idea how to do that.”

Tom: “Neither did the last guy here, but at least I know I can work with you, and you’ll work hard for me.”

Sold.  I packed my car and moved down to join him.  It was the easiest promotion I’d ever gotten.  That job was never posted in the newspaper, and if it had been, I’d have never known to look in Savannah’s newspaper – and I wouldn’t have made the cut over all the other applicants anyway.  Tom called me directly and hired me because I’d already gone through a long interview with him – I’d worked for him.

Your Coworkers Are Interviewing You Right Now

There’s a pretty good chance that if your manager quits, you might not want to work for them somewhere else. You probably also only have one manager, and you don’t want to put all your eggs in that one basket.  After all, they may never leave the company.

Your coworkers, on the other hand, are plentiful and ambitious.  I bet at least one of your coworkers leaves this nasty hole every year, and they break free to get a better job somewhere else.  Wouldn’t it be nice if they called you up a few weeks later and gave you the inside track on a new job?  It’s the easiest way to get ahead, and you’ll be working with at least one person who already knows and trusts you.

Recently, I asked you to write a testimonial for the Incredible DBA Team, and I asked you how it made you change the way you see your job.  Now it’s time to go back, read the comments, and think about how you can generate some of those same testimonials for yourself.  Your fellow developers, DBAs, sysadmins, SAN admins, managers, project managers, and end users are your very best network.

Who do you want to take you along?  What can you do today to make sure they’d want to take you along for their adventures?  How do you get them to write testimonials like these?

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

2 Comments - Leave a comment!

How to Make SQL Queries Run Faster: Contest

Written on May 14, 2012 by in SQL Server, Videos

Whether you’re a developer or a DBA, you’re probably frustrated with query performance.  Why is the app running slow?  Why does query performance vary so much?  Why can’t your queries run consistently fast?

You’ll learn the tricks of performance tuning in this three-hour session as Brent covers:

  • How to find the worst queries on the server in 60 seconds
  • What statistics are and how to tell if they’re causing problems
  • How to think like the engine and build your own execution plan
  • Why filters are cheap and sorts are expensive
  • When you should throw indexes at the problem, and exactly when to stop
  • Why temp tables might be the fastest way to fix many problems
  • How to identify parameter sniffing problems and fix ‘em for good

This session is for developers and DBAs with 1-4 years of experience who are comfortable writing queries and adding indexes, but aren’t happy with the results they’re getting.

Want to Watch the Whole Thing?

Here’s your options:

  • Join me in a live webcast for $249
  • Buy the video right now for $149
  • Leave a comment below.  I’ll draw two random winners tomorrow (Tuesday 5/15) at 11am Eastern, announce their names on our Tuesday webcast, and give them free access to both the webcast and the video.  One entry per person – US residency not required.  Update – the contest is closed.  We felt generous and gave away four complete packages to Andrew, Dustin Laine, Randy Jaye, and Yu Chen.  Congrats, and if you didn’t win – hey, you can always buy in. ;-)

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

57 Comments - Leave a comment!

Video: How Jes Borland Got Here

Written on May 10, 2012 by in SQL Server

Meet Brent Ozar PLF’s employee #1: Jes Borland, aka @grrl_geek on Twitter. May 8th was her first day with us, and we gave her a baptism of fire – throwing her straight into a group discussion webcast! We talked about her background, what she likes about SQL Server, and her running in this 30-minute video:

Jes Schultz Borland

Jes Borland is a problem solver, specializing in SQL Server. She has a degree in programming, but has worn the hats of database administrator, report writer, and trainer. Never content to sit still, she loves to run, adventure, and cook. She once ate 1.2 pounds of strawberry rhubarb pie in two minutes to beat 18 men in a pie eating contest.

More Posts

Follow Me:
TwitterFacebookGoogle Plus

0 Comments - Leave a comment!

SQL Server RDS

Written on May 9, 2012 by in Cloud Computing, SQL Server

Amazon Web Services (AWS) is a platform that offers a ton of services ranging from block storage, operating systems, to mail queues, DNS, and caching. When you say “cloud”, a lot of people think of AWS. AWS hosts a variety of businesses, large and small. Unfortunately, you still have to administer and configure your servers. That’s not a bad thing, but it’s something that many teams have to keep in mind. Configuring SQL Server in AWS environment isn’t the easiest thing on earth: the technology behind the AWS platform changes on a regular basis and it sometimes changes in subtle ways that can change how SQL Server performs.

Relational Database Service

Amazon Relational Database Service (RDS) is Amazon’s attempt at creating a hosted database platform on top of the other AWS services. The point is to take a lot of the headache out of managing relational databases. Instead of setting up and configuring servers for MySQL or Oracle, customers can have a database server up and running in a few clicks. Backups happen automatically, restores are easy to accomplish, and instances can be easily added or removed.

The problem is that there hasn’t been a way to do this with SQL Server. Companies using the Microsoft stack but invested in AWS have had to set up and configure their own SQL Servers. While this is normal for many businesses, for start ups this is an extra expense – it means that the servers are either configured by developers or operations staff, or that the company has to bring a DBA on board sooner than they had planned.

Enter SQL Server RDS

Amazon have launched SQL Server RDS: a hosted SQL Server service. Users can easily create instances of SQL Server through a wizard, web service, or command line script. It’s easier than ever to scale your infrastructure in the AWS environment.

Previously it was possible to create a new instance of SQL Server by spinning up a new SQL Server equipped AMI, but it still took time for both Windows and SQL Server to finish the sysprep process. On top of the instance set up time, a DBA would still have to configure the instance with additional security and configuration settings. In short, you could spin up instances of SQL Server in Amazon, but someone on your own team was still responsible for patching and maintenance.

Like every other AWS service, there’s a free usage tier. Developers, or really lean applications, can start out using a free version of the software and migrate up to larger instances as needed. The majority of the instances types are supported, apart from a notable absence of cluster compute instances.

SQL Server RDS gives you most of the features of SQL Server – it compares pretty closely with SQL Server and many features are fully supported. Amazon are pretty explicit about which features aren’t supported, so if you’re expecting a SQL Server Agent, maintenance plans, or the DTA, then you’re out of luck.

The Limitations

There’s a lean set of features that can be relied on to work even if your SQL server instance needs to be restarted or if the OS disk becomes corrupted. Features that rely on MSDB or any other system databases simply can’t be used because there’s no guarantee that you’ll be attached to the disk when your new OS volume is spun back up. By making sure that the OS volume never changes, it’s very easy to upgrade an instance – you just attach a new OS volume with the requisite changes at the next reboot. Because of limited access to the system database, many SSMS standard reports don’t work.

SQL Server RDS imposes a hard limit of 30 databases per instance of SQL Server. While that seems purely arbitrary, remember that there’s an unknown pool of storage underneath the instance that is invisible to you as a consumer of the product. On that note, you also can’t scale the storage underneath a SQL Server RDS instance – once you set up your storage at a certain level, that’s the amount of storage that you are stuck with.

Many DBCC commands don’t work at all – DBCC DBINFO and DBCC LOGINFO were explicitly denied. I did, however, discover that I could run DBCC FREEPROCCACHE as often as I wanted.

Keep in mind that these limitations may change over time – new features are added on a regular basis.

Configuring SQL Server

Some system tools, like sp_configure are not available as a way to set server level settings – you can run sp_configure and see the configuration values, but you can’t change them directly in SQL Server. Instead, you create a SQL Server parameter group.

DBAs may find it frustrating to use command line tools to set up SQL Server parameters, however once a parameter group has been created, it’s incredibly easy to apply the parameter group to any number of SQL Servers at the same time. The upside is that both trace flags and sp_configure settings can be modified at the same time. The downside is that the commands to set up the parameter group are not at all intuitive. Changing a the max server memory looks something like this rds-modify-db-parameter-group sqlservergroup --parameters "name='max server memory (mb)', value=65536, method=immediate". Intuitive? No. Powerful? Yes. Once you’ve set up the parameter group, you just tell RDS to create new SQL Servers inside that parameter group and they will automatically be started with those trace flags and settings in place.

Scheduling maintenance and backups

Backups

How are backups configured and maintained? By you… in a way.

During instance creation, you specify a backup retention period between 0 and 30 days. You can also specify your backup window and maintenance window – just in case you want to specify quiet hours when maintenance should occur. Point in time restore is available via a GUI, command line tools, and that’s it. Keep in mind that these aren’t database level restores,these are instance level restores – during the backup, writes will be quiesced, the storage will be snapshotted, and then writes will resume again. Point in time recovery is also available and transaction logs are backed up every 5 minutes. There’s more information and details in the AWS RDS documentation.

And A Gotcha

When you create a user, they’re automatically given access to the rdsadmin database. This is a database that the RDS team have created to provide access to some of the low level functionality, like reading trace files and other actions that you normally must be a sys admin to perform. The big gotcha here is that any user who can access the SQL server can, by default, access the rdsadmin database and execute the stored procedures in the database. Admittedly, the stored procedures that could potentially cause any harm aren’t accessible, but the unprivileged user can still see them.

Running sp_helprotect shows that guest has privileges to execute these stored procedures. Although guest can run the stored procedures, the procedures that can potentially cause changes will fail with errors like Login needs CREATE ANY DATABASE permission. So while a malicious user could determine that you’re running your system on RDS, there’s only a limited amount they could do from that point.

When Would You Use This?

Let’s be fair, it isn’t apparent to everyone when they might want to use a hosted SQL Server. There are a lot of reasons why you would want to stand up a brand new SQL Server quickly.You may want to create multiple backend servers as you scale out your application – maybe you create one instance per customer to make billing easier. Developers can spin up a full copy of the application stack to test how changes will work – they no longer need to maintain a copy of the database locally, instead a clone of the production system can be restored and made available in a matter of minutes.

How Does It Perform?

SQL Server RDS performs admirably. I ran a variety of different benchmarks against my test instances and I maxed out the CPU of the RDS instance at 42% while running around 710 transactions per section and sustaining a decent rate of throughput. The biggest bottleneck was the CPU and network connection on the clients running the benchmarking tools. Obviously, benchmarks aren’t a real measure of anything apart from the benchmark’s performance, so test your application appropriately.

Alerting and Monitoring

Amazon haven’t forgotten about setting up alerts and monitoring. Every piece of AWS comes with instrumentation in the form of Amazon CloudWatch. CloudWatch is a set of metrics that are collected from underlying OS or application server. Even though you have no access to the underlying OS, SQL Server RDS exposes many metrics that can help you determine whether you need to move to a larger instance, monitor CPU, tune for memory, or purchase more storage. It’s even possible to stack graphs to see how two instances compare to each other.

Monitoring in the AWS Management Console

Beyond that, it’s easy to make alarms on your instances that will alert you when your instance goes beyond specific performance metrics.

Creating an Alert

Once you’ve configured your alerts, you can even see how current instance performance compares to the alerts you’ve set up. All of your alerts for all AWS services are located in the same place, so you’ll be able to see just how your entire virtual infrastructure is performing.

The AWS Alerts Dashboard

How’s RDS Different From SQL Azure?

Microsoft’s database-in-the-cloud solution has a subset of SQL Server’s capabilities. It supports less SQL Server features, less datatypes, and smaller databases. Don’t get me wrong – it’s still a solid product – but it’s half the man that SQL Server is.

Amazon SQL Server RDS is full-blown SQL Server. You don’t have to change anything whatsoever about your apps – if it runs on-premise, it likely runs in Amazon RDS. Your tools like SSMS won’t know they’re talking to anything but a regular SQL Server instance.

However, neither SQL Azure nor RDS will act as a log shipping target or a database mirror: you won’t be restoring databases directly. Both Microsoft and Amazon try to automate routine maintenance for you, but as a result, routine maintenance tasks aren’t available to you. This limitation on database restores means Amazon RDS won’t replace every SQL Server running in Amazon EC2 today.

What’s an RDS SQL Server Cost?

The Amazon RDS pricing page has tabs for MySQL, Oracle, and SQL Server, and unlike SQL Azure, it gets complicated fast. Pricing depends on:

  • The SQL Server Edition – Express, Web, Standard, or bring-your-own
  • The hardware size – from micro (630mb RAM) to quadruple-extra-large (68GB memory)
  • The storage size – from 20GB to 1TB
  • The datacenter you choose – Oregon is cheaper than Tokyo, for example
  • How much bandwidth you use

And more. To get started, a micro instance with 20GB of storage running SQL Server Express Edition is just $.035 per hour, or about $306.60 per year. A Standard Edition, quad core, 15GB memory instance is $1.22 per hour, or $10,687.20 per year, but it drops to $6,219.60 if you bring your own licensing or $6,482.40 if you use Web Edition. Reserved instances become even cheaper by the hour in exchange for a one time upfront payment. The break even point typically comes at the three month mark – after three months you were better off buying a reserved instance. That quad core 15GB instance drops down to $3206.16 per year, plus a one time payment of $5730 for a 3-year reserved instance.

One More Thing

Just one more thing: the sp_Blitz that we know and love? When you use sp_BlitzUpdate to grab the latest version, you’re fetching it from a SQL Server RDS instance right now.

Jeremiah Peschka

Jeremiah Peschka has worked as a database and emerging technology expert at Quest Software where he researched new trends and technologies in the world of data storage. Over the course of his career he’s worked with companies across many industries as a system administrator, developer, and DBA. He’s been involved with all aspects of application development and deployment. He likes cheesecake, coffee, and ice cream.

More Posts - Website

Follow Me:
TwitterFacebook

4 Comments - Leave a comment!

Today BrentOzar.com Turns 10 Years Old

Written on May 7, 2012 by in Consulting, Writing and Presenting

Happy birthday to my first post from May 7, 2002.

BrentOzar.com circa 2001

1,782 posts and over 12,000 comments later, I feel like I’m still winging it, but it’s time to stop and think about the lessons I’ve learned over the last decade.  This web site has turned into a consulting company that supports three of us, and tomorrow we add our first full-time employee, Jes Schultz Borland.

Here’s what I’ve learned so far:

Blogging is either your passion, your hobby, your job, or your chore. Guess which blogs will succeed and which will fail. I’ll be the first to tell you that it doesn’t feel like blogging is my passion – it’s just a hobby. I know folks who are truly passionate about blogging – they spend hours a day working on their blog, SEO, analytics, promotion, you name it. I think that’s awesome, but you don’t have to be that passionate for your blog to succeed and turn into a company. It just takes longer when it’s a hobby, and it ain’t gonna happen if you see your blog as a job or a chore. That’s okay – just find a different route to success that doesn’t involve blogging.

There are periods in my life where it’s a chore.  There have been months where you’ll be simply overwhelmed with work and unable to blog, and that’s okay.  Just know that you’re going to lose momentum in the form of stockpiled posts and eager readers.

BrentOzar.com circa 2004

Use your most comfortable writing voice.  When you sit down at the keyboard, you want the words to just come pouring out.  When you first get started, just start typing.  Don’t try to mimic someone else’s writing style.

Readers want to get to know you, not just the topic.  If you want to write personality-free content, don’t bother blogging – contribute to Wikipedia.  It’s a wild, thriving community that appreciates quality contributors.

As your interests shift over time, so will your blog.  I started out shoe-gazing, then wrote about turtles, then focused on computers when I got a column in HAL-PC Magazine.  (Funny glimpses into history – in 2003, I predicted Windows Tablet would be a failure, was already writing about virtualization, and enjoyed bathroom humor.)  In my How to Start a Blog guide, I emphasize how important it is not to tie your personal site to a product or topic: don’t brand yourself as SQLWhatever.  Five years from now, when your focus changes, you’ll thank me.

The look matters, but not as much as the content.  Nobody ever forwarded a post to a friend and said, “You’ve gotta read this!  Their WordPress theme looks amazing!”  People don’t return for beauty – they return for content (although your content can be beauty, too.)  On the other hand, readers definitely do say, “I can’t read this – the theme is driving me crazy.”

BrentOzar.com circa 2006

The Underpants Gnomes were right.  Here’s how blogging works: Step 1: Collect Followers. Step 2: ? Step 3: Profit!  Step 2 isn’t impossible – it’s just unpredictable.  Check out how a few popular bloggers turned their hobbies into a living:

  • Jeff Atwood wrote CodingHorror, a killer blog for programmers, and turned his following into a fast user base for StackOverflow.com, a killer QA site for programmers.
  • Jenny Lawson wrote The Bloggess, a hilariously offensive blog, and made money off endorsements for Chipotle turned her following into a book deal.
  • Justin Halpern just tweeted – TWEETED, mind you – hilarious stuff as @ShitMyDadSays, and within 60 days he’d been mentioned on every talk show around.  He then turned his following into multiple book deals and a TV show.
  • Just in the SQL Server world alone, Aaron Bertrand, Brad McGehee, Grant Fritchey, Steve Jones, Tom LaRock, and most recently, Robert Davis have all turned their online followings into evangelist-type jobs where they’re not on call.
  • Little old me started a blog, and now we get so many requests for consulting services that it keeps three of us busy full time.

If sharing your knowledge online is a hobby (or a passion) for you, the profit will come sooner or later.

I’m even happier having partners.  Turning this blog into a company and partnering up with Jeremiah and Kendra has been incredibly fulfilling.  We all push each other to up our game by offering feedback on our work.  I know I do a better job of blogging knowing that Jeremiah and Kendra also have their skin in the game here, and I don’t want to let them down.  I love making them proud.

You, the reader, make everything worthwhile.  Sure, I get lots of comments that say “Please send me how to be a DBA fast” or “You’re stupid” but those pale in comparison to the thank-you emails I get.  I love hearing about someone who solved their problems or got a better job through what they read here.

Here’s to another ten years.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

27 Comments - Leave a comment!

Picking Your Cache

Written on May 3, 2012 by in Architecture

Answering questions about performance is almost becoming a catechism: “How can I make my database faster?” Use a caching server. “Which queries should I cache?” All of them. A lot of people stop there. They implement a cache in their application using some kind of in-memory hash table and call it a day. There’s more to it than that, of course. Not all caching solutions are cut from the same cloth.

Back to Basics: Memcached

Memcached is a big hash table: it’s a key/value store that lives entirely in RAM. Memcached has a few basic commands that correspond to the basic CRUD commands in any database but in memcached CRUD works by finding data based on a specific primary key – you can’t search through RAM. Like many key/value stores, memcached doesn’t have any ability to query based on the data that is stored in the value portion of the key/value pair.

What’s more, the memcached server has no native support for high availability features – it is just a cache. If the cache server goes down, it goes down. Losing a cache server might not sound terrible, but if your application depends on a fast cache for performance then losing a cache server can cause a critical performance problem.

What’s the solution to high availability with memcached? There isn’t an out of the box solution for high availability with memcached. Different drivers can use [consistent hashing][ch] to spread data across multiple memcached servers and there are libraries that support memcached replication between data centers, but none of these features are baked right in to the product. Ultimately it’s up to you to implement this yourself.

The upside of memcached is that it has been in development since 2003, it speaks a well known protocol, and many developers have run into it before. Amazon’s ElastiCache even speaks the memcached protocol; if you move into the cloud, there’s already something waiting for you. The memcached documentation also includes suggestions on ways to cache SQL queries, so your developers will have a leg up on when they start examining caching. There are also plugins for many languages, frameworks, and products – there’s a lot of support for memcached out there.

The Middle Ground: Flexible Tools

No matter which software stack your development team is using, there is going to be at least one caching solution that they can pick up and run with. Not all caching tools are as simple as memcached, others are more feature filled; Microsoft have created AppFabric Cache and developers in the Java world can use Ehcache. These two packages have the concept of availability baked into their core – both Ehcache and AppFabric Cache can cluster right out of the box. There are multiple advantages with this approach: high availability isn’t a bolt-on that depends on a third party library and management becomes much easier.

Since we mainly talk about SQL Server around this place, I’m going to keep talking about AppFabric Cache from here.

Although AppFabric Cache has only been around for a short period of time, it is being used in a number of places. Caching services are available in Azure, there’s an ASP.NET session state provider, and I’ve personally used AppFabric Cache to supply fast paging in large reports.One of the primary advantages of using AppFabric Cache is that it’s very easy for Windows admins to configure and administer – everything is handled using similar tools that administrators are already familiar with.

Like memcached, AppFabric Cache supplies a simple set of APIs to read, write, or delete data. The simplicity of AppFabric Cache makes it a logical choice for developers working with SQL Server and the Microsoft stack. On top of its simplicity, AppFabric Cache adds two features to make developers’ jobs easier. The first feature is cache expiration. Instead of relying on the cache to expire data when it is no longer used, developers can specify a time to live when saving a value to cache. As data is read, the expiration can be refreshed, but if data isn’t read for a long time, it will be marked as expired and will no longer be able to be read. The second feature is high availability – developers can save some values in multiple places at once, ensuring that data will survive the failure of a cache server.

On a feature by feature level, AppFabric Cache provides features that meet the needs of almost every application. It still has one downside – there’s no way to query data in an ad hoc fashion. Every data access is key/value. It’s possible to use different pools in the cache, but those are only separations in a logical sense. If you need to retrieve a range of data, your only option is to build inverted indexes: key/value pairs where the key is the index key (e.g. the state of Oregon) and the value is a list of indexes values (e.g. all zip codes in Oregon).

Fast and Furious: An In Memory Database

In memory databases have several advantages over pure caches. A cache is a simple key/value database and the value is nothing more than a collection of bytes. Databases, however, offer increased functionality – range scans, sorting, strongly typed data, and a host of rich commands.

Redis is a fast in-memory database. At first glance, you might think that Redis is a lot like a a cache – it looks in many ways like a simple in memory key-value store. However, Redis hides a lot of additional power: sorted sets, lists, queues, and replication are all supported features. By combining these features, it becomes possible to use Redis as something more than a cache – it becomes the primary database for fast querying. It’s easy to store user session properties in a hash, a user’s last 50 viewed pages in a list, and any number of objects as simple strings.

Once you start working with an in memory database like Redis, it’s important to start thinking of creative ways to use your database; Redis can be used as far more than a glorified key-value database. Developers are using Redis for realtime metrics, analytics, and Redis has even been called the AK–47 of databases because it’s simple, powerful, and reliable.

Wrapping Up

No matter how you choose to cache, make sure that you start caching in your applications as soon as you can. Yes, it does add extra work for developers to implement caching. But tis’ not as much work as many people would like to think. Careful and judicious use of caching will have immediate benefits for application performance – database CPU, memory, and I/O requirements will decrease and application response times will improve.

Jeremiah Peschka

Jeremiah Peschka has worked as a database and emerging technology expert at Quest Software where he researched new trends and technologies in the world of data storage. Over the course of his career he’s worked with companies across many industries as a system administrator, developer, and DBA. He’s been involved with all aspects of application development and deployment. He likes cheesecake, coffee, and ice cream.

More Posts - Website

Follow Me:
TwitterFacebook

4 Comments - Leave a comment!

Things Every DBA Should Know – Webcast and Scripts

Written on May 2, 2012 by in SQL Server, Videos

When you’re in charge of your company’s data, there are a few key things that you need to know about your job. Exactly how many production servers do you have? How many databases? When does support end on that SQL 2005 box? If a server went down, what would be the very first thing you’d do? I’ve got over a decade of experience managing production databases, and I’ll open your eyes to some surprising questions about your job responsibilities in this 30-minute video:

We cover a lot of scary things in the video, but thankfully, there’s an easy way to check for most of it – our sp_Blitz script.  We’ve got a new version out today, and here’s the changes:

  • Thomas Rushton http://thelonedba.wordpress.com/ @ThomasRushton added check 58 for database collations that don’t match the server collation.
  • Rob Pellicaan caught a bug in check 13: it was only checking for plan guides in the master database rather than all user databases.
  • Michal Tinthofer http://www.woodler.eu improved check 2 to work across collations and fix a bug in the backup_finish_date check.  (Several people reported this, but Michal contributed the most improvements to this check.)
  • Chris Fradenburg improved checks 38 and 39 by excluding heaps if they are marked is_ms_shipped, thereby excluding more system stuff
  • Jack Whittaker fixed a bug in checkid 1.  When checking for databases without a full backup, we were ignoring the model database, but some shops really do need to back up model because they put stuff in there to be copied into each new database, so let’s alert on that too.  Larry Silverman also noticed this bug.
  • Michael Burgess caught a bug in the untrusted key/constraint checks that were not checking for is_disabled = 0.
  • Alex Friedman fixed a bug in check 44 which required a running trace.
  • New check for SQL Agent alerts configured without operator notifications.
  • Even if @CheckUserDatabaseObjects was set to 0, some user database object checks were being done.
  • Check 48 for untrusted foreign keys now just returns one line per database that has the issue rather than listing every foreign key individually. For the full list of untrusted keys, run the query in the finding’s URL.

Download and run sp_Blitz now.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

3 Comments - Leave a comment!

What Does a Good DBA Look Like?

Written on May 1, 2012 by in SQL Server

Developers want to work with them.

And yes, I wore that hat well.

The Classic ASP Developer Hat

Sysadmins and SAN admins want to help them.

Managers want to hire them.

DBAs want to join them.

Who are they?  They’re the Incredible DBA Team – the group of database administrators with an awesome reputation in their company.  When they walk into a meeting, project managers breathe a sigh of relief and customers know the job is going to get done, no matter what.

Today, I’ve got a simple assignment for you in the comments.  Write a testimonial for the Incredible DBA Team from the point of view of a non-DBA.  Put your developer, sysadmin, manager, or end user hat on and write the quote from their point of view.  I’ll start:

“Every time I bring one of my slow queries to them, they’re able to explain it to me in friendly terms without making me feel stupid.  I actually want to bring them my slow code.” – Dan the Developer

Your turn – what’s your testimonial for The Incredible DBA Team?  And how does it make you think about your job differently?

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

15 Comments - Leave a comment!

Things Every DBA Should Know (But Most Don’t)

Written on April 26, 2012 by in SQL Server

What if your boss walked up right now and asked you, “How many database servers do we have?”

The classic poster

Would you start sputtering, “Uh, do you mean production, or dev? Do multiple instances on a cluster count? And by the way, who’s asking?”

Or would you confidently answer with an exact, unwavering number?

When I was a DBA, I thought I had a pretty good handle on my environment. I did proactive work like building a database server support matrix, setting up failsafe operators and alerts so I’d get notified when things broke, and even attended development planning meetings so I could see what was coming down the pike. Now that I’m a consultant, though, I realize that I wasn’t really armed for the kinds of questions business users like to throw at DBAs from out of nowhere.

How many database servers do we have?

When the business asks this question, it’s only the beginning. Here’s what they really want to know:

  • How many production servers/clusters do we have?
  • How many instances running on how many servers?
  • How many databases do we have?
  • How many applications are we supporting on each database server?

The snark starts

To start the process of surveying a Microsoft SQL Server environment, check out the Microsoft Assessment and Planning Toolkit. This free tool has a sinister purpose – setting up for a licensing audit – but it’s also really helpful to get a scan of SQL Servers buried under desks or hidden on application servers. It helps you find out about servers you’d otherwise never hear of – until they crash, at which point it’s too late.

The best DBA teams track not just the number of instances and databases, but the overall data size in the environment and the growth per week. I love being able to tell business executives, “I’m managing 10.4 terabytes right now and it’s growing an average of 3% per week.” They understand growth metrics, and it shows them that I take my job as seriously as they take company sales and expenses.

Which servers are no longer under support?

Most companies are harboring a few old database servers that soldier on just fine despite being long out of support. Database administrators often take this question personally and start responding with whines about not being able to move an old vendor application off SQL Server 2000, but take the emotion out.

Farm Gold and Level Up

The Microsoft Product Lifecycle Search is a little cryptic, but

  • SQL Server 2000 – mainstream support is long gone (2008), but you can still get extended support through April 2013.
  • SQL Server 2005 – if you thought 2000 was hard to kill, 2005 is going to be worse. It’s got enough DBA-friendly features like DMVs and database mirroring that it might just stick around forever – certainly long after April 2016 when extended support ends.
  • SQL Server 2008 – we’re looking at least through January 2019 for extended support.
  • SQL Server 2008R2 – as with 2008, at least through January 2019.
  • SQL Server 2012 – servers you deploy today are safe through July 2022, or 2023 for Express Edition.

If you’ve been pushing the business to get off SQL Server 2005 for support purposes, you should find a different approach before management finds the above links.

If that database server went down, what would you do first?

Enough with the Posters Already

The business doesn’t really care how you answer this question: they just want to hear confidence. They want to hear an immediate, well-thought-out-answer that says to the world, “I’m a data professional, and I’m the person you call to the helm when the database server hits the rocks.”

Here’s the kind of answer the business wants to hear: “I’ve got a script to take a snapshot of the database mirror in our DR datacenter and check the time of the most recent record in the sales table. I tell my manager that if we have to fail over to DR, we’re going to lose X minutes of data. Then I start at the top of our troubleshooting checklist, which is posted on the wall in my cube so I can find it fast when trouble strikes.”

Confidence is a key part of being a good DBA. Don’t just take it from me – take it from world-renowned database expert Jack Palance:

Want to Learn More?  Watch the Webcast.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

4 Comments - Leave a comment!
« Older Posts