Dong Xie's Blog

EXAM VOUCHER, one left.

2006-12-22T16:47:00Z

I still have one free exam voucher left from the Launch Tour 2005. You can use it to take one of the following exam:

70-431, 70-536, 70-551, 70-552, 70-553, 70-235

The voucher expires on 31-12-2006, that means you must take it before the new year. I'm sure the Prometric centre at Ealing, London will be open to book. Most of other testing centres will be either fully booked or closed.

Email me or leave comments, then I'll send you the code.

Merry Christmas and Happy New Year!

2.3 GByte/s, can your SAN do that?

2006-12-07T12:06:00Z

Recently I built a new server to run our beloved SQL Server 2005. The design idea came from Dr. Jim Gray's server brick and SQL Server Magazine March 2006 article "Pare Down and Power Up". Major difference with them are: I choose to use SAS JBOD and SAS RAID controller, and SAS drive. So that the RAID-1, RAID-0, RAID-10 jobs are offloaded from server CPU.

Here comes the proof screenshot during my configuration testing with IO Meter:

Figure 1, IO Meter screenshot, 2.37GB/s Reading speed, at a CPU load 4.75%

This is by far the maximum sustainable number I can achieve, although I believe the system could do better, at least in theory. You may argue that I won't get this fast in production, and yes, I agree, only half the spindle will goes for a Very Large Table File Group, others goes for LOG, TempDB, etc. But still, doing a table scan at 1GB/s for a 1TB sized table only need 15 mins.

Here is the challenge for the lucky SAN owners: please use the comment area to show people that your expensive SAN can do better than this. Information needed:

Your SAN vendor,
Model,
Peak speed,
Price tag

If anyone can kindly offer some prize, then we can run this as a competition! (Note: I hope Santa is browsing our Blog.)

How To: BULK INSERT your User Defined Types (UDT)?

2006-11-27T16:42:00Z

The task is to bulk load data from flat files into tables with UDT column. This blog post will show you the problem and the solution.

We will use the Point type as an example, which you can find the source code from the Books On Line (BOL), or from MSDN web, or from my blog file folder.

Suppose you've either compiled your source code into a DLL by using Visual Studio, or you downloaded it, the next step is to open SSMS, open a Query window, use TempDB or create a TEST database, then run these SQL statements:

CREATE ASSEMBLY Point

FROM 'D:\DongDoc\SQL Server Management Studio\Projects\UDT_Point\UDT_Point.dll'

GO;

CREATE TYPE Point

EXTERNAL NAME Point.[Point];

GO;

CREATE TABLE T1

(

Col1 VARCHAR(10) NULL,

Col2 Point NULL

)

GO;

Now we stuff our Table T1 with some value:

INSERT INTO dbo.T1 (Col1, Col2) VALUES ('TEST', CONVERT(Point, '3,4'));

INSERT INTO dbo.T1 (Col1, Col2) VALUES ('TEST', CONVERT(Point, '1,5'));

INSERT INTO dbo.T1 (Col1, Col2) VALUES ('TEST', CAST ('1,99' AS Point));

By doing CAST/CONVERT from string to UDT type, CLR Engine will call Point class' Parse(SqlString s) method. If now we do a SELECT *, the result is like:

Col1 Col2
---------- --------------------
TEST 0x008000000380000004
TEST 0x008000000180000005
TEST 0x008000000180000063

At this point, if you are a programmer, you should realized that UDT in the database is to persist object instances into Binary/Byte format. So these insert will success as well:

--INSERT Binary Representation of the UDT with type cast

INSERT INTO dbo.T1 (Col1, Col2) VALUES('TESTBS',

CAST(0x008000000380000004 AS Point));

--INSERT Binary Representation of the UDT without cast

INSERT INTO dbo.T1 (Col1, Col2) VALUES('TESTBS',

0x008000000380000004);

Now we prepare a text file to try BULK INSERT:

Data1.txt (save as Tab Delimited)

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

TEST 0x008000000380000004
TEST 0x008000000180000005
TEST 0x008000000180000063

The BULK INSERT statement:

--BULK INSERT

BULK INSERT dbo.T1 FROM

'D:\DongDoc\SQL Server Management Studio\Projects\UDT_Point\Data1.txt'

WITH(

DATAFILETYPE = 'char'

)

This is the error msg you got:

Msg 4863, Level 16, State 1, Line 3
Bulk load data conversion error (truncation) for row 1, column 2 (Col2).

Believe me, this is not a format file issue, you'll get similar errors if you provide a format file. But this will work:

Data2.txt (save as Tab Delimited)

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

TEST 008000000380000004
TEST 008000000180000005
TEST 008000000180000063

This exactly proves how BULK INSERT works inside SQL server, i.e. BULK INSERT sends the parsed Rowset to Query Engine directly, won't be bothered to do anything else. (For a good explanation on BULK INSERT/BCP, please go to www.sqlmag.com, look for April 2004 issue, topic "Bulk Loading Data into SQL Server 2000".)

But our task, if you still remember, is to load a file like this:

Data3.txt (save as Tab Delimited, can't be a csv, right?)

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

TEST 3,4
TEST 1,5
TEST 1,99

Please spend your time to play with this, see if you can get it BULK INSERTed, with or without format file. (In case you really got through, please let me know immediately, even it's 4am in the morning.)

IT WON'T WORK!

The reason according to me: all bulk operations are designed for a single purpose, i.e. -- BULK operation. So BULK INSERT won't invoke Point.Parse(SqlString s) at all, it just waiting there for you to give it some binary to pass to the Engine to write to the data file without any further change. This is actually provable, by using Debug technique, e.g. set break point inside Parse(...) method, then fire up BULK statement inside SSMS.

SO WHAT'S THE SOLUTION?

--solution:

INSERT INTO dbo.T1

SELECT TEM.Col1, CAST(TEM.Col2 AS Point)

FROM

OPENROWSET(

BULK 'D:\DongDoc\SQL Server Management Studio\Projects\UDT_Point\Data3.txt'

,FORMATFILE = 'D:\DongDoc\SQL Server Management Studio\Projects\UDT_Point\format.fmt')

AS TEM

format.fmt (save as Tab Delimited)

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

9.0
2
1 SQLCHAR 0 10 "\t" 1 Col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 20 "\r\n" 2 Col2 SQL_Latin1_General_CP1_CI_AS

(note: the length of 20 is somewhat arbitrary).

According to Sunil Agarwal of SQL Server Storage Engine Group at Microsoft, who is responsible for concurrency, indexes, tempdb, LOBS, supportability, and bulk import/export, this could be the only solution for our task. The original discussion with him can be found here. Also according to Sunil, this solution is still a streamed process, not a staged one. Yet beware the performance impact, for a million Point you have in the flat file, that's a million call to Point.Parse(), try write your code well.

So our hero who comes to the rescue is: OPENROWSET(BULK) statement. Enjoy!

SQL IO Simulator

2006-09-25T11:47:00Z

This is the new tool to replace SQLIOStress.

Link to SQL IO Simulator

Confusing Bug: ASP.NET customer control refuse to work

2006-08-09T19:07:00Z

I was converting a VS.net 2003 web application into VS 2005 project. Hundreds bugs turned up. I know that some of the bugs are not real, they are there only because other real bugs, but the compiler will complain them anyway. Here comes the Wisdom to choose which file to fix first, sadly, I haven't got much of it.

Noticed an error message complaining about this line in a .ascx file:
<MyTag:MyControl1 id="control1" runat="server" />

I found the Directive line at the top:
<%@ Register TagPrefix="MyTag" Namespace="MyNameSpace" Assembly="MyAssembly" %>

Since my control code is inside the \App_Code folder now (that's new in VS 05), there won't be an DLL file to refer to, so I removed the Assembly="MyAssembly" pair. Still, it doesn't work. Stared at the code for a long time, can't figure any error there. So I created a new web project, added the control code into \App_Code, declared my tag, it WORKs.

Only at this point, my rational thinking comes back from a beach holiday (I guess). Since \App_Code is holding lots of code files, VS 05 must be compiling it to a temp place, i.e. the whole folder into a single DLL, to be referenced by webforms, if a single bug in any files prevents this DLL to be compiled, then you just don't have that dll, then, you have nothing at all.

So, my customer control code is bug free, .ascx file too. It's some other files I should look at, really!

Montecito released! Finally!

2006-07-20T14:48:00Z

Intel announced the release of the first dual core Itanium 2 chip: code named Montecito, about yesterday. Although not very much the same specification as disclosed two months ago, e.g. the MHz, FSB, etc. But I think it will do good compared with the single core old Madison.

Bull has the new model with details on their website. Unisys also has news about that. But nothing was mentioned on HP's web, if you consider ~80% of Itanium boxes sold are HP branded. Oh, I did find one thing there under the Offer section, seems HP is trying to clear stock with a Buy one (CPU) get one free offer.

That all said, I personally love the new Bull NovaScale 3045, a 4U 4 processor system. The reason is it's new chipset. Montecito chip is dual core, HT enabled, that's 4 threads in total, a data crunch monster really. How to feed enough food to it is a big issue, if you aware that Intel hasn't been able to embed the memory controller into the CPU, thus a Northbridge chip is there, also the Front side bus (FSB), the limiting bottle-neck.

Figure 1. Intel's 8870 chipset diagram

Figure 2. Intel's 8870sp chipset.

If you look carefully, you'll notice that, both chipsets are providing a single FSB to FOUR Montecito, that's 16 threads in total. You'll understand what I'm saying here if you ever drive at 4pm on M25.

Bull's 3045 is using a new custome build chipset, with, Two FSB. So each FSB only deal with 2 processors. (Other series from Bull hasn't got this far). Shipping time: August.

This is really a long wait. I have been trying to get a Montecito box since Mar 2005, when I was at the 64bit trainning course at Microsoft Chertsey... ...

SSIS Lookup Transformation Dynamic SQL workaround

2006-07-13T14:17:00Z

In SSIS, Lookup Transformation is really an important tool. But the design of it prevents user to set the SQL statement dynamicly.

In my work, I need to prepare a text file for BULK loading into table. The original csv file could be 10 million ~ 1 billion rows. On the way I need to lookup several IDs by Names from different tables. These reference tables all have a Project_ID column, which can sub-grouping data for a specific project. Normally the original csv file only holds data for a single project.

Of cause I can just do:

SELECT xx_ID, xx_Name FROM dbo.TABLE1

to fill the Lookup Tran with full cache. But doing this will: use lots of memory; waste resource; and bad performance.

I would rather want to do:

SELECT xx_ID, xx_Name FROM dbo.TABLE1 WHERE Project_ID = ?

Which the Project_ID value is only known at runtime by user input parameter. Unfortunately Lookup Tran does not have that enabled (yet, maybe will in future version.)

So I came up with this idea: BCP dump + OPENROWSET (BULK).

First we use an Execute Process Task, to call Bcp.exe, specify the Arguments using Expressions, the Project_ID will be a SSIS variable. This is like you run this line in CMD:

c:\Bcp.exe "SELECT xx_ID, xx_Name FROM dbo.TABLE1 WHERE Project_ID = 101" queryout tempref1.txt -N -T

Then you will get a text file with columns you want, for the project that is relevant to this run. The reason to specify -N is to save char like in unicode and everything else in SQL server native format, this can save a lot both on file size and I/O speed for later reading process.

Second step, configure our Lookup Trans by set it to Use results of an SQL query, the query should be like this:

SELECT a.* FROM OPENROWSET( BULK 'c:\tempref1.txt', FORMATFILE = 'c:\tempref1.fmt') AS a

I didn't mention the step to create the format file.

If the SQL statement is correct and both the txt and fmt files are ok, when you click the Preview button, you'll see a data grid. Then just continue your normal configuration of Lookup Trans.

So far this works for my situation. I can do multiple Lookups by Bcp to different file name. Obviously I can't fire two instances of the package at the same time. Also I can't set the path and file name at runtime too.

Any comments are welcomed! (Maybe I can do a sample package if anyone interested, in Northwind or AdventureWorks context?)