Search results matching tags 'Best Practices' and 'SQL Server 2008'

SQL Server 2008 R2 Best Practices Analyzer

AaronBertrand — Wed, 25 Aug 2010 21:00:00 GMT

Maybe I'm a little late to the party, but I thought I would share with you the most recent release of Microsoft's Best Practices Analyzer for SQL Server 2008 R2 (you can also read the original blog announcement from CSS). Let me be clear: this tool is not just for people who are already on SQL Server 2008 R2. It will also happily analyze your SQL Server 2008 instances, so don't be fooled by the label.

That said, getting the thing installed was an exercise in patience. Like most people, I clicked the download link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591

And I was off to the races. Or so I thought. I still haven't developed a healthy habit of READING before CLICKING. The first time I ran the installer, I got this complaint about not having the Baseline Configuration Analyzer installed (I'm typing out the error message so that users experiencing the same thing can find this post, and I'm showing a screen shot to show how asinine it is to put a URL in a dialog but not have the ability to click or copy it):

Microsoft Baseline Configuration Analyzer 2.0 is not installed. Please install it from http://www.microsoft.com/downloads/details.aspx?FamilyID=1b6e9026-f505-403e-84c3-a5dea704ec67

To help out a bit, here is the link to download the Configuration Analyzer (which, I will admit, was much easier to obtain from a Google search than by transcribing the link from the dialog) :

http://www.microsoft.com/downloads/details.aspx?FamilyID=1B6E9026-F505-403E-84C3-A5DEA704EC67

If you're not on Windows 7 or Windows Server 2008 R2, you have the potential to receive error messages about missing PowerShell 2.0, as follows:

Microsoft Baseline Configuration Analyzer 2.0 requires PowerShell 2.0 to be pre-installed on the system.

No worries, you can download PowerShell 2.0 as part of Windows Update:

Or if you don't want to go through the hassle of Windows Update, you can also get it by installing the Windows Management Framework Core Package individually:

http://support.microsoft.com/?kbid=968930

(Now why, since PowerShell 2.0 is only a 6 MB download, it couldn't be published as part of the Configuration Analyzer setup, or at least to go fetch it dynamically during installation, I have no idea.)

I got a little bit further into the installation once I had the Configuration Analyzer installed. But then, I discovered in a very round-about way, that there is an issue with the installer when you try to put the BPA on a machine that is not connected to a domain (e.g. just about every virtual machine I own). When it tried to run the PowerShell commands during setup, I saw a PS window briefly, along with some scary-looking red text, and then it disappeared and setup returned this error message (again typed out for SEO goodness):

There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor.

Which is great to know that it wasn't BPA's fault, but something else's. But would it have been that hard to consume the error message that obviously came back from PowerShell? Or better yet, return an error message like this:

Google "BPA 2.0 error powershell"

Because as it turns out, there is a very easy fix to this, and it was the first hit for the search above. If you want to install BPA onto a machine that is not domain-savvy, instead of double-clicking the MSI file, run this command line (replacing <path> with the folder where the MSI file is located, and changing <platform> either to 32 or 64):

msiexec /i <path>\SQL2008R2BPA_Setup<platform>.msi SKIPCA=1

(Thanks to Adam Saxton for pointing this out on the CSS SQL blog.)

Once I got the BPA running, it did quickly point out several issues I was aware of (including bad partition alignment on a non-essential drive, some databases that were intentionally offline, and the fact that the error logs have a lot of history), as well as a couple of new issues:

The one I've expanded is one that Bob Ward talked about in a blog post yesterday as one of the key reasons to use the new BPA:

http://blogs.msdn.com/b/psssql/archive/2010/08/24/why-use-sql-server-2008-r2-bpa-case-1-missing-updates.aspx

Obviously it finds a bunch of things that you may already have alerting for, and probably some things that you don't. In any case, this can be a very quick way to assess a SQL Server instance without having to rummage around for all of this information manually, and even with the stumbling you may have getting it configured, it should pay off rather quickly. There are plenty of things it doesn't look for, and you can get that information from other places as well - for example, check out the BLITZ! takeover script from Brent Ozar (blog | twitter):

http://www.brentozar.com/sql/blitz-minute-sql-server-takeovers/

Anyway, I haven't really offered any input that you can't see elsewhere, but I hope it is useful nonetheless.

EDIT: If you are running on Windows Server 2003

SQL Server challenge: show me trace flag 1118 is significant

Linchi Shea — Thu, 01 Oct 2009 15:53:00 GMT

Here is a SQL Server challenge as I have failed so far:

Describe a reproducible workload that would see significant throughput improvement when trace flag 1118 is enabled.

I have seen a lot of chatters, anecdotes, and stories on the impact of trace flag 1118 on reducing tempdb contentions. But I have not seen any concrete evidence or test cases that actually show its impact on SQL Server 2005 or SQL Server 2008. The empiricist in me just itches for some solid data points.

I have also talked about the impact of trace flag 1118 in two posts here (1, 2). In either case, I was not able to find very strong evidence that T1118 was significantly useful in alleviating the tempdb contention in SQL Server 2005. Recently, there have been some more discussions on T1118. Many questions on T1118 were asked and answered in various SQL Server forums, newsgroups, websites, and lists. Two blog posts, in particular, caught my attention: Bob Dorr’s and Paul Randal’s. They approach the topic from different angles, but both are worth reading.

The consensus—not just from Dorr's and Randal's posts, but from the SQL Server community in general—seems to be that while in SQL Server 2005 and SQL Server 2008 trace flag T1118 is no longer that useful because of the improvement made to the tempdb storage allocation internals, but it may still be useful in some cases.

The trouble is that I have not seen any specific cases that show the trace flag to make a huge difference. Since I can’t seem to be able to generate a test workload that shows T1118 to make a significant difference, I have been on a vigilant lookout for any cases others may wish to share.

To that end, what Bob Dorr mentioned in his post especially piqued my interest. He said:

“For example, I am working with a customer on a TEMPDB PFS page contention. The stored procedures heavily use "select into ... #tmp where 1=2" This syntax is not moving rows because of the where clause limitation but is creating tables. This will place pressure on the allocation mechanisms. Use of trace flag 1118 may still be required to force uniform allocation behavior. ”

So I decided to give it another shot on SQL Server 2008 with two test workloads described below.

Test workloads

Two versions of a single stored procedure were used to create tempdb contentions:

-- Version 1

CREATE PROC dbo.spT1118_v1

SET NOCOUNT ON

select * into #tmp1

from sysobjects

where 1=2

select * into #tmp2

from sysobjects

where 1=2

drop table #tmp1

drop table #tmp2

This proc does nothing but to create two temp tables and then drop them. In particular, no data is inserted into either of the two temp tables.

The second version of the proc included inserting data:

-- Version 2

CREATE PROC dbo.spT1118_v2

SET NOCOUNT ON

select * into #tmp1

from sysobjects

where 1=2

select * into #tmp2

from sysobjects

where 1=2

insert #tmp1

select * from sysobjects

insert #tmp2

select * from sysobjects

drop table #tmp1

drop table #tmp2

Tests

Two tests were conducted: Test I with spT1118_v1 and Test II with spT1118_v2.

In each test, I fired the respective proc against a SQL Server 2008 instance (10.0.1600) from a multi-threaded C# program with each thread simulating a user, making calls to proc spT1118_v1 in Test I and making calls to spT1118_v2 in test II.. No wait time was included between two consecutive calls. Throughout was measured in terms of the number of calls to proc spT1118_v1 (or proc spT1118_v2) that could be processed per second on the SQL Server 2008 instance.

The test server was a DB365 with four 2.8GHz cores (two dual-core AMD Opteron 2220 sockets) and with 4GB of RAM running 32-bit Windows Server 2003 SP2 and 32-bit SQL Server 2008 (10.0.1600).

Now for the tests to be informative, the workload levels should range from light to heavy so that we don’t end up drawing any conclusion that may be applicable to, for instance, lighter load only. In this particular test environment, through experiments, I found that 6 users (i.e. 6 client threads) proved to be too light, 8 ~ 10 users produced the best transaction throughput (i.e. the highest number of calls to the proc could be made), and the throughout started to drop as the number of users continued to increase.

Consequently, the number of users tested in both Test I and Test II includes 4, 8, 10, 12, 14, 16, 18, and 20.

In addition, looking at various wait stats and wait types on the spids from the C# test client program that were calling the spT1118_v1 proc or the spT1118_v2 proc, I found that with around 8 or fewer users, waits on tempdb internal resources--especially PFS (Page Free Space) pages--were light. With around 10 users, waits on PFS pages were frequently observed, but not severe enough to reduce the throughout. Beyond 14 users, massive waits on PFS pages were consistently observed, users were blocking each other on these tempdb internal structures, and the throughput dropped significantly.

In other words, whether or not actual data was being inserted into the temp tables did not change the contention on the PFS pages.

Test results

The charts below summarize the test results. Chart I is for test I and Chart II is for Test II.

From these two charts, you may be able to argue that there was a few percentage of a difference in throughout whether or not trace flag 1118 was used. At least, in Test I where no data was inserted into the temp tables, we see that the maximum throughput difference was ~7%.

But taking into consideration margin of error, you can’t really conclude the difference was significant, especially not across all the load levels. With the users heavily waiting on the tempdb PFS pages, you could argue that the two charts actually suggest that enabling T1118 produced lower throughput than disabling T1118 in some cases, though again the difference was not significant at all.

Just want to mention that in addition to the tests already described and whose results are presented in the above two charts, I checked many other cases including more temp tables and/or large amount of data inserted into the temp tables in the test procedures. In all the cases I saw significant tempdb contentions, but in no case did I see any significant improvement when T1118 was enabled.

If T1118 is indeed useful in some cases as suggested by Microsoft, it would be nice that Microsoft publishes a test workload that clearly shows T1118 to make a significant difference. And given the specific and narrow focus of this trace flag, such a test workload should not be difficult to come by. Without seeing any such concrete proof, enabling T1118 on SQL Server 2008 remains to be one of those fuzzy feel-good and just-in-case best practices. Since there does not seem to be any downside risk to enable it and anyone else seems to believe there is potential benefit, why not just enable it?

I’m not going to argue against the desire to be on the safe side. But until I see specific evidence, it doesn’t make me feel any better about this fuzzy feel-good just-in-case best practice. So far, I have not been able to find any specific evidence, and that takes us back to the SQL Server challenge at the beginning at this post.

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.

Let's deprecate UPDATE FROM!

Hugo Kornelis — Sun, 09 Mar 2008 23:38:00 GMT

I guess that many people using UPDATE … FROM on a daily basis do so without being aware that they are violating all SQL standards.

All versions of the ANSI SQL standard that I checked agree that an UPDATE statement has three clauses – the UPDATE clause, naming the table to be updated; the SET clause, specifying the columns to change and their new values; and the optional WHERE clause to filter the rows to be updated. No FROM or JOIN – if you need data from a different table, use a subquery in the SET clause. The optional FROM and JOIN clauses were added by Microsoft, as an extension to the standard syntax (and just to make out lives more interesting, they invented different variations of the syntax for SQL Server and for Access). So when you are in the habit of using them, be prepared to review all your UPDATE statements when moving to Oracle, DB2, Sybase, MySQL, or even a different Microsoft database!

Standards? Bah, who cares?

Well, some do. Me for instance – I will never use proprietary syntax if I know a standard alternative, expect if using the latter has severe negative consequences. And maybe you will, one day, when your boss comes back from the golf course with the great news that he managed to convince a colleague (who just happens to work in an Oracle shop) to buy a copy of your company’s application instead of some off-the-shelf product. Or when there’s a great job opportunity for someone with cross platform skills. Or when you are asked to help out this new colleague with 10+ years of DB2 experience. One of the lesser known side effects of Murphy’s Law is that those who least expect having to move their database to another platform, will.

But even if you really don’t care about portability, there are other reasons to be wary of using UPDATE FROM. In fact, the most important reason why I dislike UPDATE FROM is not that it’s non-standard, but that it is just too easy to make mistakes with.

Correctness? Bah, who cares?

Well, most do. That’s why we test.

If I mess up the join criteria in a SELECT query so that too many rows from the second table match, I’ll see it as soon as I test, because I get more rows back then expected. If I mess up the subquery criteria in an ANSI standard UPDATE query in a similar way, I see it even sooner, because SQL Server will return an error if the subquery returns more than a single value. But with the proprietary UPDATE FROM syntax, I can mess up the join and never notice – SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. And there is no way of knowing which row that will be, since that depends in the query execution plan that happens to be chosen. A worst case scenario would be one where the execution plan just happens to result in the expected outcome during all tests on the single-processor development server – and then, after deployment to the four-way dual-core production server, our precious data suddenly hits the fan…

That’s all?

Well, almost. There’s one more thing. Probably not something you’ll run into on a daily base, but good to know nonetheless. If the target of the update happens to be a view instead of a base table, and there is an INSTEAD OF UPDATE trigger defined for the view, the UPDATE will fail with this error message:

Msg 414, Level 16, State 1, Line 1

UPDATE is not allowed because the statement updates view "v1" which participates in a join and has an INSTEAD OF UPDATE trigger.

Of course, most people will never run into this. But I did have the misfortune of doing so once – unfortunately, I discovered this limitation after rewriting several hundred ANSI standard UPDATE statements to the equivalent UPDATE FROM, and having to convert them all back after as much as a single test…

And that’s why you want to deprecate UPDATE FROM?

Well, no. The view with INSTEAD OF UPDATE trigger won’t affect many people. And the possibility of error can be somewhat thwarted by making sure (and double-checking) to always include all columns of the primary key (or a unique constraint) of the source table. So we’re back to the more principle point of avoiding proprietary syntax if there is an ANSI standard alternative with no or limited negative consequences. And in the case of UPDATE FROM, there are some cases where the standard syntax just doesn’t cut it.

One such scenario is when a file is read in periodically with updated information that has to be pushed into the main table. The code below sets up a simplified example of this – a table Customers, with SSN as its primary key, that stores address and lots of other information, and a table Moved, which is the staging table containing the contents of a file received from a third party listing new address for people who recently moved. I have also included the code to preload the tables with some mocked up data – the Customers table has 10,000 rows, and the Moved table has 3,000 rows, 1,000 of which match an existing row in the Customers table. The others don’t – those people are apparently not our customers.

CREATE TABLE Customers

(SSN char(9) NOT NULL,

Street varchar(40) NOT NULL,

HouseNo int NOT NULL,

City varchar(40) NOT NULL,

LotsOfOtherInfo char(250) NOT NULL DEFAULT (''),

PRIMARY KEY (SSN),

CHECK (SSN NOT LIKE '%[^0-9]%')

);

CREATE TABLE Moved

(SSN char(9) NOT NULL,

Street varchar(40) NOT NULL,

HouseNo int NOT NULL,

City varchar(40) NOT NULL,

PRIMARY KEY (SSN),

CHECK (SSN NOT LIKE '%[^0-9]%')

);

INSERT INTO Customers(SSN, Street, HouseNo, City)

SELECT RIGHT(Number+1000000000,9), 'Street ' + CAST(Number AS varchar(10)),

Number, 'City ' + CAST(Number AS varchar(10))

FROM dbo.Numbers

WHERE Number BETWEEN 1 AND 30000

AND Number % 3 = 0;

INSERT INTO Moved(SSN, Street, HouseNo, City)

SELECT RIGHT(Number+1000000000,9), 'New street ' + CAST(Number AS varchar(10)),

Number * 2, 'New city ' + CAST(Number AS varchar(10))

FROM dbo.Numbers

WHERE Number BETWEEN 1 AND 30000

AND Number % 10 = 0;

Since ANSI-standard SQL does not allow a join to be used in the UPDATE statement, we’ll have to use subqueries to find the new information, and to find the rows that need to be updated, resulting in this query:

UPDATE Customers

SET Street = (SELECT Street

FROM Moved AS m

WHERE m.SSN = Customers.SSN),

HouseNo = (SELECT HouseNo

FROM Moved AS m

WHERE m.SSN = Customers.SSN),

City = (SELECT City

FROM Moved AS m

WHERE m.SSN = Customers.SSN)

WHERE EXISTS (SELECT *

FROM Moved AS m

WHERE m.SSN = Customers.SSN);

There’s a lot of duplicated code in here. And if we were getting data from a complicated subquery instead of the table Moved, it would be even worse (though we can at least put all the duplicated code in a CTE since SQL Server 2005). Of course, writing the code is done quickly enough once you master the use of copy and paste, but the code has to be maintained as well.

Maybe even worse is that the performance of this query just sucks – if you run this (enclosed in a BEGIN TRAN / ROLLBACK TRAN, so you can run the variations below without having to rebuild the original data) and check out the execution plan, you’ll see that the optimizer needs no less than five table scans (one for Customers, and four for Moved) and four merge join operators. And that, too, would be much worse if the source of the data had been a complex subquery (and no, using a CTE will not help the optimizer find a better plan – it just doesn’t understand that the four subqueries are similar enough that they can be collapsed.

Now, if Microsoft had chosen to implement row-value constructors (as defined in the ANSI standard), we could have simplified this to

UPDATE Customers

SET (Street, HouseNo, City)

= (SELECT Street, HouseNo, City

FROM Moved AS m

WHERE m.SSN = Customers.SSN)

WHERE EXISTS (SELECT *

FROM Moved AS m

WHERE m.SSN = Customers.SSN);

But this is invalid syntax in any version of SQL Server (including the latest CTP for SQL Server 2008), and I know of no plans to change that before SQL Server 2008 RTMs.

But with using the proprietary UPDATE FROM syntax, we can simplify this, and get a much better performance to boot. Here’s how the same update is written in non-portable code:

UPDATE c

SET Street = m.Street,

HouseNo = m.HouseNo,

City = m.City

FROM Customers AS c

INNER JOIN Moved AS m

ON m.SSN = c.SSN;

And now, the optimizer will produce a plan that scans each table only once and has only a single merge join operator. Some quick tests (with much more rows in the tables) show that it executes two to three times quicker than the ANSI standard version. For that performance gain, I will gladly choose the proprietary syntax over the standard!

What’s with the title of this post then? Why deprecate a fine feature?

Patience, we’re getting there. Bear with me.

All the above is true for versions of SQL Server up to SQL Server 2005. But SQL Server 2008 will change the playing field. It introduces a new statement, MERGE, that is specifically designed for situations where rows from a table source either have to be inserted into a destination table, or have to be used to update existing rows in the destination table. However, there is no law that prescribes that any MERGE should always actually include both an insert and an update clause – so with this new statement, we can now rewrite the above code as follows:

MERGE INTO Customers AS c

USING Moved AS m

ON m.SSN = c.SSN

WHEN MATCHED

THEN UPDATE

SET Street = m.Street,

HouseNo = m.HouseNo,

City = m.City;

As you can see, the source table and the join criteria are included only once, just as in the proprietary UPDATE FROM. The execution plan (tested on the February CTP, also known as CTP6) is also quite similar, including just a few extra operators that are specific to the new MERGE statement. What really surprised me, was that the plan for the MERGE statement was estimated to be about 65% cheaper (faster) than the corresponding UPDATE FROM statement. However, I think SQL Server is lying here – a quick test with more data shows only an extremely marginal advantage of MERGE over UPDATE FROM. This test was too limited to draw serious conclusions, but I am quite sure that there will not be a 65% saving by using MERGE over UPDATE FROM. (I do expect such a saving form either MERGE or UPDATE FROM over the ANSI-compliant UPDATE statement for this case).

The good news is that:

1) The MERGE statement is described in SQL:2003 and can thus be considered ANSI standard. (In fact, SQL Server implements a superset of the ANSI standard MERGE syntax: everything described in the syntax is implemented, but there are some non-standard extensions that make the command even more useful as well. However, the example above uses only the standard features and should hence run on each DBMS that conforms to the SQL:2003 version of MERGE).

2) The MERGE statement will return an error message if I mess up my join criteria so that more than a single row from the source is matched:

Msg 8672, Level 16, State 1, Line 1

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

3) The MERGE statement will gladly accept a view with an INSTEAD OF UPDATE trigger as the target of the update.

So as you see, MERGE allows me to achieve what I previously could achieve only with an ANSI standard UPDATE statement with lots of duplicated code and lousy performance, or with a UPDATE FROM statement that hinders portability, introduces a higher than normal risk of errors going unnoticed through QA right into the production database, and has some odd limitation on views with INSTEAD OF UPDATE triggers. None of these downsides and limitations apply to MERGE. And if there are any other problems with MERGE, I have yet to find them.

With this alternative available, I fail to see any reason why the proprietary UPDATE FROM syntax should be maintained. In my opinion, it can safely be marked as deprecated in SQL Server 2008. It should of course still work, as “normal” supported syntax in both SQL Server 2008 and the next version, and in at least one version more if the database is set to a lower compatibility – but it should be marked as deprecated, and it should eventually be removed from the product. Why waste resources on maintaining that functionality, when there is an alternative that is better in every conceivable way? I’d much rather see the SQL Server team spend their time and energy on more important stuff, such as full support for row-value constructors and full support for the OVER() clause. Or maybe even on releasing Service Pack 3 for SQL Server 2005!