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?

Finding One Problem Query With Extended Events

It’s easy to find problem queries on your SQL Server after they’ve happened. You can open your favorite monitoring tool or look in the plan cache. Those options are great for finding things after they’ve happened, but what if you want to find problems as they happen?

Server Side Trace

You could set up a SQL Server Server Side Trace. This is an acceptable solution if you’re using SQL Server 2008 or earlier. There are a few problems with this approach.

Problem the first – the SQL Server Profiler UI is less than beautiful. The GUI interface is somewhat cumbersome and the scripting interface is hilariously bad. To make matters worse – to only capture a single query requires string based filtering. There’s a plan handle property available in profiler, but it’s not available for TSQL or stored procedures.

Something appears to be missing

Something appears to be missing

As much as I like string matching in T-SQL (hint: I don’t), I think it’s fair to say that Profiler/Server Side Trace are best avoided unless you’re using SQL Server 2008 or earlier.

Extended Events

Extended events are nowhere near as hard as you’d think. Trust me on this. How do I know? I set up an Extended Events session to grab a particular query.

Here’s what you do:

  1. Create a crappy stored procedure. Use your imagination.
  2. Run the stored procedure a lot.
  3. Grab the query_hash for that stored procedure. The easiest way
    I’ve found to do this is to use our plan cache query to identify it. My query_hash is 0x2B42702322C10821. What’s yours?
  4. Create an extended events session to capture that specific query hash.

Step 4 isn’t as easy as it sounds. Astute readers will note that I never said this was easy, just that it’s not as hard as you would think.

Even though the query_hash is stored as a hexadecimal in SQL Server, Extended Events would prefer to see an unsigned 64-bit integer. To get this magic number, take your query_hash and cast it as either a BIGINT or a DECIMAL(20,0):

SELECT CAST(0x2B42702322C10821 AS DECIMAL(20,0));
/* my magic number is 3117177188303046689. */

Once you convert the query_hash to a magic_number, create your Extended Events session. I chose to capture the sp_statement_completed and sql_statement_completed events and a number of server level events like time, app name, and client host name. Here’s what it would look like capturing this to a file:

ADD EVENT sqlserver.sp_statement_completed(
    WHERE ([sqlserver].[query_hash]=(3117177188303046689.))),
ADD EVENT sqlserver.sql_statement_completed(
    WHERE ([sqlserver].[query_hash]=(3117177188303046689.)))
ADD TARGET package0.asynchronous_file_target
(SET filename = 'C:\temp\XEventSessions\query_hash.xel',
     metadatafile = 'C:\temp\XEventSessions\query_hash.xem',

Once you have that up and running, you should be able to start the session, if it isn’t already started, by running:


Just like that, you’ll be capturing your terrible queries to disk where you can mine the extended events files for gold and glory at your own convenience. If you want to query it, it’d look something like this:

WITH events_cte AS (
            'datetime2')) AS [event time] ,
        xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
          AS [client app name],
        xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
          AS [client host name],
        xevents.event_data.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(max)')
          AS [database name],
        xevents.event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') 
          AS [duration (ms)],
        xevents.event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') 
          AS [cpu time (ms)],
        xevents.event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS [logical reads],
        xevents.event_data.value('(event/data[@name="row_count"]/value)[1]', 'bigint')  AS [row count]
    FROM sys.fn_xe_file_target_read_file
          null, null) 
    CROSS APPLY (select CAST(event_data as XML) as event_data) as xevents
FROM events_cte
ORDER BY [event time] DESC;

Cleaning Up After Yourself

Once you’re done watching a specific query or queries, make sure you clean up after yourself. There’s no reason to add extra load to SQL Server when you aren’t watching. Make sure to stop and remove your Extended Events session:

/* Stop the Extended Events session */
/* Remove the session from the server.
   This step is optional - I clear them out on my dev SQL Server
   because I'm constantly doing stupid things to my dev SQL Server. */

Augment Your Techniques

This won’t replace any other techniques that you’re using to find poorly performing queries. However, you can use this technique to find problem queries as they’re happening.

Different monitoring techniques provide you with a different view of the data. Our plan cache query shows you a historical record of what’s been going on, but it can only show so much. If a query has been run 14,400 times over the last 24 hours, does that mean it’s been run 10 times a minute for that entire period or is there a period of activity where that query gets run 1,440 times a minute for 10 minutes straight?

This isn’t going to replace the need for other tools you have at your disposal. It’s just another way to get the job done.

Rollback: What happens when you KILL a session?

It’s Friday afternoon. You, the DBA, are looking forward to a long weekend. You’re going to walk out the door in 30 minutes and enjoy some family time. The phone rings and you pick it up. On the other end, Joe Developer says, “There’s a query that’s been running for a long time. Can you kill it?”

You log into SSMS and use sp_who2 to confirm that the user has an open transaction. You issue the (infamous) KILL command.

Twenty-five minutes later, you’re still sitting there, waiting for the transaction to roll back. You’re sweating. You really want to leave for the weekend. Another ten minutes pass, and finally, it completes.

What happened? Why did SQL Server take so long to kill the process? What happens in a rollback situation?

What Happens in a Transaction

When a transaction is being committed, each step is written into the transaction log. Each value that is being inserted, updated, or deleted is recorded so that if there is a failure before the log is written to the data file, the appropriate rollback or roll forward can take place. The transaction may take and release locks on tables or indexes.

Want to see this in action? You can use the undocumented fn_dblog command to view the entries in the log.

What Happens in a Rollback

When we issue a KILL, every step of the transaction that has been written to the transaction log must be undone. The object the transaction was changing must be put back to the state it was in prior to the change. Was a record inserted? It needs to be deleted. Was a record updated? The previous value must be restored. Was a record deleted? It needs to be added back in. You can use fn_dblog to see this in action, too.

If SQL Server is simply undoing what was once done, why does it sometimes take longer?

If the transaction had to take a lock on a row or table to perform the operation, it will have to take that lock again – and other processes might be using that object now. Also, consider that a rollback is mostly single-threaded. If the transaction ran using four cores initially, and the rollback is now only using one, it will take more time.

Try going down backwards, hopping on one foot. I dare you!

Try going down backwards, hopping on one foot. I dare you!

Imagine this: you’ve decided to climb a tower with 10,000 stairs. You get to stair 9,999 and decide you don’t want to complete the climb. You can’t wish yourself to the bottom step – you have to go back down. But now, you’re tired – and have to do this single-threaded. Imagine having to hop down the stairs backwards, on one foot.

Tracking Progress

You are able to track the progress of a rollback. After you issue a KILL command, run KILL [Session ID] WITH STATUSONLY. This will show you approximately how much time is remaining in the rollback – but other processes in the database could affect that.. Consider the information here a guideline, not a hard-and-fast time.

Another thing to keep in mind: what you might not have known when you issued that KILL on Friday at 4:30 pm is that the transaction was going to finish at 4:32 pm. But, you had to wait until 5:05 for the rollback to complete. Always be cautious when using this command!

Solving the root cause

A one-time KILL issued to solve an “oops” may not need further investigation. But if you find yourself having to issue these commands for the same application, at the same time each week, it’s time to dig deeper. You want to work with the owners of the application or process to determine why this keeps happening, repeatedly. If it’s a long-standing process or transaction, what’s changed to make it take so long now? Perhaps you’re doing a mass insert or update, and could break it into chunks. If it’s a new process, look at what else is going on at that time. Could the statements be written more efficiently? Could it be scheduled at another day or time?

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.

Extended Events – It Doesn’t Have To Be Hard

A lot of folks would have you think that Extended Events need to be complicated and involve copious amounts of XML shredding and throwing things across the office. I’m here to tell you that it doesn’t have to be so bad.

Collecting Blocked Process Reports and Deadlocks Using Extended Events

When you want to find blocking, you probably turn to the blocked process report. You mess around with profiler on your SQL Server 2012 box. You probably feel a little bit dirty for clunking around in that old interface, but it gets the job done.

There’s a better way… Well, there is at least a less awful way: Extended Events.

ADD EVENT sqlserver.blocked_process_report(
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',

/* Make sure this path exists before you start the trace! */

With that, you’ve created an Extended Events session to grab blocked processes and deadlocks. Why both? The blocked process report makes use of the deadlock detector. Since large amounts of blocking are frequently synonymous with deadlocking, it makes sense to grab both at the same time. There are a few other things we’ll need to do to make sure you can collect blocked processes:

EXEC sp_configure ‘show advanced options’, 1 ;
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
/* Start the Extended Events session */

At this point, you’ll be collecting the blocked process report with Extended Events. There’s no profiler session to set up, just start and stop the Extended Event session at your leisure.

Reading the Block Process Report from Extended Events

We’re saving the blocked process report to disk using Extended Events. Now what?

We need to get that blocked process data out of the Extended Events files and somewhere that we can better analyze it.

WITH events_cte AS (
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
      AS blocked_process_report,
      AS deadlock_graph
  FROM    sys.fn_xe_file_target_read_file
     null, null) 
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL 
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name] 
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  [duration (ms)],
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL 
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;

In this query, you read from an Extended Events session that’s being saved to disk and perform XML shredding to get client information. It isn’t a pretty query, but it does the job very well.

Viewing the Extended Events Deadlock Graphs

Extended Events deadlock graphs use a slightly different XML schema than what SSMS expects. You should see an error along the lines of “There is an error in XML document”. For folks using SQL Server 2012 and earlier, you can either parse the XML by hand or use SQL Sentry Plan Explorer.

Viewing the Extended Events Blocked Process Report

But what about the blocked process report? After all, your users are complaining about blocking, right?

Michael J. Swart has created tools to view the blocked process report. It’d be awesome if you could use it, but Michael’s blocked process report viewer uses the output of a server side trace to read blocking information. These Extended Events files are different enough that you can’t use them outright. You can, however, create a table that will let you use the blocked process report viewer:

    EndTime DATETIME,
    TextData XML,
    EventClass INT DEFAULT(137)

WITH events_cte AS (
           'datetime2')) AS [event_time] ,
            AS blocked_process_report
    FROM    sys.fn_xe_file_target_read_file
         null, null) 
        CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
INSERT INTO bpr (EndTime, TextData)
FROM events_cte
WHERE blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NOT NULL 
ORDER BY [event_time] DESC ;

EXEC sp_blocked_process_report_viewer @Trace='bpr', @Type='TABLE';

While you still have to read the XML yourself, this will give you a view into how deep the blocking hierarchies can go. Collecting this data with Extended Events mean that you won’t have to sit at your desk, running queries, and waiting for blocking occur.

Extended Events – Not That Hard

Extended Events aren’t difficult to use. They provide a wealth of information about SQL Server and make it easier to collect information from complex or difficult to diagnose scenarios. You really can collect as much or as little information as you want from SQL Server. When you get started, the vast majority of your work will be spent either looking up Extended Events to use or formatting the output of the queries into something meaningful.

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.

Back Up Transaction Logs Every Minute. Yes, Really.

Right now, your transaction log backups are set up to run every 15 minutes, every hour, or maybe every few hours.

It’s time for a reality check. Figure out what internal customer owns the data inside the database, and walk into their office. Ask them how much data they’re comfortable losing.

You’re probably going to have to repeat the question a couple of times because they won’t believe these words are coming out of your mouth. After they eventually recover, they’ll blurt out words like “NONE” and “ZERO” and “ZOMG”. Give them a quick overview of how much it costs to have a no-data-loss system – typically in the millions of dollars – and then ask for another answer.

They’re going to ask for a list of options and their costs. Forget the fine-grained details for now – let’s just assume one of your solutions involves transaction log backups.

The big hand is on the network cable, and the little hand is on the power cable.

The big hand is on the network cable, and the little hand is on the power cable.

Is it more expensive to run your log backups:

  • A – Every hour
  • B – Every 15 minutes
  • C – Every 5 minutes
  • D – Every minute

It’s a trick question – they all cost the same.


Nope – it actually means less overhead. To keep the numbers easy, let’s say you generate 60GB of transaction log data per hour. Would you rather back up 1GB per minute in small chunks, or kick the server in the junk every hour trying to back up 60GB all at once? The users would definitely notice the latter – the server will slow to a crawl while it handles that firehose of log data for a sustained period.


Yes, but if you think you’re really going to manually restore log backups through the GUI, one at a time, you haven’t been through a real mission-critical crash. Long before disaster strikes, you need to be familiar with a script-based method to automatically restore all of the log files in a directory. Test it, get comfortable with it, and then when the outage hits, you can let your script restore everything ASAP, no clicks required.


If you have too many databases and your jobs can’t keep up, it’s time to start breaking up the log backups into multiple jobs. Some of my clients use two jobs, for example – one to back up all odd-numbered databases by the database ID in sys.databases, and another one to back up all the evens. The log backups don’t stress out their servers by any means, and users don’t notice the impact of two simultaneous log backups in two different databases.


I’m just as serious as you were when you randomly picked 15 minutes or 1 hour as the log backup schedule. The point is that it’s not for the geeks to decide – it’s for the customer to decide. Ask your business user about the business needs for their data, and then give them a menu of options to meet that goal.

You think they’re all going to insist that all data is mission-critical, but you’d be surprised. Once they see the costs involved with log shipping, AlwaysOn Availability Groups, SAN replication, and so forth, they’re able to make better informed decisions about which data really is priceless, and which data we could really re-enter at minimal cost.

But until you ask them this question, they assume you’re never going to lose data.

San Diego Class Recap

We had a lot of fun this month teaching SQL Server classes at the San Diego Harbor Hilton. (We wrote about how we picked the cities earlier.) Even when we teach, we learn, and then we share what we learned, so here we are.

San Diego Harbor Hilton

San Diego Harbor Hilton

We like to try new things with our products and services, and this year’s classes brought a few firsts for us. Let’s recap what’s new and how it worked.

We tried new class titles and module groupings. This year, we’re running a 2-day class on How to Be a Senior DBA and a 3-day class on SQL Server Performance Troubleshooting. We wanted to find out if we’d gotten the module mixes right.

To learn, we surveyed the bejeezus out of the attendees at the start. How did they rate their skills at each module before we started, and which modules were they looking forward to the most? Then at lunchtime and end-of-day, we gave them more surveys to find out if they would recommend each module to others.

We learned right from the first day – it was obvious from the looking-forward-to question that the attendees weren’t interested in one particular module, so we gave them the option to switch it out for another module instead. That went really well, and we’ll adapt the Chicago and Philadelphia itineraries to include that change by default.

Be Your Neighbor's Consultant

Be Your Neighbor’s Consultant

We added a lot of attendee interaction. One of the best ways to learn something is to teach it to someone else. In the high availability architecture module, we ran a 5-minute portion where you had to describe your app’s RPO/RTO needs to the person sitting next to you, and they had to recommend the right HA/DR infrastructure.

Great side effect: this got the attendees out of their shell right from the start! Before the very first bio break, each attendee knew at least one other person’s SQL Server challenges and felt comfortable talking about it.

Kendra Discusses the Outage Post-Mortem

Kendra Discusses the Outage Post-Mortem

We added optional after-hours group lab exercises. For example, on the first night of the How to Be a Senior DBA class, we broke attendees up into teams and handed them a production outage to fix. We watched via the projector as each team worked on their servers.

After the outage finished, the team conducted a post-mortem meeting just as they would (maybe?) in real-life, explaining what caused the outage, how they fixed it, and how they would ensure it didn’t happen again. Prizes went to the best solvers and best explainers.

Jeremiah Teaching

Jeremiah Teaching

We used attendee questions to improve the training. While the training takes place, one of us sits in the back of the room and transcribes every attendee question.

Now that the training is over, we’ll revisit the slides and demos to see where we can build those answers right into the training to keep making each delivery better. (We do this at our conference sessions, user group sessions, and webcasts too.)

Granted, we can’t just add material like crazy – otherwise we’d run way long every day – but sometimes it makes sense to take out portions of training material that isn’t working well, add in other portions, or reorder slides to introduce answers in a way that makes more sense for attendees. We have some slide decks we’ve been working on for several years, and gradually improving this way the whole time.

Brent Teaching with Hardware

Brent Teaching with Hardware

We added hands-on hardware for illustration. One of my favorite teaching icons says that if you want to teach students an abstract topic, give them something to put their hands on. I brought motherboards, memory, hard drives, and cables in to teach about how wait stats work, and as various queries ran onscreen, we talked about the physical holdup.

This class was the first time I’d tried this approach in person, and this is a great example of a session I’ll need to tweak over time. The time budgeting failed miserably – I didn’t get to go as deep as I wanted because I tried to cover too many things in too short of time. Now the fun part starts – I get to revisit the presentation, the attendee questions, and the subject matter, and figure out how I can impart maximum learning in 60 minutes.

Kendra Teaching

Kendra Teaching

We added prerequisite training videos and resources. To make sure everybody was on the same page when class started, each attendee got homework when they enrolled. They got a login for our training video system that gave them access to a group of videos we picked just for them.

When the in-person class started, their training login suddenly had access to all of the scripts and resources for the class, too. We use that same delivery system to handle all of our resources, and we love how it works.

We even extended it based on what we learned in the first class. One of the Senior DBA students suggested that we compile a list of learning resources mentioned during the class, build a PDF of the links, and add that PDF to the training site, too. Great idea! Plus, since we’ll be teaching these same classes in Chicago and Philadelphia later this year, we can even start with that same resource list because many of them will be applicable.

San Diego (the Hilton is on the left)

San Diego (the Hilton is on the left)

See, we learn a lot even when we’re teaching. We have such a great time with this, and we can’t wait to keep sharing what we learn. If it sounds like fun, join us in Chicago or Philly later this year.