SQL Server Performance

The “Balanced Data Distributor” for SSIS

SQL Server Performance Team — Wed, 25 May 2011 22:24:00 GMT

The “Balanced Data Distributor” for SSIS
Len Wyatt, SQL Server Performance Team

There is a new transform component available for SQL Server Integration Services. It’s called the Balanced Data Distributor (BDD) and the download is available here. The BDD provides an easy way to amp up your usage of multi-processor and multi-core servers by introducing parallelism in the data flow of an SSIS package.

Functionality of the BDD

The functionality of the BDD is very simple: It takes its input data and routes it in equal proportions to its outputs, however many there are. If you have four outputs, roughly ¼ of the input rows will go to each output. Instead of routing individual rows, the BDD operates on buffers of data, so it’s very efficient.

Some of you will already be noticing that there is no transformational value in the BDD, and no control over which data rows go to which output. You may be wondering, what the heck is the value of that?

The value of the BDD comes from the way modern servers work: Parallelism. When there are independent segments of an SSIS data flow, SSIS can distribute the work over multiple threads. BDD provides an easy way to create independent segments.

This diagram gives a trivial example:

If you run this data flow on a laptop, there probably won’t be any speed advantage, and there may even be a speed cost. But suppose you run this on a server with multiple cores and many disk spindles supporting the destination database. Then there might be a substantial speed advantage to using this data flow.

When to use the BDD

Using the BDD requires an understanding of the hardware you will be running on, the performance of your data flow and the nature of the data involved. Therefore it won’t be for everyone, but for those who are willing to think through these things there can be significant benefits. Here is my summary description of when to use BDD:

There is a large amount of data coming in.
The data can be read faster than the rest of the data flow can process it, either because there is significant transformation work to do or because the destination is the bottleneck. If the destination is the bottleneck, it must be parallelizable.
There is no ordering dependency in the data rows. For example if the data needs to stay sorted, don’t go and split it up using BDD.

Relieving bottlenecks in the SSIS Data Flow

Let’s talk about bottlenecks, since changing bottlenecks is what BDD is all about. A bottleneck is whatever limits the performance of the system. In general there are three places that could be the bottleneck in an SSIS data flow: The source, the transformations, or the destination.

Bottlenecks in the Source

If the limiting factor is the rate at which data can be read from the source, then the BDD is not going to help. It would be better to look for ways to parallelize right from the source.

Bottlenecks in the Transformations

If the limiting factor is the transformation work being done in the data flow, BDD can help. Imagine that there are some lookups, derived columns, fuzzy lookups and so on: These could easily be the components limiting performance. Make two or four or eight copies of the transformations, and split the data over them using the BDD. Let the processing run in parallel. If there are several transformations in the data flow, put as much as you can after the BDD, to get more things running in parallel.

Bottlenecks in the Destination

If the limiting factor is the destination, BDD might be able to help - you need to determine whether the destination can be run in parallel. You might be surprised at some times when it can. One example is when loading data into a simple heap (table with no indexes) in SQL Server. With the database properly distributed over a number of disks, it is quite possible to load in parallel with good performance. When working on the ETL World Record a while ago, we used a heap for a side experiment and found that loading 56 streams concurrently into a single heap was almost as fast as loading 56 streams into 56 independent tables. Many sites already drop or disable their indexes during data loading, so this could be more of a freebie than you would expect. More recently we saw a benefit from parallel loading into SQL Server Parallel Data Warehouse (PDW). PDW is an architecture designed for parallelism!

When the destination does not support parallel loading

A final case to consider is when the limiting factor is the transformation work being done in the data flow but the destination cannot receive data in parallel for some reason. In this case, consider using BDD to parallelize the transforms followed by a Union All to rejoin the data into a single flow; then a single destination can be used. Here is an illustration:

Best practice – balanced!

One final note: Whatever you put behind the BDD, be sure the same work is being done on all paths. It doesn’t make logical sense to have the paths be different, and from a performance point of view, you want them all to be the same speed. Remember, the “B” in BDD stands for “Balanced”.

Conclusion

Someday maybe SSIS will be able to do the work of the BDD automatically, but for now you have an easy way to amp up your usage of multi-processor and multi-core servers by introducing parallelism in the data flow of an SSIS package.

Kilimanjaro Ships!

SQL Server Performance Team — Wed, 21 Apr 2010 07:42:34 GMT

SQL Server 2008 R2 launches today! While the name “R2” sounds like an incremental update to SS2008 (or half of a Star Wars droid), in fact Kilimanjaro (the internal code name) is packed with big features. We’ve also got a lot to show for our work on performance and scalability. The four results at the top of the TPC-E performance chart are all on R2. This includes two results with more than 64 hardware threads, possible because of support added in Windows Server 2008 R2 and SQL Server 2008 R2 for up to 256 logical processors. The published results span the range from 2 to 16 CPUs, 24 to 96 cores, and 24 to 128 hardware threads. No one else has shown performance and scalability on TPC-E, the most rigorous industry-standard measure of OLTP performance available today. (See TPC-E – Raising the Bar in OLTP Performance for details on TPC-E.)

Five TPC-E benchmark results on SQL Server 2008 R2 published to date with five hardware partners show how SQL Server is driving scalability to new levels on x64 servers. The top result more than doubles the top perf on SQL Server 2008. Congratulations to Intel on the performance of the Nehalem-EX processor, which now holds the top two performance positions. (See this video for more on the breakthrough performance of Windows and SQL Server on Nehalem-EX.)

Here are the TPC-E results published on SQL Server 2008 R2 as of 4/20/10.

Sponsor	System	tpsE	Price/ tpsE	System Availability Date	Server Procs/ Cores/ Threads	CPU Type
	NEC Express5800/ A1080a-E	3,141	768.92 USD	6/24/10	8/64/128	Intel Xeon X7560 2.26GHz
	IBM System x3850 X5	2,022	493.92 USD	7/30/10	4/32/64	Intel Xeon X7560 2.26GHz
	Unisys ES7000 Model 7600R Enterprise Server (16s)	2,012	958.23 USD	5/6/10	16/96/96	Intel Hex-core Xeon X7460 2.66GHz
	Dell PowerEdge R910	1,933	328.58 USD	5/6/10	4/32/64	Intel Xeon-Quad Core x7560 2.27GHz
	HP ProLiant DL385G7	887	296.00 USD	5/6/10	2/24/24	AMD Opteron(tm) 6176 SE 2.3GHz

You can find out more about R2 at www.sqlserverlaunch.com.

- Charles Levine

New TPC Results on SQL Server 2008 R2

SQL Server Performance Team — Mon, 30 Nov 2009 19:31:55 GMT

On November 2, 2009, Unisys published two new TPC results (TPC-E and TPC-H) using the newly announced SQL Server 2008 R2* and Windows Server 2008 R2**. These results were published on the 96-core Unisys ES7000 system. These are the first SQL Server TPC results using more than 64 cores and they demonstrate the outstanding scalability of the Windows and SQL Server platform.

The TPC-E publication was 2,012 tpsE with a price performance of $958.23 USD/tpsE. The configuration will be available by May, 6, 2010. The TPC-H publication was 102,778 QphH@3000GB with a price performance of $21.05 USD/QphH@3000GB. This configuration will also be available by May 6, 2010.

You can see all the details for these outstanding results on the TPC web site at the following links:

Unisys ES7000 7600R Enterprise Server – TPC-E

Unisys ES7000 7600R Enterprise Server – TPC-H

* – SQL Server 2008 R2 will be generally available by May 6, 2010.

** – Windows Server 2008 R2 is generally available now.

- Jamie

Great New TPC-H Results with SQL Server 2008

SQL Server Performance Team — Mon, 17 Aug 2009 20:35:50 GMT

Our partners at Dell and HP have published 2 new TPC-H results with SQL Server 2008 using Fusion-io’s ioDrive solid-state technology. These are the first TPC results using solid-state technology and they illustrate the high performance along with total cost-of-ownership savings you can achieve. These results dramatically improved the price/performance for systems using industry standard components. These results prove that solid-state technologies can be used to reduce costs without giving up high levels of performance.

HP also published a 300GB result on their ProLiant DL785 platform with SQL Server 2008. This publication illustrates the high performance and solid price/performance using industry standard components from HP and Microsoft.

Please follow the links for all the details on each benchmark result.

- Jamie

Using SSIS to get data out of Oracle: A big surprise!

SQL Server Performance Team — Sat, 15 Aug 2009 04:38:00 GMT

Since late last year, Microsoft has made the Attunity connectors to Oracle databases available to Enterprise Edition customers. We all recognized right away that these connectors were faster than the pre-existing options from either Microsoft or Oracle, when moving data into or out of an Oracle database. It wasn’t immediately obvious what speeds we could expect from the connectors, so I did some experimenting to see. This posting summarizes some findings from those experiments done earlier this year, but rather than report all the details I will then zero in on the key lessons and one big surprise that came out of the work.

Before getting in to my findings, let me give a little anecdote heard from a colleague: There is at least one SSIS customer that now uses SSIS and the Attunity connectors to move data from an Oracle database to an Oracle database, because SSIS with Attunity outperforms any of the Oracle options! While I can’t corroborate that, the information to follow is from my own measurements.

To do this work, I used two machines each with 24 cores (64-bit, 2.4 GHz), one for SSIS and one for Oracle. The machines were practically an embarrassment of riches for this simple benchmark. The SSIS machine had flat files to read (when loading data into Oracle) or write (when extracting data from Oracle). The SSIS packages were super simple, just a flat file source and an Oracle destination, or an Oracle source and a flat file destination. The data was 75 million rows, 133 bytes per row, of the LINEITEM table from the TPC-H benchmark, as generated by the DBGEN utility.

Some basic findings (remember, you mileage will vary):

Putting data into an Oracle database using the Attunity connectors clocked 30,000 rows per second, on the order of 20 times faster than using the OLE DB connectors to Oracle.
Extracting data from Oracle moved 36,000 rows per second, about 20% faster than using the OLE DB connectors.
The above measurements were taken using “mixed” data types: Numbers were put in NUMBER fields, dates were put in DATE fields, etc. A funny thing happened though when all the data was put in string fields (VARCHAR2 use used for everything). Now we could hit 42,000 rows per second loading data into Oracle, and 76,000 rows per second extracting from Oracle!
The Fast Load option is supposed get higher performance through the use of the DirectPath API. In my experiments, I didn’t see a consistent advantage of Fast Load over non-Fast Load. The thing that Fast Load did seem to do was shift more of the CPU time from the Oracle process to the SSIS process. This could mean that if you have multiple concurrent SSIS packages sending data to Oracle, using Fast Load might let Oracle receive the data faster. Given my experience with Fast Load, I can only recommend that you check its performance in your own situation. Note: I’ve been told that Fast Load will be fixed in a maintenance release later this calendar year. So while I’m not promising anything, it’s likely that this will change.
The default batch size for the Oracle destination connector is 100 rows. Setting the batch size to 10,000 rows gave a boost of 10% to 50%, depending on other elements of the configuration. (When using Fast Load, you specify the buffer size instead of the row count. So estimate the buffer size needed to hold the number of rows you want, and use that number.)
When using the Oracle source, setting batch size to 10,000 rows gave a boost of around 10%, depending on other elements of the configuration.
I wanted to know how important it was for SSIS to be on a separate machine from the Oracle database. There was a good network connecting the source and destination servers, and also plenty of CPUs and memory on the servers. What I saw was a negligible difference between the case where SSIS and Oracle were on the same server and the case where SSIS and Oracle were on separate systems. My recommendation: Look at what resource is the most loaded in your environment, and configure to lighten the load on that resource.

The idea that performance with string data would be so different from performance with natural data types was a big surprise. The difference was especially pronounced when extracting data from Oracle. Now let’s face it, we would prefer to see data extracted from Oracle and placed in SQL Server databases! Given the big speed disparity and the fact that most real-world data needs to be in natural data types, I wondered if the same thing would happen if data was cast to string types in the query that SSIS issues against Oracle. So instead of having SSIS simply read the table, I gave it this query to run:

select
    TO_CHAR(L_SHIPDATE),
    TO_CHAR(L_ORDERKEY),
    TO_CHAR(L_DISCOUNT),
    TO_CHAR(L_EXTENDEDPRICE),
    TO_CHAR(L_SUPPKEY),
    TO_CHAR(L_QUANTITY),
    L_RETURNFLAG,
    TO_CHAR(L_PARTKEY),
    L_LINESTATUS,
    TO_CHAR(L_TAX),
    TO_CHAR(L_COMMITDATE),
    TO_CHAR(L_RECEIPTDATE),
    L_SHIPMODE,
    TO_CHAR(L_LINENUMBER),
    L_SHIPINSTRUCT,
    L_COMMENT
from ATTUSER.LINEITEM

Then before inserting the data into SQL Server using the SQL Server destination, I put in a data conversion task to get all the data into the correct types.

At this point you must be thinking, “Surely converting the data twice can’t be the fastest way!” Well, here are the results: The first run below read the mixed data types using the Attunity Oracle source with default settings, converted to SQL Server types, then wrote to the SQL Server destination. The second run was like the first, with the addition of setting the batch size larger. The third run was like the first, but on reading from Oracle all the columns were converted to text as discussed above. The last test was like the third, with the addition of setting the batch size larger. Using the string conversion and larger batches, the run was over two times faster than the obvious out-of-the-box configuration.

Overall, the Attunity connectors for Oracle really were fast, as expected. In doing this work a few lessons turned up that hopefully help you get optimal performance.

- Len Wyatt

Great new SQL Server performance on Intel's Xeon 5500 series, aka Nehalem-EP

SQL Server Performance Team — Tue, 31 Mar 2009 19:56:00 GMT

Yesterday, Intel launched its new Xeon 5500 series processors, code named Nehalem-EP. This is essentially the first server-class version of what Intel launched on the desktop as Core i7 last fall. With no frontside bus, and an embedded, multi-channel memory controller in the processor package, it is smoking fast.

Pat Gelsinger did a side-by-side performance demo which launched an SSRS report, running reporting queries against a 1.5 TB SSAS OLAP cube, built using a Microsoft adCenter data set. The demo showed how Nehalem-EP is 2X faster than a Xeon 5400 on the same workload, with the same DRAM and I/O configuration. Not too shabby, but we've seen even faster results (~3-4X faster) on workloads which are more memory bandwidth-intensive, like data warehousing or in-memory OLAP workloads.

Also yesterday, our good friends at Fujitsu, Dell, and IBM, used SQL Server 2008 to show off the performance of Intel's new processor, in their latest 2P server platforms, by delivering great new TPC-E results which have break-through per-processor performance and price/performance. Please follow the links for the full scoop on each benchmark result. Congratulations to each!

-David

An ETL World Record Revealed (Finally)

SQL Server Performance Team — Wed, 04 Mar 2009 02:38:00 GMT

We suppose a more appropriate title would have been: Better Late Than Never.

David should begin by apologizing. Last month was the first anniversary of the ETL world record we set last year with SSIS, loading 1 TB of data in under 30 minutes. While Len did a nice blog post on the project at the time, we had promised to return to our favorite ETL practitioners (that's you) with more details, pulling back the curtain on Oz so to speak.

It only took a mere year to get around to that, and a lot of water has gone under the bridge since then. Happily, the paper's now done and published to the web for your reading pleasure. Whew!

-David Powell, Len Wyatt & Tim Shea

Windows Server 2008 + SQL Server 2008 = Geo-replication Benefits

SQL Server Performance Team — Tue, 25 Nov 2008 05:04:00 GMT

After running an experiment, using datacenters on each coast of the U.S., our friends in MSCOM ops verified that the combination of Windows Server 2008 and SQL Server 2008 are great for enabling database geo-replication, thanks to a 100X gain in performance. The experiment provided sufficient evidence to motivate a more ambitious proof-of-concept case study with MSDN.

A new whitepaper, published this week, explains how SQL Server 2008 was able to take advantage of the all-new core networking stack in Windows Server 2008 to deliver revolutionary gains in replication performance across wide-area networks. The paper includes information on an MSDN case study, in which MSCOM ops significantly lowered MSDN page load times, thanks to replication of MSDN content from the U.S. to Europe.

-David

SQL Server is Movin' On Up!

SQL Server Performance Team — Mon, 10 Nov 2008 21:34:00 GMT

At last week's WinHEC in Los Angeles, SQL GM Quentin Clark joined Windows Server VP Bill Laing on stage, to announce our upcoming support for more than 64 logical processors, the current limit in Windows and SQL Server. This expanded scale-up capability is planned to be released in SQL Server code named "Kilimanjaro", when running on top of Windows Server 2008 R2.

We expect to support up to 256 logical processors in this release, though that's a soft limit. The hard limits are much higher than this, but we won't support what we can't test. You'll likely see this 256LP soft ceiling get higher in each release, now that we've finally done the heavy lifting of raising the roof on Windows and SQL Server.

The keynote demo (@33:26), which Bill and Quentin did together, went well. Many thanks to our friends at HP and IBM for all their support and for the use of these great servers.

In case you're wondering, a logical processor is a sub-unit of a physical processor/socket/package. Typically today an LP means a core, but it can also mean a hardware thread (nee hyper-threading).

-David Powell

TPC-E – Raising the Bar in OLTP Performance

SQL Server Performance Team — Fri, 24 Oct 2008 02:46:00 GMT

Glenn Paulley, Director of Engineering at Sybase iAnywhere, posted a commentary titled “The State of TPC-E” on his blog three weeks ago (10/3/08). A better title would have been “All TPC-E Results Are On Microsoft SQL Server. Why?” Mr. Paulley takes issue with Brian Moran’s statement that “the most rational answer is that Oracle and IBM have tried to top Microsoft’s numbers and simply can’t”. He says that while it may be true, he doubts it and says there are other plausible reasons why DB2 and Oracle have yet to publish any TPC-E results. Curiously, he doesn’t say why Sybase hasn’t published TPC-E results. Since he is, presumably, in a position to know, one can only conclude that he would rather not say. Readers can reach their own conclusions about what that might mean.

To his credit, he cites this IBM whitepaper for explaining that TPC-E was designed to be more realistic than TPC-C. There are numerous ways, detailed in the whitepaper, in which TPC-E is far superior to TPC-C. Let’s compare TPC-E to TPC-C. As the table below shows, in TPC-E the schema is substantially richer and more complex, there are twice as many transactions, and only TPC-E requires essential capabilities such as referential integrity and RAID protected storage.

	TPC-C	TPC-E
Schema
Number of database tables	9	33
Foreign keys	9	50
Tables with foreign keys	7	27
Check constraints	0	22
Partitioning Characteristic	unrealistic; single dimension common to 8 of 9 tables	realistic; two independent dimensions
Transactions
Number of transactions	5	10
Database roundtrips per transaction	1	min 1; max 5
Capabilities
Referential Integrity Required	No	Yes
Storage Protection (e.g. RAID) for Database Required	Log Only	Everything
Timed Database Recovery test	No	Yes

Mr. Paulley chooses to focus on the query complexity of TPC-E. While that’s somewhat interesting, a comparison to TPC-C would have provided important context. For example, TPC-E has 156 DML statements. Although TPC-C doesn’t include pseudo-SQL the way that TPC-E does, if it did and followed the TPC-E style, it would be fewer than 30 DML statements. By this measure, TPC-E has more than five times as many distinct DML operations as TPC-C.

But more importantly, TPC-E is not and was never intended to be a query optimizer test. The pseudo-SQL code in TPC-E is an example, not a requirement. Unlike TPC-H which strictly limits changing the specified SQL, in TPC-E test sponsors are free to rewrite the SQL anyway they like as long as it is functionally equivalent. One vendor might rewrite it to remove all joins while another might rewrite it to include more joins or more complex joins. The same is true of group by and order by clauses. In our view, Mr. Paulley’s objection that TPC-E isn’t a good optimizer test is misplaced.

After discussing query complexity, Mr. Paulley offers four reasons why Microsoft is the only database vendor publishing TPC-E results.

· “TPC-E is a moving target” – While it’s true that the TPC-E spec is up to version 1.6.0, the assertion that the workload has changed significantly is unsupported by the facts. None of the transactions has changed in any way that impacts performance. All spec revisions have been classified as “minor” changes by the TPC and results across all spec revisions are comparable. The number of revisions to the spec since it was first released actually reflects a deep commitment by the members of the TPC-E committee to clean up rough edges and address areas of ambiguity before they become issues in published results. A better gauge of the high quality of the TPC-E spec is that to-date 18 results have been published by six vendors spanning 15 months, but there have been no compliance challenges.

· “Both DBMS vendors and hardware suppliers have a substantial investment in TPC-C expertise.” On this point we agree with Mr. Paulley. But we draw different conclusions. All of the major DBMS companies have spent years picking through every detail of TPC-C. It has been optimized to such a degree that it long ago stopped driving customer-relevant engineering improvements. TPC-C is 16 years old and has changed little since 1992. Saying that we should continue using TPC-C because we know it so well is like saying that we should drive horse and buggies because we have a lot of expertise in blacksmithing. This is a mindset trapped in the past and doesn’t serve our customers.

· “TPC-E isn’t that cheap.” In fact, TPC-E is substantially less expensive to configure and run than TPC-C. Two results from IBM within the last month prove the point. As you can see in the table below, running on the same server, the TPC-C configuration was more than five times more expensive than the TPC-E configuration. Further, on the four proc server, the TPC-C result had 1361 disks with no data protection, while the TPC-E result had 400 disks with RAID-5. Which is the more customer-relevant configuration?

	TPC-C	TPC-E
Hardware	IBM System x3850 M2	IBM System x3850 M2
Procs / Cores / Threads	4 / 24 / 24	4 / 24 / 24
Performance	684,508 tpmC	729 tpsE
Price/perf	2.58 $/tpmC	457 $/tpsE
Total System Cost	$ 1,763,438	$ 333,646
Publication Date	9/15/08	9/15/08
Availability Date	10/31/08	10/10/08
Memory	256 GB	128 GB
Storage	1,344 x 73.4GB disks 16 x 500GB disks 1 x 73GB	400 x 73.4GB disks
Data Storage Protection	None	RAID-5
TPC Result Details	Link	Link

· “Customers continue to desire and reference TPC-C results.” Granted, TPC-C has stood the test of time. But today it is outdated, over-optimized, and of questionable relevance. Customers hold onto TPC-C because it is familiar and available, not because it is better. Database vendors need to exercise leadership. As Mr. Paulley says “Microsoft is an early adopter of TPC-E”. At this point, though, the early adopter window has passed. TPC-E was ratified 20 months ago. The first result was published 15 months ago. There are 18 published results. We believe that customers will readily embrace TPC-E as a superior benchmark as more results become available.

The more time that goes by, the more one is inclined to believe that Brian Moran is right – other database vendors aren’t publishing because they can’t beat the existing SQL Server results. We invite Sybase and Mr. Paulley to prove us wrong. We are confident that once Sybase runs TPC-E instead of just writing about it, Mr. Paulley will gain a new appreciation for just how challenging and technically rigorous TPC-E is compared with TPC-C.

Charles Levine

SQL Server Performance Engineering

SQL Server 2008 is on its way with great performance

SQL Server Performance Team — Wed, 06 Aug 2008 20:01:00 GMT

Just left the final shiproom meeting for SQL Server 2008, and am happy to say every team has signed off, so the product is now in the hands of manufacturing, and in process toward web and media availability for you. We've shipped! MSDN and TechNet subscriber downloads are now live, with more to come.

It's been less than three years since SQL Server 2005, but we're pleased to give you a great new release of SQL Server which not only adds fantastic new capabilities to your data platform, but also delivers broadly better performance. We used new industry standard benchmark workloads, and customer workloads, to drive us toward delivering better real-world performance...and we didn't take the easy road.

The best example of that is our use of TPC-E, a far more modern, realistic, and challenging benchmark workload than its predecessor. We used TPC-E to improve the scalability of our relational engine, in ways that should be more relevant to your own OLTP database workloads. We're proud of partners, like IBM, NEC, and Unisys, who were able to demonstrate great scalability, up to 64 cores, using SQL Server 2008 running TPC-E, and like Fujitsu-Siemens and Dell, who have shown industry leading price/performance. Results like these should also be more useful for system sizing.

Almost five years of effort by TPC members were invested in the development of TPC-E, and it shows. The workload uses synthetic data which is far more realistic, by modeling real-world data. And compared to its antique predecessor, TPC-E's schema has ~3X more tables and primary keys, 2X as many columns, and 4X more foreign keys. And here's a radical thought for a modern OLTP benchmark workload: include check constraints, referential integrity, and reliable storage. Don't customers actually put DBMS servers into production expecting that?

Because we believe so strongly TPC-E drives us to better meet your needs, I am announcing today this is the first release of SQL Server which will not include published TPC-C benchmark results. Like other great thoroughbreds, TPC-C had a great run, and we were proud to ride it while it was still relevant to customers. But today, we're turning that great old race horse out to pasture for a well-deserved rest.

In SQL Server 2008, we have also invested lots of effort to improve our data warehousing performance, and the performance of our BI services. SSIS, SSRS, and SSAS, each show many double-digit gains in performance, which we hope you will enjoy. The new world record we set with SSIS, loading 1 TB of data in under 30 minutes, gives you a sense of this commitment to BI performance. We are also proud of our first-ever TPC-H 10 TB result.

There are a couple other ways in which this release improves on our past work. First, we've focused more energy on improving 64-bit SQL Server's performance on x64 AMD Opteron and Intel Xeon architectures. Given the price and density of RAM, and great new x64 CPUs available, this is a perfect time to take a closer look at the performance of your database servers, and consider doing fresh deployments, or migrations, on x64 SQL Server, which can use the additional RAM for everything, not just the buffer cache. Second, we invested more heavily in performance regression testing, both in automation and in the breadth of our test coverage. These investments have been reflected in broadly positive feedback from the community as well as internal and external beta sites. While we'll always have more to do, we feel this release marks an important step forward.

Welcome to the beginning of a new era for SQL Server. We hope you enjoy working with SQL Server 2008 and look forward to your feedback.

-David Powell
SQL Performance Engineering

SQL Server 2008 launched today, with great performance and scalability

SQL Server Performance Team — Thu, 28 Feb 2008 02:07:51 GMT

Today, as the old saying goes, is a red-letter day, with the launch of Windows Server 2008, Visual Studio 2008, and SQL Server 2008.

Our team has been heads down, working to ensure SQL Server 2008 is delivered to you with great performance and scalability. You’ll see signs of this in the new SQL Server February 2008 Community Technology Preview, which includes great new performance features in the engine, SSRS, SSAS, and SSIS, as well as just thumping good performance.

But don’t take my word for it: ask NEC, IBM, SAP, HP, and Unisys. Today, our partners are delivering proof this is the best release yet of SQL Server!

Here’s a quick round-up of the industry standard benchmark results our partners published today, Feb 27, 2008, using Windows Server 2008 and SQL Server 2008. Details of the Transaction Processing Performance Council (TPC) results can be found on www.tpc.org. More information on the SAP SD result is available on SAP’s web site.

#1 TPC-E result of 1,126 tpsE at a cost of $2,771/tpsE, using a 64-core Intel Itanium-powered Express5800/1320Xf system. This result demonstrates the power of NEC’s architecture and the scalability of SQL Server 2008 for enterprise OLTP workloads since it is our first 64C TPC-E result
TPC-E result of 479 tpsE at a cost of $1,591/tpsE, using a quad-socket, 16-core IBM x-Series x3850 M2 system. This is a 14% gain over IBM’s previous x3850 M2 TPC-E result with SQL Server 2005
HP’s newest SD three-tier result of 34,000 users, which is #1 on quad-processor industry-standard servers, and is 88% faster than a previous quad-processor result on SQL Server 2005. This result shows the power of HP’s BL680C blade servers and Intel Xeon 7300 series processors. Did you ever think you’d see the day a blade could be expected to handle the workload volume of 97% of SAP deployments worldwide?
And last, but not least: HP published today the first-ever TPC-H result on SQL Server at the 10 TB scale factor: 63,650 QphH at $38.54/QphH, using a powerful 64-core Integrity Superdome server with HP SAS storage. Unless you’re Walmart, the odds are high your DW is smaller than this!

Industry standard benchmarks are great, but unfortunately they don’t yet cover all usage scenarios customers care about. ETL is a key part of any production DW workflow, and we’ve been paying special attention to the performance of SQL Server Integration Services, our ETL tool included with SQL Server. With improvements to the core SSIS processing engine in SQL Server 2008, and improvements in 64-bit connectivity, we decided to take SSIS out for a spin, to show what it could really do. Along the way, we and Unisys set a new world record for loading over 1 TB of data in under 30 minutes, beating a previous result posted by Informatica. Check out Len Wyatt’s more detailed blog post on this. We’d sure love to see the industry come together to create a standardized ETL benchmark workload.

Lastly, some leading ISVs put SQL Server 2008 through its paces, and were very pleased with the result:

Camstar showed world-record scale of 205 MES transactions/second and 60% space reduction when using SQL Server 2008’s database compression on Windows Server 2008
Microsoft Dynamics CRM 4.0 demonstrated record scale at 24,000 concurrent users, with sub-second response rate, using SQL Server 2008 on Windows Server 2008
Siemens Teamcenter 2007, SQL Server 2008, and Windows Server 2008 ran with 5,000 concurrent users, and 50% space reduction from database compression
Microsoft Dynamics AX showed up to 70% improvement in throughput scalability and response time, maximizing performance while reducing database growth using SQL Server 2008 and database compression

Look for even more performance gains in the final SQL Server CTP, before we ship this summer!

ETL World Record!

SQL Server Performance Team — Wed, 27 Feb 2008 22:36:00 GMT

Today at the launch of SQL Server 2008, you may have seen the references to world-record performance doing a load of data using SSIS. Microsoft and Unisys announced a record for loading data into a relational database using an Extract, Transform and Load (ETL) tool. Over 1 TB of TPC-H data was loaded in under 30 minutes. I wanted to provide some background material in the form of a Q&A on the record, since it’s hard to give many details in the context of a launch event. We are also planning a paper that talks about all this, so think of this article as a place-holder until the full paper comes along. I hope you find this background information useful.

- Len Wyatt

How fast was the data load?

More than one terabyte of data was parsed from flat files, transferred over the network and loaded into the destination database in less than 30 minutes, a world record beating all previously published results using an ETL tool. That is a rate in excess of 2 TB per hour (650+ MB/second). To be precise, 1.18TB of flat file data was loaded in 1794 seconds. This is equivalent to 1.00TB in 25 minutes 20 seconds or 2.36TB per hour.

Why is this important?

Businesses have ever-increasing volumes of data stored in many heterogeneous systems. Thay want to know that an ETL tool they choose will be able to support any data volumes they might require. Microsoft has been making a significant investment in SQL Server Integration Services (SSIS), and this record illustrates the capability of SQL Server Integration Services 2008, SQL Server 2008 and the Unisys ES7000 to handle a significant volume of data at a dramatic speed.

Why not just do a bulk load of the data?

It is rare in businesses today that data is always available on the destination system, and does not need to be standardized or corrected for errors before loading. These rare cases are the times that bulk loading data makes sense. Data integration can involve complex transformation rules, error checking and data standardization techniques. ETL tools like SSIS can perform these functions such as moving data between systems, reformatting data, integrity checking, key lookups, tracking lineage, and more. SSIS has proven itself to be a versatile ETL tool, and now it is shown to be the fastest one as well.

What data did you choose to load?

DBGEN tool from the TPC-H benchmark was used to generate 1.18 TB of source data. The data were partitioned by DBGEN, allowing it to be loaded in parallel from multiple systems. DBGEN generates data on customers, parts, suppliers, orders and line items. It is broadly representative of a wholesale business. The data contain a variety of data types, including dates, money amounts, integers, strings and flags.

Please note that the ETL loading results are not TPC-H benchmark results and should not be compared to TPC-H benchmark results.

Was this a certified benchmark?

There is no commonly accepted benchmark for ETL tools. Microsoft thinks there should be. Industry standard benchmarks can lead to healthy competition, better products, and better publication of the techniques used to get high performance. Microsoft would welcome the opportunity to join with others in the industry to define a common benchmark that reflects the real-world uses of ETL tools.

The use of TPC-H data for this project was a convenience. This is not a TPC-H benchmark result.

How does this compare to your competitors?

Multiple competitors have published results based on TPC-H data. Informatica has the fastest time previously reported, loading 1 TB in over 45 minutes. SSIS has now beaten that time by more than 15 minutes.

There are other claims of fast times that have been made, but on non-standard data sets and without enough information to allow any meaningful comparison. This is part of the reason Microsoft would support the creation of an industry standard ETL benchmark.

What system configuration was used?

The database server ran on a Unisys ES7000/one Enterprise Server , with 32 socket dual core Intel® Xeon^TM 3.4 Ghz (7140M) processors , 256 GB RAM and 8 dual port 4Gbit HBA’s . The SQL Server data was stored on an EMC Clariion CX3-80 SAN with 165 (146 GB/15 krpm) spindles. The database server ran a pre-release build of SQL Server 2008 Enterprise Edition (V10.0.1300.4, built just before the “February 2008 CTP”) on the Windows Server 2008 x64 Datacenter Edition operating system.

Four servers acted as data sources, modeling the fact that data comes from a variety of systems in a modern enterprise. Each source server ran SSIS packages that sent data across the network to the database server. The source servers ran SSIS from SQL Server build V10.0.1300.4, on the Windows Server 2008 operating system. Source data came from flat files, as it was generated by DBGEN.

For the source servers, 4 Unisys ES3220L servers with Windows2008 x64 Enterprise Edition were used. Each server is equipped with 2 x 2.0GHz quad core Intel® processors, 4GB RAM, a dual port 4Gbit Emulex HBA and Intel PRO1000/PT network card. The source data was read from 2 x EMC Clariion CX600 SAN’s with 45 spindles each.

The Source servers were connected to the ES7000/one server database server with private dual port 1Gb Ethernet connections.

Why use multiple source systems?

Modern large businesses are complex operations. Large data sets are often the result of multiple data feeds. This made the test more realistic by mimicking a real world ETL scenario.

What do the SSIS packages look like?

There was just one package, though the source systems ran multiple instances of it. It is quite simple: There is one control flow for each “stream” of data generated by DBGEN. The control flow has one data flow for each table, each data flow reading data from a flat file source and writing to the SQL Server database via OLEDB. Using this data set there is a one-to-one column mapping between the flat file data and the database tables.

Did Windows Server 2008 figure in to this?

A lot of innovative engineering work in Windows Server 2008, including significant improvements in memory management, PCI and block storage I/O, and core networking, helped achieve this great performance. Because of these advances, Windows Server 2008 sustained about 960 megabytes per second over the Ethernet network, during processing of one large table.

Were secret internal tricks were needed to make this work?

No secret internal tricks or special builds were needed. Although this project used a pre-release version, it was a regular SQL2008 Enterprise Edition build. No special code in the product was used. Everything we did could be replicated by others.

The main thing done in the relational database was to use “soft NUMA” and port mapping to get a good distribution of work within the system. This is a published technique; you can find articles about it on MSDN. We also set the –x flag on starting SQL Server. This reduces the time SQL Server spends collecting performance statistics at run-time.

In SSIS we made sure the data types used in the SSIS data flows matched the types used in SQL Server, so the data did not need to be converted again after the initial conversion of strings read from flat files. Fast Parse is set on the text file fields where it applied.

The network connections on the server used the built-in Intel PRO/1000 GbE controllers. Released versions of network drivers were used, and Ethernet jumbo frames were configured to better support this bulk streaming scenario. Window Server 2008’s new TCP/IP receive window autotuning was set to “restricted”. The IntPolicy tool was used to ensure the ES7000 server NICs’s interrupts & DPCs occurred on a CPU affinitized to the same NUMA node as the NIC.

A complete list of settings and optimizations will be included in the paper when it is released.

Spool operators in query plan...

SQL Server Performance Team — Thu, 30 Aug 2007 22:38:40 GMT

I came across a question in the relationalserver.performance newsgroup where a customer was wondering about the spools seen in a recursive query execution plan. The query is shown below:

USE Northwind;
Go

WITH EmpChart AS
(
SELECT EmployeeId, ReportsTo, 1 AS treelevel
FROM Employees
WHERE (Employees.ReportsTo = 2)
UNION ALL
SELECT e.EmployeeId, e.ReportsTo, treelevel +1
FROM Employees e
JOIN EmpChart ec
ON e.ReportsTo=ec.EmployeeID
)
SELECT * FROM EmpChart;

The plan for the above query shows an index spool and a table spool. They are one and the same. The plan is shown below:

|--Index Spool(WITH STACK)
|--Concatenation
|--Compute Scalar(DEFINE:([Expr1013]=(0)))
| |--Compute Scalar(DEFINE:([Expr1003]=(1)))
| |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Employees].[PK_Employees]), WHERE:([Northwind].[dbo].[Employees].[ReportsTo]=(2)))
|--Assert(WHERE:(CASE WHEN [Expr1015]>(100) THEN (0) ELSE NULL END))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1015], [Recr1006], [Recr1007], [Recr1008]))
|--Compute Scalar(DEFINE:([Expr1015]=[Expr1014]+(1)))
| |--Table Spool(WITH STACK)
|--Compute Scalar(DEFINE:([Expr1009]=[Recr1008]+(1)))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Employees].[PK_Employees] AS [e]), WHERE:([Northwind].[dbo].[Employees].[ReportsTo] as [e].[ReportsTo]=[Recr1006]))

The index spool is also a lazy spool here meaning rows get inserted into the spool during execution of the recursive part also. Additionally, the rows from the index spool is read using a stack-like mechanism otherwise the recursive part may visit the same rows again. Here is how to read the plan with the recursive query:

1. Start with the anchor / top-most part

The anchor part of the recursive CTE first gets executed and the spool is created with index. Note the stack option also in the spool. This indicates that rows are read in a FIFO manner.

2. Next the recursive part of the query

|--Nested Loops(Inner Join, OUTER
REFERENCES:([Expr1015], [Recr1006], [Recr1007], [Recr1008]))
|--Compute
Scalar(DEFINE:([Expr1015]=[Expr1014]+(1)))
| |--Table Spool(WITH STACK)
|--Compute
Scalar(DEFINE:([Expr1009]=[Recr1008]+(1)))
|--Clustered Index
Scan(OBJECT:([Northwind].[dbo].[Employees].[PK_Employees] AS [e]),
WHERE:([Northwind].[dbo].[Employees].[ReportsTo] as
[e].[ReportsTo]=[Recr1006]))

This is the nested loop join between the spool (created in step #1 for the anchor query) and the recursive part of the query. Since this is eager spool, rows will be populated into the spool also until the recursion is completed or the maximum level is reached. The maximum level check is done using the assert operator above the nested loop join:

|--Assert(WHERE:(CASE WHEN [Expr1015]>(100) THEN (0) ELSE
NULL END))

3. Now, the way to tell which spools are related or the same is to look at the properties of the spool operator in the query plan output. The index spool has a property called NodeId which will referenced by the table spool as PrimaryNodeId property in another part of the plan.

Lastly, SQL Server can also create a plan with an eager spool which can be seen below for the query. In case of eager spool, query execution can continue only after the eager spool has been fully created. This is different from the lazy spool.

select count(distinct ShipVia), count(distinct ShipCountry)
from Orders as o, Customers as c
where o.CustomerID = c.CustomerID;

1. To read, the plan we will start again from the top part which contains the eager spool population.

| |--Table Spool
| |--Hash Match(Inner Join,
HASH:([c].[CustomerID])=([o].[CustomerID]),
RESIDUAL:([Northwind].[dbo].[Customers].[CustomerID] as
[c].[CustomerID]=[Northwind].[dbo].[Orders].[CustomerID] as
[o].[CustomerID]))
| |--Index
Scan(OBJECT:([Northwind].[dbo].[Customers].[Region] AS [c]))
| |--Clustered Index
Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o]))

Here you can see the hash join between customers and orders table that populates the eager spool table.

2. The ShipVia distinct count is computed as follows by reading from the spool.

|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
| |--Stream
Aggregate(DEFINE:([Expr1010]=COUNT([Northwind].[dbo].[Orders].[ShipVia] as
[o].[ShipVia])))
| |--Hash Match(Aggregate, HASH:([o].[ShipVia]),
RESIDUAL:([Northwind].[dbo].[Orders].[ShipVia] as [o].[ShipVia] =
[Northwind].[dbo].[Orders].[ShipVia] as [o].[ShipVia]))
| |--Table Spool

3. Similarly, the ShipCountry distinct count is computed using the same spool. You can see this by looking at the NodeId and PrimaryNodeId properties of the spool operators in the query plan or showplan xml.

|--Compute
Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)))
|--Stream
Aggregate(DEFINE:([Expr1011]=COUNT([Northwind].[dbo].[Orders].[ShipCountry]
as [o].[ShipCountry])))
|--Hash Match(Aggregate, HASH:([o].[ShipCountry]),
RESIDUAL:([Northwind].[dbo].[Orders].[ShipCountry] as [o].[ShipCountry] =
[Northwind].[dbo].[Orders].[ShipCountry] as [o].[ShipCountry]))
|--Table Spool

4. Finally, since COUNT() aggregate always returns one row, we just do a nested loop join between the two parts of the tree above to return a row.

|--Nested Loops(Inner Join)
|--Compute
Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1010],0)))
....
|--Compute
Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)))

Hope this helps you read execution plans that contain the various spool operators.

Umachandar Jayachandran

What's swimming in your bufferpool?

SQL Server Performance Team — Fri, 18 May 2007 20:19:00 GMT

When doing a performance investigation a useful thing to do is look at what data is present in the buffer pool. This can be used to analyze impact of running a query on the state of data pages in buffer pool. By collecting the pre and post picture of buffer pool, you can see the cost of running a query in terms of physical IOs that happened. You may argue that this can be done by looking at statistics IO output; however if you are running a series of queries and want to see a consolidated data and not data about individual queries, this query is a great help.

The contents of the buffer pool can also reveal which pages are accessed most frequently by your applications and often reflect the actual I/O that is happening. How can frequently access pages in memory also cause disk I/O? When lots of different objects are accessed overtime the proportion of data in the buffer pool reflects the frequency of access. This happens because data pages of infrequently accessed objects get kicked out of main memory over time.

If you are not familiar with the buffer pool, it contains several types of objects such as data pages and plans. For more information on the buffer pool see Buffer Management http://msdn2.microsoft.com/en-us/library/aa337525.aspx

The following query can be used to look at the contents of the buffer pool -

select

count(*)as cached_pages_count,

obj.name as objectname,

ind.name as indexname,

obj.index_id as indexid

from sys.dm_os_buffer_descriptors as bd

inner join

(

select object_id as objectid,

object_name(object_id) as name,

index_id,allocation_unit_id

from sys.allocation_units as au

inner join sys.partitions as p

on au.container_id = p.hobt_id

and (au.type = 1 or au.type = 3)

union all

select object_id as objectid,

object_name(object_id) as name,

index_id,allocation_unit_id

from sys.allocation_units as au

inner join sys.partitions as p

on au.container_id = p.partition_id

and au.type = 2

) as obj

on bd.allocation_unit_id = obj.allocation_unit_id

left outer join sys.indexes ind

on obj.objectid = ind.object_id

and obj.index_id = ind.index_id

where bd.database_id = db_id()

and bd.page_type in ('data_page', 'index_page')

group by obj.name, ind.name, obj.index_id

order by cached_pages_count desc

An example of what it returns –

1. Run following command to remove all clean data pages from the buffer pool – (DO NOT TRY THIS COMMAND ON PRODUCTION MACHINES)

DBCC DROPCLEANBUFFERS

Running buffer pool analysis query had following results –

cached_pages_count ObjectName IndexName IndexId

------------------ ------------------ ---------------------- -----------

15 sysobjvalues clst 1

3 sysallocunits clust 1

2 syshobtcolumns clust 1

2 sysrowsetcolumns clust 1

2 sysrowsets clust 1

2 sysschobjs clst 1

…

2. Run the following query on AdventureWorks database –

select * from Person.Address

where city like 'Bothell'

This is going to read from disk the data pages needed to execute the query. Run the buffer pool analysis query again to see the change.

cached_pages_count ObjectName IndexName IndexId

------------------ ------------------ ---------------------- -----------

278 Address PK_Address_AddressID 1

15 sysobjvalues clst 1

4 sysmultiobjrefs clst 1

3 sysallocunits clust 1

2 syshobtcolumns clust 1

2 sysrowsetcolumns clust 1

…

As you can see now there are data pages in buffer pool from the Address table. Additionally since only clustered index pages for Address table are present, no other indexes were used in the query.

Another tool which can help in this case is DBCC MEMORYSTATUS output. The advantage of the query in the entry is nice result set which can be stored in a temp table.

Authors:

Tony Voellm

Gaurav Bindlish

Adjust buffer size in SSIS data flow task

SQL Server Performance Team — Fri, 11 May 2007 23:22:00 GMT

The data flow task in SSIS (SQL Server Integration Services) sends data in series of buffers. How much data does one buffer hold? This is bounded by DefaultBufferMaxRows and DefaultBufferMaxSize, two Data Flow properties. They have default values of 10,000 and 10,485,760 (10 MB), respectively. That means, one buffer will contain either 10,000 rows or 10 MB of data, whichever is less.

You can adjust these two properties based on your scenario. Setting them to a higher value can boost performance, but only as long as all buffers fit in memory. In other words, no swapping please!

- Runying Mao

Implement Parallel Execution in SSIS

SQL Server Performance Team — Fri, 11 May 2007 23:18:00 GMT

SQL Server Integration Services (SSIS) allows parallel execution in two different ways. These are controlled by two properties as outlined below.

The first one is MaxConcurrentExecutables, a property of the package. It defines how many tasks (executables) can run simultaneously. It defaults to -1 which is translated to the number of processors plus 2. Please note that if your box has hyperthreading turned on, it is the logical processor rather than the physically present processor that is counted.

Example:

Suppose we have a package with 3 Data Flow Tasks. Each task has 10 flows in the form of “OLE DB Source -> SQL Server Destination”.

Set MaxConcurrentExecutables to 3, then all 3 Data Flow Tasks will run simultaneously.

Now whether all 10 flows in each individual Data Flow Task get started concurrently is a different story. This is controlled by the second property: EngineThreads.

The EngineThreads is a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5.

Example:

Again let’s use the above example.

If we set EngineThreads to 10 on all 3 Data Flow Tasks, then all the 30 flows will start off at once.

One thing we want to be clear about EngineThreads is that it governs both source threads (for source components) and work threads (for transformation and destination components). Source threads and work threads are both engine threads created by the Data Flow’s scheduler. So in the above example, a value of 10 for Engine Threads means up to 10 source threads and 10 work threads.

Multi tasking is a double-edge sword. In SSIS, we don’t affinitize the threads that we create to any of the processors. So if the number of threads exceeds the number of available processors, you might end up hurting throughput due to an excessive amount of context switches. Be cautious!

- Runying Mao

Something about SSIS Performance Counters

SQL Server Performance Team — Wed, 02 May 2007 03:16:00 GMT

SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package:

Buffers in use
Flat buffers in use
Private buffers in use
Buffers spooled
Rows read
Rows written

“Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks. During package execution time, you will see these counters fluctuating. But once the package finishes execution, their values should return to the same value as what they were before the execution. Otherwise, buffers are leaked. In occasions like that, please contact Microsoft PSS.

“Buffers spooled” has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping. In a case like that, please follow my previous blog (“Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives”) to set Data Flow Task properties BLOBTempStoragePath and BufferTempStoragePath appropriately for maximal I/O bandwidth.

“Rows read” and “Rows written” show how many rows the entire Data Flow has processed. They give you an overall idea about the execution progress.

- Runying Mao

Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives

SQL Server Performance Team — Wed, 02 May 2007 03:12:00 GMT

BLOBTempStoragePath and BufferTempStoragePath are two properties on Data Flow Task. They define where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to BLOBTempStoragePath. All other buffer data will be swapped to BufferTempStoragePath. See the figure below for where these two properties are exposed in designer.

Now what could cause a buffer to swap? There are two possible causes. The first one is when a memory allocation fails. The second one is when Windows signals the low memory resource notification event. Both will trigger SSIS to reduce its working set. SSIS does so by moving buffer data to disk.

When BLOBTempStoragePath is not set, the paths as defined by the system variables TEMP and TMP will be used. The same rule applies to BufferTempStoragePath.

For better performance, we recommend both BLOBTempStoragePath and BufferTempStoragePath point to fast drives. We also suggest that the drives for BufferTempStoragePath and BlobTempStoragePath be on separate spindles in order to maximize I/O throughput.

- Runying Mao

Set up OLE DB source to read from View efficiently

SQL Server Performance Team — Sun, 29 Apr 2007 23:27:00 GMT

Introduction

OLE DB source adapter is one of the most commonly used components in SSIS data flow task. In this article, we will discuss a very important performance observation about this adapter.

Use “SQL Command” to pull data from a view

OLE DB source adapter can be set up to work in either “SQL command” data access mode or “Table or view” data access mode. See the figure below for where to set Data Access Mode in OLE DB Source Editor.

In most cases, whether you use “SQL command” data access mode or “Table or view” data access mode does not make any difference performance wise. But when you are setting up OLE DB source adapter to read data from a view, the performance difference can be huge. In such an occasion, we suggest you set data access mode to “SQL command” and specify the command as, for example, “SELECT * FROM view_name”. In our in-house testing, we have seen “SQL command” data access mode runs about 17 times faster than “Table or view” data access mode in certain scenario.

If you are interested in knowing the cause for such a difference, read on.

In “Table or view” access mode, the OLE DB source adapter calls OpenRowset to get column metadata at Validate phase. The data returned from OpenRowset include more than just column metadata. Thus the adapter issues “SET ROWCOUNT 1” statement to retrieve column metadata information. “SET ROWCOUNT 1” causes an inefficient execution plan (i.e. Nested Loop) to be cached and later used in the subsequent Execute phase.

In “SQL command” access mode, the OLE DB source adapter calls “sp_prepare” to get column metadata at Validate phase, and “sp_execute” at Execute phase. The execution plan used at Execute phase is Hash Match which is more efficient than Nested Loop.

Conclusion

This article has described a performance tip when setting up OLE DB source adapter to read data from a view. We suggest user to set OLE DB source adapter in “SQL command” data access mode for better performance in such a case. We hope you will find this tip useful and helpful in your package design.

- Runying Mao

Getting Optimal Performance with Integration Services Lookups

SQL Server Performance Team — Tue, 24 Apr 2007 21:03:47 GMT

Introduction

Most users see good performance from SSIS packages using out-of-the-box configurations and with little tuning. Sometimes, though, it is necessary to do tuning to get optimal performance. One of the most commonly used transformations is the Lookup transformation. There are several techniques for getting optimal performance from a Lookup transform in SSIS. A few of these are surprising the first time you see them. This article discusses some tuning techniques that we have found can be important in different situations.

A common need in a data warehousing context would be to look up a surrogate key based on one or more business keys, before a new row is inserted into the warehouse. The screen shot below shows a highly simplified example: An arriving order record contains a customer name and phone number. Before an order can be inserted in the database, the customer name and phone number must be looked up to find the correct customer key. This simplified example will be used as the basis for the discussion in this article.

Most designers would use the obvious default behavior of the Lookup transform, selecting a table or view to look up in, as shown below.

Be selective about the lookup columns

The example scenario showed selecting data from a table. After naming the table, the designer also chooses what columns will be used in the lookup and what columns will be returned as a result of the lookup. This is done in the Columns tab of the Lookup Transformation Editor. The figure below shows that the columns C_NAME and C_PHONE will be referenced from the table, and the data from C_CUSTKEY will be returned.

Only three columns from the ORDERS table are used in this example. However, choosing a table will be interpreted as “SELECT *”. This will cause the transformation to fetch un-needed data in the other columns that do not participate in lookup operation. That is a waste of effort and memory. We recommend choosing “Use results of an SQL query” instead of naming a table, and in the query selecting only the columns that are used.

This in turn changes the column mappings. Now it is clear that only the desired columns have been selected. In our experience this can lead to a substantial gain in Lookup performance. In one case we saw recently, using this technique resulted in a doubling of the package speed.

Enable memory restriction

Reducing the data returned by the Lookup has the advantage that less work is required to return the data, and it also reduces memory usage by SSIS. This is the next area for discussion: memory usage. Being more selective about the rows returned is one technique for saving memory; another is to enable memory restriction in the Lookup transform.

Two things happen when memory restriction is enabled on a Lookup: First, the amount of memory that the Lookup is allowed to use is limited. A cache policy is used and new rows are added to the cache on demand. Second, the new rows are added to the cache individually. In other words, SSIS will query the relational database whenever data for a row cannot be located in the internal cache kept by SSIS. These are singleton (single-row) queries, unlike the large set-based table queries that occur when memory restriction is not enabled. The next figure shows where in the Lookup Transformation Editor the setting is made. In the example, 20 megabytes are allowed for the Lookup cache. This is user-settable and must be adjusted to the needs of each lookup scenario.

The benefit of enabling memory restriction is that large lookups can be performed which might not be possible otherwise. The cost is that singleton queries are used, which collectively are usually slower than a single table query. However, we have found that this performance cost is not always as bad as one might expect, if appropriate indexes are present on the lookup table. In one recent experiment, we ran a package with and without memory restriction in a lookup. The lookup table for this package had millions of rows, and about 10% of them were actually referenced by the incoming data. When there was an index on the lookup table, the package with memory restriction ran only 25% longer than the package without memory restriction. When there was not an index on the lookup table, the package with memory restriction failed to complete after 24 hours.

Consider a case where incoming records must be looked up against a large table, but the incoming records match only a few of the records in the lookup table. It might actually be faster to enable memory restriction, thus avoiding a load of the entire table, and letting the few records be fetched individually. A decision to use memory restriction for this reason must be considered in the specific context of each application.

Conclusion

Most of the time you will get good performance from Lookup transforms with default settings. Sometimes, lookups can become significant in the performance of an SSIS package. This article has given a few tips that we have found make the biggest difference in lookup performance. We hope you find them useful in your package designs.

Be selective about the columns you request in a lookup
Enable memory restriction if the lookup is using too much memory
Be sure you have an appropriate index on the lookup table when using memory restriction
Consider enabling memory restriction if the lookup refers to a small number of rows from the lookup table

- Runying Mao and Len Wyatt

What’s so temporary about tempdb?

SQL Server Performance Team — Sat, 14 Apr 2007 03:33:00 GMT

Other than #temptables, @localvariables and many other objects being stored in tempdb what’s so temporary about it? TempDB is always present on each SQL Server instance and can be a determining factor of your overall performance. In many ways tempdb could be named scratchdb or pagefiledb. The following papers will help you understand what goes into tempdb and how to plan your deployments. These articles are for both developers and administrators.

Working with tempdb in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Capacity Planning for tempdb

http://msdn2.microsoft.com/en-us/library/ms345368.aspx

Enjoy..

Tony Voellm

SQL Server 2005 SP2 Re-release and post fixes

SQL Server Performance Team — Thu, 12 Apr 2007 00:20:55 GMT

Bob Ward from PSS has a wonderful blog article that explains the details about the re-release of SQL Server 2005 SP2 and fixes posted later. This is a must read for anyone deploying SQL Server 2005 SP2 to understand the various hotfixes, GDRs and procedures. Please visit his link for more details.

SQL Server 2005 SP2 Re-release and post fixes

-- Umachandar

Microbenchmarking

SQL Server Performance Team — Tue, 10 Apr 2007 02:03:17 GMT

Microbenchmarking is the art of testing your machine's basic configuration like disk latency, memory latency, CPU performance using series of tests. You can find more details at:

Types of benchmarks

How do you assess the performance of a new machine with SQL Server? Do you run industry standard benchmarks like TPCC and TPCH? If you cannot run those yourself then what are the tests that you run. What are some of the tests that can can assess metrics like disk IO performance, CPU speed or memory speed? You could use simple TSQL batches to access some of the metrics without running your workload. Below are some of the simple tests.

Disk Performance

Below are some of the steps you could take to determine the disk performance of your new hardware.

Run DBCC DROPCLEANBUFFERS to ensure data is not resident in memory
Do a SELECT COUNT(*) query using the clustered index of a table that is spread across the LUNs you want to test
Run DBCC DROPCLEANBUFFERS again to ensure data is not resident in memory
Next do a SELECT COUNT(*) query from the clustered index of a table that is spread across the LUNs you want to test, but now with a DOP 1

Memory Subsystem Performance

To determine the performance of your memory subsystem, you can run the query with MAXDOP 1 as referenced in step #4 above after all of the data you scan is resident in memory. This will give you the memory subsystem performance.

CPU Performance

To determine the CPU difference, you can select the same single row from a table in a loop or do some simple increment operation in a loop. This should also be done when all of the data you scan is resident in memory.

Network Performance

To determine the network latency, you can run the same CPU test from a remote client and send requests from the client in a tight loop.

During all of these tests, monitor the following using the PerfMon counters:

Response Time
CPU usage (on client and server)
IO throughput
Network throughput

These are simple tests to perform and metrics to keep track of could be a valuable tool in your toolkit. Each individual test above and also in comparison with each other constitutes a micro benchmark and could be a useful guide in optimizing queries and making tradeoffs.
One test missing form above is something that tests seek performance of the disk subsystem. There are several tests that can be added.

What other tests do you use and why? What metrics do you keep track of? Please share them with us and the rest of the SQL community.

-- Ashit

New MSDN Books Online search functionality

SQL Server Performance Team — Fri, 30 Mar 2007 21:52:55 GMT

Check out the new Books Online search functionality online. The link below provides a scoped search of Books Online that returns a more precise and targeted result set. You can use it to search Books Online content quickly.

http://search.live.com/macros/sql_server_user_education/booksonline

Please direct your feedback to SQLServerUE@hotmail.com.

-- Umachandar