Michael J. Swart

October 6, 2015

Don’t Abandon Your Transactions

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 11:59 am

DGQuoteAbout eight years ago, Dan Guzman wrote a post called Use Caution with Explicit Transactions in Stored Procedures. In it, he talks about error handling and transactions, specifically with respect to the XACT_ABORT setting.


Microsoft’s docs for XACT_ABORT are pretty clear. The setting determines whether “SQL Server automatically rolls back the current transaction when a statement raises an error”.

And in nearly every scenario I can think of that uses a transaction, this automatic rollback is the desired behavior. The problem is that it’s not the default behavior. And this leads to Dan Guzman’s advice where he strongly recommends that SET XACT_ABORT ON be included “in all stored procedures with explicit transactions unless you have a specific reason to do otherwise.”

What Could Go Wrong?

When a statement inside a transaction fails (for whatever reason) and XACT_ABORT is set to off, then…

  • That transaction is abandoned.
  • Any locks taken during that transaction are still held.
  • Even if you close the connection from the application, .NET’s connection pooling will keep that connection alive and the transaction on SQL Server stays open.
  • Fortunately, if another application reuses the same database connection from the connection pool, the old transaction will be rolled back.
  • Unfortunately developers can’t count on that happening immediately.
  • Abandoned transactions can cause excessive blocking leading to a concurrency traffic jam.
  • Also, abandoned transactions can interfere with downstream solutions. Specifically ones that depend on the transaction log. Transaction logs can grow indefinitely. Replication solutions can suffer. If RCSI is enabled, the version store can get out of hand.

Some (or all) of those things happened to us last week.

Steps To Take

Here are some things you can do:

Do you have abandoned transactions right now?
It’s not too hard to identify these abandoned transactions:

-- do you have abandoned transactions?
select p.spid, s.text as last_sql
from sys.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where p.status = 'sleeping'
and p.open_tran > 0

Also if you use sp_whoisactive, you can identify these processes as those with a sleeping status and at least one open transaction. But there’s a trick I use to identify these quickly. The sql_text value in the output of sp_whoisactive will typically begin with CREATE PROCEDURE. When I see that, I know it’s time to check whether this connection is sleeping or not.

Follow Dan Guzman’s advice to include SET XACT_ABORT ON in all stored procedures with explicit transactions.
You can actually find the procedures in your database that need a closer look

-- find procedures that could suffer from abandoned transactions
FROM sys.procedures 
where OBJECT_DEFINITION(object_id) like '%BEGIN TRAN%'
and OBJECT_DEFINITION(object_id) not like '%XACT_ABORT%'
order by name

Set XACT_ABORT ON server-wide
If you choose, you can decide to set the default value for all connections to your server. You can do that using Management Studio:

Or via a script:

-- turn the server's xact_abort default on
declare @user_options_value bigint;
select @user_options_value = cast(value as bigint)
from sys.configurations 
where name = 'user options';
set @user_options_value = @user_options_value | 0x4000; 
exec sp_configure N'user options', @user_options_value;
-- (if necessary) turn the server's xact_abort default off
declare @user_options_value bigint;
select @user_options_value = cast(value as bigint)
from sys.configurations 
where name = 'user options';
set @user_options_value = @user_options_value & 0x3fff; 
exec sp_configure N'user options', @user_options_value;

Code Review

I love code reviews. They’re more than just a tool for improving quality. They’re learning opportunities and teaching opportunities for all involved.

Last week, I invited readers to have a look at a procedure in a post called Code Review This Procedure. I was looking for anyone to suggest turning on XACT_ABORT as a best practice. It’s a best practice where I work, but things like this slip through. We should have caught this not just during testing, but during development. It’s obvious with hindsight. But I wanted to determine how obvious it was without that hindsight. I guess it was pretty subtle, the XACT_ABORT was not mentioned once. That’s either because the setting is not often used by most developers, or because it is easily overlooked.

But here are some other thoughts that readers had:

Many people pointed at concurrency and transaction isolation levels as a problem. It turns out that concurrency is very hard to do right and nearly impossible to verify by inspection. In fact one of my favorite blog posts is about getting concurrency right. It’s called Mythbusting: Concurrent Update/Insert Solutions. The lesson here is just try it.

Cody Konior (blog) submitted my favorite comment. Cody writes “I often can’t disentangle what the actual impact of various isolation levels would be so I go a different route; which is to create a quick and dirty load test”. I can’t determine concurrency solely by inspection either, which is why I never try. Cody determined that after hammering this procedure, it never failed.

He’s entirely right. Concurrency is done correctly here. Ironically, most of the fixes suggested in other people’s code reviews actually introduced concurrency issues like deadlocks or primary key violations.

People also suggested that blocking would become excessive. It turns out that throughput does not suffer either. My testing framework still managed to process 25,000 batches per second on my desktop without error.

Validating inputs
Some people pointed out that if NULL values or other incorrect values were passed in, then a foreign key violation could be thrown. And they suggested that the procedure should validate the inputs. But what then? If there’s a problem, then there are two choices. Choice one, raise no error and exit quietly which is not ideal. Or choice 2, raise a new error which is not a significant improvement over the existing implementation.

Avoiding the transaction altogether
It is possible to rewrite this procedure without using an explicit transaction. Without the explicit transaction, there’s no chance of abandoning it. And no chance of encountering the trouble that goes with abandoned transactions. But it’s still necessary to worry about concurrency. Solutions that use single statements like MERGE or INSERT...WHERE NOT EXISTS still need SERIALIZABLE and UPDLOCK.

Error handling
I think Aaron Mathison (blog) nailed it: I’m just going to quote his review entirely:

Since your EVENT_TICKETS table has required foreign keys (evidenced by NOT NULL on all columns with foreign key references) the proc should be validating that the input parameter values exist in the foreign key tables before trying to insert into EVENT_TICKETS. If it doesn’t find any one of them it should throw an error and gracefully rollback the transaction and return from the proc.

The way it’s designed currently I think you could get an error on inserting to EVENT_TICKETS that would fail the proc and leave the transaction open.

October 1, 2015

Code Review This Procedure

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:28 am

Earlier this week we encountered a web-site outage caused by a database procedure. I’m going to blog about that problem (in detail) in a post scheduled for next week. But before I publish that post, I want to know how subtle or obvious the problem was. It seems obvious to me now, but I have the benefit of hindsight. I wonder whether we could we have avoided this during the code review stage of development.

So before I publish the details, I invite you to do a code review of this procedure in the comment section.

The Procedure

Here’s the procedure. It suffers from the same thing that burned us this week. Do you see any issues with it? Tell me in the comment section.

  @EventId BIGINT,
  @VenueSeatId BIGINT,
  @PurchaserId BIGINT,
  @PurchaseMethodId BIGINT
    -- If the row exists, grab details about the purchaser
      @pid = PurchaserId,
      @pmid = PurchaseMethodId,
      @dt = PurchaseDate
    WHERE EventId = @EventId
      AND VenueSeatId = @VenueSeatId;
    IF ( @pid IS NULL )
      -- The row doesn't exist, insert the row
      SET @dt = SYSDATETIME()
        ( EventId, VenueSeatId, PurchaserId, PurchaseMethodId, PurchaseDate )
        ( @EventId, @VenueSeatId, @PurchaserId, @PurchaseMethodId, @dt );
      SELECT @pid = @PurchaserId,
             @pmid = @PurchaseMethodId;
  -- return details about the purchaser
    @pid as PurchaserId,
    @pmid as PurchaseMethodId,
    @dt as PurchaseDate;

The Schema

Here’s a subset of the table definitions that this procedure is meant to use.

    PRIMARY KEY (EventId)
  -- etc...
    PRIMARY KEY (VenueSeatId)
  -- etc...
    PRIMARY KEY (PurchaserId)
  -- etc...
    PRIMARY KEY (PurchaseMethodId)
  -- etc...
  PurchaserId BIGINT NOT NULL,
  PurchaseMethodId BIGINT NOT NULL,
  PurchaseDate DATETIME2 NOT NULL,
    PRIMARY KEY CLUSTERED (EventId, VenueSeatId),
    FOREIGN KEY (EventId) REFERENCES dbo.[EVENTS] (EventId),
    FOREIGN KEY (PurchaserId) REFERENCES dbo.PURCHASERS (PurchaserId),
    FOREIGN KEY (PurchaseMethodId) REFERENCES dbo.PURCHASE_METHODS (PurchaseMethodId),

September 15, 2015

Troubleshooting Tempdb, a Case Study

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 11:15 am
Tackling A Hairy Problem
This series includes a number of stand-alone posts which can fit together to tell a bigger story

At work, we store error messages from our web servers in a database. Up until recently, we were loading them using procedures that use wide TVPs. We discovered that if we saw a large enough spike in errors, then we’d also see tempdb contention which would flood our error logging service with more errors. This positive feedback loop made our database server unresponsive and the system as a whole couldn’t recover without intervention. That’s the problem I tackled, and I want to explain my troubleshooting process.

The Troubleshooting Landscape

Let’s start at the beginning. Here’s an abstract visualization of how I see throughput and performance thresholds.

The blue area represents the load sent to the database. The red bars are examples of performance thresholds that can limit throughput. Based on hardware and configuration, those red lines can be moved up or down. The lowest red line is the performance ceiling. In this example, notice that adding more CPUs would not allow more throughput unless tempdb contention is also tackled:

The Troubleshooting Landscape

The Troubleshooting Landscape

I like to show this graph to people in order to illustrate the implicit relationship between a database developer and a database administrator. There’s obviously a lot of overlap, but in general:

  • The red lines are part of the DBA’s job. It is up to the DBA to provide and configure a database server that can support the load sent by the application.
  • The blue area is part of the developer’s job. It is up to the developer to make most efficient use of the hardware given.

Happy databases are ones where the blue and the red don’t meet.

Microsoft DBAs are the only ones that have to worry about tempdb
But look at that tempdb contention limit. I like to point out to anyone who will listen that tempdb contention is Microsoft’s fault. Every minute spent on tempdb problems is time spent working around a defect in SQL Server. It’s frustrating. It’s already hard enough worrying about CPU and IO without worrying about logical contention caused by the database engine. I feel like this guy:

Gimli feels betrayed

My Troubleshooting Workflow

So if you’ve been following my blog for the past few weeks, this is what I’ve been leading up to. With a ton of hindsight, here’s my workflow for troubleshooting tempdb contention:


Some of the early information in the early steps can be detected using sp_whoisactive, and some of the last steps are links to other parts of this blog series.


The world is rarely as nice and predictable as we model it to be. Database load is no exception. Database load is not a smooth thing. It’s spikey and uneven and it consists of an unpredictable variety of queries.

Once when I thought that tempdb transactions per second was the best metric to watch, I captured this graph over a couple of days:


The outage seems unrelated to tempdb activity

The spikes in tempdb transactions correspond to nightly report runs or maintenance. But we experienced a database outage caused by tempdb contention during a period of time where tempdb usage should have been tolerable. This was an outage where practically no queries completed.

And that was puzzling to me. I knew it was had to be one of two things. Either

  1. the metric I was using – tempdb transactions per second – did not accurately reflect the activity that I wanted to measure. Or
  2. the database activity (both volume and variety) changed so suddenly, that I couldn’t measure it. It not only caused an outage, but it also prevented me from measuring the effect

It turns out that both ideas were correct. I embarked on a process to learn more about tempdb and that’s when I found out that wide TVPs were multipliers of tempdb usage. Taking that into account, I discovered that our error logging procedure was very very expensive. And it wasn’t too long to find that any spike in errors could quickly cause a feedback loop. So quickly that our servers couldn’t cope and couldn’t recover.

What We Did

We did a couple things. We isolated our logging database onto a separate server. Our logging service was adjusted to not use TVPs as frequently. This helped. Our tempdb slowdowns were no longer server killers, they were merely throttles. Another adjustment to the number of data files reduced our tempdb headaches to almost zero and this meant that we could finally focus on the next bottleneck in our troubleshooting landscape.

In the past few months, I’ve tackled this bottleneck and about a dozen others. In fact, if I had time enough, you’d probably see more posts like this one about troubleshooting and tackling performance challenges. After a lot of work by many people, we were able to support as much as 58K transactions per second and we’re really proud of that. It’s been a difficult but fun couple of months.

Thanks for reading. I hope you enjoyed the series.

August 31, 2015

Avoid Frequent use of TVPs With Wide Rows

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am
Tackling A Hairy Problem
This series includes a number of stand-alone posts which can fit together to tell a bigger story

Let’s say there’s a procedure that uses a table-valued parameter (TVP), and that TVP type has a large maximum width. Something like:

-- Create a message table type
  ( Message VARCHAR(MAX) );

Then each time the procedure is executed, SQL Server will pre-allocate six extents (48 pages) of space in tempdb whether or not it’s needed.

Frodo Baggins Quote

Measuring tempdb Activity

So what’s the problem? Well, if these procedures are called frequently (like hundreds or thousands of times per second), then the extra allocations for wide TVPs can be excessive. These allocations can cause tempdb latch contention.

I found that SQL Server will allocate tempdb space based on the width of a TVP table. If a TVP uses a table type that has a NVARCHAR(10) column, then SQL Server doesn’t pre-allocate any space at all. But if the TVP table type has a NVARCHAR(4000) column, then SQL Server will allocate six extents of space. To measure the tempdb activity exactly, I wrote a program which let me generate this:

PFS Operations

This chart takes some explaining. But first notice that a single query can cause up to 56 tempdb PFS operations!

Here’s how my program helped me create this chart. It starts by creating a table type. The table type has one column of type NVARCHAR(X). Next, the program executes a simple query that uses a table-valued parameter of that table type. The program measures the number of tempdb allocations and deallocations for various TVP widths: (X ranges from 1 to 4000). Also notice that

  • I’m focusing on PFS operations here, but GAM activity shows similar activity
  • The x axis corresponds to the maximum width of the column in the table type
  • The y axis corresponds to the number of PFS operations found in tempdb. An extent is allocated with one PFS operation, but deallocated one page at a time, this results in nine PFS operations per extent.
  • The steps correspond to four, five and six extents.
  • There is some overhead associated with creating and dropping tempdb objects. But that overhead is avoided with temp table caching. And those operations have been removed from this chart.
  • Table types that can be wider than a 8000 bytes such as those with multiple columns or with NVARCHAR(MAX) columns are treated the same as table types with an NVARCHAR(4000) column; they’re given six extents of space.

What I Think

I’m still making an assumption that transaction log operations on PFS and GAM pages share a one-to-one relationship with latches on those pages. But experimentally, I have seen that skinny TVPs do in fact enjoy much higher throughput than wider TVPs. In general, this is really hard to measure. I’m frustrated that there are no good metrics to help measure this kind of thing.

Six extents for wide TVPs is really excessive and causes unnecessary pressure on tempdb latches when used frequently. I now begin to worry about the width of table types and the frequency that they’re used as table-valued parameters. But I don’t think we should have to. Stuff like this should be the concern of the database engine, not the database administrator.


While writing this post, I really appreciated the help of Paul White and his post Temporary Table Caching Explained

I also appreciate the huge amount of information on SQL Server internals posted by Paul Randal on his blog. Especially posts like Understanding data vs log usage for spills in tempdb.

August 12, 2015

What’s Going On Inside Tempdb?

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 11:56 am
Tackling A Hairy Problem
This series includes a number of stand-alone posts which can fit together to tell a bigger story

I discovered that digging into tempdb’s transaction log using the undocumented fn_dblog is a surprisingly useful technique for finding data to help battle tempdb contention. But it’s not easy. The transaction log is cryptic and sorting through the information can be difficult.

I wanted more tempdb internals info. Be careful what you wish for.

Like many many others, I’ve found it difficult to avoid or diagnose tempdb allocation contention issues. Typical advice includes

  • Use multiple equally-sized data files for tempdb
  • Use trace flag 1118 in order to allocated full extents instead of mixed extents
  • Look at your applications to reduce queries that use tempdb

It’s that last point that is so tricky. I need to find out what parts of my application are using tempdb but it’s used for so many different things. Robert Davis lists eighteen different ways that SQL Server uses tempdb in his whitepaper Demystifying tempdb Performance and Manageability. So in order to tackle tempdb allocation contention, there are two things I want to do. I want to (A) measure tempdb allocations and (B) dig into tempdb allocations.

The Trouble (a Quick Recap)

Remember, tempdb is a resource shared by everyone using the database server. When a process wants to use space in tempdb, it allocates pages. Part of that allocation involves calling dibs for those pages by updating the Page Free Space (PFS) page and the Global Allocation Map (GAM) page. But updating those pages involves taking a short-lived latch on them. So with enough activity, we can see latch contention on these pages. Other people have done a better job explaining that than I did just now. You can probably find many of those explanations by googling tempdb latch contention.

Measuring Tempdb Allocations

So if latches on tempdb pages are a limited resource, then it would be wonderful to measure how often latches are taken there. That way developers could experiment to determine what activities use tempdb and to what extent. Right now, that can’t be measured. Maybe we’re spoiled. We have a wide variety of extended events and a rich set of dynamic management views that let us dig really deep into SQL Server internals. But when it comes to measuring tempdb latches, there’s no suitable metric.

Here’s how Microsoft comes close:

Metric Why It’s Not What I Want
Performance counter Database transactions/sec specifically the tempdb database. This is a gauge metric and it would be useful if every tempdb transaction allocated a small number of pages. But at least one kind of activity – tempdb spills – will allocate many pages in a single tempdb transaction and that activity gets hidden.
sys.dm_db_task_space_usage and sys.dm_db_session_space_usage These are useful when you want to categorize tempdb allocations. It’s reports the number of tempdb allocations that have been taken for any given point in time. But tempdb allocations taken by long-running transactions can be over-reported and short-running transactions tend to be missed. This makes it difficult to tell whether the data indicates typical tempdb allocations or whether it’s just showing long held allocations.
Performance counter Page latch waits in Wait Statistics, specifically Waits started per second. This is an alarm metric and not specific to tempdb.
Performance counter category Latches (any counter). Again, these are all alarm metrics and not specific to tempdb.
Peformance counter Pages Allocated/sec or Page Deallocations/sec in Access Methods I really like this one. It’s not tempdb specific, but it’s a gauge metric and it’s getting close to what I want.
Performance counter Tempdb latches/sec with instances on GAM, SGAM and PFS Oooh perfect. Too bad this performance counter is fictional. It doesn’t exist. I would love this metric if it existed because it would be a gauge metric that most directly corresponds to tempdb contention problems.

Digging into Tempdb Allocations

Putting metrics aside for the moment, I’ve also tried a number of ways to dig into events and logs in order to tie tempdb use with user activity. I’ve had limited success with these two methods:

  • I found that polling the DMV sys.dm_os_waiting_tasks (an alarm metric) doesn’t always give me the sql text I want.
  • There are new extended events called sqlserver.mixed_extent_allocation and sqlserver.mixed_extent_deallocation introduced in SQL Server 2014, but these are focused on mixed extents (which I already avoid by enabling trace flag TF-1118).

But it turns out that tempdb’s transaction log is rich with information (even though a lot of it is cryptic).

Digging into Tempdb’s Transaction Log

The easiest way to look at tempdb’s transaction log is by using fn_dblog:

use tempdb;
select * from fn_dblog(null, null);

But it can be a heavy query. Here’s a light-weight query that gets the most recent 5 seconds of activity or 10000 rows whichever is less:

-- Collect tempdb log activity
use tempdb;
-- get the latest lsn for tempdb
declare @xact_seqno binary(10);
declare @xact_seqno_string varchar(50);
exec sp_replincrementlsn @xact_seqno OUTPUT;
set @xact_seqno_string = '0x' + CONVERT(varchar(50), @xact_seqno, 2);
set @xact_seqno_string = stuff(@xact_seqno_string, 11, 0, ':')
set @xact_seqno_string = stuff(@xact_seqno_string, 20, 0, ':');
-- wait for five seconds of activity:
waitfor delay '00:00:05';
select top 10000 * 
from fn_dblog(@xact_seqno_string, null);

This actually lets me answer a lot of questions:

What activity is there against PFS or GAM pages?
Each operation (allocation or deallocation) is likely one page latch. So we have:

select *
from fn_dblog(@xact_seqno_string, null)
where Context in ('LCX_PFS', 'LCX_GAM', 'LCX_SGAM')

or aggregated we have:

select count(*), Context
from fn_dblog(@xact_seqno_string, null)
where Context in ('LCX_PFS', 'LCX_GAM', 'LCX_SGAM')
group by Context
order by count(*) desc;

What kinds of tempdb transactions are there?

select count(*), [Transaction Name]
fn_dblog(@xact_seqno_string, null)
where Operation = 'LOP_BEGIN_XACT'
group by [Transaction Name]
order by count(*) desc;

Transaction names are cryptic, but they are organized by activity and we can use some educated guesses to figure out what’s going on. For example, queries that use table valued parameters are called TVQuery and are often associated with transactions called AllocFirstPage, FCheckAndCleanupCachedTempTable and if the TVP’s table type has a primary key defined, then offline index build.

PFS or GAM activity by tempdb transaction type
Putting it all together:

with recentTempdbLogs as
    select top 10000 * 
    from fn_dblog(@xact_seqno_string, null)
TransactionNames as
    select [Transaction Id], [Transaction Name]
    from recentTempdbLogs
    where Operation = 'LOP_BEGIN_XACT'
select tn.[Transaction Name], rtl.Context, COUNT(*) as Operations
from recentTempdbLogs rtl
join TransactionNames tn
    on tn.[Transaction Id] = rtl.[Transaction ID]
where Context in ('LCX_PFS', 'LCX_GAM', 'LCX_SGAM')
group by tn.[Transaction Name], Context
order by COUNT(*) desc

How Is This Actionable?

Remus Rusanu has a post titled How to read and interpret the SQL Server log. It gives an introduction to some of the internals involved with what you see in the transaction log. It’s not always obvious why understanding SQL Server internals helps in real world scenarios. I’ve found that it’s most useful for troubleshooting by focusing avenues of investigation (whether it’s TVPs, tempdb spills, version store stuff, etc…).

In my case, I used this data to narrow down the type of activity. Specifically I used

  • the Transaction SID column (giving me the user),
  • the SPID which gives me the session id and helps me tie it to data collected elsewhere
  • the AllocUnitName which I used to tie to a particular tempdb objects

More on what I found next week.

August 5, 2015

Types of Performance Metrics

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:00 am
Tackling A Hairy Problem
This series includes a number of stand-alone posts which can fit together to tell a bigger story

There seem to be two main kinds of performance metrics, ones that measure trouble and ones that measure resources. I’ll call the first kind “alarm” metrics and the other kind “gauge” metrics. Alarm metrics are important, but I value gauge metrics more. Both are essential to an effective monitoring and alerting strategy.

In the land of Mordor where calls get dropped


Alarms are great for troubleshooting, they indicate that it’s time to react to something. They have names containing words like timeouts, alerts and errors, but also words like waits or queue length. And they tend to be spikey. For example, think about a common alarm metric: SQL Server’s Blocked Process Report (BPR). The report provides (actionable) information, but only after a concurrency issue is detected. Trouble can strike quick and SQL Server can go from generating zero BPR events per second to dozens or hundreds. Alarm metrics look like this:

Alarm Metric

Alarm Metric


Now contrast that with a gauge metric. Gauge metrics often change value gradually and allow earlier interventions because they provide a larger window of opportunity to make corrections.
If you pick a decent threshold value, then all gauges can generate alerts (just like alarms do!). As they approach trouble, gauges can look like this:

Gauge Metric

Gauge Metric

And the best kind of gauge metrics are the kind that have their own natural threshold. Think about measuring the amount of free disk space or available memory. Trouble occurs when those values hit zero and those guages look like this:

Decreasing Gauge Metric

Decreasing Gauge Metric


I compare different gauges and alarms to further explain what I mean.

Alarms Gauges
Avg. Disks Read Queue Length Disk Reads/sec
Processor Queue Length % Processor Time
Buffer Cache Hit Ratio Page lookups/sec
“You are running low on disk space” “10.3 GB free of 119 GB”
Number of shoppers waiting at checkout Number of shoppers arriving per hour
Number of cars travelling slower than speed limit Number of cars per hour
Number of rings of power tossed into mount doom Ring distance to mount doom

Hat tip to Daryl McMillan. Our conversations led directly to this post.

June 24, 2015

How to Create Indexed Views Online

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am

One of the limitations of indexed views is that their initial clustered indexes cannot be created online. Try it and you’ll get this error:

Msg 1967, Level 16, State 1, Line 34
Cannot create a new clustered index on a view online.

In Guidelines for Online Index Operations, Microsoft explicitly excludes the “Initial unique clustered index on a view” as an on-line operation. Challenge accepted.

Solution Overview

I’ll be honest. This solution is not for everyone. Especially if you don’t like to get your hands dirty.

The idea is to create a bit column in one of the base tables called IsMigrated which is initially 0. Add an extra where clause to the definition of the view IsMigrated = 1 so that the view is initially empty. Create the index and then gradually update the IsMigrated values to 1.

What follows is an example of what I mean.

The Setup

Consider these two tables I made up:
In my scenario, queries will often join these tables together and sometimes concatenate the columns BaseURL and URL. I want to create the following view and index it to facilitate URL lookups. That view looks like this.

      WHEN L.IsExternal = 1 THEN L.URL
      ELSE C.BaseURL + L.URL
    END AS FullURL,
        WHEN L.IsExternal = 1 THEN L.URL
        ELSE C.BaseURL + L.URL
      END ) AS FullURLChecksum
    ON C.ClientId = L.ClientId
  WHERE L.DateDeleted IS NULL;

And the base tables can get big. In my example I’ll use 50,000 clients and 200 links per client (giving 10 million rows in LINKS). So now when the initial unique clustered index is created, it can takes minutes to complete and the base tables are unavailable for the whole duration.

Offline Method

For comparison purposes, here is one offline method of creating an indexed view.

  • Create the view (0 seconds)
  • Scan a base table to warm up the cache (20 seconds)
  • Create the initial clustered index on the view (1 minute 50 seconds offline)
  • Create an additional nonclustered index on the view (2 minute 30 seconds)

Depending on the circumstances, that offline step could take longer and could be a problem. The next method attempts to get around that.

Online Method

Follow these steps

  • Add a new bit column IsMigrated (default 0) to one of the base tables. In my case I use the table LINKS and because I’m using SQL Server 2012, this step is instantaneous (0 seconds)
  • Create the view (0 seconds)
  • Warm up the cache by scanning the LINKS base table (20 seconds)
  • Create the initial clustered index on the view. With no writes and minimal reads this is very quick. Check it out.

      ON dbo.LINK_FULLURLS(LinkId);
     Query executed successfully.
     Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, …
     Table ‘LINKS’. Scan count 3, logical reads 511159, physical reads 0, read-ahead reads 0, …
     SQL Server Execution Times:
       CPU time = 1295 ms, elapsed time = 833 ms.

  • Create the additional nonclustered index which is also initially empty (0 seconds)
  • Change the default of IsMigrated to 1 (0 seconds)
  • Update the value of IsMigrated to 1 in batches. (8 minutes 4 seconds but online)

I don’t know if you caught it, but there is no step that removes the column IsMigrated. It stays behind messing up the data model. To remove it would require that we modify the definition of the view and that would unravel everything we did. That’s the catch.


Here are the times that I saw:

Indexed View Creation Timing

With my method, I’m not eliminating the offline step, I’m reducing its duration. It normally takes a long time to create the clustered index. I measured about half a minute of reading and two minutes of writing. My method’s offline step avoids all of the writes with the IsMigrated column and avoids most (or all) of the physical reads by warming up the cache.

Caveats and Other Notes

  • The demo scripts I provided are not a recipe but an example of how to implement this strategy. If you feel like using this strategy yourself, you’re going to have to write and test your own scripts.
  • This indexed view is not quite as useful as your typical indexed view. Normally SQL Server can sometimes choose to use an indexed view when executing queries that don’t even mention the view. But the extra IsMigrated = 1 clause in the view prevents SQL Server from doing so.
  • Other databases like Oracle and Postgres have things called Materialized Views. Those can sometimes store data that’s a bit stale, but they don’t suffer from the same online troubles that SQL Server’s Indexed View does. Sometimes the grass is greener on the other side of the fence.
  • I wish I didn’t need the workaround. If you’d like to add your voice to the feedback I gave to Microsoft, then vote up this connect item Create Clustered Indexes on Views WITH (ONLINE=ON).
  • I tried creating a temporary index (filtered, covering and narrow) on the base tables to help the view creation. It didn’t seem to help too much because my largest bottleneck is on writes, not reads.
  • I tried creating a check constraint on IsMigrated = 0 before I created the index hoping that SQL Server could use it to reduce the reads to zero. It didn’t help.

Use Case

Like I mentioned, this method will be useful when you have no opportunity to apply offline changes to live databases and you don’t mind messing up your data model. I doubt that many will use this method. I think it’s rare to belong to an organization that can’t tolerate downtime but can tolerate this data modeling sloppiness.

If you have a large amount of development time and resources, a better way to handle this situation might be to build in some wiggle room for offline maintenance like this. A slightly more graceful alternative is to build reduced functionality into your application like the ability to disable a single feature or operate in read-only mode. This is easier said than done of course, but it accommodates a much wider variety of solutions to deployment challenges like this.

May 20, 2015

It’s Hard To Destroy Data

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 8:00 am

Had I been born later, I probably would have gone with Wreck-It Ralph It is surprisingly difficult to delete data permanently. SQL Server has sophisticated features that prevent all kinds of data loss, but the opposite is not true. SQL Server has very few features that help you destroy data.

But why would anyone need to destroy data? Lots of reasons.

  • Some laws and data retention policies require that data older than a certain age gets destroyed.
  • Developers often ask for production data, or a subset. A DBA could decide to give them a backup having first dropped certain tables or columns that contain personally identifiable information.

There are countless other reasons why you might want to destroy data and I’m going to focus on three general use cases: destroying tables, destroying columns and destroying rows.

Straight Up Deleting Is Not Enough

I’m going to demonstrate that deleting data is not equivalent to destroying data. Deleting data is just hiding it.

First create a table, populate it and see what it looks like on disk:

USE DeletedDataDemo;
SELECT col1, col2, col3 
INTO dbo.Table1
  VALUES (1, 'aaaaaaaaaaaa', 'bbbbbbbbbbbb'),
         (2, 'cccccccccccc', 'dddddddddddd'),
         (3, 'eeeeeeeeeeee', 'ffffffffffff')
) data (col1, col2, col3)
DBCC IND(DeletedDataDemo, 'dbo.Table1', 1) -- file 1, page 216 for example
--update the following command to look at that page
DBCC PAGE (DeletedDataDemo, 1, 216, 2);

Notice that the last command DBCC PAGE shows the contents of the table as it appears on disk.

Data dump

That data is still there

And now drop the table that was just created:

DROP TABLE dbo.Table1;
-- or
-- ALTER TABLE dbo.Table1 DROP COLUMN col3;
-- or
-- DELETE dbo.Table1 WHERE col1 IN (2,3);
DBCC PAGE (DeletedDataDemo, 1, 216, 2);

Run the DBCC PAGE command and notice that the data values are still there! That data cannot be queried but it remains on disk. Even though this data is in deallocated space, this data can survive backups and restores (even after checkpoints).

Repeat the demo from the beginning. But this time, instead of dropping the table, try deleting rows or dropping a column. Just like the dropped table the data remains on disk.

Try to Destroy Data by Cleaning Pages

So SQL Server doesn’t actually delete data, it hides it and deallocates the space. A ghost cleanup job can eventually clean up this data but you can’t depend on how frequently it runs. That leads to stackoverflow questions like this one: How can I securely destroy some data using sql server 2008?

One of the answers there mentions that sp_clean_db_free_space can force the cleanup to run early. Cleaning pages is exactly what’s required to get rid of that data in deallocated space. But cleaning pages consumes a lot of I/O and maybe that’s why SQL Server 2008 introduced a lot of control over the granularity of cleaning pages:

  • Use sp_clean_db_free_space for a whole database.
  • Use sp_clean_db_file_free_space for a single data file.
  • Use DBCC CLEANPAGE(@dbid, @fileid, @pageid) after a CHECKPOINT for a single page.

These commands force SQL Server to write zeros to any deallocated space.


Dropped-Column Data Sticks Around

Columns are a special case. SQL Server doesn’t reclaim the space that is freed by dropped columns. And this dropped column data is not touched by any of the page cleaning methods. In order to destroy this data, it’s necessary to add a few manual steps. These steps can destroy column data by either overwriting column values or by rebuilding its table’s indexes.

How To Destroy Data

Here’s a table that shows the syntax to use when trying to destroy data.

When destroying … Use this syntax
DROP TABLE dbo.Table1;
EXEC sp_clean_db_free_space 'DeletedDataDemo';
DELETE dbo.Table1 WHERE col1 = @col1;
EXEC sp_clean_db_free_space 'DeletedDataDemo';
(except columns in heaps)
EXEC sp_clean_db_free_space 'DeletedDataDemo';
(alternative syntax)
UPDATE dbo.Table1 SET col3 = N'';
EXEC sp_clean_db_free_space 'DeletedDataDemo';
Other data structures
(Columnstore, Full-text, XML indexes, Service Broker Queues etc…)
Not evaluated. I don’t know how long this data sticks around.

The Lessons

Microsoft warns you that these data cleaning procedures are very I/O intensive so I would be reluctant to suggest these methods when alternatives exist.

The clumsiness of the dropped column scenario makes me wonder about other scenarios where SQL Server deletes data but doesn’t destroy it.

And this leads directly to this bit of advice:

Don’t distribute backups of databases that have contained sensitive information.

April 27, 2015

The Appeal Of Lightning Talks

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 11:32 am

Lightning talks are quick presentations, usually five-ten minutes long, and I really enjoy them.

But it’s not (only) about indulging short-attention-span habits. Lightning talks tend to be really dense with interesting information. The speaker is forced to say one thing and nothing else. Speakers need to make a choice between important content and not-so important content. It’s very difficult for the speaker, but it’s great for us in the audience. 
It’s possible to get dense information into a hour long session, but those sessions are more prone to contain filler.

The best lightning talk I’ve ever watched is this one, Fighting Dirty In Scrabble by Mehal Shah.

He just crushes that talk. I use him as a model for a really good content-driven lightning talk.

That’s why I’m excited about attending the third annual member presentations at Toronto PASS.

Lightning Talks About SQL in Toronto

It’s basically like an open mic night. If you’re going to attend one user group meeting in Toronto, this is the one. I plan on giving a talk about my favorite nemesis, tempdb.

The Toronto PASS user group meeting is tomorrow (April 28, 2015) at 5:30 pm at the Northern District Library (near Eglinton and Yonge).
By the way, if any local friends from KW want to catch a ride to Toronto, I’ll be leaving KW in the afternoon.

April 6, 2015

Finding Scalar Aggregate Indexed Views in Your Database

Filed under: Miscelleaneous SQL,SQL Scripts,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:35 am

Fellow MVP Paul White recently blogged about a SQL Server bug. His blog post is called An Indexed View Bug with Scalar Aggregates

It’s a really well-written article (as always). After reading it, it’s natural to wonder whether your databases have any such indexed views?

Chances are that you don’t have any. These indexed views aren’t too common, but if you do have indexed views that use scalar aggregates, here’s a query that can help you find them.
The following query finds indexed views without GROUP BY that have exactly one row in any partition.

with IndexedViewIds as
  SELECT [object_id] from sys.indexes
  SELECT [object_id] from sys.views
IndexedViewInfo as 
    OBJECT_SCHEMA_NAME([object_id]) as SchemaName,
    OBJECT_NAME([object_id]) as ViewName,
    OBJECT_DEFINITION([object_id]) as [Definition]
  FROM IndexedViewIds
FROM IndexedViewInfo v
    SELECT * 
    FROM sys.partitions
    WHERE [object_id] = v.[object_id]
    AND [rows] <> 1
  AND v.[definition] NOT LIKE '%GROUP BY%'


The query isn’t perfect. It’s actually possible (but rare) to get false positives here. This query doesn’t look for any aggregate keywords. So look for them in the SELECT list. Also make sure no GROUP BY clause exists.

It’s also possible (but rare) to miss some indexed views when GROUP BY gets mentioned, but not used. For example, if an indexed view definition contains the phrase GROUP BY in a comment, it won’t show up in this list.

(For my curious co-workers, none of our indexed views use scalar aggregates)

Older Posts »

Powered by WordPress