Blog

Welcome to sp_BlitzCache™ v2.4. This release brings a few changes and bug fixes.

  • Fixed a logical error in detecting the output table. Thanks to Michael Bluett for pointing that out.
  • Sorting by executions per second finally works. Thanks to Andrew Notarian and Calvin Jones for submitting this week.
  • Added a @query_filter parameter – this allows you to only look at “statements” or “procedures” in the plan cache.
  • A check was added to identify trivial execution plans that have been cached. If you’re seeing a lot of these, you need to fix it.
  • The @reanalyze parameter was added. When set to 0, sp_BlitzCache™ will pull fresh data from the plan cache. When set to 1, though, sp_BlitzCache™ will re-read the results temporary table. This is helpful if you want to save off results in Excel and display results so you can tune queries.
  • Added the ability to see a query’s SET options. This is hidden just to the right of the plan in the results grid.
  • Moved the #procs temp table to a global temp table named ##procs. This shouldn’t be a problem because you’d probably get angry if two DBAs were running this stored procedure at the same time any way.

Download it right now!

Update: Denis Gobo noticed that sp_BlitzCache™ could potential clobber global temp tables. Global temp table names have been updated in sp_BlitzCache™ to avoid this in the future. Make sure you’re using v2.4.1.

2 comments ↑ Back to top
The best poster that isn't hanging on your wall - yet

The best poster that isn’t hanging on your wall – yet

We give away a lot of stuff – scripts, setup checklists, e-books, posters, you name it.

But we kept hearing a theme from folks: “Wow, I’ve seen one of your tools before, but I had no idea there were so many others!” In order to get everything, they had to go all over the place in our site.

To fix that, we’ve got a new easy button: our free SQL Server download pack. Now when you get anything, you’ll get everything in a single zip file, plus get email notifications whenever there’s a new version.

Enjoy, and hope we make your job suck just a little less.

0 comments ↑ Back to top
Jeremiah-Caffeinates-TSQL

Jeremiah has coffee at the PASS Summit

We’re a group of specialists and teachers here at Brent Ozar Unlimited. But we didn’t start that way– we started out as engineers and developers.

So what was it that changed our career trajectories? For each of us, we had a lot of smaller factors along the way, but there have been one or two big game changers that have really made a difference.

Jeremiah: The PASS Summit

The first time I went to the PASS Summit, I attended at my own expense and used up half of my vacation time to attend. I’d been involved in the SQL Server community for a few short months, and I had been chatting via email with a goofy DBA named Brent Ozar.

Brent and I both attended pre-conference sessions, but we were in different ones. It happened that our speakers took breaks at same time, and I ended up running into Brent over on one of the breaks. We chatted and ended up eating lunch together.

I’m a shy person and a big conference can be daunting when you don’t know anybody, so I ended up tagging along with Brent throughout a lot of the conference. The upside of hanging out with Brent is that he’ll talk to anybody – speakers, Microsoft employees, or random attendees. The PASS Summit was my gateway into both the SQL Server community – I made a lot of friends that week – and into my current career.The connections I made at the PASS Summit that year planted the seeds for the rest of my career.

Kendra: Twitter

Someone helped me discover the typo and find it.

Kendra’s first tweet. #SQLHELP didn’t exist yet.

I never would have guessed that Twitter would change my life. And I’ve never been passionate or crazy about Twitter – I think it’s useful, but if I go a day or two without using it, I don’t mind.

But Twitter is the way that I started connecting to the larger SQL Server community. I started following people, then following their friends, and started tweeting about SQL Server. I asked and answered questions. Twitter gave me a sense of what other SQL Server developers and DBAs cared about, what problems they faced, and what they were interested in.

I’m a pretty naturally shy person, and I never knew what to talk to people about at conferences and events when I first met them. Twitter has helped me solve that problem: you can sense the mood of a conference while you’re there. You can tweet about it. You can talk about the tweets with people!

To get started with Twitter, check out our free eBook.

Jes: SQL Saturday

Jes at SQLSaturday Chicago

Jes at SQLSaturday Chicago

Specifically SQL Saturday Chicago 2010 – my first! I’d been attending my “local” user group for a few months, I’d been reading blogs, I was participating in forums, and I was even on Twitter before I went to this event. I knew I loved working with SQL Server, and I knew there were people that could help me when I had questions. But getting to attend an event – for free, nonetheless! – and getting to meet these people was amazing.

I remember attending a session by Brad McGeehee, who’d written “Brad’s Sure DBA Checklist”, which I kept in a binder at my desk. I remember getting to watch Chuck Heinzelman and Michael Steineke set up a cluster and test it. I even went to a session by one Brent Ozar about a script called sp_Blitz. At the end of the day, with a full brain, I went to the lobby and saw a group of the presenters sitting around chatting. I bravely approached and joined the circle, which included Brent and Jeremiah, and chatted with them. Had I not attended that event, had I not made the connections I did, I doubt I would be where I am in my career today!

Look for a SQL Saturday near you!

DOUG: SQL CRUISE

Dinner with Vikings

Doug as his true viking self

I’d recently presented at my first two SQL Saturdays when the opportunity came to go on SQL Cruise. A contest that for some cosmic reason I felt was begging me to enter — send in your story of victory with SQL Server and win a free spot on the cruise. I made a video about refactoring bad code with a magical hammer, and won the contest. But that was just the beginning.

During the cruise, I:

  • Had a dream about a murder mystery happening on the cruise, which led me to write and present my SQL Server Murder Mystery Hour session.
  • Got some phenomenal career advice from people like Brent, Tim Ford, and Buck Woody, that is still helping me to this day.
  • Became friends with the three people who ended up hiring me three years later for the best job I have ever had.

That’s my career adventure. What will yours be?

Brent: Meeting “Real” DBAs

In My Native Habitat

The DBAs told Brent this hat would make his code better.

I was a lead developer at a growing software business and I felt like I had no idea what was going on inside the database. Our company suddenly grew large enough to hire two “real” database people – Charaka and V. They were both incredibly friendly, fun, and helpful.

I was hooked. Who were these cool people? Where did they come from? How did they learn to do this stuff?

They both took time out of their busy jobs to answer whatever questions I had, and they never made me feel stupid. They loved sharing their knowledge. They weren’t paranoid about me taking their jobs or stealing their secrets – and looking back, they probably just wanted me to be a better developer.

They insisted that sure, I had what it took to be a database admin. I went for it, and it’s been a rocket ship ever since.

2 comments ↑ Back to top

Every year, when I attend the PASS Summit conference, I liveblog the announcements. Let’s take a quick walk down memory lane:

PASS 2013 keynote announcements:

  • SQL Server 2014 CTP2, the last preview before the official release
  • You could deploy in-memory OLTP (Hekaton) without rebuilding your application (which just simply wasn’t true)
  • AlwaysOn AG secondaries in Windows Azure
  • Backup to Windows Azure
  • Data files in Windows Azure
  • Office365 PowerQuery – the ability to use plain English queries against your data

PASS 2012 keynote announcements:

  • In-memory OLTP (Hekaton) feature
  • Hadoop integration coming at some point
  • SQL 2012 Parallel Data Warehouse
  • HDInsight and PolyBase

So, looking back – which of these features are you using?

Yeah, me too. And at first, I thought that sucked.

But I’ve Been Doing Keynotes Wrong.

Photo Credit: Alden Jewell

Photo Credit: Alden Jewell

In preparation for this year’s keynotes, I looked back and got really frustrated. Every year, new features were trotted out, and I got really frustrated because they didn’t make any real-world sense. I blogged angrily about how these features wouldn’t ever catch on with the mainstream.

But Microsoft is playing a different game.

Microsoft is treating keynotes the way car manufacturers treated car shows back in the 1950s-1960s. It wasn’t about showing people what they’d be buying in the showroom next year – it was about a race to the top of technology, showing off that your brand was thinking farther ahead than anybody else.

Nobody’s actually putting their on-premise SQL Server’s data files in Windows Azure. It’s surreal, the same way flying cars are surreal. But who cares? This year, I’m embracing the wild and the fantastic. Bring on the turbine-powered databases that don’t even allow me to change indexes. Show me iPad-based BI that will probably never ship. For one keynote, I’m going to suspend disbelief and just enjoy the make-believe.

And I’ll be tweeting my reactions from @DBAreactions.

2 comments ↑ Back to top

If you’re going to a SQL conference for the first time, join us to learn how things work. We’ll share what happens when you walk in the door for the first time, where to go after hours, what to bring, and what NOT to bring.

3 comments ↑ Back to top

We’ve added two new one-day pre-conference classes to our 2015 calendar:

sqlsat364_web

SQLSaturday Boston Pre-Con: Developer’s Guide to SQL Server Performance – You’re stuck with a database server that’s not going fast enough. You’ve got a hunch that the biggest bottleneck is inside the database server somewhere, but where? In this one-day class, you’ll learn how to use powerful scripts to identify which queries are killing your server, what parts of the database server are holding you back, how to tackle indexing improvements, and how to identify query anti-patterns. The class is just $99 – learn more now.

SQLRally Nordic Copenhagen: Performance Tuning When You Can’t Fix Queries – Brent is flying over to Rally again! Here’s the abstract: 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. Brent Ozar does this every week, and he’ll share his proven methodologies to performance tune with indexes, SQL Server configuration switches, and hardware. Learn more now.

0 comments ↑ Back to top

So you’ve got a great idea for a new feature to add to sp_BlitzSomethingOrOther, what’s the best way to get started?

The Documentation Says…

If you read our code contribution guidelines, you should write new code, write a test, sign an agreement, and then send us your code. That’s technically correct, but it’s a daunting task. After all – five people are going to be looking at your code and then thousands more might be looking at your code.

The documentation is technically correct (like most documentation), but it assumes a lot.

If you go by what the documentation suggests, we’ll definitely see your code, but there’s a decent chance that we’re not going to accept your code contribution.

Start Small

The best way to get started on any existing project is to start small. It’s rare to write a major feature on your first contribution to any new project or product – there’s a significant barrier to entry around code formatting and style, future features, and work in progress.

The best way to help out is to fix a bug in the code.

Right now you’re saying, “Fix someone else’s bugs? No way!”

Hear me out.

When you find and fix a bug in the code, you’re signaling a few things. The first thing you signal is that you have a better eye for detail than the moron who wrote the code. The second thing you signal is that you want to help that moron make their software a little bit better.

Build Trust and Understanding

Contributing small fixes to an existing code base goes a long way to establishing trust. It’s one of the ways that we all work together and bring new employees up to speed with our tools. We don’t throw each other into the deep end (much). Instead we get familiar with our own software tooling by looking for issues and fixing them. We build up trust in each other as we’re building up knowledge.

By fixing bugs, you’re building trust and establishing a working knowledge around a particular code base.

Beyond building trust, you’re also getting an understanding of how a particular piece of code is put together. As an example, sp_BlitzCache is riddled with dynamic SQL, XQuery, XPath, and strange DMV ordering. It’s all done for a reason, and that reason is performance. A few changes would take sp_BlitzCache from finishing in 30-90 seconds to finishing in 30-90 minutes – I should know, I’ve introduced those changes before.

As you’re in the code fixing bugs, you’ll spot places to add more features and functionality. This is a great place to strike up a conversation with the authors about adding those new features, or at least getting them on a roadmap.

Sometimes, we’re already working on a feature but we haven’t made anything about it public yet. You don’t want to spend hours writing a new feature only to see it come out in a completely different format. Building up that relationship of trust means we’ll be chatting with you about our ideas and you’ll be aware of our crazy ideas as they happen.

Code review is hard!

Code review is hard!

…But Test First

The best reason to start out by fixing bugs is that we have a very strange test set up. By testing your changes the same way we test our changes, you can rest assured that your changes will be accepted on their merit, and not rejected on a technicality.

We test our code changes on multiple versions of SQL Server and we use case sensitive instances. A simple mistake in a column name can stop a query from running, for some users, and we’d rather be safe than sorry.

Too Long; Didn’t Read

In short, the best way to get started contributing to sp_BlitzWhatever is:

  1. Find a bug.
  2. Fix the bug.
  3. Submit your fixes.
  4. Rinse. Repeat.
  5. Work up to implementing bigger fixes & features.

Get started today, head over to http://support.brentozar.com and pick out a bug that someone has found. Submit your ideas at http://brentozar.com/contributing-code.

2 comments ↑ Back to top

I love free tools. I also love analyzing SQL Server’s wait statistics. But I’m not a fan of Activity Monitor, a free tool in SQL Server Management studio, which helps you look at wait stats.

Activity Monitor just doesn’t give you the whole truth.

I fired up a workload with HammerDB against a test SQL Server 2014 instance. My workload runs a query that’s very intensive against tempdb, and it’s really beating the SQL Server up by querying it continuously on seven threads.

Let’s look at our wait statistics. Here’s what Activity Monitor shows in SQL Server 2014:

ActivityMonitorSQLServer

“Buffer Latch” must be my problem.

Here’s what our free procedure, sp_AskBrent shows for a 10 second sample while the workload is running. I ran: exec sp_AskBrent @ExpertMode=1, @Seconds=10;

sp_AskBrent

Hmmmm, that top wait type doesn’t seem like it was in the first screenshot at all.

Here’s what Adam Machanic‘s free procedure, sp_WhoIsActive, shows for an instant in time during the workload. I ran: exec sp_WhoIsActive

spWhoIsActive

Here I get specifics on exactly what type of wait is is and the related query.

Let’s compare.

Activity monitor groups wait types. It took a whole lot of waits and rolled them up into ‘Buffer Latch’. This isn’t necessarily a bad thing, but I’ve never heard of documentation that explains what’s rolled up into which groups. By comparison, sp_AskBrent showed me the specific wait types PAGELATCH_UP, PAGELATCH_SH, and PAGELATCH_EX, with the amounts for each one. sp_WhoIsActive even showed me the type of page that is having the bottleneck (GAM) and the database and data file (tempdb’s data file 1).

Activity monitor leaves out wait types. sp_AskBrent showed that in aggregate, my #1 wait was CXPACKET over the sample. That tells me that a lot of my queries are going parallel. That’s not necessarily a bad thing, but it’s important for me to know that about my workload at the time. It helps me know that I want to learn more, and make sure that the right queries are going parallel. (In this case, the query that’s going parallel is pretty cheap, and is just as fast single threaded. My throughput goes up dramatically if I adjust the Cost Threshold setting up a bit.)

Friends don’t let friends use Activity Monitor. It may be convenient, but it doesn’t tell you the truth. Do yourself a favor and use a free tool that gives you wait statistics straight up.

 

12 comments ↑ Back to top

Your users want Relativity to be up at all times. What’s the first step? How much work is involved? What will it all cost? I’ll give you a simple worksheet to get management and the IT team on the same page, and then show you how to turn those specs into a rough project plan and budget.

You’ll learn how to choose between different high-availability methods, and understand why clustering is such a no-brainer choice in this 22-minute video.

Got questions? Join us for our Tuesday Q&A webcast where I’ll answer your Relativity questions.

0 comments ↑ Back to top

The latest version of our free SQL Server health check adds some nifty new stuff:

  • Checks for non-default database configurations like enabling forced parameterization or delayed durability
  • Looks in the default trace for long file growths or serious errors like memory dumps
  • Checks Hekaton memory use and transaction errors
  • Warns about database files on network shares or Azure storage
  • Added the server name in the output if you enable @CheckServerInfo = 1
  • Discontinued the Windows app version (was prohibitively expensive to get it into the Windows app store)
  • And miscellaneous bug fixes and improvements

Get the latest version in our free download pack, and if you’ve got questions, hit up support.brentozar.com. Enjoy!

10 comments ↑ Back to top
css.php