Search results matching tags 'Architecture' and 'Performance'

Accelerate OLTP with HP and Microsoft's New High Performance Reference Architecture

KKline — Tue, 06 Mar 2012 15:25:00 GMT

If you haven't started to read Shashank Pawar (blog), you're missing out. Shashank is part of Microsoft Australia and has been writing some very good content lately. Here's an example from the Reference Architecture for High Performance SQL Server:

HP and Microsoft engineering teams have worked together to create a reference architecture to Accelerate Online Transaction Processing (OLTP) database workloads with a fully-flash based HP/Microsoft architecture and achieve significant performance increases, simplified database manageability, and industry leading TCO.

The details come in a torrent after that leading paragraph with lots of pretty pictures and charts to help explain. This is great stuff, especially for competitive platforms such as Oracle Exadata.

Read more about the new HP High Performance Reference Architecture for SQL Server 2012 here.

And just out of curiousity, are any of you using high performance architectures such as Oracle Exadata, IBM Netezza, or Teradata? I'd love to hear your feedback, questions, and comments.

Enjoy,

-Kev

-Follow me on Twitter

-More on my KevinEKline.com

Bad database practices: moving data to procedures vs. moving procedures to data

Linchi Shea — Fri, 30 Oct 2009 04:00:00 GMT

Is it better to move data to procedures or move procedures to data?

The answer is, of course, “it depends.” Let’s consider a scenario where you have two SQL Server instances: ServerA and ServerB, and you have a procedure on ServerB (call it procB), but need to access data on ServerA.

Three database solutions are common:

Solution 1: You can first copy the data from ServerA to ServerB (using any number of technologies such as replication or a simple query via a linked server), and then have procB access the data locally on ServerB, however many times.

Solution 2: You can have procB access the data on ServerA from ServerB through a linked server using a mechanism such as a distributed join or an openquery.

Solution 3: You can create a procedure procA on ServerA, and have procB make a remote procedure call to procA to retrieve its result, and then further process the returned data in procB on ServerB, if necessary.

In a given application, any of these three solutions may be optimal, depending on the characteristics of that application. In particular, it depends on (1) the amount of data the procedure needs to access, (2) how current the data must be, and (3) how often the procedure needs to access the data.

For instance, if the amount of data on ServerA is large, it is okay for procB to read from a copy of the data that may be slightly or even significantly lagging behind its source, and (3) it needs to be accessed frequently in procB, then some version of Solution 1 can be a good choice.

The key consideration is that moving data, especially a large amount of data, across servers can be expensive, and moving a piece of code is cheap.

It follows that if a process involves moving a large amount of data across server boundary for processing on a different server, it may be cheaper to move the code, or a relevant part of it, to where the data resides, and only send back the result, if the result is significantly smaller than the amount of data needed for processing.

Note that what is considered ‘a large amount’ should not be measured in terms of how many gigabytes, or even how many megabytes. Rather, it should be measured by the relative cost of moving the data versus that of the total process. So for instance, your procedure may be moving only 128KB of data from server A to server B, which generally speaking should be inexpensive, and the whole procedure executes quickly. But if this procedure is called very frequently, moving that 128KB constantly effectively ends up moving ‘a large amount’ of data, and can become rather expensive.

This sounds trivial. But unfortunately, I have seen too many cases in the real world where little thought is given to these considerations when creating procedures that access data across multiple servers.

In some instances, it is a case of abstraction abuse. By that, I mean people sometimes are too easy getting carried away with using other stored procedures and views (i.e. abstraction units), not knowing that they are incurring expensive cross-server data operations, or not exploring alternative options to cut down moving data across servers.

In other cases, programming convenience ends up trumping application performance. For instance, I have seen a case that goes something like the following:

The procedure has a three table join that includes two tables from a linked server and a very small local table. The result is inserted into a temp table. The temp table is further processed in a series of update statements. Some of the updates use yet another very small local table. The final result set from these updates is small, and is used in the rest of the procedure.

It turns out that the two remote tables are relative large, and this procedure is called frequently. In addition, since the distribution join touches a significant proportion of each table of these two remote tables, basically all the rows are copied over locally to process the join.

One way to optimize this procedure is to move the code segment mentioned above to the remote server and wrap it in a procedure there. This basically moves the entire process of preparing the final result set into the procedure on the remote server, and the local procedure now just needs to make a call to this new procedure, gets the small result set and move on with the rest of its processing. This is a perfect example where you want to move your procedures to your data instead of moving your data to your procedures.

So the bad practice is to not think through the trade-offs between moving data to procedures and moving procedures to data when architecting cross server solutions, and end up moving an unnecessary amount of data.

Amdahl’s Law

Linchi Shea — Tue, 12 Feb 2008 04:43:00 GMT

For the past few weeks, I’ve been working on a diverse array of issues ranging from studying SQL Server performance on various multi-core processors, pondering the implications of many-core processors, troubleshooting SQL Server performance problems, looking at the scalability of Oracle RAC and Sybase shared-disk clusters, and so on.

Everywhere I turned, I bumped into the signs of Amdahl’s law, in particular Amdahl’s parallelism law. If you studied the computer architecture in school, you almost certainly have come across Amdahl’s various laws including his parallelism law below:

If a computation has a serial component S, a parallel component P, and there are N processors, then the maximum speedup is as follows

Speedup =	S + P
	S + P / N

This law is often stated in various alternative but equivalent forms. The following is an alternative expression of Amdahl’s law in terms of the fraction of the computation that must be done serially (F):

Speedup =	1
	F + (1 – F) / N

One of many important implications of this law is that even if the number of processors goes to infinity, the maximum speedup you may get is:

Max Speedup =	S + P
	S

Therefore, if the serial component is a large fraction of the whole computation, you won’t get much speedup no matter how many processors you may have. On the other hand, if the parallel component is large, the speedup can be significant.

So, if your system has a shared global state, for consistency it must be accessed (updated) in a serialized fashion. This law suggests that if your workload is seriously bottlenecked on this global state, you won’t get much performance improvement by focusing your efforts on getting better processors, more processors, or improving any other hardware or software parallel components.

In the case of using the Intel multi-core processors, if your workload happens to bottleneck on its front-side bus, it most likely would not help to switch to faster processors. For an interesting discussion of the implications of Amdahl's law on multicore computing, I highly recommend reading this article: "Amdahl's Law in the Multicore Era".

And if you are designing a database application, but the design relies heavily on the shared tempdb, thus causing significant serialization on the tempdb system data structures, the scalability of your app will suffer.

In the case of RAC and Sybase shared-disk clusters, if your workload is such that it cause a huge amount of inter-node traffic to maintain cache coherency, your app will not scale well with more nodes in the cluster.

These observations are intuitive, and are easy to understand without you ever being exposed to Amdahl’s law. However, Amdahl’s law elegantly highlights the common thread to you, and is worth revisiting from time to time.

Where does Time Go?

Linchi Shea — Wed, 02 Jan 2008 05:02:00 GMT

A significant part of my job is to evaluate how SQL Server (and sometimes other DBMSs) performs on various hardware platforms, in particular on the processors and its related chipsets as they are being released. So naturally, I’ve been paying attention to performance analysis of DBMSs.

One of the papers at the top of my reference list for this area was written by a group of researchers from University of Wisconsin: “DBMSs On A Modern Processor: Where Does Time Go?” This paper was published in 1999, but its findings and methodology are still interesting.

The key conclusion of the paper is that when data is memory resident, the main bottleneck to the DBMS performance is L2 data cache misses, and to some extent L1 instruction cache misses. This conclusion appears to be valid even with today’s commercial DBMSs, given that the conclusion is still being quoted in recently published studies.

If you are interested in the DBMS performance with respect to processors and memory hierarchies, I highly recommend this paper.

Best and Worst Checkpoint Performance

Linchi Shea — Thu, 01 Nov 2007 04:05:00 GMT

The best documentation on the I/O behavior of SQL Server checkpoints is found in SQL Server 2000 I/O Basics by Bob Dorr. In particular, you should read the following carefully:

SQL Server uses the following steps to set up another page for flushing and repeats for up to 16 total pages inclusive of the first page.

Do a hash lookup for the next contiguous page. For example, if the page to be flushed were page 100, SQL Server searches for page 101 in the buffer hash array.

If the page is not located, then the end of contiguous I/O block is established and the I/O is posted.

If the page is located, acquire latch to prevent further modifications if the page may be dirty.

Check to ensure that the page is dirty and needs to be written. If not, release the latch and consider the end of contiguous I/O block as established and submit the asynchronous I/O request.

If dirty, follow the steps preceding these that tell you how to flush a single page.

After the set of pages to be flushed is determined, the WriteFileGather function is invoked to post (Async / OVERLAPPED) the I/O request with the associated callback function to complete the I/O operation.

In SQL Server I/O Basics, Chapter 2, Bob Dorr further states that, "SQL Server 7.0 and 2000 could issue a WriteMultiple operation for up to 16 pages (128 KB). SQL Server 2005 extends the capability of WriteMultiple up to 32 pages (256 KB)."

For most disk I/O subsystems, large I/Os--such as 256K writes--are much more efficient than small I/Os such as 8K writes. It thus follows that if SQL Server checkpoints are forced to issue single-page I/Os, checkpoint performance would seriously suffer.

To see this for myself, I conducted a little test on SQL Server 2005. First, I created a heap table that is one page per row, and populated it with 1,000,000 rows, resulting in ~8GB of used space. The table schema is as follows:

CREATE TABLE tbTest(
    i      int NOT NULL, 
    filler char(8000) NOT NULL
)

And the table was populated with the following script:

SET IMPLICIT_TRANSACTIONS ON

DECLARE @batch_size int
SET @batch_size = 100000  

DECLARE @i int
SET @i = 1
WHILE @i <= 1000000
BEGIN
    INSERT tbTest(i, filler) VALUES (@i, ' ')

    IF (@i % @batch_size = 0)
    BEGIN
       COMMIT TRAN 
    END
    SET @i = @i + 1
END
if @@trancount > 0
   commit tran

SET IMPLICIT_TRANSACTIONS OFF

Since the test server had 12GB physical memory allocated to the SQL Server buffer pool, all the pages of this table could be cached in memory. I then disabled automatic checkpoints with trace flag 3502 so that I could control when I wanted to checkpoint the database, and study the checkpoint performance.

I examined two extreme SQL Server 2005 database checkpoint scenarios:

The Best Scenario where all the dirty pages were contiguous and all the checkpoint I/Os should be 256K writes, and
The Worst Scenario where no two dirty pages were contiguous and all the checkpoint I/Os should therefore be 8K writes

The best scenario was accomplished with the following code:

update tbTest
   set filler = 'xyz'
 where i < 500000

declare @dt datetime
set @dt = getdate()
  checkpoint
select datediff(second, @dt, getdate())

And the worst scenario was produced with this code:

update tbTest
   set filler = '123'
 where i % 2 = 0

declare @dt datetime
set @dt = getdate()
  checkpoint
select datediff(second, @dt, getdate())

There was no difference in execution duration between the two UPDATE statements. After all, they both performed a full table scan and updated the same number of rows (i.e. 500,000). However, note that the first UPDATE statement modified the first 500,000 consecutive rows, thus 500,000 contiguous pages, whereas the second UPDATE statement modified every other row, leaving no two dirty pages contiguous.

The following chart shows a significant contrast between the checkpoint durations of these two rather extreme scenarios:

In this test, it took about four times as long to checkpoint the same number of dirty pages in the worst scenario (duration =~ 80 seconds) as it did in the best scenario (duration =~ 20 seconds). The performance difference was staggering.

How did I know that SQL Server checkpoints were doing large or small I/Os? That was through observing the disk counters while the checkpoints were in progress. In the worst scenario, the Avg. Disk Bytes/Write counter remained almost a constant of 8192 bytes = 8KB, while in the best scenario, the same disk counter hovered around 260,000 bytes =~ 254KB. [Thanks, Andrew Kelly, for pointing out a mistake here--the two underlined words were incorrectly transposed. Updated 2007/11/01.]

It is also interesting to note that in the best scenario, the Current Disk Queue Length counter was about 20 for most of the time, whereas in the worst scenario the Current Disk Queue Length spiked to 100 or even higher. Apparently, SQL Server was trying to push the I/Os harder with more outstanding async I/O write requests when it detected that a checkpoint was issuing small block sized I/Os. This didn't quite do the trick as that pushed up the I/O latency--as measured by Avg Disk sec/Write--to greater than 48ms per write in the worst scenario as compared to ~20ms per write in the best scenario, even when the former was doing smaller I/Os.

So besides confirming what Bob Dorr described with a concrete example, are there any practical implications or can we apply the discussions to anything practical? Of course, but I'll leave that as an exercise for you to ponder.

Hash Partition as a Design Method

Linchi Shea — Tue, 23 Jan 2007 02:35:00 GMT

SQL Server 2005 does not support hash partition as a product feature. But that doesn't mean you can't apply the concept of hash partition in your application or database design.

Recently, I worked on an application that used SQL Server to persist the customized ASP.NET session state information, similar to the SQL Server mode for ASP.NET session state management. The table schema and the stored procedures were similar to those found in the Microsoft KB article 311209. The most salient points to note are:

The session state information was stored in a single table,
Every row was associated with a GUID session state id, and
Every stored procedure had a session state id parameter

In studying the scalability and capacity of the session state database, I found that under stress load the database transaction throughput was not constrained by CPU, memory, disk I/Os, or network. But rather, the transaction throughput was limited by contentions inside SQL Server. More precisely, almost all the SQL Server resource waits were found to be on page latches. Therefore, reducing these page-level contentions should lead to higher transaction throughput.

One potential solution was to split the single table into multiple tables. There were several different ways to split the table. One approach was to split the table into multiple tables of the identical structure in the same database.

For the load test, I took a different, but simpler, route to split the table into multiple identically-structured databases. As a matter of fact, I created 20 clones of the original database. These 20 databases differed only in their names. I kept the original database name, but removed all the user tables, and replaced all the stored procedures that were directly called by the application with their corresponding wrapper stored procedures. The only function of a wrapper stored procedure was to route the calls from the application to the 20 newly created databases, which were not directly visible to the application.

So how would the incoming stored procedure calls be routed? Hash partition was the answer.

Basically, since each call contained a GUID session state id, we could map that GUID to a number corresponding to one of the 20 databases, and route the call to that database. The following is a sample procedure for routing a call to store procedure ExecCommand:

CREATE PROCEDURE dbo.ExecCommand

@id varchar(32),

@param1 varchar(128),

{ other parameters }

SET NOCOUNT ON

DECLARE @hash int

SET @hash = (abs(checksum(@id)) % 20) + 1

IF (@hash = 1)

EXECUTE sessionDB1.dbo.ExecCommand

@id,

@param1,

{ other parameters go here }

ELSE

IF (@hash = 2)

EXECUTE sessionDB2.dbo.ExecCommand

@id,

@param1,

{ other parameters go here }

ELSE

... -- other @hash values

ELSE

IF (@hash = 20)

EXECUTE sessionDB20.dbo.ExecCommand

@id,

@param1,

{ other parameters go here }

ELSE

-- Should never reach here

RAISERROR('Invalid code path for session id %s and hash %d', 17, -1, @id, @hash)

RETURN

The hash function in the above code snippet is (abs(checksum(@id)) % 20) + 1. This function maps a GUID to an integer between 1 and 20, inclusive, and the mapping is relatively evenly distributed.

So how effective was this hash partition strategy? I ran the same stress load test after the hash partition was applied with everything else being the same. The following chart shows the performance difference:

With the original single-table design, the chart shows that after 50 simulated users the database was stressed out, and the transaction throughput actually started to drop significantly as even more users were added. However, when the table was hash partitioned into 20 databases, it took more than 200 simulated users to saturate the databases. For 100 users, the transaction throughput improved by about 50%, and for 200 users, the throughput improved by about 65%.

Not bad for a simple backend change that does not require any change on the application side!

32-bit vs. x64

Linchi Shea — Tue, 02 Jan 2007 16:02:00 GMT

When it comes to the issue of 32-bit vs. x64, you can easily walk away from a presentation with an impression that going to x64 will most likely give your SQL Server application a performance boost. I'd like to show you--with some test data--that you should be careful with that impression.

The following three environments were tested on exactly the same hardware (an HP DL 585 with four dual-core sockets and16GB of physical memory):

SQL2005 x86x86. In this test environment, SQL Server 2005 32-bit Enterprise Edition (RTM) runs on Windows Server 2003 32-bit Enterprise Edition SP1.
SQL2005 x86x64. In this test environment, SQL Server 2005 32-bit Enterprise Edition (RTM) runs on Windows Server 2003 x64 Enterprise Edition SP1.
SQL2005 x64x64. In this test environment, SQL Server 2005 x64 Enterprise Edition (RTM) runs on Windows Server 2003 x64 Enterprise Edition SP1.

The following four different types of workloads--generated with a custom-built TPC-C-like test kit--were applied to each of the above three test environments:

Memory-bound read-only workloads. The test database was scaled for 100 warehouses with the database populated at about 8.5GB. 4GB out of the 16GB physical memory was allocated to the SQL Server 2005 instance. Read only stored procedures (doing work similar to the TPC-C OrderStatus and StockLevel transactions in a 50/50 mix) were submitted from 200 threads (simulating 200 users) running on a separate computer.
In-memory read-only workloads. The test database was scaled for 100 warehouses with the database populated at about 8.5GB. 12GB out of the 16GB physical memory was allocated to the SQL Server 2005 instance. Read only stored procedures (doing work similar to the TPC-C OrderStatus and StockLevel transactions in a 50/50 mix) were submitted from 200 threads running on a separate computer.
I/O-bound read-only workloads. The test database was scaled for 1000 warehouses with the database populated at about 79GB. 12GB out of the 16GB physical memory was allocated to the SQL Server 2005 instance. Read only stored procedures (doing work similar to the TPC-C OrderStatus and StockLevel transactions in a 50/50 mix) were submitted from 200 threads running on a separate computer.
I/O-bound read-write workloads. The test database was scaled for 1000 warehouses with the database populated at about 79GB. 12GB out of the 16GB physical memory was allocated to the SQL Server instance. Both read-only and read-write stored procedures (all five TPC-C transactions--NewOrder, OrderStatus, Payment, StockLevel, and Delivery--in the standard mix) were submitted from a workload driver on a separate computer.

The test results are shown in the following four tables. The transaction throughput was measured as the number of StockLevel transactions per second for the read-only workloads and the number of NewOrder transactions per second for the read-write workloads.

Table 1. Memory-bound Read-only Workloads

Environment	Transaction per second (StockLevel)
SQL2005 x86x86	2800
SQL2005 x86x64	2700
SQL2005 x64x64	2600

Table 2. In-memory Read-only Workloads

Environment	Transaction per second (StockLevel)
SQL2005 x86x86	3600
SQL2005 x86x64	3680
SQL2005 x64x64	2800

Table 3. I/O-bound Read-only Workloads

Environment	Transaction per second (StockLevel)
SQL2005 x86x86	220
SQL2005 x86x64	215
SQL2005 x64x64	225

Table 4. I/O-bound Read-write Workloads

Environment	Transaction per second (NewOrder)
SQL2005 x86x86	450
SQL2005 x86x64	440
SQL2005 x64x64	440

The absolute number of transactions per second is not important. Instead, you should focus on their relative values among the three test environments. The test results tell us the following:

For the in-memory read-only workload, x64 SQL2005 on x64 Win2K3 (i.e. SQL2005 x64x64) achieved about 2800 transactions per second, while both SQL2005 x86x64 and SQL2005 x86x86 reached about 3600, which is 29% better than SQL2005 x64x64.
For the other workloads, there was little or no significant difference in transaction throughput whether it's 32-bit or x64.

Now, these test results do not suggest in any way or shape that you should not go to x64. But rather, after you walk away from a seminar that touts x64 and before you commit your application to x64, do some tests yourself with your application workload! Of course, you may decide to move to x64, not for any immediate performance gain, but for future growth and scalability. That--coupled with a hardware upgrade--can be a good reason in itself indeed!

Rowset string concatenation: Which method is best?

Adam Machanic — Thu, 13 Jul 2006 01:15:00 GMT

Yeah, yeah, yeah, let's get this out of the way right from the start: Don't concatenate rows into delimited strings in SQL Server. Do it client side.

Except if you really have to create delimited strings in SQL Server. In which case you should read on.

There was a little discussion on SQLTeam about the best way to concatenate. I recommended a scalar UDF solution, whereas Rob Volk recommended a solution involving a temp table.

I mentioned my dislike for the temp table solution for a couple of reasons. First of all, it relies on a clustered index for ordering. That will probably work in this example, but is not guaranteed to always work and relying on indexes rather than ORDER BY for ordering is definitely not a habit I want anyone to get into. The clustered index as it was described in Rob's example also has another problem that I didn't even notice until I was writing this entry. But I'll get to that in a moment. The second reason I dislike the temp table is that I felt it would be less efficient than the scalar UDF.

Rob didn't agree about the efficiency. And so I set out to prove him wrong...

We'll use the Authors table in Pubs. I want a comma-delimited list, per state, of the last name of each author who lives there.

First, the scalar UDF:

USE pubs
GO

CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SET @Output = ''

	SELECT @Output =	CASE @Output 
				WHEN '' THEN au_lname 
				ELSE @Output + ', ' + au_lname 
				END
	FROM Authors
	WHERE State = @State
	ORDER BY au_lname

	RETURN @Output
END
GO

To find the list I want:

SELECT DISTINCT State, dbo.ConcatAuthors(State)
FROM Authors
ORDER BY State

... And the adaptation of Rob's temp table method... I did change two things due to problems I discovered during testing. One, I've altered the au_lname column to VARCHAR(8000); the column in the Authors table is VARCHAR(40), not large enough for all of the California authors. What if we were dealing with a much larger dataset? Second, I added an IDENTITY column, and I'm clustering on that instead of the actual data to get the ordering. I'm doing so because of the VARCHAR(8000). Index rows can be a maximum of 900 bytes, so if we had enough data to exceed that length, this method would fail.

CREATE TABLE #AuthorConcat
(
	State CHAR(2) NOT NULL,
	au_lname VARCHAR(8000) NOT NULL,
	Ident INT IDENTITY(1,1) NOT NULL PRIMARY KEY
)

INSERT #AuthorConcat 
(
	State,
	au_lname
)
SELECT
	State, 
	au_lname
FROM Authors
ORDER BY 
	State, 
	au_lname

DECLARE @Authors VARCHAR(8000)
SET @Authors = ''
DECLARE @State CHAR(2)
SET @State = ''

UPDATE #AuthorConcat
SET @Authors = au_lname =	CASE 
				WHEN @State = State THEN @Authors + ', ' + au_lname 
				ELSE au_lname END,
	@State = State

SELECT State, MAX(au_lname) 
FROM #AuthorConcat
GROUP BY State

Clever, but more complex and harder to read than the scalar UDF version. Output is identical, but that's not why we're here. Which one is more efficient?

Drumroll, please...

Results were tabulated using STATISTICS IO, STATISTICS TIME, and Query Analyzer's Show Execution Plan. DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE were run before each test.

Scalar UDF Method
Total cost: 0.0492
Total Scan count: 1
Total Logical reads: 2
Total Physical reads: 2
Total time: 25 ms

Temp Table Method
Total cost: 0.2131
Total Scan count: 4
Total Logical reads: 9
Total Physical reads: 2
Total time: 88 ms

So in conclusion, neither method is incredibly taxing with the tiny Pubs dataset, but I think I have proven that the UDF is far more efficient.

Update, February 28, 2005: Modified the adapation of Rob Volk's method to use a CREATE TABLE instead of SELECT INTO, as the latter is not necessarily guaranteed to insert rows in the right order for the sake of this example. Thanks to "PW" on SQLServerCentral for pointing this problem out. Note that this changed the total costs very slightly -- for the better -- but the UDF still performs better by quite a large margin.