Search results matching tags 'performance' and 'SQL Server 2008'

The Curious Case of the Optimizer that doesn’t

Hugo Kornelis — Thu, 03 May 2012 22:13:00 GMT

The optimizer is the part of SQL Server that takes your query and reorders and rearranges your query to find the optimal execution plan. In theory.

In practice, that doesn’t always work out well. Often, the optimizer manages to come up with brilliant ways to execute a complex query very efficiently – but sometimes, it misses an option that appears to be so simple that you can only stare in utter amazement at the execution plan before going to the Connect site.

Here is an example I recently ran into. I tested it on SQL Server 2012 and on SQL Server 2008 R2, and it reproduces on both. Execute the query below in the AdventureWorks sample database, with the option to Include Actual Execution Plan enabled (Ctrl+M), or request an Estimated Execution Plan (Ctrl-L).

SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;

When following the flow of data (by reading the execution plan from right to left), we see that the data is first read (with an unordered clustered index scan, since there is no better index available), then sorted by SalesLastYear, so that Rank1 can be computed (using two Segment operators and a Sequence Project operator – don’t ask). After that, the rows are sorted again, now by SalesYTD, and we see another combination of two Segment and one Sequence Project, for the calculation of Rank2. And then, finally, the rows are re-sorted by SalesLastYear so that they can be returned in the requested order.

Now the big question is: why does the plan include two sort operators that both sort the data in the same (SalesLastYear) order? If the task of the optimizer is to find smart ways to rearrange computation order for better performance, why doesn’t it simply compute Rank2 first and Rank1 after that? In that case, the rows are already in the SalesLastYear order after the last Sequence Project, so no extra sort is needed. The execution plan of the query below confirms this suspicion:

SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;

Indeed, the execution plan of this query includes only two Sort operators instead of the three we had in the first execution plan. If you include both queries in a single batch, you’ll see an estimated cost of 59% for the first query, and 41% for the second. (Some people think that the percentages shown in an Actual Execution Plan are an indication of the actual cost; that is not the case – the percentages are computed from the Estimated Subtree Cost property of the left-most SELECT operator). The SalesTerritory table is too small to measure any actual performance differences, but I tried queries with a similar pattern on the SalesOrderDetail table (121,317 rows), and on FactProductInventory in AdventureWorksDW (776,286 rows) and I did see an actual difference in execution time. No surprise, but now I know for sure!

So, we have seen that simply reordering the two columns that use an OVER clause reduces the query cost by about 30%. How is it possible that such a simple, basic reordering strategy is not considered by the optimizer? Surely, this can only be a bug?

That’s what Fabiano Neves Amorim thought when he filed this bug on Connect. But, as you can see, the bug has been closed as “By design”. That probably doesn’t mean that someone wrote a design document telling the optimizer team to make sure that OVER clauses must always be evaluated in the order in which they appear in the query, even if a different order would be cheaper. I rather think of it as “missing an optimization opportunity is not a bug; the results are still correct, just a bit slower – so we’re going to close this “bug” report”. In this case, maybe the optimization opportunity was not identified during the design phase, or it was just too hard to implement. The latter statement may sound ridiculous at first (how can such a basic rewrite be too hard?), but you have to be aware that the optimizer does not operate on the original query text, but on an internal representation that is based on mathematics and set theory. Rewrites that may be complex in the query text may be obvious in this representation, but the reverse can also be true – so I’m prepared to accept the comment that Andrew Richardson made on behalf of Microsoft to the above Connect item: that it would be fairly complicated for the Query Optimizer.

That does not mean I agree with the rest of Andrew’s comment. He suggests that this is a case where we should not rely on the optimizer, but rewrite our queries ourselves, especially since it’s such an easy rewrite in this case. Well, I would agree with that, except that: (a) this missed optimization opportunity is not documented, so how are developers supposed to know that they may need to reorder columns in a SELECT clause for optimal performance? (that is one of the reasons for this blog post); and (b) the behavior of the optimizer in this situation is not documented, so it can change at any time; I’d hate to rewrite all my queries and then find that the sysadmin just installed a patch and now the optimizer always starts with the last instead of the first OVER clause (or, worse, I don’t find it and just get all the bad performance right back).

However, Andrew is right in so far that, at this time, rewriting queries does consistently improve performance in all my tests. So at this time, rewriting does seem to be the right thing to do. Just keep in mind that you have to test all your queries, not only on your test server but also on your production hardware, and that you’ll have to repeat these tests on a regular basis (at least after each patch, CU, service pack, or other change).

The basic rewrite pattern is simple – for each query that uses OVER clauses with different ORDER BY subclauses as well as an ORDER BY clause on the query that matches one of the ORDER BY subclauses, make sure that the OVER clause that uses the matching ORDER BY comes last in the SELECT list. If you have a client that expects the columns in a particular order, the rewrite becomes a bit more complex – in that case, you have to use a CTE that includes the OVER clauses in the optimized order, and then you can reorder the columns in the query that references the CTE – as shown in this example:

WITH MyCTE
AS (SELECT   TerritoryID,
             Name,
             SalesLastYear,
             SalesYTD,
             RANK() OVER (ORDER BY SalesYTD) AS Rank2,
             RANK() OVER (ORDER BY SalesLastYear) AS Rank1
    FROM     Sales.SalesTerritory)
SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         Rank1,
         Rank2
FROM     MyCTE
ORDER BY SalesLastYear;

These rewrites are indeed the best option for the time being – but I still think that the optimizer should be improved to do these rewrites internally. So I decided to file a new item on Connect, this time labeling it as a suggestion instead of a bug. If you agree with me that this would be a great investment of Microsoft’s engineering time, then please add your vote to this item. (Or vote it down if you think I’m just being ridiculous). But don’t stop there! Microsoft knows me; they know I’m a geek who plays around with options like this and then runs into this issue. No real production databases were hurt during the production of this blog. And if I am the only one, then, frankly, I myself will say that they have better things to do with their engineering time. However, if I know that this affects real people, I can make a much stronger case to Microsoft for getting this fixed.

So – go out to your production servers and find if you use queries with this pattern (two OVER clauses with different ORDER BY and an ORDER BY on the final query), then check to see if you should rewrite them. And then report back – add a comment here or on the Connect item; share if this affected you, and how much performance you were able to gain as a result of the rewrite.

If Microsoft knows that their customers would actually benefit, they’ll be much more inclined to add this improvement to the optimizer then if it’s just about me, a geek moaning about an edge case that no one in the real world would ever run into.

Performance impact: Try/Catch blocks in SQLCLR – a follow up

Linchi Shea — Fri, 08 Jul 2011 03:48:00 GMT

My previous post showed a simple test that appears to suggest that you may experience significant performance degradation if multiple users are calling the same SQLCLR function at the same time and they are all catching a lot of exceptions.

However, it’s not clear whether that behavior is limited to SQLCLR or applies to .NET in general. To see if I would run into similar behavior, I wrote a simple C# program for a quick test. To simulate the concurrent exception-handling load, the test program spawns 10 background threads, each calling the following method nonstop (the complete program is listed at the end of this post):

static void TryCatch()

{

int c = 0;

for(int i = 0; i < 200000; i++)

{

try { c= i/c; } catch (Exception) { c = 1;}

}

Note that variable c is assigned value 0, thus forcing a divide-by-zero exception, and this exception is handled 200,000 times in a loop.

The elapsed time of the method is measured both when there is no additional background threads and when 10 additional threads are running.

On my old two-core 2GB PC workstation, the following output from the program is typical among many runs:

C:\junk>test2.exe

TryCatch() without any background thread = 15617968 ticks

TryCatch() without any background thread = 15559616 ticks

TryCatch() without any background thread = 15566064 ticks

TryCatch() without any background thread = 17472496 ticks

TryCatch() without any background thread = 15782952 ticks

Thread 0 Called TryCatch() 1000 times.

Thread 1 Called TryCatch() 1000 times.

Thread 2 Called TryCatch() 1000 times.

Thread 3 Called TryCatch() 1000 times.

Thread 4 Called TryCatch() 1000 times.

Thread 5 Called TryCatch() 1000 times.

Thread 6 Called TryCatch() 1000 times.

Thread 7 Called TryCatch() 1000 times.

Thread 8 Called TryCatch() 1000 times.

Thread 9 Called TryCatch() 1000 times.

TryCatch() with 10 background threads = 17498336 ticks

TryCatch() with 10 background threads = 17535984 ticks

TryCatch() with 10 background threads = 17664424 ticks

TryCatch() with 10 background threads = 17515200 ticks

TryCatch() with 10 background threads = 17465312 ticks

TryCatch() with 10 background threads = 17498432 ticks

TryCatch() with 10 background threads = 17508656 ticks

TryCatch() with 10 background threads = 17710856 ticks

At least for this test, the adverse concurrency impact that we saw with SQLCLR--and reported in the previous post--is not observed.

Although it’s not strictly an apple-to-apple comparison between this test without SQLCLR and that described in the previous post with SQLCLR, the end user experience is so different that it calls into question why SQLCLR does not seem to handle many concurrent exceptions as gracefully. I don’t have an answer.

I have absolutely no knowledge of how SQLCLR works internally, and can’t explain the concurrency behavior observed in the previous post.

By the way, when I set variable c to 1 in the TryCatch() method, thus avoiding the exception, its concurrency impact (or the lack of) did not change much, if at all.

Anyway, here is the test program. For the output presented above, the program was compiled with .NET Framework 3.5.

using System;

using System.Diagnostics;

using System.Threading;

public partial class Test

{

public static void Main()

{

Stopwatch stop_watch = new Stopwatch();

// warming up a bit

for(int i = 0; i < 5; i++)

{

TryCatch();

}

// measure the elaped time without any additional background threads

for(int i = 0; i < 5; i++)

{

stop_watch.Reset();

stop_watch.Start();

TryCatch();

stop_watch.Stop();

Console.WriteLine("TryCatch() without any background thread = {0} ticks",

stop_watch.ElapsedTicks);

Thread.Sleep(2000);

}

Thread[] user_threads = new Thread[10];

for(int i = 0; i < 10; i++)

{

user_threads[i] = new Thread(new ThreadStart(StartTryCatch));

user_threads[i].Name = i.ToString();

user_threads[i].IsBackground = true;

user_threads[i].Start();

Thread.Sleep(10);

}

Thread.Sleep(5000);

// now measure the elaped time again with 10 additiona threads running

for(int i = 0; i < 20; i++)

{

stop_watch.Reset();

stop_watch.Start();

TryCatch();

stop_watch.Stop();

Console.WriteLine("TryCatch() with 10 background threads = {0} ticks",

stop_watch.ElapsedTicks);

Thread.Sleep(2000);

}

// this will never be reached. You have to Cltr-C to stop the program

for (int i = 0; i < 10; i++)

{

if (user_threads[i] != null)

{

user_threads[i].Join();

}

static void StartTryCatch()

{

int i = 0;

while (true)

{

if (i % 1000 == 0)

{

Console.WriteLine("Thread {0} Called TryCatch() 1000 times.", Thread.CurrentThread.Name);

}

TryCatch();

Thread.Sleep(10);

i++;

}

static void TryCatch()

{

int c = 0;

for(int i = 0; i < 200000; i++)

{

try { c= i/c; } catch (Exception) { c = 1;}

}

};

Performance impact: too many try/catch blocks may be harmful in SQLCLR

Linchi Shea — Wed, 06 Jul 2011 04:40:00 GMT

If you have many try/catch blocks in your .NET code and your code execution actually passes through them, you should expect a performance hit. That’s intuitive and no surprise.

What is surprising is the extent to which you may experience severe performance degradation when multiple users are executing a piece of SQLCLR code (e.g. calling a SQLCLR table valued function) that contains many try/catch blocks. In other words, there may significant and adverse concurrency implication if you use too many try/catch blocks in your SQLCLR code.

Here is an example to prove it!

I have implemented two SQLCLR table-valued functions: tvf_GetDataWithTryCatch() and tvf_GetDataWithoutTryCatch(). These two functions return exactly the same resultset: 2000 rows with 100 columns of integers. The functions simulate the case where the integer values are out of bound (i.e. greater than the maximum value allowed for the integer datatype, which is SqlInt32.MaxValue in the attached C# code), and when that happens, the functions return NULL instead.

There are many ways to check whether a value is equal to or greater than SqlInt32.MaxValue. As the name implies, tvf_GetDataWithTryCatch() implements the check in a try block and returns NULL in the corresponding catch block. The tvf_GetDataWithoutTryCatch() function, on the other hand, implements the check with a C# conditional operator (?:).

When there is no background concurrent load

The tvf_GetDataWith TryCatch() function is expected to be slower than the tvf_GetDataWithoutTryCatch() function because of the inherent overhead with a try/catch block, and tests show that is indeed the case. On my rather antiquated PC with 2GB of physical memory, the following query

select * from dbo.[tvf_GetDataWithTryCatch]()

returns all the 2000 rows in about 8 seconds, whereas this query

select * from [dbo].[tvf_GetDataWithoutTryCatch]()

returns in about 1 second. Consistently, the function doing 200,000 try/catch’es is about eight times slower than the function doing 200,000 conditional operations. So far so good! No surprises!

When there is background concurrent load

When I have 10 users running tvf_GetDataWithoutTryCatch() nonstop against the same SQL Server 2008 instance in the background, the tvf_GetDataWithoutTryCatch() returns in about 2 seconds consistently. So running additional load of tvf_GetDataWithoutTryCatch() does slow it down a bit, but not a lot.

Now, when I have 10 users running tvf_GetDataWithTryCatch() nonstop in the background against the same SQL Server 2008 instance, the performance of tvf_GetDataWithTryCatch() degrades dramatically. This time it takes about 76 seconds to return all the 2000 rows. That is about 10 times slower than it is when there is no concurrent load. And it is about 38 times slower than tvf_GetDataWithoutTryCatch() under the same 10-user background concurrent load. Note that tvf_GetDataWithoutTryCatch() returns in about 2 seconds regardless whether the 10-user background load is calling tvf_GetDataWithTryCatch() or calling tvf_GetDataWithoutTryCatch().

If you think 38 times slower is bad, in real production environments I have seen far worse concrrency impact as a result of too many try/catch blocks.

With the attached C# code, you can run the tests yourself to see the behavior. Your results may differ in some details, but you should see a similar pattern. If not, post back a comment and let us know.

SQL Server 2008: Lock Escalation, INSERTs, and a Potential Bug

Adam Machanic — Fri, 30 Oct 2009 13:10:00 GMT

Lock escalation is a funny thing. I've found myself on numerous occasions waging war against its concurrency-sapping existence, and rarely have I found myself wishing that it would work more aggressively. But there is a time and place for everything, and yesterday I discovered that a major change has occurred with regard to lock escalation in SQL Server 2008.

The idea behind lock escalation is simple: Lock management is not free. According to BOL, each lock requires around 100 bytes, and that can really add up. So after a certain number of smaller locks have been taken, it sometimes makes sense to drop them in favor of one bigger lock. This can amount to huge savings, especially when dealing with millions of rows.

Lock escalation has been around for several SQL Server versions--as long as I've been using the product, as far as I'm aware (though I didn't even know of its existence for the first few years)--and we expect things to more or less work as they always have. So when things suddenly change, I tend to get annoyed. And how do we discover when something as internal as lock escalation changes? You would either have to be a complete internals geek--someone like Kalen--or notice a problem. When it comes to lock escalation, I fall into the latter category.

Here's what happened: I was doing a large data load of around 100 million rows, and checked the system at around the halfway point, using my Who is Active procedure. What I saw was that the load was humming along, but the monitoring software had 5 active sessions open, the longest of which had been running for 25 minutes. This was an immediate red flag--you never want your monitoring software taking 25 minutes to do its queries. Kind of defeats the point. These sessions were all hitting the sys.dm_tran_locks DMV, which unfortunately tends to not scale too well when a lot of locks get created, so I did a COUNT(*)--which took almost five minutes to run--and discovered that I was dealing with 58 million open locks.

A quick script and a few tests later and I figured out what the problem was: The lock escalation algorithm for INSERTs no longer works the same way in 2008 as it did in 2005. Unfortunately, the BOL entry is quite vague and a bit on the confusing side so I'm not certain whether this change was made on purpose. Based on my tests escalation still behaves for SELECTs identically--the issue seems to only be with INSERTs (and perhaps other DML operations--I haven't tested yet).

To see this on your end, try the following script in both SQL Server 2005 and SQL Server 2008:

USE tempdb
GO

CREATE TABLE x
(
    i INT NOT NULL PRIMARY KEY
)
GO

BEGIN TRAN
    INSERT x
    SELECT TOP (40000)
        ROW_NUMBER() OVER
        (
            ORDER BY (SELECT NULL)
        ) AS r
    FROM
        master..spt_values a,
        master..spt_values b
    ORDER BY
        r

    SELECT
        COUNT(*)
    FROM sys.dm_tran_locks
    WHERE
        request_session_id = @@SPID
ROLLBACK
GO

DROP TABLE x
GO

In SQL Server 2005, the final query will return either 1 or 2 (why either? I'm actually not certain; the second lock, when it is taken, references an object ID that doesn't seem to exist--a mystery for another day). In SQL Server 2008, on the other hand, the final query will return 40,066 or 40,067. 40,000 KEY locks, 65 PAGE locks, and either 1 or 2 OBJECT locks (again, the mystery object shows up).

So is this a major problem? If you're doing large data loads, I believe that it is. Again, according to BOL each lock takes up 100 bytes, so in my case when I looked I had 5.8 GB of my precious RAM taken up by lock data. I would have much preferred an escalation, as would have occurred in previous versions of SQL Server. There is a workaround: use a TABLOCK hint. But if you're dealing with third-party databases this may not be an option. And even SQL Server's own features, such as Change Data Capture, will not work with this fix.

I've filed a bug on Connect, and I hope you'll vote for it. I'm not certain that this is a bug, per se, or something done "by design", but I feel that the behavior is less than ideal and that the algorithms should be rolled back to their pre-SQL Server 2008 behavior.

Heaps, forwarding pointers, ALTER TABLE REBUILD and non-clustered indexes

TiborKaraszi — Fri, 28 Aug 2009 10:15:00 GMT

Let's start with some background on forwarding pointers:

Forwarding pointers in heaps can be a mess to get rid of. A forwarding pointer happens when you modify a row so that the row doesn't fit on the same page anymore. SQL Server moves the row to a new page and leaves a forwarding pointer where the old row used to be. This means that non-clustered indexes are not affected by the moving of the row - it can still point to the old location. So the modification is less costly than if all non-clustered indexes needed to reflect the row move. But having forwarding pointers can be bad for performance when you read data. A perhaps less known fact is that a scan over a table needs to follow forwarding pointers - essentially "jumping back and forth" a lot if you have many forwarding pointers. That can be really bad for performance (if you have table scans, of course). So, how do we get rid of forwarding pointers? Well, we can shrink the database file, but that is a little like curing a headache by jumping into a big meat grinder. We can export all data and re-import it - not very practical.

Greg Linwood reminded me that in SQL Server 2008, we can do ALTER TABLE ... REBUILD. Now, I knew about this option, and every time I tell about it in class I've been thinking silently for myself "I need to test whether this is a way to get rid of fwd pointers". (You generally talk about ALTER TABLE ... REBUILD when you talk about enabling compression on a heap.) So, doing a REBUILD of a table using ALTER TABLE sounds promising. Will it get rid of forwarding pointers? Will it also rebuild all non-clustered indexes?

Quick answer for those who don't care reading the TSQL script:
ALTER TABLE ... REBUILD will remove forwarding pointers, but for some strange reason it will also rebuild all non-clustered indexes on that table.

See the TSQL code below. It was adapted from a challenge by Linchi Shea to produce a data loading script resulting in worst performance (where I immediately thought of forwarding pointers). See for instance http://sqlblog.com/blogs/linchi_shea/archive/2009/06/07/t-sql-exercise-simplest-data-loading-script-to-produce-worst-query-performance-june-7th-update.aspx.

USE tempdb 
GO 

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'test') 
   DROP TABLE test 
GO 
CREATE TABLE test ( 
   x INT NOT NULL 
  ,x2 INT NOT NULL 
  ,y CHAR(10) NOT NULL DEFAULT ('') 
  ,z CHAR(10) NOT NULL DEFAULT('') 
) 
  
DECLARE @rows INT = 666666, @toKeep INT = 400000, @diff INT 
  
INSERT test (x, x2) 
SELECT TOP(@rows) 
       ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS r 
      ,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) % 10 AS s 
  FROM master..spt_values a CROSS JOIN master..spt_values b 
GO 
ALTER TABLE test ALTER COLUMN y CHAR(892) 
ALTER TABLE test ALTER COLUMN z CHAR(100) 
GO 
  
DECLARE @rows INT = 666666, @toKeep INT = 400000, @diff INT 
DELETE TOP(@rows - @toKeep)  
  FROM test WHERE x2 IN(2, 4, 6, 8) 
GO 

CREATE INDEX x1 ON test(x) 
CREATE INDEX x2 ON test(x2) 

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test'), NULL, NULL, 'DETAILED') 
--First  run (no nc indexes ): 387157 fwd records (out of 400000 rows), 96104 pages 
--Second run (two nc indexes): 387157 fwd records (out of 400000 rows), 96105 pages 

CHECKPOINT 
DBCC DROPCLEANBUFFERS 
DECLARE @t time = SYSDATETIME() 
ALTER TABLE test REBUILD 
SELECT DATEDIFF(ms, @t, CAST(SYSDATETIME() AS time)) 
--First run, no non-clustered indexes, three subsequent executions (ms): 19351, 20683, 20275 
--Second run, with two non-clustered indexes, three subsequent executions (ms): 31803, 35065, 37511 

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test'), NULL, NULL, 'DETAILED') 
--First  run, heap = "index" 0 (no nc indexes ): 0 fwd records, 50002 pages 
--Second run, heap = "index" 0 (two nc indexes): 0 fwd records, 50003 pages 
--But: avg_page_space_used_in_percent changes for the nc indexes after the rebuild. 
--That I take as nc indexes are also rebuilt.

Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE

Adam Machanic — Mon, 24 Aug 2009 19:31:00 GMT

It would be fair to say that there aren't a huge number of programmability features added to SQL Server 2008 that will have a great impact on most of our day-to-day lives as SQL Server developers. The release was clearly manageability themed rather than programmability themed, and I can count the most interesting and far-reaching developer enhancements on a few fingers: the DATE data type is a great time-saver, TVPs are fun (but not nearly the game changer they could have been), and Change Tracking will certainly help with caching scenarios.

MERGE is a feature that initially made this list for me. I was really looking forward to using it and thought it would make life easier for many ETL tasks requiring "upsert" functionality. My first impression of the feature was that people wouldn't love the syntax, and even now that I'm used to it I still think it leaves a bit to be desired. My second impression came when working on a large data load--hundreds of millions of rows--and I discovered that compared with the temp table approach I'd been using in the SQL Server 2005 version of the procedure, the MERGE performance was dismal. My first attempt showed a 20x decrease in performance (that's twenty times slower, not percent). While spending a day and a half messing with the thing and experimenting with various combinations I saw as much as a 100x decrease in performance at one point, but after losing much blood and sweat, and having shed many tears, I finally managed to tune it to the point where it was only twice as slow as the previous methodology. And that's when MERGE left my list of interesting features.

Open-minded kind of guy that I am, I didn't completely dismiss MERGE, and it's a good thing; it provides us with at least two features, independent of its ability to do "upsert", that not only put it back on my list of interesting features, but right at the top--as the number one most important developer enhancement in SQL Server 2008.

The first feature is the ability to do joined updates, without encountering the potential data quality issues that the UPDATE FROM syntax can cause. Hugo Kornelis has already done a great job of describing that ability, so I won't discuss it here. But please, read Hugo's post before writing another UPDATE FROM. You'll be happy you did.

The second enhancement is equally important, but I haven't seen coverage of it in any of the blogs or other sources I read (aside from Ward Pond mentioning it in passing). The MERGE statement's OUTPUT clause can do a special trick that other OUTPUT clauses cannot: it has the ability to output data not only from the "inserted" and "deleted" virtual tables, but also from either the source or destination table. This means that MERGE, even without its "upsert" capabilities, is surprisingly more powerful than the INSERT, UPDATE, or DELETE statements and can effect better logging, auditing, and more precise ETL processes. A true win-win.

But before we jump into MERGE's capabilities in this area, a bit of background is in order for those who might not be well-versed in all of the T-SQL enhancements that have been added in the past two versions of SQL Server.

Background #1: The OUTPUT Clause

Added in SQL Server 2005, the OUTPUT clause gives DML constructs--INSERT, UPDATE, DELETE, and now MERGE--the ability to redirect the results of the DML either back to the caller as a rowset or to a table. This is accomplished by leveraging the same idea that we have in triggers: "inserted" and "deleted" virtual tables. When inserting data you have access to a virtual table called "inserted", when deleting you have access to "deleted", and when updating you have access to both tables. This is great for getting back IDENTITY values, among other things:

DECLARE @x TABLE 
(
    x INT IDENTITY(1,1) NOT NULL, 
    y INT NOT NULL
);

INSERT @x
(
    y
)
OUTPUT 
    inserted.x, 
    inserted.y
SELECT 2 AS y
UNION ALL
SELECT 1;

UPDATE @x
SET
    y = y + 1
OUTPUT
    deleted.y AS old_y, 
    inserted.y AS new_y;
    
DELETE @x
OUTPUT
    deleted.x
WHERE
    y = 3;

In addition to simply getting back values, we can, as mentioned, also redirect them to a table:

DECLARE @x TABLE 
(
    x INT IDENTITY(1,1) NOT NULL, 
    y INT NOT NULL
);

DECLARE @y TABLE 
(
    x INT NOT NULL,
    y INT NOT NULL
);

INSERT @x
(
    y
)
OUTPUT 
    inserted.x, 
    inserted.y
INTO @y
SELECT 2 AS y
UNION ALL
SELECT 1;

SELECT 
    *
FROM @y;

As an aside, what I would really love to be able to do is reroute the output into one or more scalar variables when I know that only a single row will be affected by the operation. If you agree, please vote on the Connect item I've filed asking for that capability.

I suspect that if you are a SQL Server developer and can not readily find--or have not already found--uses for the OUTPUT clause then you're not getting challenged much in your day to day work, and it's time to go look for a new job. On the projects I've done in the past few years I have found it to be one of the top few enhancements; it has really made all sorts of tasks much easier to do in SQL Server 2005 and 2008 than they were in SQL Server 2000. And that brings us to our next stop, the MERGE statement...

Background #2: Basic MERGE Support for the OUTPUT Clause

Prior to the release of SQL Server 2008, Itzik Ben-Gan wrote a massive white paper covering the various T-SQL enhancements we could expect with the new version. Included therein is a short section about MERGE, including some information on the OUTPUT clause. Itzik's coverage is centered around the fact that MERGE can do something that could never be done before with a SQL Server DML statement: cause rows in a table to be inserted, updated, and deleted atomically, in a single statement.

To make life easier for those who want to write generic code, a special function was introduced that can be used in the OUTPUT clause with MERGE. This function, $action, outputs one of the following values to let you know which action affected tho row: INSERT, UPDATE, or DELETE. You can, of course, get this same functionality by looking at columns from both the inserted and deleted virtual tables and writing a CASE experssion, but it's much easier to simply use a built-in function. The following example shows how the $action function works in practice:

DECLARE @x TABLE 
(
    x INT IDENTITY(1,1) NOT NULL, 
    y INT NOT NULL
);

INSERT @x
(
    y
)
SELECT 2 AS y
UNION ALL
SELECT 1;

MERGE INTO @x AS tgt
USING 
(
    SELECT 2 AS y
    UNION ALL
    SELECT 3
) AS src ON
    src.y = tgt.y
WHEN MATCHED THEN
    UPDATE
        SET tgt.y = tgt.y + 1
WHEN NOT MATCHED THEN
    INSERT 
    (
        y
    )
    VALUES
    (
        src.y
    )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $action AS dml_action,
    inserted.x AS new_x,
    deleted.x AS old_x,
    inserted.y AS new_y,
    deleted.y AS old_y;

I should also mention here that SQL Server 2008 includes better support for the OUTPUT clause via a feature called "composable DML." This is not a MERGE enhancement--it works with any DML statement--but it's tangentially related and certainly of interest to people who might be reading this post.

The composable DML feature allows you to do something very strange indeed: treat DML statements that use the OUTPUT clause as derived tables, and use the result of the OUTPUT in an outer INSERT-SELECT. This is helpful when you need a bit more precision; for example, perhaps you want to populate a history table, but you're only interested in archiving the data that's been updated or deleted--the newly inserted data is already in the live table. To filter the results down using the composable DML feature you would do something similar to what is shown in the following example:

DECLARE @x TABLE 
(
    x INT IDENTITY(1,1) NOT NULL, 
    y INT NOT NULL
);

INSERT @x
(
    y
)
SELECT 2 AS y
UNION ALL
SELECT 1;

DECLARE @history TABLE
(
    x INT,
    y INT NOT NULL,
    dml_action VARCHAR(6) NOT NULL,
    action_date_time DATETIME2(7) NOT NULL
)

INSERT @history
(
    x,
    y,
    dml_action,
    action_date_time
)
SELECT
    x.old_x,
    x.old_y,
    x.dml_action,
    SYSDATETIME()
FROM
(
    MERGE INTO @x AS tgt
    USING 
    (
        SELECT 2 AS y
        UNION ALL
        SELECT 3
    ) AS src ON
        src.y = tgt.y
    WHEN MATCHED THEN
        UPDATE
            SET tgt.y = tgt.y + 1
    WHEN NOT MATCHED THEN
        INSERT 
        (
            y
        )
        VALUES
        (
            src.y
        )
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE
    OUTPUT
        $action AS dml_action,
        deleted.x AS old_x,
        deleted.y AS old_y
) AS x
WHERE
    x.dml_action IN ('UPDATE', 'DELETE');

SELECT *
FROM @history;

Note that as of SQL Server 2008 the results of the outer operation must be an INSERT-SELECT. That's a rather frustrating limitation, and I really hope the SQL Server team loosens the restrictions in the next version of the product.

Payload: MERGE, OUTPUT, and Access to Source and Destination Columns

Now that the groundwork has been laid--MERGE, OUTPUT, OUTPUT with MERGE (or is it the other way around?), and composable DML--we can finally get to the impetus behind this post, which is based on two factors. First of all, MERGE adds more power to the OUTPUT clause than just the $action function; it also allows us to access source and destination columns that we can't get to using INSERT, UPDATE, or DELETE. And second, MERGE can be used for standalone inserts, updates, or deletes. This means that it doesn't matter if we can't get to the data using the other three statements; we have MERGE and it can be those other statements for us when we need it to.

So what's so great about the ability to access source columns that you're not using as part of the data modification? Let's take a look at a simple example. Here's the scenario: You've been asked to load a flat file into the database. Each line of this flat file contains information about a person: a name, current salary, a list of up to three companies of employment, and a list of up to three childrens' first names, all in comma-delimited format. Here are the tables we want to load the data into:

USE tempdb
GO

CREATE TABLE people 
(
    name VARCHAR(100) NOT NULL,
    current_salary INT NOT NULL,
    person_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY
)
GO

CREATE TABLE previous_companies
(
    company_name VARCHAR(100) NOT NULL,
    person_id INT NOT NULL REFERENCES people (person_id),
    PRIMARY KEY (company_name, person_id)
)
GO

CREATE TABLE childrens_names
(
    child_name VARCHAR(100) NOT NULL,
    person_id INT NOT NULL REFERENCES people (person_id),
    PRIMARY KEY (child_name, person_id)
)
GO

And here is some sample data:

Joe Smith,110000,Microsoft,,,John,Sue,Ed
Jane Baker,50000,Oracle,Teradata,,George,,
Tom Jones,0,,,,Anne,Cathy,
Joe Smith,100000,Microsoft,,,Steve,,
Kate Davis,150000,Google,Oracle,Microsoft,Don,Amy,

After taking a quick glance at this data I hope you can see right away that we have some issues. Looking at the first and third lines you can see that we have duplicate names--Joe Smith--and therefore the name is not a good candidate for a primary key. Salary, with this small set, will work fine if combined with name, but of course it's well within the realm of possibility that two people can have the same salary, especially when working for the same company as both of our Joe Smiths do. We could add childrens' names to the mix, but of course there is even no guarantee that two people with the same name, working for the same company and making the same salary, won't have a child with the same name. I'm sure it's happened before and will happen again.

We've been reassured that each line really does represent a unique person, so we have to make it work. No problem once we're in the database--we can deal with the issue using the surrogate key on the "people" table--but how should we handle the data on the way in?

Assuming that we're doing all of this processing in T-SQL, the logical process is straightforward: we're going to have to insert the names and salaries into the "people" table, get back one person_id per name, and then do the inserts first into previous_companies and then into childrens_names. Again, this wouldn't be a big deal with the small sample data set here--we could, after inserting the names and salaries, join those back to the input data set (assume that it's in a temp table at this point) in order to map back the surrogate keys. And that would work well enough; but as more columns are added this will get trickier and trickier, and more prone to the introduction of a nasty bug that will cause the keys to be mismapped. There must be a better way--and thanks to MERGE, there is.

The solution to this problem requires a couple of steps. First create a surrogate key on the incoming data, separate from the surrogate key that will eventually be assigned when rows are inserted into the "people" table:

CREATE TABLE #data
(
    name VARCHAR(100),
    salary INT,
    company1 VARCHAR(100),
    company2 VARCHAR(100),
    company3 VARCHAR(100),
    child1 VARCHAR(100),
    child2 VARCHAR(100),
    child3 VARCHAR(100),
    input_surrogate INT IDENTITY(1,1) NOT NULL PRIMARY KEY
);

INSERT #data
VALUES
    ('Joe Smith',110000,'Microsoft',NULL,NULL,'John','Sue','Ed'),
    ('Jane Baker',50000,'Oracle','Teradata',NULL,'George',NULL,NULL),
    ('Tom Jones',0,NULL,NULL,NULL,'Anne','Cathy',NULL),
    ('Joe Smith',100000,'Microsoft',NULL,NULL,'Steve',NULL,NULL),
    ('Kate Davis',150000,'Google','Oracle','Microsoft','Don','Amy',NULL);

Once we have this input_surrogate populated, we need to be able to access the values in order to match them to the surrogate generated during the insert to the "people" table. And this is where the power of MERGE kicks in:

CREATE TABLE #surrogate_map
(
    input_surrogate INT NOT NULL,
    person_id INT NOT NULL
);

MERGE INTO people AS tgt
USING #data AS src ON
    1=0 --Never match
WHEN NOT MATCHED THEN
    INSERT
    (
        name,
        current_salary
    )
    VALUES
    (
        src.name,
        src.salary
    )
OUTPUT
    src.input_surrogate,
    inserted.person_id
INTO #surrogate_map;

In this example I've forced the MERGE statement to behave as an INSERT statement by using a predicate of 1=0; since this will never match on anything, the WHEN NOT MATCHED clause will fire for every row, thereby causing that row to be inserted.

The key element on the OUTPUT side of the equation is the seemingly-innocent third-to-last line. I'm using "src.input_surrogate"--a column that is not used as part of the INSERT--as part of the OUTPUT clause. This little feature enables us to reliably map the person_id surrogate to the input_surrogate without playing any games or worrying about complex logic or coding bugs. Simply stated, it just works. And the rest of the ETL process, from here on out, is simple enough, thanks to this mapping, that I won't have to bore you with it.

This OUTPUT feature of MERGE is not only useful for INSERTs and mapping of keys. Any time you're doing DML based on joins between tables, you might consider using this feature to help with logging. For example, you might want to delete some rows from one table by matching the rows with another table based on a range predicate. Want to know which rows matched and caused deletions to occur? No problem:

MERGE INTO people AS tgt
USING 
(
    SELECT 100000 AS salary
    UNION ALL
    SELECT 50000 AS salary
) AS src ON
    tgt.current_salary BETWEEN src.salary AND src.salary * 1.10
WHEN MATCHED THEN
    DELETE
OUTPUT
    src.salary AS source_salary,
    deleted.person_id,
    deleted.current_salary;

Prior to MERGE, properly doing this kind of thing would have required much more complex code: Most likely you would have to do the work in at least two steps, first finding potential matches and storing them in a temporary table, then going back to do the DELETE. MERGE and OUTPUT enables all of this work to be done in a single statement, and while this may or may not be more efficient (see my notes at the beginning of this post) it is definitely more elegant--and in most cases maintainability is more important when performance differences aren't extreme.

There are a number of takeaways here. First of all, MERGE used as an "upsert" tool may or may not be the best choice. In my experience performance differences will be extreme enough to warrant not using it for that purpose. But I encourage you to give it a try and not rely on my experiences as a guide; and after testing, I would definitely like to hear what kinds of results you're getting, especially if you're migrating legacy code and testing both methods. Second, MERGE is not only an "upsert" tool. It can be used for INSERTs and DELETEs, and as Hugo showed in the post I linked to, it is the number one choice for UPDATEs when you need to do the update based on a join. Third, thanks to the power of the OUTPUT clause, MERGE is incredibly useful in the areas of ETL and logging--and probably many others that I haven't considered yet.

I'll conclude by saying that a lot of times the true power of a new feature can be subtle and difficult to identify without spending some time thinking and experimenting. As end-users we're often quick to dismiss something we don't immediately understand and while that's probably not entirely unfair in a lot of cases, in this one it was. I initially cast MERGE aside but thanks to being somewhat open-minded I now realize that it is actually quite powerful when used in the correct context. I have already used it several times in situations similar to those described in this post and look forward to a long relationship with this new DML construct.

T-SQL Exercise: Simplest data-loading script to produce worst query performance – June 7th Update

Linchi Shea — Mon, 08 Jun 2009 01:31:00 GMT

All the previously posted results (May 25^th and May 29^th) on this exercise were obtained with query parallelism disabled (i.e. the sp_configure ‘max degree of parallelism’ option was set to 1).

Since the following test query is sensitive to query parallelism, we need to see what impact query parallelism may have.

DBCC DROPCLEANBUFFERS

SELECT COUNT(*) FROM dbo.test;

It turns out that with parallelism the test query exhibited significantly different performance characteristics with the three data sets we have been using as test vehicles than without parallelism. And again, the nature of the storage system proved to be a significant confounding factor. The following table shows the results with query parallelism on both the internal drive (the C: drive) and the drive presented from a departmental level disk array (the E: drive), the same two drives used in the May 25^th update and the May 29^th update.

Drive	Test Run	Elapsed Time (second)
Internal (C:)	Adam’s script – test run 1	90
	Adam’s script – test run 2	88
	Adam’s script – test run 3	87
	Tibor’s script – test run 1	217
	Tibor’s script – test run 2	219
	Tibor’s script – test run 3	226
	Linchi’s script – test run 1	325
	Linchi’s script – test run 2	324
	Linchi’s script – test run 3	324
Disk array (E:)	Adam’s script – test run 1	74
	Adam’s script – test run 2	75
	Adam’s script – test run 3	72
	Tibor’s script – test run 1	93
	Tibor’s script – test run 2	97
	Tibor’s script – test run 3	89
	Linchi’s script – test run 1	51
	Linchi’s script – test run 2	51
	Linchi’s script – test run 3	51

Note the dramatic change in the relative impact of the data sets on the test query when the storage performance and query parallelism were added to the mix. In particular, note how the test query was able to perform much faster on the faster drive (drive E:) with some data sets.

I just want to present the data points in this post, and will dive in a bit in the next follow up.

Performance impact: thread mode vs. fiber mode

Linchi Shea — Mon, 04 May 2009 19:56:00 GMT

SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber. Switching among fibers is handled in the user mode with the objective of reducing the cost of calling into the kernel for context switches.

Whenever fiber mode is discussed, Microsoft has always piled a huge load of ominous warnings, generally related to the potential stability issues that may come up with fiber mode. And because of these warnings, fiber mode is rarely, if ever, used in real world production environments. Staying away from fiber mode is of course the right thing to do because many of the ‘external SQL Server components’ that may not be fiber-mode friendly are usually essential in a real production environment.

If you run a bare minimum SQL Server instance (e.g. no XML, no mail, no linked servers), can you expect performance gain from fiber mode? Generally speaking, you may not see any performance gain. But there are cases you can see performance gains.

I was curious whether I could see any performance gain at all on a low-end server such as a four-core HP ProLiant DL365 G1 with 4GB of RAM. Note that this is a rather old and obsolete server model.

It turns out that under certain workloads, fiber mode can give you a huge performance boost. For my tests, I populated a TPC-C like database with about 9GB of data, and ran read-only workloads against the database (with the two read-only transactions in TPC-C in a 50/50 transaction mix). I configured each client to wait for 20 milliseconds before submitting the next query. The following chart shows the difference in transaction throughput at various load levels (in terms of the number of simulated concurrent users running queries against the server) between thread mode and fiber mode. The data points were obtained on a SQL Server 2008 instance (Enterprise x64 Edition and the build level was 10.0.1600).

If we look at the cases for 400 and 600 users for example, the performance gain from thread mode to fiber mode was ~ 40% (with the thread-mode transaction throughput as the baseline). This was a huge gain. I ran the tests in various random orders and repeated the tests at various random times. The results were consistent and reproducible.

I was surprised to see such a difference myself for two reasons. One is that I didn’t see much of a performance difference between thread mode and fiber mode when I ran the same workload in the same test environment without the 20ms wait time. I observed only about 5% consistent but marginal gain, switching from thread mode to fiber mode when the next query was submitted immediately after the previous one was finished. I can’t explain why the wait time made the difference.

The other reason I was surprised is that I had never seen any performance gain from enabling lightweight pooling or fiber mode before, though I had never tried this specific workload and had never tried this on SQL Server 2008.

I’m not sure if there is any practical value in this post because stability concerns will and should always trump any potential performance gain, especially when that performance gain is rather elusive. But at least it’s good to report that I have seen some real performance gain with fiber mode instead of just hearing somebody else talking about it.

Performance impact: a large number of virtual log files – Part II

Linchi Shea — Thu, 12 Feb 2009 05:40:00 GMT

In my previous post on the performance impact of having a large number of virtual log files (VLFs) in a transaction log, I showed that a large number of VLFs could be very bad for SQL Server 2008 performance. The test workloads were large batch delete, update, and insert. In other words, they were single monolithic transactions that generated a large number of transaction records. Intuitively, these large transactions would cause SQL Server to cross many VLF boundaries and incur the penalty of crossing these boundaries.

The question then became: how about workloads that submit small transactions such as common OLTP workloads?

As it turns out, a typical OLTP workload may not experience any performance degradation at all, even when the number of VLFs is into the 20,000 range.

The supporting evidence lies in the test results of a TPC-C like benchmark that I ran against the same test databases dbVLF_small and dbVLF_large, whose setup was described in the previous post. These two test databases were configured identically except that one had 16 VLFs in its transaction log and the other had 20,000 VLFs in its transaction log. The test workload used the standard TPC-C read-write transaction mix, which means that the workload was relatively write heavy.

The test environment was the same as described in the previous post. The test database was sized for 100 TPC-C warehouses, which translates into ~9GB for all the data and indexes.

But I’ll skip any details regarding the test setup and test data, and go straight to present the test results, which are highlighted in the following chart:

For this OLTP workload, the number of VLFs in the transaction log did not make any significant difference. This, I guess, is good news. Did I hear a sigh of relief?

Performance impact: a large number of virtual log files – Part I

Linchi Shea — Mon, 09 Feb 2009 17:13:00 GMT

It is generally known that having a large number of virtual log files (VLFs) in a database transaction log is undesirable. A blog post by the Microsoft support team in Stockholm showed that a large number of virtual log files could seriously lengthen the database recovery time. Tony Rogerson also reported that lots of virtual log files were bad for triggers and log backups on SQL Server 2000.

This blog post explores two questions:

Is a large number of VLFs in a transaction log still a significant performance factor in SQL Server 2008?
Does a large number of VLFs have an adversely impact on application-related operations such as INSERT, UPDATE, and DELETE?

As I’ll show in the rest of this post, the answers to both questions are yes. Let me first describe the test, and then present the test results.

The test databases

I created two databases: dbVLF_small and dbVLF_large on the same SQL Server 2008 instance (Enterprise x64 Edition and the build number was 10.0.1600). The host was an HP DL585 with four single core 2.2GHz Opteron processors and 32GB of RAM (28GB of which was allocated to the buffer pool). The following table highlights the key properties of these two test databases:

Property	Property value
	dbVLF_small	dbVLF_large
Data file size	20GB	20GB
Data file location	E: drive	E: drive
Log file size	10GB	10GB
Log file location	F: drive	F: drive
Recovery mode	Full	Full
Number of VLFs	16	20,000

As you see, the only masterial difference between these two databases was the number of virtual log files. The dbVLF_small database had 16 VLFs (a small number of VLFs), and the dbVLF_large database had 20,000 VLFs (a large number of VLFs). It’s probably a bit extreme for a database to have 20,000 virtual log files. But it’s not completely unreasonable if you let your database log file to grow in small increments. Note that my intention is to highlight the impact in case if your database does end up with a large number of VLFs.

The test table

An identical table, called customer, was created in both databases. The DDL for the customer table is as follows:

CREATE TABLE customer

(

c_id int,

c_d_id tinyint,

c_w_id int,

c_first char(16),

c_middle char(2),

c_last char(16),

c_street_1 char(20),

c_street_2 char(20),

c_city char(20),

c_state char(2),

c_zip char(9),

c_phone char(16),

c_since datetime,

c_credit char(2),

c_credit_lim numeric(12,2),

c_discount numeric(4,4),

c_balance numeric(12,2),

c_ytd_payment numeric(12,2),

c_payment_cnt smallint,

c_delivery_cnt smallint,

c_data char(500)

)

CREATE UNIQUE CLUSTERED INDEX ci_customer on customer(c_w_id, c_d_id, c_id)

This was the customer table used in the TPC-C benchmark.

The test data

3,000,000 rows—with data as specified for the TPC-C customer table— were bulk copied into the customer table in both databases. The index was then rebuilt, and sp_spaceused showed the table size to be about 2GB.

The tests

Three simple tests were run against the customer table in each database. The common characteristics of these three tests were that they were a single large transaction.

The insert test

SELECT * INTO customer_tmp
FROM customer
WHERE 1=2;
go
INSERT customer_tmp
SELECT * FROM customer;

The update test

UPDATE customer
SET c_data = lower(c_data);

The delete test

DELETE FROM customer;

The test results

The following table summarizes the test results:

Test	Elapsed time (sec) in database: dbVLF_small	Elapsed time (sec) in database: dbVLF_large
The insert test	281	1069
The update test	174	1554
The delete test	193	936

The following chart gives a better visual representation of the same results:

In all the three cases, it was significantly (should I say dramatically) slower to perform these common SQL operations when the database had 20,000 VLFs than when the database had 16 VLFs. The insert was about four times slower. The update was about eight times slower, and the delete was about five times slower.

So the test results confirm that it is definitely a significant performance issue in SQL Server 2008 to have a large number of virtual log files in a transaction log. Moreover, the test results show that the problem can be felt by common SQL operations such as insert, update, and delete in large batch processes.

What does this all mean? First, pay attention to the often-repeated best practice advice: do not grow your database log files in small increments (because that is often how you introduce a large number of VLFs into your transaction log). For instance, if you know you’ll need 10GB for your log, it’s best to allocate 10GB in one fell swoop. Secondly, if you do find a large number of VLFs in your transaction log, it may be worth the maintenance effort to reduce the number. You can reduce the number of VLFs by first shrinking the log file to a minimum size, and then allocating the required space in one ALTER DATABASE statement. (SQL Server MVP Tibor Karaszi has a thorough discussion on shrinking log files at his site)

------

By the way, the 20,000 virtual log files were generated as follows. First, create the database in the full recovery mode with a 512KB initial log file size and 512KB file growth increment. Then, backup the database, and create a dummy table with a single char(8000) column. Finally, inserting data into this table in a loop until the log file size reaches 5000MB. This is essentially the same procedure used by the Microsoft support team in Stockholm in their post.

To prepare for the insert, update, and delete tests, backup the log, and expand the log file to 10GB.