SPRINGFIELD – Local systems administrator Jackie Toole was carried through city streets today on the backs of ecstatic Acme Tooling employees.

For months, Acme’s accounting department had complained of slow performance in their payroll application. “Saving a new hire took minutes, and we had no idea what was going on. Jackie did something, and next thing you know, everything was just blazing fast. It was incredible!”

Fellow admin Marianne O’Reilly took a moment out of the parade to say, “We’d been throwing hardware at it left and right, from a terabyte of memory to a huge new solid state storage device. We just couldn’t crack the problem. Jackie just nailed it right away with that defrag. Even the worst database queries just fly now!”

In related news, local training schools have suffered a dramatic decrease in enrollment as word continues to spread that programming quality just doesn’t matter.

0 comments ↑ Back to top

REDMOND, WA – Microsoft announced today that SQL Server vNext’s editions will be named by their new marketing partner, Taco Bell.

“We’re excited that SQL Server Supreme Edition is now our top-of-the-line product, replacing Enterprise Edition. When you hunger for warm, satisfying In-Memory OLTP wrapped in the power of unlimited memory, only SQL Server Supreme Edition satisfies,” said product manager Mark “Tex” Souza of Microsoft’s Data Platform Group.

The database icon, laid sideways and covered in cheese

The database icon, laid sideways and covered in cheese

Souza continued, “Like the Sriracha Quesarito, which is the best of a quesadilla and a burrito rolled into one, Windows Azure SQL Database offers a great hybrid of the SQL Server you know and love, plus the tasty goodness of Microsoft-managed cloud services. Therefore, we’re renaming it as the Microsoft Databasarito.”

At press time, we’re still trying to confirm that Express Edition has been added to the Taco Bell Dollar Cravings menu, and track down the rumor of a new product code named Fourthdatabase.

3 comments ↑ Back to top

Have you ever been watching queries crawl, feeling sort of helpless because you can’t change them? And even if you could, it’s hitting a new custom column in an ISV table that no one bothered to tell you about. I’ve been there. I’ve been pomade deep there.

I’ve also had my Robot tell me: “there’s an index for that, Erik.”

And Robot was right! But how could I sneak an index on this 180 million row table without making things worse? It’s the middle of a production day. Slow is bad; blocked is worse.

Well, creating an index ONLINE just needs a lil’ lock at the beginning and then a lil’ lock at the end, and just like the Ghost of Milton Berle that watches you while you sleep, you’ll never know it was there.

But you have to specify the ONLINE option, even in Enterprise Edition, that your company lovingly spent your bonuses for the next decade to license. Here is an example that breaks contrived speed:

No Business Use Whatsoever

AdventureWorks went out of business because of queries like this.

Seems reasonable. What say you, execution plan?

>Implying I need an index

Hey, look, it’s that thing Robot told me about. Thanks, Robot.

Except unlike Robot, I have a feeling. Just one. And today that feeling cares about end user experience and satisfaction. I want to get this index into the party pronto. This is the index that SQL and Robot agree on. But c’mon, we both know there’s WAYYYY more stuff going on here.

Missing Index Details from SQLQuery4.sql - NADA.AdventureWorks2014 (sa (59))
The Query Processor estimates that implementing the following index could improve the query cost by 99.0347%.

USE [AdventureWorks2014]
ON [Sales].[SalesOrderDetail] ([SalesOrderDetailID])


To prove it, we can script out simple index out from the GUI.

04-Go To The GUI

And here’s what that gives us. Note that ONLINE = OFF here.

05-GUI Results


If we go back in and check out the options, we can turn ONLINE = ON here.


06-Back To The GUI

And now looking at how it scripts out, ONLINE = ON

07-Better GUI Results

Which means it’s totally safe to just roll this out any ol’ time.

Just kidding, follow change management procedures and TEST TEST TEST. Also remember that robots and missing index hints are sometimes just plain dumb. They’re not terribly aware of existing indexes, and they don’t consider the columns they’re asking you to index. I’ve seen SQL demand indexes on 4 – 5 (MAX) columns, or ask for just one column to be indexed that could easily be covered or included in another existing index.

If you want to see what’s up with your indexes, missing, existing, hypothetical, or somewhere in the middle of that Bizarre Love Triangle, click here to check out sp_BlitzIndex®.

Kendra says: I keep trying to use ONLINE=MOSTLY, because that seems accurate, but it never works.

Brent says: Once I got started typing out CREATE INDEX by hand, I fell into the rut of doing it every single time, and I forgot about all the cool options available in the GUI. I’m conditioned to just say ONLINE = ON, but there’s other cool options too – like 2014’s amazing performance improvements if you sort in tempdb.

7 comments ↑ Back to top

Learn the basics of Foreign Keys in SQL Server: trust, join elimination, cascading deletes, and indexing child keys. Limited on time? Check out the Table of Contents below.

Table of Contents:

03:46 – Foreign keys change performance
04:12 – Let’s say we have no foreign key
04:41 – This query has a join
05:05 – The plan with no foreign key
05:34 – Add a foreign key relationship
05:54 – Run the same query
06:30 – Foreign key join elimination
06:38 – Try to add an invalid PostTypeId
07:08 – You can use NOCHECK
07:54 – Now can I add that row?
08:16 – But when I try to re-enable my untrusted foreign key…
08:29 – I get an error if I use WITH CHECK
09:29 – Does foreign key join elimination work now?
09:33 – I get an error if I use WITH CHECK
09:36 – Does foreign key join elimination work now?
09:37 – Nope, the foreign key isn’t trusted
09:51 – Delete the row that violates the key
10:11 – Checking keys needs heavy locks
11:42 – Join elimination may not happen
12:24 – What about cascading updates/deletes?
13:31 – Example syntax – cascading deletes
14:34 – Behind the scenes…
15:51 – Cascading deletes
17:03 – Do I always need to index foreign keys?
18:14 – Creating the foreign key
19:12 – Indexes on the child table can help
19:38 – Takeaways
20:24 –

Brent says: when you’re deciding whether or not you should include foreign keys in your database, this video will help.

9 comments ↑ Back to top

Can I upgrade an existing instance without migrating?

Keep your columns and rows inside the database while you prepare for your upgrade.

Keep your columns and rows inside the database while you prepare for your upgrade.

This is nothing against SQL Server 2014, but I can’t stand in-place upgrades. Over the years I’ve had in-place upgrades work flawlessly on a few instances, and then had an install issue cause it to fail in the middle on other instances. Usually the critical instances, just because I’m not always lucky. And when upgrade fails, it doesn’t always roll back completely, or allow you to just re-run it. You may be down for a good long time.

But you’ve got backups, right? Really recent ones? Even so, how long does it take to restore them, if you need to do that to a different location? (And did you set it up right?)

While in-place upgrades may be fine for test and dev environments, they aren’t a good fit for your production instances where RPO and RTO are critical.

Should I raise the database compatibility level to 120 to use the new cost-based optimizer?

If you can test it for all your queries and know if it’ll be right for you, you can turn it on. Most folks can’t be sure of this so they start with it off to reduce risk from the migration.

The new cost based optimizer is very exciting, but there’s definitely a chance you can hit performance regressions. If you can’t test in advance, turning it on at the same time you migrate makes your troubleshooting more complex if you hit a problem.

Can we restore a full backup with NORECOVERY, run CHECKDB, and then later restore a differential?

You can’t run CHECKDB unless you’ve done a full restore and made the database writable. That means you can’t apply a differential backup afterwards.

You can potentially mitigate the risk by running a full CHECKDB against the database prior to running the backup. You may also run the backup with CHECKSUM (not a substitute for CHECKDB but it does apply some protection), and then run CHECKDB in a maintenance window shortly after the migration.

It’s all about your risk tolerance.

Is it still helpful to run DBCC UPDATEUSAGE after a migration or upgrade?

DBCC UPDATEUSAGE is typically no longer needed and only impacts output from sp_spaceused, anyway. Check the ‘remarks’ section on its page in books online for the full details:

People got into the habit of this because it was needed to upgrade to SQL Server 2005. But it’s OK, you can let go of that habit (and it’s worth it, this command can be pretty slow).

Should I run sp_updatestats after a migration or upgrade?

This was a big deal when upgrading to 2005 because of changes they made to statistics, but it’s not needed specifically for SQL Server 2014 upgrades. Some folks like to do this to kick the tires, but don’t go crazy or think it’s magical.

What should I set ‘max server memory’ to for SQL Server 2014 if I’m running Standard Edition?

Possibly to more than you think. Max server memory in Standard Edition is limited to 128GB for the buffer pool in SQL Server 2014, but you may want to set it higher so that other parts of SQL Server can access memory above that level. Read more here.

How do I turn on the tempdb IO enhancement in SQL Server 2014?

You don’t have to, it’s just on.

Are there any known upgrade issues?

Microsoft keeps a list here, along with a pre-upgrade checklist:

Which cumulative update should I use?

You should definitely use one if you care about performance, but the choice can be very complicated. Especially if you’re using Availability Groups.  There is no easy answer: read all the Cumulative Update articles and test heavily before you go live.

Want more SQL Server setup help? Check out our setup guide for SQL Server.

And while you’re here, please don’t forget to think about your version of Windows.

15 comments ↑ Back to top

Stop. Before you think about upgrading your existing SQL Server to the latest version, think it through.

In retrospect, putting a Prius engine in the Gran Prix wasn't my best idea.

In retrospect, putting a new Prius engine in the Gran Prix wasn’t my best idea.

Your hardware is old. Even if it’s just a couple of years old, the latest advances in processors, memory, and storage mean it’s practically an antique.

Your Windows is old, too. Over the years, you installed a bunch of crap that you probably shouldn’t have put on the production box. You could uninstall it, but even that will leave leftovers all over the place.

You’ve learned a lot since the first time. When you first installed it, you didn’t know that you shouldn’t install extra services you don’t need, or that you should use 4 TempDB data files, or that you should format the drives with 64K NTFS allocation units. Now that you know about really good setup checklists, you wish you could do it all over again – and now is your chance.

Patching takes forever. You need to bring both Windows and SQL Server completely up to date, but it can take minutes or hours of downtime to get all the right patches applied. And then there’s the BIOS and firmware, too. Instead of guessing how long the old box will take to shore up, just build a new one and get it perfect.

You need to test the new Cardinality Estimator. For several versions, your query plans haven’t changed when you upgraded, but those days are over. SQL Server 2014 brings dramatic changes to the Cardinality Estimator, and you need to check your queries against it to prevent a 100%-CPU-surprise on go-live day.

You need a plan B. Patching and upgrading has a serious potential for failure. In theory, you could restore the OS backups and try again, but have you ever tested that? What happens if it fails, and you have to try it all on the fly? What’s your recovery time objective?

Kendra says: It’s funny, in-place upgrades always worked fine for me, unless it was on a production server.

10 comments ↑ Back to top

First, decide what you want to get really good at. Then try to break it in many possible ways and analyze why it broke.

I think it's going to explode

I think it’s going to explode

The more you break something, the more you’ll understand it.

I use this technique all the time. Last year, I encountered a problem with a lack of worker threads in SQL Server in a client environment. That issue was particularly difficult, because when the problem occurred it was difficult to observe the SQL Server without using the Dedicated Admin Connection (DAC). At the time, I built some repro scripts in my own environment to show how the issue started, why it was tricky to observe, and how to observe it and confirm the queries at the root of it all without restarting the SQL Server. And just recently I wrote new scripts breaking the same thing in different ways — and showing how parallelism can be a factor — for our 2015 Performance Troubleshooting class. Taking the time to break it myself taught me nuances about workers in SQL Server that I wouldn’t have learned otherwise.

“But Kendra, I don’t have time for this!”

Here’s how to make time. Mix and match these three ideas:

1) Make it a team exercise.

One person breaks something in pre-production, the other has to fix it. You save and publish notes on the error messages and how you responded.

2) Tie it to a learning program.

Purchase one of our training video or in-person classes, and design a learning program that uses the training class as a launching board. Set a goal to write your own scripts breaking something for at least 5 of the modules.

If you really want to lock in the knowledge, write down a summary of what you’ve learned in your own words. You can blog it or publish it as notes for your team at work to reference. Your own notes will help you over time more than you expect.

3) Set goals for mastering specific items for the year.

Talk through it with your manager and document why you want to master the topic, and three things you want to achieve at work after you’re done.

10 comments ↑ Back to top

Ever wonder how somebody else does it? Watch over my shoulder for this beautifully recorded one-hour session at SQLRally Nordic in Copenhagen from a couple weeks ago:

The resources, scripts, and my Be Creepy process are all free too. Enjoy, and check out all of the session recordings for even more free learnin’.

4 comments ↑ Back to top

Kendra here– we’re really excited to announce that we’ve hired Erik Darling on to join our team as a consultant. Erik’s first day is today.

To introduce Erik, we’re sharing some of his answers to our call for job applications. We were so impressed, we knew we had to talk to him right away.Erik-Robot

What are your favorite things about your DBA job?

Getting a 10 production server/100tb of data environment configured to best practices and maintained and (WEEKLY) DBCC checked.

Migrating legacy apps to new VMs.

Helping the devs with in-house applications and reports (lots of code and index tuning).

Setting up Failover Clusters, install and config SQL.

Learning, learning, learning.

Being the only DBA.

What parts of the DBA job do you not enjoy?

Filling out paperwork, archiving to tape, being on call from 7am to midnight seven days a week, being the only DBA.

Tell us about a time you broke a production server.

So this one time I had to move a 5tb database to a near-line SATA LUN, and I was all like, “GUI? GU you! I’m gonna PoSH this ogre to icy hell.”

That’s when I found out that the Move-Item command basically eats like all the memory it can if you don’t have LPIM set up for SQL, and this dual 12, 512GB behemoth basically churned to a halt, couldn’t RDP, couldn’t VNC, nothing.

On the plus side, I could use PowerShell to kill the PowerShell process remotely.

After that I just restored a copy of the database to the new LUN and dropped the old one. That went a lot better.

Why would you be great at this job?

I like solving big problems. I like teaching people how to solve little problems. I once taught a developer who barely spoke English how to cross apply with a drawing. It may or may not have been a New Order album cover. I can talk about SQL all day. Most of it is right, too, especially if I think about it first. I am all self taught and have never had anyone senior to me, so I have plenty of straight up horribly grim determination to learn things and put them into practice.

What do you think will be the hardest part of this job for you?

Not sneaking onto Jeremiah’s Twitter account to change his name to Bodega Mayonnaise.

Brent says: I swear, we didn’t just hire him because of the tattoos. But I’m not going to say they weren’t a contributing factor.

Jeremiah says: I’m glad to have a co-worker who shares my appreciation of plaid, tattoos, and crazy T-SQL.

35 comments ↑ Back to top

Raise your hand if you’re storing BLOBs in the database.

I get to say that a lot during our training. Every time I say it, nearly every hand in the room goes up. Some hands go up faster than others, but eventually nearly every hand is up.

It’s a design that happens far more often than it should, but it does happen.

Why Store BLOBs in the Database?

People put binary data in the database because they need the data to be point in time consistent with the rest of the database. It’s not enough to save space in the database if you can’t recover the file to a moment in time.

Think about this scenario:

  1. 8:00AM – Brent saves a new contract.
  2. 9:00AM – Kendra modifies the contract and saves it.
  3. 9:30AM – Jeremiah modifies the contract and saves it.

If the contract is being stored inside the database, we can recover to any point in time and have the appropriate version of the document. It may not be the most current version of the contract, but it’s the version of the document that’s consistent with the rest of the database.

Why Not Use the Filesystem?

File systems are great. They do an excellent job of storing files and organizing them into folders. File systems don’t do a great job of being point in time consistent with a relational database. There’s no transaction log to help us roll back writes that are in flight.

It’s a lot of work to get a full database backup and a file system back up to be remotely close to the same point in time. Restoring a database to a point in time is easy. Restoring a file system to a point in time is close to impossible.

Why Not Use an Appliance?

appliancesThere’s a third option available – some kind of appliance that sits between database and the file system. The appliance should manage file metadata and provide all access to the files in the file system.

Commercial databases ship with features that sound similar. SQL Server has a FILESTREAMdata type and Oracle has both a BFILE and ORD data type. Both of these types let the database interact with files in the file system. But they still have a problem – you’re stuck managing data through the database. Let’s be clear: this is not an appliance.

Content Addressable Storage (CAS) is a mature technology. The idea behind CAS is that a hardware device handles the meta-data for a given incarnation of a file. A developer sends a file into the CAS appliance and the CAS appliance returns a pointer to the file. Whenever the file changes, a new copy is created and a new handle is returned to the developer. Files can’t be modified, so any thing stored in the database can only point to the right version of the file.

We can combine this with a database pretty easily. Instead of storing a file path in the database, we store the handle that we get back from the CAS system.

How Does CAS Solve the Problem?

The main reason people store BLOBs in the database is so they can get blobs that are consistent with a point in time in the database. By using a storage device that cannot be modified (the CAS), we can make sure that the location we’ve stored in the database is always the right location – there’s no way to tamper with the files that we’re storing, so whatever gets stored in the database is correct.

There’s overhead to this approach – old data may never get cleared out. Typically, though, CAS systems store data on large, slow disks. There’s little need for the throughput that we use for a relational database store system. Do those cat pictures really need to be stored on RAID 10 SSDs? Moving BLOB storage outside of the relational database will free up resources for serving queries. Picking the right way to store your BLOB data will make it easier to scale your system.

Kendra says: Finding the right storage for large objects is a huge architectural decision that impacts performance and availability. Choose wisely!

Brent says: Want your SQL Server’s DBCCs and backups to run faster? This can help a lot.

Doug says: “It’s a lot of work to get a full database backup and a file system back up to be remotely close to the same point in time.” -> This is a major drawback that’s easily overlooked. Make sure everyone’s okay with that possibility when choosing the file system for BLOB data.

34 comments ↑ Back to top