Blog

Updated sp_Blitz®, sp_AskBrent® Tutorial Videos [Video]

Every month, tens of thousands of people get our First Responder Kit – a free SQL Server download pack with scripts, e-books, worksheets, and posters. They usually get started by watching our tutorial videos, so we figured it’s time to update ’em.

Here’s how to use sp_Blitz®:

And here’s how to use sp_AskBrent®:

To get ’em, get our First Responder Kit now. Enjoy!

Defragmentation Proven to Completely Fix Performance Issues

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.

Online Index Creation and Cruel Defaults

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]
GO
CREATE NONCLUSTERED INDEX [IX_SOD_SODID]
ON [Sales].[SalesOrderDetail] ([SalesOrderDetailID])

GO
*/

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.

Why You Shouldn’t Upgrade SQL Server

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.

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

Getting BLOBS out of the Database

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.

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.

SQL Server Common Table Expressions

Common table expressions are a feature of SQL that lets a developer create a query that can be referenced multiple times. This feature gives developers another tool to add flexibility or just to simplify code.

Why Common Table Expression?

Why is it called a Common Table Expression (CTE)? Because that’s what the documentation calls it!

Selection_20150225-002

No, seriously, that’s it. Different databases have different terms for this feature. That’s what we call it in SQL Server.

What does a CTE do?

A CTE effectively creates a temporary view that a developer can reference multiple times in the underlying query. You can think of the CTE as if it were an inline view.

Here’s a slight re-write of a StackExchange query to find interesting and unanswered questions.

WITH unanswered AS (
    SELECT p.id
    FROM dbo.Posts p
    WHERE (SELECT COUNT(*)
           FROM dbo.Posts a
           WHERE a.ParentId = p.Id
                 AND a.Score > 0) = 0
          AND CommunityOwnedDate IS NULL
          AND ClosedDate IS NULL
          AND ParentId IS NULL
          AND AcceptedAnswerId IS NULL
)
SELECT TOP 2000 unanswered.Id AS [Post Link] ,
       (sum(t.[Count])
           / 10.0 + us.Reputation
           / 200.0 + p.Score * 100)
       AS Weight
FROM   unanswered
       JOIN dbo.Posts p ON unanswered.Id = p.Id
       JOIN PostTags pt ON pt.PostId = unanswered.Id
       JOIN Users u ON u.Id = p.OwnerUserId
GROUP BY unanswered.Id, u.Reputation, p.Score
ORDER BY Weight DESC ;

The CTE, lines 1 – 12, effectively creates a temporary view that we can use throughout the rest of the query. You can also think of it in the same way that you’d think of a derived table (a join to a subquery).

CTEs make it possible to simplify code, express complex ideas more concisely, or just write code quickly without having to worry as much about structure. They’re a great feature of the SQL language that many people overlook.

If you want to learn more about CTEs, check out our upcoming Advanced Querying and Indexing class in both Chicago and Portland. We’ll cover the basics of CTEs, pitfalls, and performance tuning options.

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.

css.php