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


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 and pick out a bug that someone has found. Submit your ideas at

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:


“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;


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


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.


11 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 Enjoy!

4 comments ↑ Back to top
How it feels to say the exact wrong thing.

How it feels to say the exact wrong thing.

It can be a tricky to introduce yourself to people at conferences when you read their blogs and watch their presentations and videos. You feel like you know them, but they don’t know you yet. What the heck do you say? So you take a deep breath, you head on over, and then… you say something really awkward.

I’ve totally been there too, with my foot right in my mouth. I am truly socially awkward, so much so that reviewing posts like this helps me get better at small talk before a big event.

Dodge the Backhanded Compliment

“Your presentations are great, even though ____.”

What it sounds like: “You’re not that good.”

What to say instead: “Your presentation on [folding paper towels] was great.”

It’s OK if you already knew how to fold a paper towel, they won’t assume you’ve never seen one before. Just stop before the “even though” or “but”. Less is more! And trust me, a simple concrete statement explaining that someone’s hard work helped you out will truly mean a lot to them. If you want to follow it up with a question to start a conversation, you can chase it with, “What inspired you to present on that topic?” (If you have real criticism, it’s valuable to share that too– after a few sentences. It’s kinda weird to lead with that.)

Sneak Past that Accidental Brush Off

“I wish I could come to your talk, but _____”

What it sounds like: “I’m not coming to your presentation.”

For newer speakers, it’s disheartening for them to hear this: they just hear “I’m not coming” and they immediately picture having to present to a room full of empty seats. That can have kind of a “sad Eeyore” ring because many speakers fear “what if nobody shows up?” (This is really hard to not say by accident, I’m still training myself out of it.)

What to say instead: “I like your title and abstract for [Cool Story, Bro].” It’s OK, you don’t have to attend. But you also don’t have to explain that you’re not going, unless they specifically ask. If you want to start a longer chat, it’s also great to ask them, “I’m thinking of attending Mladen’s session on [Security for Developers]. Do you know any other good sessions on that topic?” Speakers love to help you figure out what session to attend, even if the session isn’t their own.

Ooops, Did I Just Kinda Call You Ugly?

“You look so much _______  in person!”

What it sounds like: “You look short/dumpy/frumpy/bad in some context.” Even if you’re saying they look great now, this, uh, implies that’s not always the case.

What to say instead: “It’s great to meet you face to face.” Don’t worry, they think it’s great to meet you as well. If you’re in a place where you can have a chat, just ask, “How did you get started [publishing hilarious animated gifs on the internet]?”

Pack Your Bags For A Quick Guilt Trip

“Do you remember me? We met at ________.”

What it sounds like: “It makes me feel bad that you don’t remember me. And this might be a trick question and I’m totally trolling you, you won’t know till you answer.”

Some people are really good with names and faces. Oh, how I envy those people! For the rest of us, we do truly feel guilty if we met you before and you know who we are and we don’t remember your name. The real problem with this is that it’s hard for the conversation to not fall flat after this. It doesn’t go anywhere.

What to say instead: “I think we may have met at [a store selling panty hose in Texas] a few years back. It’s great to see you again!” Just adding a little “maybe” in there automatically puts the other person at ease if they don’t remember the situation for whatever reason.

Boy, My Tribe Sure Is Dumb!

“I love your blog posts. My developers are so dumb though! They always are doing _____.”

What it sounds like: “I don’t have anything nice to say about anyone. And maybe you’re a member of that club.”

I think some folks start like this because it’s a way of saying, “we must have this in common, right?” But it just doesn’t work so well. Leading with a negative statement gives the conversation a weird vibe, and you’re gambling that the person actually agrees with you. Sometimes they don’t!

What to say instead: “I can really relate to that blog post you wrote on [juggling chainsaws].” And if you don’t remember a specific post to talk about, but you can think of a topic, just mention that. Letting people know that you read their work is pretty darn exciting for them, all by itself.


“You talk to ___, a lot right? Where are they?”

What it sounds like: “You’re not that important to me, but your friends are.”

It’s totally normal to talk about what you’ve got in common, but don’t start with the people commonalities. Talk about what’s important to that other person – their work, presentation, family, or even just get coffee.

What to say instead: “What’s the #1 thing on your mind this week?” This lets the person share their excitement with something, and it’s contagious. You might learn something about a fun insider event too!

How to End It, Short And Sweet

Most initial conversations at conferences aren’t very long. You’ve got sessions to go to, there’s tons of people around saying hi to each other. But don’t be afraid to end it: you’ll probably run into one another again soon. Offer to trade business cards! That will help you remember each other– especially if your business card has your picture on it.

Small Talk is Just a Skill

You’ve got to start a conversation somewhere, and at a conference, you start it with small talk. We aren’t all naturally good at it, though.

Know this: even if you do end up with your foot in your mouth, it’s OK. Just smile and keep meeting new people. We’ve all been there, and it’s not nearly as big a deal as it feels like when your face turns red.

8 comments ↑ Back to top

If you’re going to be working with Oracle, you need to be able to get a better handle on what’s going on with the Oracle database. Just like other database platforms, Oracle provides a data dictionary to help users interrogate the database system.

Looking at System Objects

Database administrators can view all of the objects in an Oracle system through the DBA_% prefixed objects.

You can get a list of all available views through the dba_objects system view:

/* There's a gotcha here:
   if you installed Oracle as suggested, you'll be using a
   case sensitive collation. That's not a big deal, just
   don't forget that while you don't need to capitalize object
   names in SQL*Plus, you do need to capitalize the names while
   you're searching.
FROM dba_objects
WHERE object_name LIKE 'DBA_%';

And the results:


Just over 1000 views, eh? That’s a lot of system views. If you just want to examine a list of tables stored in your Oracle database you can use the dba_tables view to take a look. Here we’ll look at the EXAMPLE database schema:

SELECT owner,
FROM   dba_tables
WHERE  tablespace_name = 'EXAMPLE'
ORDER BY owner,
       table_name ;

The curious can use the desc command to get a list of all columns available, either in the dba_tables view, or any of the tables returned by querying dba_tables.

User Objects

A user shouldn’t have access to the DBA_ views. Those are system level views and are best left to people with administrative access to a system. If a user shouldn’t have that level of access, what should they have? Certainly they should have access to their own objects.

Users can view their own data with the USER_ views. There’s a user_objects table that will show information about all objects visible to the current user. If you just want to see your own tables, you can use the user_tables view instead:

SELECT table_name,
FROM   user_tables ;

Of course, users may have access to more than database objects that they own. In these cases, users can use the ALL_ views to see everything that they have access to:

SELECT COUNT(DISTINCT object_name) FROM all_objects
SELECT COUNT(DISTINCT object_name) FROM dba_objects ;

Running this query nets 52,414 rows in all_objects and 54,325 in dba_objects. Clearly there are a few things that I don’t have direct access to, and that’s a good thing.

System Status with V$ Views

Oracle’s V$ views record current database activity. They provide insight into current activity and, in some cases, they also provide insight into historical activity. There are a number of dynamic performance views (Oracle’s term for the V$ views) covering everything from waits to sessions to data access patterns and beyond.

As an example, you can view all sessions on an Oracle database using the v$session view:

SELECT sid, username, machine
FROM v$session
WHERE username IS NOT NULL ;

Oracle has a wait interface, just like SQL Server. Waits are available at either the system or session level. The v$system_event view shows wait information for the life of the Oracle process. The v$session_event view shows total wait time at a session level (what has this process waited on since it started). You can look at currently running (or just finished sessions) using v$session_wait.

Using this, we can look into my session on the system with:

SELECT  wait_class,
FROM    v$session_event
WHERE   wait_class <> 'Idle'
        AND SID = 255 ;


Sample output from the Oracle v$session_event table.

I’m waiting on me

Don’t be afraid to explore on your local installation. There’s no harm in playing around with different Oracle features to determine how they work and what kind of information you can glean from them.

You can also use the GV$ views, thanks to Jeff Smith for pointing out my omission. These are views that are designed for Oracle RAC so you can see the health of every node in the RAC cluster. The upside of this is that you can get a big picture of an entire cluster and then dive into individual nodes using the V$ views on each node. You can even execute queries that use the GV$ views, even if you don’t have RAC, and you’ll be just fine.

A Word of Warning

Be careful with the both the data dictionary and the V$ views – querying certain views may trigger license usage to show up in the dba_feature_usage_statistics view. Before using features like Active Session History or the Automatic Workload Repository, make sure that you have the proper features licensed for your Oracle database. Using these optional features for your own education is fine.

4 comments ↑ Back to top

In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.

The question is, how much work is SQL Server doing when you call a function? If it’s the SELECT clause, is it called once – processing all rows – or once for each row in the result set, regardless if that’s 1 row or 100,00? What if it’s in the WHERE clause?

I’ll let you in on a little secret: if a function is used in the SELECT or WHERE, the function can be called many, many times. If the function is very resource-intensive, it could be causing your query to be very slow – and you would never see the execution of the function within the execution plan of the calling query.

Yep, SQL Server’s execution plans can be a bit vague when it comes to functions – and by “a bit vague”, I mean, “They don’t show up at all”. You need to dig deeper!

I’m going to run a few demos against the AdventureWorks2012 sample database in a SQL Server 2014 instance to show this!

First, I create a scalar-value function that will return the five left-most letters of a LastName.

CREATE FUNCTION [dbo].[ParseLastName](@LastName VARCHAR(50))
-- Returns the 5 left characters of the last name

SET @ret =
LEFT(@LastName, 5)


Then, I create an Extended Events session to track statement completion. (Note: I have only tested this on SQL Server 2014, no lower versions.) (Using SQL Server 2008 R2 or earlier? You could create a server-side trace to capture sp_statement_completed and sql_statement_completed, but it won’t give you some functionality I’ll show later.)

CREATE EVENT SESSION [CaptureFunctionExecutions] ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
ADD EVENT sqlserver.sql_statement_completed(
ADD TARGET package0.ring_buffer


I start the Extended Events session, and then turn on actual execution plans.

I start with a simple query, which returns 19,972 rows.

FROM Person.Person;

The execution plan shows an index scan and has a cost of 0.10451.

function 1

Looking at the details of the index scan, I see Estimated Number of Executions is 1, and Number of Executions is 1.

Let’s look at the same query when it performs the same calculation as the function – LEFT(LastName, 5).

SELECT LastName, LEFT(LastName, 5)
FROM Person.Person; 

There’s now an additional operator – a compute scalar. The cost has risen slightly to 0.106508.

function 2

Now, I will modify the query to call the function from the SELECT clause.

SELECT LastName, dbo.ParseLastName(LastName)
FROM Person.Person; 

Looking at the execution plan, I see an index scan and a compute scalar. The cost is the same as before –  0.106508.

function 3

Expanding the properties for the compute scalar, I see the function, but it says there is only one execution.

function 4

A quick glance at my Extended Events live feed tells a different story.

function 5

If I add grouping by statement, I can see the function was actually executed 19,972 times – once for each row in the result set.

function 6

That’s a lot more work than advertised!

Does the same thing happen if the function is in the WHERE clause?

SELECT FirstName, LastName
FROM Person.Person
WHERE dbo.ParseLastName(LastName) = 'McCar'; 

Two rows are returned. The execution plan now has an index scan, a compute scalar, and a filter. The cost is 0.118091.

function 7

The Extended Events session again shows 19,972 executions – once for each row in the index.

function 8

The data isn’t filtered out until after the function is called, so it is executed once for each row.


These examples prove that whether one or many rows are returned as the query result set, if a function is used in the SELECT or WHERE, the function can be called many, many times. It could be one of the top resource-consuming queries in your server!

How can you see if a function is bringing your server’s performance down? Look at the top queries in your plan cache using our sp_BlitzCache tool, by total CPU and by number of executions, to see if this is happening to you.

13 comments ↑ Back to top

Today at Relativity Fest in Chicago, kCura Relativity 9 introduces the option to move some text storage out of Microsoft SQL Server and into kCura’s new Data Grid, a tool built atop the open source Elasticsearch.

Is kCura abandoning SQL Server? No, but understanding what’s going on will help you be a better database administrator and developer.

kCura’s Challenges with Microsoft SQL Server

To recap some of my past posts on Relativity, it creates a new SQL Server database when one of the end users creates a new workspace and starts loading data. Over the coming weeks, data pours into SQL Server at completely unpredictable rates. We have no idea how many documents are going to be acquired from subpoenaed hard drives, file servers, backup tapes, Facebook messages, you name it. I’ve seen workspaces grow from zero to ten terabytes in a single week, all without the systems administration teams even knowing it’s happening. They ran their weekly backup size report, and surprise, surprise, surprise.


Data streams into Relativity during business hours at the very same time hundreds or thousands of document reviewers are running queries against those very same tables. The entire team is under tight time deadlines, and there’s no way to take databases (let alone servers) offline for loads.

And oh yeah, we’re often contractually bound not to lose any attorney work product whatsoever out of the database.

This is all doable with traditional relational databases, but it ain’t easy. It’s made even tougher by the fact that many Relativity hosting partners are understaffed, many without even a full time database administrator.

How kCura and Jeremiah Planned for Change

For the last couple of years, Andrew Sieja has repeatedly asked me a tough question: “If you were going to redesign Relativity from the ground up, and anything was on the table, what would it look like?” He faced a classic case of Innovator’s Dilemma – his team had built a wildly successful product, but there’s innovation coming from everywhere, and sooner or later somebody was going to beat him to the next level. While SQL Server was getting the job done, alternative data storage platforms beckoned with some really cool advantages, and he needed to take advantage of them before his competitors did.

kCura brought in Jeremiah to work through the options out on the market. There are a gazillion new data storage & search options out there, and some of them claim to do a phenomenal job on absolutely everything. (Hint: they’re usually lying.) Jeremiah helped them prioritize the features they needed most, and then recommended the right fit for them.

The end result is the newly announced kCura Data Grid, an extremely scalable and performant search platform built atop the open source Elasticsearch. You might recognize Elasticsearch from my demos of Opserver, Stack Exchange‘s open source monitoring tool, because Stack also migrated their SQL Server full text search out into Elasticsearch. They’re not alone – Elasticsearch has plenty of high profile case studies.

The Benefits and Risks of Elasticsearch

We typically see 70-90% of a Relativity workspace’s space consumed by extracted text and audit logging, both of which are great fits for Elasticsearch. Pushing that data out of SQL Server potentially means:

  • Reduced storage costs – while SQL Server relies on expensive shared storage (typically $5k-$10k per terabyte), ES achieves redundancy with multiple commodity boxes (typically $1k-$2k per terabyte). This adds up fast for big workspaces.
  • Faster search – ES is mind-numbingly fast. Seriously.
  • Easier scale-out – it’s really, really hard (and expensive) to scale out a single multi-terabyte database across multiple Microsoft SQL Servers when people can create new databases at any time. (It’s even hard enough just to scale a single known database across multiple servers!) It’s easy to add ES replicas for higher performance and availability.

It’s not a silver bullet, and as with any technology change, there are risks and limitations:

  • Security – ES doesn’t have any built in, so kCura had to build their own.
  • Backups – when everything is in one data platform, it’s easy to back up everything at the same moment in time. Split the data, and you run into challenges – but these aren’t really new for Relativity. The databases and the native files couldn’t be backed up to the same point in time either.
  • Management – Relativity hosting partners don’t have ES expertise on staff, and they’ll need training as ES becomes a mission critical part of their infrastructure.

What This Means for SQL Server Developers and DBAs

Microsoft SQL Server is an amazing relational database, a Swiss Army knife of a persistence layer. Sure, it can handle tables and joins, but more than that, it can do things like full text search, spatial data, CLR code execution, and scale-out via multiple methods. You can build a product backed by SQL Server and go a long, long, long way.

I don’t think SQL Server ran out of capabilities here, but kCura needed to plan for orders-of-magnitude growth in storage and search capabilities over the coming years. They grew one hell of a big, powerful business solution with a single database back end, and they’ve got the luxury of a large development staff and a bunch of new data storage options.

Premature optimization is the root of all evil. When you’re building the product you need today, the right database is the one you already know well. As your product grows, keep learning your own database, plus learn the other options out there. The storage and search markets are changing so dramatically every year – don’t make a bet on one today unless you have to, because tomorrow might bring an even better solution for your needs.

For more details about Relativity 9, check out kCura’s Relativity 9 page.

0 comments ↑ Back to top

Tune in here to watch our webcast video for this week! To join our weekly webcast for live Q&A, make sure to watch the video by 12:00 PM EST on Tuesday. Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!

Is your SQL Server wasting memory? Join Kendra to learn how to identify when memory is going to waste, and track down whether it might be due to licensing, schema problems, fragmentation, or something else. Register now.

Looking for the queries from the video?

There are two short queries that check out the sys.dm_resource_governor_workload_groups and sys.dm_os_nodes DMVs. For those, just read up on the topic linked in Books Online and write a very simple select.

The longer query that looks at memory usage by in the buffer pool is a simple adaptation from the Books Online page on sys.dm_os_buffer_descriptors. Check it out and customize it for your own needs!

10 comments ↑ Back to top