Going, Going, Gone: Chicago Performance Class Selling Out

Your application wants to go fast, so I guess that means our tickets go fast.

For our upcoming Chicago training classes in May, the 3-day SQL Server Performance Troubleshooting class is just about sold out. (There’s still about 15 seats left for our How to Be a Senior DBA training class, though.)

If you missed out this round, we’re doing the same classes in Philadelphia this September. Read more about the classes here, or read about how our San Diego attendees liked it earlier this year.

Don’t hold out for additional cities or dates – this is our entire 2014 training calendar. We’re working on our 2015 lineup as we speak, though – let us know what you’re looking for, and you might just influence our training class decisions.

Brent Teaching with Hardware

Brent Teaching with Hardware

Updated “How to Think Like SQL Server” Videos

On my East Coast user group tour last month, I presented my How to Think Like the SQL Server Engine course to a few hundred folks. I’m always trying to learn and adapt my delivery, and I noted a lot of attendee questions this round, so I updated the course.

This is my favorite course I’ve ever done. In about an hour and a half, I cover:

  • Clustered and nonclustered indexes
  • Statistics, and how they influence query plans
  • How sargability isn’t just about indexes
  • T-SQL problems like table variables and implicit conversions
  • How SQL turns pages into storage requests

If you’ve always wanted to get started learning about SQL internals, but you just don’t have the time to read books, this course is the beginning of your journey.

Attendees give it rave reviews, but this one is one of my favorites:

Go check it out and let me know what you think.

Microsoft Cloud Rebranded as Microsoft Pale Blue

It’s time to learn another new set of acronyms.

Effective today, Microsoft’s as-a-service brand is changing names again. As recently as last week, the product’s name had been changed from Windows Azure to Microsoft Azure, but industry observers noted that Microsoft’s web pages actually referred to a different name - Microsoft Cloud.

“Our research found that the primary barrier to adoption was pronounciation,” said an inside source. “No one could say the damn word correctly, and nobody wanted to look stupid, so they just recommended Amazon Web Services instead.”

Thus the new name, Microsoft Cloud – but it ran into more branding problems right away, said the source. “We tried to trademark our virtual machines and databases, but you-know-who had a problem with our names, MC-Compute and MC-Database. People kept calling them McCompute and McDatabase. It probably didn’t help that our combination program was called the Value Menu.”

Enter the New Brand: Microsoft Pale Blue

The new Microsoft Pale Blue logo

The new Microsoft Pale Blue logo

Satya Nadella, Microsoft’s new CEO, picked the name himself. “Microsoft Pale Blue captures the wide-open possibilities of the empty sky. Everybody knows that blue is the best color for logos, so why not take it to the next level? Let’s use the color name as our brand.”

“Microsoft has learned to play to their core strength – product rebranding,” said industry analyst Anita Bath. “Nobody goes through product names like they do. Metro, Vista, Zune, PowerWhatever, Xbone, you name it, this is a company that understands brands are meaningless.”

Nadella has realigned Microsoft’s organizational structure to support the new mission. “Developers are building more and more applications with cloud-based services and Javascript. We have to understand that it’s the right combination for today’s agile startups.” The new Pale Blue & Javascript division will be led by John Whitebread, a developer widely known in the community as the beginning and end of this kind of work.

“We’re also announcing new datacenters – or as we call them, Pale Blue Regions – in China, North Korea, and Iran,” said Microsoft spokesperson Pat McCann. “We don’t believe politics should stop people from having access to the best technology, and we’re committed to aggressively growing our regions. Anytime we see new cloud demand, we’ll open a PBR.”

Today’s announcements did not include any numbers about customers or revenues, however, and questions remain. A few European reporters at today’s announcement asked Nadella if he thought security concerns around Microsoft reading customer data or NSA back doors might be barriers to cloud adoption. Nadella paused for a moment, then said, “No, no way. That can’t be it. It’s gotta be the brand name.”

How to Add Nonclustered Indexes to Clustered Columnstore Indexes

SQL Server 2012 introduced nonclustered columnstore indexes, but I never saw them used in the wild simply because once created, they made the underlying table read-only. Not a lot of folks like read-only tables. (Bad news, by the way – that limitation hasn’t disappeared in 2014.)

SQL Server 2014 brings clustered columnstore indexes, and they’re totally writeable – you can insert, update, or delete into them. They’re best suited for wide data warehouse fact tables that have lots of columns, but your queries might only want a few of those columns, and they might pick any of the columns for filtering. These types of tables are notoriously difficult to index, and columnstore indexes can give you dramatic performance improvements here.

Inventor of The Locke Technique

Inventor of The Locke Technique

Books Online says you’d better be sure your access patterns all benefit from columnstore indexes, because you can’t add any nonclustered indexes to your columnstore tables. The CREATE CLUSTERED COLUMNSTORE INDEX syntax page explains that the clustered columnstore “is the only allowable index on the table,” meaning you can’t add non-clustered indexes.

Or can you?

Allow me to demonstrate what I call the Locke Technique:

CREATE TABLE dbo.SimpleTable(
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

AS SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.SimpleTable;

CREATE UNIQUE CLUSTERED INDEX CLIX_ProductKey ON dbo.vw_SimpleTable (ProductKey);
CREATE NONCLUSTERED INDEX IX_OrderDateKey ON dbo.vw_SimpleTable(OrderDateKey);

Presto – you can have as many nonclustered indexes as you need – and technically, in a strange twist, the clustered index on the view is a nonclustered index on the table as well.

These have the same drawbacks as any other nonclustered index: slower inserts/updates/deletes on the underlying table, more complex query tuning, more space requirements, and so on. They also have even more drawbacks because the schema-binding view means you can’t alter the columnstore table without first dropping the nonclustered indexes and the view.

After You’ve Been a DBA, What Do You Do?

You’ve been managing database servers for a few years.

Now what?

Get more technical – every time a new project comes in, sharpen your skills. Get more involved in failover clustering, storage, virtualization, or the cloud. Write technical presentations to teach your own company’s staff, and then the public.

Business intelligence – if you’re constantly surrounded by valuable data, and you’ve got a creative eye, you can help make sense out of all those letters and numbers. There’s never been a better time to help people get actionable insight out of your tables. It’s a wildly different career path than DBA – it involves more time with people, and less time with maintenance plans.

Consult – get technical enough, and develop a reputation for solving tough problems quickly, and you can change gears entirely. Instead of working for a single company, you can move around from company to company, giving guidance on how to put out more fires faster.

Contract – in some markets, you can keep doing what an employee does, but as an outsider. Consultants tell companies what to do, and contractors do what they’re told. If you enjoy the job duties and don’t really care for the big picture, contracting can be more lucrative.

Management – if you have enough IT pros around you, and you’ve got a knack for people skills, you can really shift gears and manage people instead of servers. Caution, though: this is nothing to do with databases whatsoever. The skills that served you so well troubleshooting a cluster may not help you motivate an employee you don’t like to do a task they hate.

Architecture – your constant interactions with developers teach you more and more about building large systems. Because you’re more senior than other folks in the shop, you end up giving design guidance. Eventually you spend more time doing guidance than managing databases, or they hire a junior DBA in to backfill your production duties.

Stagnate – keep doing exactly the same thing. You’re going to get all of the servers into pretty good shape – not perfect, but good enough that your phone isn’t ringing every night. You’ll have more time to spend playing 2048 at work, but you’re not going to make dramatically more money if you’re not doing dramatically better work.

So what’s your plan?

When Do You Fire the DBA?

I know a lot of DBAs, but it’s really, really rare that I’ve seen one get fired. I don’t think it’s ever happened during one of my consulting engagements, and I’ve seen some incredibly horrific database disasters (not to mention a whole lotta ugly near-misses).

So I asked Twitter:

Not Telling Management About Problems

That’s fair – I can understand if someone’s databases have problems that they don’t know about, because we can’t all know about every risk. But if you do know about a risk and you don’t inform management, that’s bad. Which leads to…

Doing Dirty Deeds

What if someone came to you and offered you fifty thousand bucks for a copy of your company’s database backups? Or more realistically, just asked you to up their login permissions to see more databases as a favor?

The devil doesn’t come wearing red horns and a cape. He comes as a friend who needs a favor and will pay you handsomely for it.

Being Untrainable

Not Having Backups

This one’s interesting because it happens a lot. No, seriously, it’s the first thing I check when I start a consulting engagement, and maybe 1/4 to 1/2 of the time, the backups aren’t working the way the DBA assumed.

After all, think about your own databases – you’re just assuming the backups worked okay last night because you didn’t get an email. If someone walked into your cube right now and checked every database for a clean (non-corrupt) backup that made it to tape or offsite, are you ready to bet your job on it? Especially if other teams are involved in sweeping the backups off to tape?

I’m much more tolerant of this mistake now because I see it so often. DBAs get distracted by performance issues because that’s what users complain about. Nobody complains about missing backups until it’s too late.

Making a Fatal Mistake

I love those last two words from Allan – stated consequences. If the company makes it clear ahead of time that certain mistakes are unforgivable, then yep, that can be a recipe for firing. If you’re in that kind of shop, you’d be wise to print out that list of unforgivable mistakes in a really large font and stick it to your wall near your monitor as a constant reminder.

Not Being Customer-Friendly

Over on DBAreactions, I make a lot of bad jokes about developers, SAN admins, network admins, sysadmins, and your momma.

The reality, though, is that I love these people because they’re struggling just like us DBAs are. They’re working to get better, and I have to help them get better as part of my own struggles. As much as we’d like to think we DBAs know everything about everybody else’s jobs, often our attitudes create a problem that’s a bigger liability than our perceived knowledge.

Not Improving Despite Guidance

Buck sums up the real thing DBAs need to be aware of.

Ideally, you build your own learning plan to up your game and keep your data safeguarded. You sharpen your own knives, and you train for the day that you have to respond to the unthinkable outage.

Less-than-ideally, your managers notice that your knives are those plastic ones you get in coach class on airplanes, and they write you a plan as part of your annual review. You make progress on it with that jump start, and you keep treading water in your career.

Or not-ideally-at-all, you put your company’s data at risk, they take you to Human Resources, and you sign off on a very urgent plan to get your learn on.

So, go get your learn on before the company notices.

T-SQL Tuesday: Stop Tuning with Wait Stats Percentages

TSQL2sDay150x150For this month’s T-SQL Tuesday topic, Michael J. Swart told us to argue against a popular opinion, and man, is that right up my alley. I’ve told you to stop worrying about index fragmentation, stop backing up your logs once an hour, and to stop running $14k of licensing on $3k of hardware.

You’re probably checking SQL Server wait stats periodically to find your SQL Server’s bottleneck – and that’s a good thing. Instead of checking Perfmon counters and trying to figure out what’s the slowest part of your server, at least wait statistics tell you what SQL Server has been waiting on while running queries.

But it all falls apart when you say, “67% of the time, SQL Server is waiting on ____, so we should focus there.”

We need to understand the difference between latency and throughput.

Explaining Latency and Throughput with Shipping Companies

If we run an online store and we wanted to measure how fast a delivery service works, we could call them to pick up a single envelope, and then measure the amount of time it took to arrive at its destination. We could then say, “That service can deliver exactly one package per day. If our business is going to grow, we’re gonna have to find a delivery service that can ship faster, because we need to move more than one package per day.” We could then focus all our efforts trying to use local courier services, or spreading our load across multiple shipping companies.

Ernie arrives at her destination.

Ernie arrives at her destination.

But we would be morons.

Instead, we need to put MORE packages out on our doorstep and call the delivery service to get it. They’ll send a truck, pick up all the packages, and deliver them to various destinations. As we try to ship more and more packages, we’ll probably need to upgrade to a loading dock, or maybe even multiple loading docks, and set up an arrangement with our shipping company to send more trucks simultaneously.

Latency is the length of time it takes to deliver a single package.

Throughput is the number of packages they can deliver per day.

Just because our business is waiting overnight for a single package to be delivered doesn’t mean we have to go finding a new shipping company. It’s completely normal. We need to keep pushing our business to figure out where the breaking point is. Are packages piling up at the door because the shipping company only has a single small cargo van? Sure, that’s the point at which we worry.

How This Relates to SQL Server Wait Stats

In a recent load testing engagement, the DBA told me, “We’ve only got a few end users hitting the system, and we’re already seeing 80-90% of our waits in PAGEIOLATCH. The data file storage simply isn’t able to keep up.”

We switched from using his wait stats script to sp_AskBrent® instead, which shows the cumulative amount of time spent waiting on each wait type. In any given 5-second span, the server was spending less than a second waiting on PAGEIOLATCH. Furthermore, the average wait time was less than 5 milliseconds each time – indicating that the storage was responding fairly quickly to each request.

The server was sitting idle, and the DBA was reading wait stats incorrectly. Sure, the majority of time spent waiting was due to storage, but there just wasn’t much time spent waiting period.

“Crank up the load,” I said. “Quadruple the amount of work you’re throwing at the server.”

Everybody in the room looked at me like I was crazy, but they agreed – and the SQL Server still didn’t flinch. We kept upping and upping the load, and finally we did find a breaking point, but it wasn’t storage. Just as you can pile up a lot of boxes in front of your house and the shipping company will pick them all up to deliver them in the same amount of time, the SQL Server’s storage kept right on delivering every result within 5-6 milliseconds.

The Moral of the Story

When using wait stats for monitoring, make sure you’re looking at the total number of seconds spent waiting per second on the clock. If you sample waits for 5 seconds on a 16-core server, don’t freak out about 5 seconds worth of wait. Each core can have multiple queries piled up, all waiting on different resources, so even 15-20 seconds of wait time during a 5-second period may not indicate problems.

Upcoming (Mostly Free) SQL Server Training Events

Tired of giving your boss dumb looks when she asks you a tough SQL Server question? Let’s fix that – get yourself registered for any of these upcoming events. Most of ‘em are free, and many of ‘em are virtual so you can attend at work:

Kendra Little

Mar 11

How to Get Your First DBA Job

Triage Tuesday 30 minute Webcast starting at 11:30 AM Central
You’d love to become a Database Administrator, but how do you get your foot in the door? Ten years ago, Kendra Little was in your position. Since then, she landed her first job as a SQL Server DBA, worked her way up to Senior DBA, and is now a partner in a database consulting company. Along the way, she’s hired Junior and Senior DBAs and she helps employers develop interview procedures for database administrators. In this 30 minute session you’ll learn what employers look for, steps you can take to make yourself a great candidate, and how to build the right resume to get your first DBA job. Register now.

Brent Ozar

March 11

Your Database Server is a Loser

Sponsored by Dell – 1 Hour Webcast starting at 1PM Central, 2PM Eastern
If your database server goes down at a certain date/time, it’s likely to lose much more data. If you’re responsible for making sure SQL Server doesn’t lose data, you need to understand how your backup schedule really works. You can’t just monitor for backup job success – you have to learn how to query MSDB to check your risk exposure. Microsoft Certified Master Brent Ozar will explain:

  • Where SQL Server stores RPO/RTO data
  • How to query it to find out how much data you’ll lose
  • How to monitor it going forward to protect your job

Register now.

Brent Ozar

March 12

Philadelphia SQL Server User Group:
How to Think Like the SQL Server Engine

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this 90-minute session with Microsoft Certified Master Brent Ozar, you’ll role play as the database engine while Brent gives you queries. You’ll learn first-hand about clustered indexes, nonclustered indexes, execution plans, sargability, statistics, TempDB spills, and T-SQL anti-patterns. Register here.

Brent Ozar

March 13

PASS DC (Chevy Chase) User Group:
How to Think Like the SQL Server Engine

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this 90-minute session with Microsoft Certified Master Brent Ozar, you’ll role play as the database engine while Brent gives you queries. You’ll learn first-hand about clustered indexes, nonclustered indexes, execution plans, sargability, statistics, TempDB spills, and T-SQL anti-patterns. Register here.

Brent Ozar

March 14

Richmond SQL Server User Group:
How to Think Like the SQL Server Engine

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this 90-minute session with Microsoft Certified Master Brent Ozar, you’ll role play as the database engine while Brent gives you queries. You’ll learn first-hand about clustered indexes, nonclustered indexes, execution plans, sargability, statistics, TempDB spills, and T-SQL anti-patterns. Register now.

Jes Schultz Borland

March 18

Don’t Fear the Execution Plan

Triage Tuesday 30 minute Webcast starting at 11:30 AM Central
Have you ever been curious about how SQL Server returns the results of your queries to you? Have you ever opened an execution plan but been bewildered by the results? Have you dabbled in query tuning, but weren’t sure where to focus your efforts? Join Jes as she takes you through the basics of execution plans. She’ll show you how to read them, how to spot common problems, how to spot help, and tools that will make your job easier. Register now.

Brent Ozar

March 25

Get to Know Our Free SQL Server Scripts

Triage Tuesday 30 minute Webcast starting at 11:30 AM Central
You’ve heard about sp_Blitz®, sp_BlitzIndex®, sp_AskBrent®, and our First Responder Kit, but you’ve never gotten around to using them yourself. Brent will demo all of them, show you how to use them effectively, and take your questions and ideas about what you’d like to see added to them. Register now.

Brent Ozar

March 26

How to Get Ready for SQL Server 2014

Dell 1-Hour Webcast starting at 1:00PM Central
Stay one step ahead of the significant changes coming to SQL Server. Even if you’re not using SQL Server 2014 yet — heck, maybe you’re not even using SQL Server 2012 — this webcast is something you shouldn’t miss, whether you’re a DBA or a developer.. If you learn about the new ways SQL Server stores data, you can start making your apps and databases 2014-friendly today. Register now.

Kendra Little

Apr 1

World’s Worst Performance Tuning Techniques

Triage Tuesday 30 minute Webcast starting at 11:30 AM Central
Could your attempts to improve performance be slowing down your SQL Server? Learn which performance tuning techniques have hidden pitfalls in this free 30 minute talk by Microsoft Certified Master Kendra Little. Register now.

Brent Ozar

April 11-12

SQLSaturday #267 Lisbon, Portugal

Brent is flying across the pond to go visit one of the best-regarded SQLSaturdays in Europe. He’s doing:

Virtualization, Storage, and Hardware for SQL Server (Pre-Con) – You’re a DBA who manages SQL Servers, and you’re frustrated with slow performance. The VMware and SAN admins tell you it must be a SQL Server problem, but you’re not so sure. Microsoft Certified Master Brent Ozar went through the same problems, and he decided to go behind enemy lines. He became a SAN admin and VMware admin, learned how to tie these parts together, and figured out how to configure both sides of it to work well. This all-day pre-conference session is 100€. Seats are limited – register now.

How to Think Like the Engine – You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this session with Microsoft Certified Master Brent Ozar, you’ll role play as the database engine while Brent gives you queries. You’ll learn first-hand about clustered indexes, nonclustered indexes, execution plans, sargability, statistics, TempDB spills, and T-SQL anti-patterns. (This is a regular session at SQLSaturday Lisbon.)

Real-Life SQL 2012 and 2014 Availability Group Deployments – You want to deploy AlwaysOn, but you’re concerned about hidden drawbacks, performance impacts, and how it affects development. Brent Ozar has deployed SQL 2012 and SQL 2014 Availability Groups at big web sites including and the Discovery Channel, plus used it to scale out reads at data warehouses. Come learn what he’s found in real-life deployments of AlwaysOn.

Register for SQLSaturday Lisbon.

Brent Ozar Jeremiah Peschka Kendra Little

April 13-16, 2014

SQL Intersection Spring

This spring, you can join us in Orlando, Florida for a pre-con, sessions, and a post-con focused on developer and architect topics:

Pre-Con: Developer’s Guide to SQL Server Operations with Jeremiah Peschka and Kendra Little – You’re a developer who has to administer SQL Servers, but you’ve never had formal training. You’re not entirely sure what’s going on inside this black box, and you need a fast education on how SQL Server works. In one day, you’ll learn how to make your SQL Server faster and more reliable. You’ll leave armed with free scripts to help you find health problems and bottlenecks, the knowledge to avoid common pitfalls, and a plan to get SQL Server under control.

Post-Con: Make SQL Server Apps Go Faster with Brent Ozar, Jeremiah Peschka, and Kendra Little – You’re a developer or DBA 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 just one day, 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.

Conference Sessions:

  • Defining a Data Strategy – Jeremiah – If you’ve worried about outgrowing your current database or wasting countless hours moving to the wrong data platform, listen up. There is an overwhelming array of database options on the market, knowing which to pick is difficult. Before jumping in, it’s important to have a list of questions to make your decision easier. In this session, we’ll cover a set of questions to get your team started in the decision making process. This session is for senior developers and software architects looking to expand their horizons.
  • Developers: Who Needs a DBA? (200) – Brent – You store data in SQL Server, but you don’t have enough work to keep a full-time DBA busy. In just one session, you’ll learn the basics of performance troubleshooting, backup, index tuning, and security. Brent Ozar will teach you the basic care and feeding of a Microsoft SQL Server 2005, 2008, or 2012 instance and give you scripts to keep you out of trouble.
  • Find and Fix Your Worst Query Patterns (300) – Kendra – You’d love to make your queries faster, but you’re not sure what to change. Kendra Little will teach you how to identify bad patterns in the execution plans of the biggest, baddest queries running against your SQL Server. You’ll see Kendra triage bad queries in action and get an easy-to-use reference you can use to diagnose problems in your query execution plans back at work.
  • Hadoop By Example – Jeremiah – By now you’ve probably heard the words “Big Data” and “Hadoop”, but you’re not sure what they mean, much less how to get started. Maybe you’re struggling with storing a lot of data, rapidly processing a huge volume of data, or maybe you’re just curious. There are a bewildering array of options and use cases within the Hadoop ecosystem. Every day I help customers understand their data problems, understand where Hadoop fits into their environment, and determine how they can use Hadoop to solve their problem. This session provides an introduction to the components of Hadoop, a discussion of when it’s appropriate to use Hadoop, and examples to help you get started.
  • How to Attract and Hire Top Talent (200) – KendraYou need to hire a talented SQL Server developer or DBA, but you don’t know a clock hand from a lock escalation. Kendra Little will teach you how to write a job description that attracts the right candidates, and which anti-patterns cause experienced applicants to look elsewhere. You’ll learn how to filter out unqualified candidates–even if you’re not a SQL Server expert–while asking questions that make the best data professionals eager to join your team.
  • How to Tune Queries (300) – Brent – You’re in a dark room with an ugly query. What do you do next? Microsoft Certified Master Brent Ozar shares his time-tested method of tuning queries starting with simplifying, analyzing the execution plan, making index tweaks, and measuring improvements with statistics. You’ll learn a repeatable process you can use to make queries go faster.
  • Increase Application Concurrency with Optimistic Locking (200) – Kendra – You need to scale up a transactional application using SQL Server–without being dragged down by locking and blocking. Kendra Little will teach you how to use optimistic locking to increase throughput while keeping performance fast.
  • Scale Out! Scale Out! An Introduction to Riak – Jeremiah – Developers have a lot of choices when it comes to storing data. In this session, we’ll introduce .NET developers to Riak, a distributed key-value database. Through a combination of concepts and practical examples, attendees will learn when Riak might be appropriate, how to get started with Riak using CorrugatedIron (a full-featured .NET client for Riak), and how to solve data modeling problems they’re likely to encounter. This talk is for developers who are interested in backing their applications with a fault-tolerant, distributed database.
  • Think Like the Engine: SQL Server for Developers (200) – Brent – You’re comfortable writing queries to get the data you need, but you’re much less comfortable trying to design the right indexes for your database server. In this class with Microsoft Certified Master Brent Ozar, you’ll learn how SQL Server uses clustered indexes, nonclustered indexes, and statistics to build execution plans.
  • The Art of Search – Jeremiah – Are you sick of full table scans caused by `LIKE` queries? Are the users clamoring for additional functionality in searches that will bring your database server to its knees? Or, worse, require an upgrade to Enterprise Edition? In session, Jeremiah Peschka will introduce attendees to the SOLR search engine. We’ll look at ways to index and search information, create complex searches, and keep SQL Server performing well. This session is for anyone who is interested in taking their search functionality to a different level.

Register now.

Jes Schultz Borland

April 8

What the Execution Plan Doesn’t Tell You

Triage Tuesday 30 minute Webcast starting at 11:30 AM Central
The execution plan is a gold mine of information about how your query performed. You can tell the steps that were taken, the number of rows that were processed, and even get index or statistics hint. But what isn’t the execution plan telling you? Jes will show things that you might not realize are problems – and how to fix them! Register now.

Doug Lane

April 15

The Developers Guide to Understanding DBAs

Triage Tuesday 30 minute Webcast starting at 11:30 AM Central
Developers, when your DBA asks how much memory your new SQL Server needs, do you know how to answer? Do you worry about the DBA judging your code? In this free 30-minute session, join Doug Lane to learn what really matters to DBAs and what they expect you to know. Register now.

Jeremiah Peschka

April 22

Introduction to Extended Events

Triage Tuesday 30 minute Webcast starting at 11:30 AM Central
Extended Events were introduced with SQL Server 2008R2. Extended Events is a replacement for Server Side Traces and so much more – it’s a lightweight way to look at SQL Server performance, events, deadlocks, locking, blocking, and more. In this webcast, Jeremiah Peschka provides a high-level introduction to Extended Events. You’ll learn about how Extended Events are structured, how to find out what’s available, and where to get started. Register now.

Brent Ozar

April 26

SQLSaturday Chicago

The sessions haven’t been picked yet, but Brent’s fingers are crossed that he’ll be one of the chosen ones. You can register now.

Brent Ozar

June 4

FoxPASS Northeast Wisconsin:
Troubleshooting Shared Storage for DBAs

Your SQL Server’s data lives on the SAN, and you’re not happy about that. All the Perfmon metrics you gather seem to point to a storage problem, but the SAN admin says the storage is sitting around bored, so it must be a SQL Server problem. Brent Ozar feels your pain – years ago, he was a DBA in the same position, so when his SAN admin quit, Brent took the job. In just 90 minutes, he’ll teach you what’s inside the SAN, why multipathing is so important, how to test storage throughput, and why TempDB should be on local SSDs.

The Worst Database User

“Can you tell me a little about this code?” I asked, keeping my best poker face on.

“I’m really proud of it. It gathers a whole bunch of stuff from accounting, human resources, manufacturing, you name it. I want to be able to track how we’re doing over time.” He nearly hovered in his chair with excitement, eagerly pointing out the different places he was gathering data from. “It’s like having my finger on the pulse of what matters most to the company.”

“That sounds really cool. And there’s some really fine-grained stuff in here – it looks like you really are taking the pulse of the company. How often do you run these queries?”

“It depends – some of them every day, some of them every minute because I need better sampling on those.”

I started to ease into the tough questions. “Every minute, wow. Are you concerned about the overhead?”

“No no, they’re really small queries, only took me a few minutes to write. And it’s not like I’m doing SELECT *,” he laughed.

“Oh, good. And did you index the tables involved? Sometimes adding indexes can really help ease the load of frequent queries.”

He paused for a second. “I never thought about that – but I can’t, right? I can’t add indexes on that kind of thing, can I?” Suddenly he realized he was in unsafe territory.

“No, I guess not. I wonder why that is. But who cares - it’s gotta be worth the cost to get such valuable information, right?”

Your job isn't to gather metrics you won't use.

Your job isn’t to gather metrics you won’t use.

“Oh yeah! You bet.”

“So how often do you query that data?”

He paused again. “Well, not very often. I’m pretty busy.”

“Did you query them today?”


“Yesterday, or last week, maybe? Like before you called me in?”

His enthusiasm was gone, and he eyed me with suspicion.

“You’re way too busy to query this data, aren’t you? You’re busy putting out fires, fixing people’s mistakes, and keeping the lights on. You don’t really need this data gathered every minute, across all of your servers, do you?”

See, that user was the DBA, and when I showed him the top resource-using queries in the plan cache, they were all his monitoring queries. Queries that hit tables he couldn’t possibly index, because they weren’t actually tables at all – they were dynamic management views that SQL Server had to compute each time he accessed them. Queries that faithfully stored away data in tables he’d never bother to review.

And now you know the rest of the story.

How to Use Partitioning to Make kCura Relativity Faster

kCura Relativity is an e-discovery program used by law firms to find evidence quickly. I’ve blogged about performance tuning Relativity, and today I’m going to go a little deeper to explain why DBAs have to be aware of Relativity database contents.

In Relativity, every workspace (case) lives in its own SQL Server database. That one database houses:

  • Document metadata – where the document was found, what type of document it is
  • Extracted text from each document – the content of emails, spreadsheets, files
  • Document tagging and highlighting – things the lawyers discovered about the documents and noted for later review
  • Workspace configuration – permissions data about who’s allowed to see what documents
  • Auditing trails – who’s searched for what terms, what documents they’ve looked at, and what changes they made

For performance tuners like me, that last one is kinda interesting. I totally understand that we have to capture every activity in Relativity and log it to a table, but log-sourced data has different performance and recoverability requirements than other e-discovery data.

The AuditRecord table is append-only. We don’t go back and modify AuditRecord data – we just constantly add to it, tacking on data at the end of the table. This means it has different backup requirements – I certainly don’t want to be doing a full backup on this table every day, repeatedly backing up the exact same data over and over and over when it will never change.

It will never get deleted. We have to keep this data throughout the life of the case because the client may need to go back through time to see who did what during the case. This means it will keep growing and growing, making backups tougher over time.

In big cases, it can dominate the database. I’ve seen instances where the AuditRecord table consumed more than half of the database size – meaning in a 1TB database, 500GB of the contents are AuditRecord. This means backups will take twice as long.

In the event of a disaster, I don’t need it right away. In our 1TB workspace example, I would prefer to restore the 500GB workspace data first, let the lawyers in to do document review, and then take my time restoring a separate 500GB AuditRecordArchive database.

Splitting AuditRecord Into an Archive Database

In order to pull this off, I need two separate databases:

  1. Workspace database – the normal EDDS12345 database for workspace 12345. Inside this database, I have all the normal Relativity tables, but only a small AuditRecord table with the current audits – say the last 7 days. As people review documents, I’d log that data into this AuditRecord table.
  2. AuditRecordArchive database – say, EDDS12345_AuditRecordArchive. In here, I have one AuditRecordArchive table that has all of the AuditRecord data more than 1 week old.

Once I’ve set this up, then I need a job that sweeps the EDDS12345.AuditRecord data into the archive database once per week. Immediately after that job finishes, then I do a full backup and DBCC of EDDS12345_AuditRecordArchive – and then I don’t need to back it up again until the next sweep.

If I want to get really fancy, I don’t do daily DBCCs or index maintenance against that AuditRecordArchive database either. If anything goes wrong with it, like database corruption, I just restore to last week’s full backup and I’m off to the races. This means less downtime for database maintenance.

Great News! kCura Supports This

It’s called “partitioning the AuditRecord table”, and your kCura support contacts can walk you through it. It doesn’t involve SQL Server partitioned tables at all – they just call it partitioning because it’s the same basic concept, only done with application-level code.

However, I don’t recommend doing this by default across all your databases. This technique is going to instantly double the number of databases you have and make your management much more complex. However, I do recommend reviewing your largest workspaces to see if AuditRecord is consuming half or more of the database space. If so, consider partitioning their AuditRecord tables to get faster backups, database maintenance jobs, and restores.

At the risk of sounding like a fanboy, this is one of the reasons I love working with the kCura folks. They really care about database performance, they take suggestions like this, and they implement it in a way that makes a real difference for customers.

This is also why database administrators need to:

  1. Understand the real business purpose of the biggest tables in their databases
  2. Build working, productive relationships with their software vendors
  3. Come up with creative approaches to ease SQL Server pains
  4. Help the vendors implement these approaches in software