Blog

“Breaking” News: Don’t Install SQL Server 2014 SP1

Yesterday, Microsoft announced availability of Service Pack 1, saying:

As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below.

Yeah, about that commitment to software excellence.

This morning, the download is gone:

Notice: The SQL SSIS team has found an issue with SP1 installation if SSIS catalog is present in the SQL Server instance.They are currently investigating this issue including possible workarounds and fixes.

Oof – the term “possible workarounds and fixes” doesn’t sound good for those who jumped in and applied the patch. A commenter on the MS Data Platform Insider blog reported that it hosed the master database broke the instance in a way suspiciously similar to a similar bug in SQL Server 2012 SP2.

(And jeez, what is it with service packs lately? Remember the SQL 2012 SP1 100% CPU issue? I’m starting to think you’re safer with cumulative updates than with service packs.)

Remember, kids, don’t rush into patching. If your servers are mission critical, test in your staging environment first – staging is the DBA’s development. (No, your development environment isn’t staging – because your developers make their living in the dev environment, and if you broke that with SP1 yesterday, you’ll be slaving away today to get your dev instance back up and running.)

Update: the fix is in. If you applied SP1, follow the instructions in this StackExchange post.

Watch Brent Tune Queries [Video]

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’.

Ten Ways to Tell if Your SQL Server is a Clown Car

Commuting to work.

Commuting to work.

Sometimes we pile just a few too many duties onto our servers. They start to become a clown car – the classic joke where dozens of clowns come pouring out of an impossibly small car. (The physics behind it are actually really interesting.)

So how can you tell if your SQL Server has crossed the line into clown car territory? It’s easy:

10. You have both production and development databases in it.

9. Everyone runs SSMS and Visual Studio by remote desktopping into the server.

8. You’ve enabled remote desktop services for more people to log in simultaneously.

7. It has the engine, SSRS, SSAS, SSIS all installed and running, but less than 32GB RAM.

6. It has multiple instances of each of those services running. (Bonus points for different versions.)

5. Application servers point to a file share hosted by this database server.

4. You’re monitoring it, and the monitoring software’s repository also lives on the same server you’re monitoring.

3. You built a spreadsheet to track your affinity masking, Resource Governor, and max memory settings.

2. When you say “the server,” you don’t have to clarify because everyone knows it’s your only one.

1. You’re reading this blog on the server right now.

Kendra says: If you care about performance, you’ve got to start pulling clowns out of that tiny car.

How to Measure SQL Server Workloads: Wait Time per Core per Second

When I ask you how fast your car is, there’s a two common metrics for answers:

  • How fast it can go
  • How much horsepower or torque its engine can deliver

If I ask you how hard your car is working right now, you’d probably answer with:

  • Its current speed (in MPH or KPH)
  • Its current engine workload (in RPM)

Conveniently, these two numbers are shown front and center on most car dashboards:

Tachometer and speedometer

Tachometer and speedometer

For SQL Server, those numbers are:

Current speed: Batch Requests per Second – the number of queries your server is currently handling. It’s available via Perfmon counter, and it’s on the dashboard of most monitoring software.

Wait Time per Core per Secondwait stats is the technique of measuring how much SQL Server is waiting on. You can’t tune wait stats using percentages – instead, you have to look at the total amount of time involved.

Compare these two five-second moments in the life of a SQL Server:

Workload Comparison

Workload Comparison

It’s clear that in Workload A, nothing is going on. Sure, 100% of our waits are on storage, but we don’t really have a storage problem. If Workload A was a car, it would be rolling along at 1mph, waiting for the driver to hit the gas pedal.

In workload B, there’s much more going on – but do we have a performance problem yet? That’s not quite as clear. To get the real picture, I have to tell you how many cores the server has.

Let’s Use Wait Time Per Core Per Second

Waits over time don’t really give you a clear picture – you have to divide the workload by the number of cores you have. Let’s say they’re both running on a VM with 8 virtual cores:

Workload comparison with 8 vCPUs each

Workload comparison with 8 vCPUs each

The VM on the left isn’t doing any work. It’s coasting.

On the VM on the right, for every second on the clock, each of its cores is spending .4 seconds (400 milliseconds) waiting on something (disk, memory, locks, etc.) While that might sound significant at first, it’s still really not doing any hard work. It’s not unusual for each core to spend several seconds per second waiting on different resources – and that’s fine, because SQL Server’s schedulers are good at switching over and running other queries that don’t need to wait.

For example, say you fire off a big SELECT query that scans a huge table, and we have to go get a lot of data from disk to accomplish it. That query’s tasks can wait for dozens (or hundreds or thousands) of milliseconds while other queries get their work done.

That’s why to measure SQL Server workloads, I’m proposing a new metric: Wait Time per Core per Second. That one metric, by itself, is a lot like the tachometer on a car’s dashboard. It’s not a measurement of how powerful your car is, but it does tell you how hard your car is working at this moment in time.

sp_AskBrent® now gives you this metric.

We’ve added new key metrics to the default output, including Batch Requests per Second, and Wait Time per Core per Second:

sp_AskBrent v13 with Wait Time per Core per Second

sp_AskBrent v13 with Wait Time per Core per Second

Note the new output down around priority 250-251 – it helps you get a fast idea of whether the SQL Server is working hard right now, or hardly working.

You can download our First Responder Kit with sp_AskBrent® now, and in our training classes, I show you more details on how I use it to triage real-world performance emergencies.

sp_AskBrent v14 adds CPU % Utilization

When your SQL Server is having performance problems, sp_AskBrent® checks a bunch of common trouble spots. In this week’s new version, it also checks sys.dm_os_ring_buffers for the most recent CPU utilization report from SQL Server and returns it.

Here’s what it looks like when the server isn’t under load – note that “No Problems Found” means the rest of the alerts are just information about the SQL Server:

sp_AskBrent on low-load server

sp_AskBrent on low-load server

And here’s a heavily loaded server:

sp_AskBrent on a server under high CPU loads

sp_AskBrent on a server under high CPU loads

I haven’t been blogging about this tool much, but I’ve become quite proud of it over the last few months.

Parameters include:

@Seconds = 5 – you can run it for longer periods like 60 seconds to see the server’s health during that time range. It’s really useful when you’re tuning a query or doing a presentation demo – fire it off on a 60-second span, go run your query or workload in another window, and then come back to sp_AskBrent to see what the effects were. Especially useful when combined with…

@ExpertMode = 1 – returns more result tables including wait stats, Perfmon counters, and my personal favorite, file stats – which shows you how much your workload read and wrote from your data/log files and TempDB:

sp_AskBrent file stats with expert mode on

sp_AskBrent file stats with expert mode on

@OutputDatabaseName, @OutputSchemaName, @OutputTableName – you can log results to tables. Some folks are running sp_AskBrent® every 5 minutes in a SQL Agent job, dumping the data to a table, so that they can go back in time and trend what happened on the server. This got better in last month’s v13, which also added the ability to output the file/Perfmon/wait stats details to individual tables too.

To get started, download our First Responder Kit with our latest scripts, posters, and e-books.

Quiz: Are You the Next Brent Ozar Unlimited Consultant?

Hey SQL Server DBAs — we’re hiring!Help-Wanted-Unicorn

Here’s a quick five question quiz. Give yourself one point for every ‘yes':

  1. Have you been a database administrator for a few years?
  2. Does planning how to keep an application reliable and safe from disasters sound like fun?
  3. Do you love helping people make SQL Server go faster?
  4. Would you like to have a cartoon character of yourself?
  5. Can you see yourself dropping into our Office Hours sessions occasionally to help people for free?

If you got five points, you just might be the next Brent Ozar Unlimited employee!

Here’s what to do next:

Read more about the job here to find out what we’re looking for and what benefits we offer.

Then tell us about you!

We plan to accept applications through Friday, March 6, but don’t wait too long to submit — life moves fast sometimes.

Update March 7 – applications are closed, and we’re interviewing candidates. Thanks!

Woohoo, We Won Two 2014 Tribal Awards!

This year’s Simple Talk Tribal Awards are out, and the video announcements are pretty funny:

We won in two categories: BrentOzar.com won the Best Blog, and the Twitter Account of the Year goes to @BrentO.

I’ll let Sally Fields take over from here:

[Video] How to Become a Senior DBA

To figure it out, we have to define what the words senior and DBA even mean. I explain in this video.

To follow along, print this image out:

Senior DBA skills grid

Senior DBA skills grid

Kendra says: The part of this that resonated with me most was how to work with your existing management to show that you’ve been growing your skills – I wish I’d heard that back when I was struggling to figure out how to be a Senior DBA!

Jeremiah says: The best part of this was the reminder that I don’t need to be a specialist everywhere. If I had kept this advice in mind when I was struggling to become a Senior DBA.

Jes’s thoughts: my favorite advice from this video is to focus on 1-2 things to learn in the next 6-12 months, and make a plan for that. When I started, I assumed I’d be able to learn everything at the same rate – I know now that isn’t true. In 2015, I plan to learn more about Extended Events and virtualizing with VMware.

Doug says: Love it. It’s all true in my experience too — you can’t become a specialist without focus, you can’t focus without a plan, and you won’t get noticed for a promotion (or a new job) if you aren’t really good at a few things. No one in the SQL Server world ever made for themselves a reputation for being good at everything.

And the next step after finishing the video and building your learning plan: learn to say no.

Brent’s Bad Idea Jeans [Video]

Over the years, I’ve come up with some rather “interesting” answers to SQL Server problems including:

In this 20-minute video, I’ll explain what drove me to these crazy solutions, and it’ll be up to you to decide whether they’re awesome or awful.

For questions & answers about these, tune in to our Tuesday webcast.

How Would You Change AlwaysOn Availability Groups?

SQL Server 2012 introduced AlwaysOn Availability Groups, a way to achieve high availability, disaster recovery, and scale-out reads. SQL 2014 brought some improvements around higher uptime and more scale-out, and all signs point to continued improvements in the next version of SQL Server, too. (I love it when Microsoft brings out features like this and continues to invest in them over time.)

A lot of the emails I get start with, “I’d like you to help me implement AlwaysOn AGs,” but it’s funny – most of the projects don’t end up actually deploying AGs. There’s a few barriers to adoption, and even when you’ve built an Availability Group, management can be a little tricky. Don’t get me wrong – I love the feature – but it comes with some surprises.

Rather than me prejudicing you, I’ll just put it out there as a question:

How would you change AlwaysOn Availability Groups?

Leave your answer in the comments. (And yes, Microsoft is watching.) Bonus points if you link to your Connect request.

css.php