Stop People From Changing Tables with This One Trick

Developers hate his bad idea jeans:


Presto, the schemabinding option means no one can change the underlying tables. (At least, until they figure out about your view.)

Obligatory disclaimer: seriously, this is a really bad idea. But it’s hilarious.

Why is SQL Server Slow Sometimes – But Only Sometimes?

Have you got a SQL Server that usually performs just fine, but every now and then, everything falls to pieces? Users complain about performance, and then minutes – or hours – later, the problem just mysteriously solves itself?

Here’s a few things to look for:

Are data or log files growing? When SQL Server needs to grow a file, it can bring all transactions in that database to a grinding halt. You can mitigate this issue by turning on Instant File Initialization, pre-growing out your data and log files, and using smaller autogrowth sizes. To figure out if this is your root cause and which cure makes the most sense, run sp_Blitz® and look for the warning about data and log file growths that have been taking more than 15 seconds.

Are queries being blocked? If one query takes out a lock that prevents other queries from getting their work done, it presents interesting symptoms. Your SQL Server’s CPU use might look low – but it’s because so many queries are sitting around idly waiting. To diagnose, start by running sp_AskBrent® with the @Seconds = 0, @ExpertMode = 1 parameters to check your wait stats since startup. If LCK* waits are significant, then start digging at the database level. Run sp_BlitzIndex® in each database, and look for the Aggressive Indexes warnings about indexes that are a frequent blocking sore spot. Consider dumping indexes you don’t need on those tables (to speed up deletes/updates/inserts), and adding the right indexes that you do need (to speed up other queries).

Sometimes I choose the wrong coffee tap, and I'm predictably slow.

Sometimes I choose the wrong coffee tap, and I’m predictably slow.

Are queries being rolled back? If a query fails – either due to being canceled, or running into an error, or hitting a deadlock – SQL Server has to undo their work. This may cause blocking, or it just may hammer storage. This one’s a little trickier to catch. If you run into it live, then you can see it with sp_AskBrent® – it warns about queries that are currently rolling back.

Did a bad plan get into cache? When you run parameterized SQL, SQL Server builds an execution plan based on the first set of parameters it sees. Normally, parameter sniffing is a good thing, but sometimes it goes awry. You can have a plan get pushed out of cache (due to memory pressure or statistics changes) and then get compiled based on the first – possibly unusual – set of parameters that come in. That plan might not work so well for other sets of parameters, and it can degrade performance on the whole box until a new plan is generated for it. You can learn more about solutions in this blog post about parameter sniffing, and to diagnose it, run sp_BlitzCache®. Look at the top resource-consuming queries, and see if an unusual one has suddenly jumped to the top of the charts.

Are shared resources under pressure? In virtualization environments, other guests may be using an increased amount of CPU, memory, or storage throughput, giving your guest less horsepower. In shared storage environments, other servers might be doing backups or disk-intensive workloads. In multi-instance servers, another instance on the same box might be the real culprit. To identify these, check out wait stats with sp_AskBrent®.

Looking back at what I’ve written, I’m sure someone’s going to say, “Brent, you’re just trying to pitch tools.” And sure, I am – they’re free. I want you to use the same powerful tools that we use every day in our consulting. Your job is hard enough without reinventing the wheel!

Announcing Our 2015 Black Friday Sale on SQL Server Training

You need SQL Server training, but your boss is too cheap to pay full price, and you’re willing to stay up late and join in on a mad rush for good deals.

Have we got a deal for you. Several deals, in fact, and this year we’re announcing them all ahead of time so you can get your budget approval on:

Midnight Doorbusters:

  • $1 seats for in-person training classes – just 3 seats per class title will be available (3 for The Senior DBA Class of 2016, and 3 for SQL Server Performance Troubleshooting). You can buy your seat in any of the cities/dates we’re offering.
  • 50% off in-person classes for the first 20 buyers – if you don’t get in on the $1 coupon, you’ve still got a good shot at saving thousands of dollars.
  • 75% off video training classes for the first 75 people – you can buy as many videos as you want, too!

As a reminder, here’s the 2016 in-person class lineup:

SQL Server Performance Tuning with Brent: 5 days, $3995:

  • February 22-26 in San Diego
  • April 11-16 in Newark
  • June 6-10 in Chicago
  • August 15-19 in Portland
  • December 5-9 in Philadelphia

The Senior DBA Class of 2016 with Brent: 5 days, $3995:

  • January 11-15 in Newark
  • March 7-11 in Chicago
  • May 2-6 in Denver
  • July 11-15 in Philadelphia

All day long deals:

There’s no cap on these awesome deals – even if you miss the doorbusters, you’ll still be able to get these all day long:

  • How to Think Like the Engine – absolutely free all day. Add it to your cart and check out, and you’ll get 18 months access at no cost.
  • 50% off video classes
  • 25% off in-person classes
  • $499 Everything Bundle – that’s a $1,553 value at 68% off.

The Fine Print

To get these deals, you’ll need the coupon codes – which we’ll announce on our company Twitter account, @BrentOzarULTD, at midnight Chicago time as we cross between Thursday, Nov 26th, and Friday, Nov 27th. Here’s a world clock calculator to show the time in your time zone. The all-day-long deals will be all day in the Chicago time zone.

You don’t have to follow us on Twitter – you can just refresh that page if you want – although of course you probably won’t catch it as quickly as our Twitter followers, and timing is everything on a sale like this. The doorbuster coupons typically sell out in the first 60 seconds.

Coupons are limited to one use per person, one time each, with no stacking. (That means if you try to buy 3 of the same course, the coupon won’t work, and someone else will sneak in ahead of you and score. Same thing if you try to use multiple coupons at once.) Coupons are not valid on already discounted items like bundles. All sales are final – here’s the terms and conditions.

If you get an error during the checkout process, that means someone else grabbed the coupon before you. It’s entirely first-come-first-serve, with no rain checks.

The eagle-eyed amongst you will notice a lineup change in our 2016 classes, too: Brent will be the only in-person training instructor next year. Jeremiah and Kendra will still see you at conferences and user groups, though.

Go check out the courses to think about what you’ll buy. Have fun!

Is My SQL Server Too Big for Virtualization or the Cloud?

Let’s set size aside for a second. Here’s the order in which you should try new technologies – whether it’s virtualization, cloud, storage, or whatever:

  1. Development servers with less than 100GB of data
  2. Production servers with less than 100GB of data
  3. Development servers with <1TB
  4. Production servers with <1TB
  5. Development servers with >1TB
  6. Production servers with >1TB

It’s simple: start with the lowest risk, easiest-to-manage servers first. Learn your lessons on smaller servers, then gradually use the technology on larger and larger servers.

If at any level, your users complain about performance and you can’t figure out how to fix it, stop at that level. Don’t go farther.

It’s not that 1TB of data is too big to virtualize or to move into the cloud – it’s not. But your skills may not be up to the task, and you need to sharpen those skills before you attack a larger performance problem.

It all comes back to my post, “How Many Databases Can I Put on a SQL Server?”

What kCura Relativity Best in Service Means for SQL Server DBAs


kCura Relativity is a legal e-discovery application that hosts its data in SQL Server. You can host it yourself, or hire a hosting partner who specializes in hosting it for you. The best hosting partners compete for Best in Service status, an award that means what it says on the tin.

Starting this year, kCura’s toughened up the requirements for Best in Service. It ain’t Average in Service, or Good Enough in Service. As a result, database administrators have some extra work to do.

Best in Service requires a disaster recovery plan.

Deep, calming breaths. I didn’t say a warm standby data center – I just said a plan. It might be perfectly okay for your plan to be, “Step 1: panic. Step 2: call Brent to bring our databases back from the dead.”

Here’s the thing, though: at some point, one of your customers is going to ask to see the plan. (I know, because I’ve been involved in some of those conversations.) You don’t want to show them a plan that looks that way.

Use these resources to get started:

Best in Service Requires Transparency

kCura’s all about transparency, right down to the Trust portal that shows Best in Service partner service status. Today, publishing your scores is optional – but come summer, it’s going to be mandatory.

That means if you miss backups or DBCCs, the whole world’s gonna know.

And it’s going to cost your business real money.

I’m wildly excited about this because it brings business focus to a very important DBA job duty. It’s your job to make sure the data is safe and secure. Start measuring it and checking today before you have awkward management conversations this summer.

#FreeCon 2015 PASS Summit Recap

We believe that when you’re at a community event, the most valuable thing isn’t what’s up on the screen. Instead, it’s building relationships with the people around you.

With that in mind, over the last five years, we’ve run a series of FreeCons – totally free networking events for the SQL Server community.

This year’s FreeCon at the PASS Summit in Seattle was our most ambitious yet. We rented out a movie theater for 50 folks and shared:

  • Identifying what you’re proud of now, and sharing that with those around you
  • Setting goals for yourself over your next year
  • Learning about personal branding
  • Understanding the difference between outbound and inbound marketing
  • Planning which inbound marketing tasks to do, in which order, and how long they’ll take
  • And of course, throughout the event, sharing all of that with those around you, and building meaningful lifelong relationships

We’re really proud of the results and the reactions:

Ed Leighton-Dick wrote: “For many years, I focused primarily on the technical aspects of my profession. That focus served me well, but I realized several years ago now that I needed to start developing some soft skills, also. Skills such as marketing have continued to elude me, but yet, they have become critical as I build my consulting practice. Thanks to Brent and Kendra for all their work in putting on the FreeCon, and thanks to all the rest of the attendees for a fantastic day – I learned a lot from all of you!”

Chris Yates wrote: “One thing that stuck out to me was the authenticity shown by both Brent, Kendra, and Doug along with the 50 or so of us who were asking questions.”

And Richie Rump tried to follow along:

Dazed and Confused at the #freecon. Need more sleep and coffee. #summit2015 #summit15

A photo posted by @jorriss on

Thanks to everyone who joined us – you were really the secret to making this event a lasting success.

Free SQL Server Magnetic Poetry Kits

We were looking at magnetic poetry words on a public bathroom wall (true story, insert TempDB joke here) and thought, “You know what would be really awesome? If there was one of these for databases.”

So we made ’em.

SQL Server Magnetic Poetry

SQL Server Magnetic Poetry

Each of us put together our own favorite database words, so we each have our own sheet.

Availability Group politely fails over app fatally bombs fantastic #SQLpoetry

A photo posted by Brent Ozar Unlimited (@brent_ozar_unlimited) on

AlwaysOn loves and hates. #SQLpoetry

A photo posted by Brent Ozar (@brento) on

To get one, just track us down at a conference or user group. This week, Doug & Kendra will be at the PASS Summit in Seattle, and I’ll be at SQL Intersection in Vegas. The more of us you meet, the more words you’ll be able to work with, since we’ll each carry our own sheets.

Share your favorites with us on Instagram or Twitter with the tag #SQLpoetry. We’ll pick out our favorites and send you a little something special.

Introducing Our 2016 Scholarship Program for SQL Server Training

You work with Microsoft SQL Server – maybe as a developer, report writer, analyst, sysadmin, or DBA.

You want to learn more about how to make it faster and more reliable.

But your company can’t afford training. Maybe it’s a charity that needs to save every dollar to make a difference in the lives of others. Or maybe it’s a non-profit arts foundation, or a small startup that’s barely making ends meet.

We want to hear from you. We’re going to pick 25 SQL Server professionals to get access to a tailored set of training classes that focus on issues relevant to each person’s pain points.

The fine print:

  • You must already have a job working with SQL Server.
  • You must work for a foundation, non-profit, charity, or similar company that’s doing good work. It can totally be a for-profit company, just as long as they’re making a difference. (If you work for Ginormous Profitable Global Corporation, forget it.)
  • Your company or government rules must allow you to receive free or discounted training. (Some companies prohibit their employees from accepting gifts.)
  • You can be anywhere in the world. (Some of our scholarships will involve in-person training, and some will be online video training.)

Apply now – applications close Friday, November 6th.

Free SQL Server Training: Architecture, 2016, Metrics, Index Maintenance, and More

No, we won't teach you how to paddleboard, because we already forgot.

No, we won’t teach you how to paddleboard, because we already forgot.

Do you like to shake sticks?

Because we’ve got more upcoming SQL Server webcasts than you can shake a stick at:

Easy Architecture Design for HA and DR – Clustering, log shipping, mirroring, AlwaysOn Availability Groups, replication – database administrators have so many confusing choices. Microsoft Certified Master Brent Ozar will cut through all the confusion and give you a simple worksheet. You’ll learn how to get the right RPO and RTO numbers in writing from the business users, then turn those requirements into the right features for you.

How to Think Like the SQL Server Engine – normally $29, and for one day we’re giving it to you completely free. When you pass in a query, how does SQL Server build the results? We’ll role play: Brent Ozar will be an end user sending in queries, and you’ll be the SQL Server engine. Using simple spreadsheets as your tables, you’ll learn how SQL Server builds execution plans, uses indexes, performs joins, and considers statistics. This session is for DBAs and developers who are comfortable writing queries, but not so comfortable when it comes to explaining nonclustered indexes, lookups, sargability, fill factor, and corruption detection.

What’s New in SQL Server 2016? – SQL Server 2016 is just around the corner. There are a lot of new features in the box, and improvements to old ones. There’s a lot to learn, and you need to start planning before SQL Server 2016 hits the streets. In this webcast, Jeremiah Peschka will share a high level overview of what’s next in SQL Server 2016.

Prove It!: Collecting the Right Metrics to Show Performance Gains – It’s easy to say adding more memory, faster drives, or a new index will help. How do you prove it really did help? What numbers and counters should I be watching? Doug Lane will explain which numbers matter when trying to show your changes were worth the cost.

Register now by just filling out your name/company/email. See you there!

Announcing Brent’s SQLSaturday DC Pre-Con: Performance Tuning When You Can’t Fix the Queries

Your users are frustrated because the app is too slow, but you can’t change the queries. Maybe it’s a third party app, or maybe you’re using generated code, or maybe you’re just not allowed to change it. Take heart – there’s still hope.

I do this every week, and I’ll share my proven methodologies to performance tune with indexes, SQL Server configuration switches, and hardware.

In this one-day workshop on Friday, December 4 at the Microsoft office in Chevy Chase, you’ll learn:

  • Simplified internals – how SQL Server turns your query into an execution plan
  • Wait stats solutions – a list of fixes for most common bottlenecks
  • Plan cache analysis – how to discover the most resource-intensive queries so you know if indexes and hardware will work
  • Monitoring metrics – what DMVs and Perfmon counters to monitor, and what thresholds to alert on
  • Hardware warning signs – how to prove your server is undersized for your workloads
  • Tools and scripts – the best free and paid tools to make your tuning job easier

This session is for developers, DBAs, and consultants who have to make SQL Server go faster. You should be comfortable writing queries and creating tables, but not as confident about interpreting SQL Server’s DMVs and diagnostic data. I’ll train you the same way I train my own clients.

This session will be demo-heavy, and you can play along. Bring a laptop running SQL Server 2005 or newer, with at least 100GB of free drive space. We’ll distribute the Stack Overflow demo databases on USB drives for you to restore, and you can follow along with the demos. You’ll get the load test scripts, run them against your laptop, and see how we solve various challenges.

Space is limited to 100 attendees. Get your spot now for $199, and then register for SQLSaturday DC too. See you there!