March 2007 - Posts

I've been keeping an eye on this space, first with Gigabit's I-RAM which offers 4GB of battery backed RAM drive that acts as a SATA drive but lately with Samsungs SSD.

Samsung have a Solid State Disk offering - 16GB or 32GB, and have announced a 64GB disk - take a look at the press release.

No price yet, but the 32GB is priced at £289 ex VAT on scan (http://www.scan.co.uk/Products/ProductInfo.asp?WebProductID=558790).

Here are the figures from the press release...

1.8”-type flash-SSD / rotating-disc HDD Feature Comparison
Samsung’s 64GB
Flash-SSD
Samsung’s 32GB
Flash-SSD
80GB HDD
Read/Write Speed
R : 64MB/s (4.3)
W : 45MB/s (6.4)
R : 53MB/s (3.5)
W : 30MB/s (4.3)
R : 15MB/s (1)
W : 7MB/s (1)
Weight
15g (1/4)
15g (1/4)
61g (1)
Power Consumption
O : 0.5W (1/3)
I : 0.1W (1/15)
O : 0.5W (1/3)
I : 0.1W (1/15)
O : 1.5 W (1)
I : 1.5W (1)
*. () represent the performance comparison with HDD
**. 32GB Flash-SSD was first launched in 2006
***. 64GB Flash-SSD was first launched in 2007

What does that mean in real terms for us as SQL people? Not alot at the moment because these are built to go in laptops, it does mean our demo's will be quicker I suppose :).

Imagine the performance boost you will get by putting tempdb on one of these, tempdb is used for all sorts of things and it means all sorts of things causing writes and reads to and from the disk, so with rotational disk technology you will always have a performance penalty in way of disk seek times; but with these solid state disks that all disappears. The read and write speeds are not comparable with what we can get out of todays hard drives - the figures they give (15MB for read and 7MB for write) probably relate to the disk having absolutely no write cache and the 15MB, well, not sure where they get that figure from - perhaps it was a random xKB read test causing a lot of disk seek delay.

These guys (HyperOS Systems) do a DDR based solution in the form of their product HyperDrive4 which looks very good and well thought out, the price tag is quite high though at £2,395 for a 32GB system; that said, if your system is suffering through disk performance and you've gone as far as you can with query tuning then its an option.

 

When using foreign key constraints watch out for blocking problems – remember for each reference clause and row you are inserting or updating (if the column you are updating is part of a foreign key reference) SQL Server will do a look up query to make sure the corresponding row exists in the referenced (parent) table.

An interesting behaviour came out of the wood work this week, I’m of the camp that every table should have a clustered index hence my surprise at a blocking problem I had with a client this week.

When the referenced table is a heap my query is fine and I get no blocking, change the reference column to be a clustered index and you get blocking, let’s have a look at the behaviour...

Heap (a table without a clustered index)

create table parent (

    id  int not null constraint sk_parent unique nonclustered,

 

    somedata varchar(20) not null

)

go

 

create table child (

 

    parent_id int not null references parent( id )

 

)

go

 

--  Make a row exist in the Parent...

INSERT parent ( id, somedata ) values( 1, 'NATKEY' )

 

Now to our test, run this on connection A..

BEGIN TRAN

 

UPDATE parent

    SET somedata = '12345'

where id = 1

 

Now, run this on connection B...

BEGIN TRAN

 

INSERT child ( parent_id ) VALUES( 1 )

 

All is fine and dandy, there is no blocking.

Clustered Index

create table parent (

    id  int not null constraint sk_parent unique CLUSTERED,

 

    somedata varchar(20) not null

)

go

 

create table child (

 

    parent_id int not null references parent( id )

 

)

go

 

--  Make a row exist in the Parent...

INSERT parent ( id, somedata ) values( 1, 'NATKEY' )

 

Now to our test, run this on connection A..

BEGIN TRAN

 

UPDATE parent

    SET somedata = '12345'

where id = 1

 

Now, run this on connection B...

BEGIN TRAN

 

INSERT child ( parent_id ) VALUES( 1 )

 

Interestingly connection B now blocks, but why? To answer that question we need to see what locks are being held and on what, we also need to understand more on how a heap and table with a clustered index is structured.

First though, a quick summary of Locking:

Key for Mode column

S  :: Shared Lock           I :: Intent          U :: Update Lock           X :: Exclusive

Key for TextData column

[PLANGUIDE]                Execution plan for the statement
1:41                              <file number>:<page number in database>
(010086470766) Hash of the key value in the index

Locks held for Heap method

Profiler Output for HEAP

 

The first thing we see from our profiler trace is that there are 3 data pages involved in this bit of work – pages 41, 80 and 120. Let’s take a look at what these pages are, to do this we use DBCC PAGE which is very well explained in Paul Randal’s Storage Engine blog here: http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx.

First we enable output to our query window:

dbcc traceon ( 3604 )

Next we take a look at the first page, page 41:

dbcc page( 'concurrency', 1, 41, 3 )


Couple of things here, the output gives us a result set which shows the KeyHashValue – look familiar? It’s in the profiler trace TextData column too.

FileId PageId      Row    Level  id (key)    HEAP RID           KeyHashValue
------ ----------- ------ ------ ----------- ------------------ ----------------
1      41          0      0      1           0x7800000001000000 (010086470766)

PAGE: (1:41)

BUFFER:

BUF @0x0339A0FC

bpage = 0x9693A000                   bhash = 0x00000000                   bpageno = (1:41)
bdbid = 27                           breferences = 0                      bUse1 = 47401
bstat = 0x1c0000b                    blog = 0xbbbb79bb                    bnext = 0x00000000

PAGE HEADER:

Page @0x9693A000

m_pageId = (1:41)                    m_headerVersion = 1                  m_type = 2
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 103    m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594044678144                                
Metadata: PartitionId = 72057594039697408                                 Metadata: IndexId = 2
Metadata: ObjectId = 405576483       m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 13                         m_slotCnt = 1                        m_freeCnt = 8081
m_freeData = 109                     m_reservedCnt = 0                    m_lsn = (25:116:36)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED           

The two important bits of information that I draw your attention to is Metadata: IndexId and Metadata: ObjectId, IndexId of 2 is our non-clustered index; ObjectId: 405576483 is the table ‘parent’.

Let’s now have a look at page 120..

dbcc page( 'concurrency', 1, 120, 3 )

PAGE: (1:120)

 

 

BUFFER:

 

 

BUF @0x033B68FC

 

bpage = 0x9753A000                   bhash = 0x00000000                   bpageno = (1:120)

bdbid = 27                           breferences = 0                      bUse1 = 47665

bstat = 0x1c0000b                    blog = 0x9bbb79bb                    bnext = 0x00000000

 

PAGE HEADER:

 

 

Page @0x9753A000

 

m_pageId = (1:120)                   m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8008

m_objId (AllocUnitId.idObj) = 102    m_indexId (AllocUnitId.idInd) = 256 

Metadata: AllocUnitId = 72057594044612608                                

Metadata: PartitionId = 72057594039631872                                 Metadata: IndexId = 0

Metadata: ObjectId = 405576483       m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 8                          m_slotCnt = 1                        m_freeCnt = 8073

m_freeData = 142                     m_reservedCnt = 0                    m_lsn = (25:133:57)

m_xactReserved = 0                   m_xdesId = (0:708)                   m_ghostRecCnt = 0

m_tornBits = 0                      

 

Allocation Status

 

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              

PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                         DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED           

 

Slot 0 Offset 0x60 Length 21

 

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

 

Memory Dump @0x0858C060

 

00000000:   30000800 01000000 0200fc01 0015004e †0..............N        

00000010:   41544b45 59††††††††††††††††††††††††††ATKEY                   

 

Slot 0 Column 0 Offset 0x4 Length 4

 

id = 1                              

 

Slot 0 Column 1 Offset 0xf Length 6

 

somedata = NATKEY    

As can be seen from IndexId and ObjectId, the page belongs to the ‘parent’ table and is a data page – it has an IndexId of 0 which means its part of a heap. If we did the same for page 80 we would find that page 80 is a data page for the table ‘child’, there is no corresponding index page because the table ‘child’ has no indexes defined.

Let’s summarise this, SPID 77 has an Update Lock on the Index page and corresponding key on that page, it has an Exclusive lock on the data page 120 and Record ID we are updating.

The important thing to take away is that the Update lock will not block a reader (readcommittedlock). Let’s prove this...

Making sure you have rolled back the transactions from the previous example (check by printing @@TRANCOUNT) and then cleared the profiler trace we can do a test..

In Connection A run this...

begin tran

 

select *

from parent with ( updlock )

In Connection B run this...

select *

from parent

The trace will look like this...

Profiler Trace showing behaviour of FK lookup 

You can see that on SPID 77 the Update lock is applied which prevents anybody else from updating this row, SPID 52 freely reads the row. Note – this simulates the behaviour against the index page data and not the data page, obviously if you update the data on the data page then the reader will block which is what we would see if using UPDATE instead of the SELECT ... WITH ( UPDLOCK ). As an aside to make SPID 52 block we would of used UPDLOCKX which is what happens when you use UPDATE, as another aside - ignore the hint READCOMMITTEDLOCK which is shown above, that lock is the default behaviour in SQL Server anyway.

Lost me yet? Done with the heap, so let’s look at what happens with a table with a clustered index instead.

Our next script deals with the clustered index, having rolled back the two transactions in the test query windows, run this...

drop table child

drop table parent

go

 

create table parent (

    id  int not null constraint sk_parent unique clustered,

 

    somedata varchar(20) not null

)

go

 

create table child (

 

    parent_id int not null references parent( id )

 

)

go

 

--  Make a row exist in the Parent...

INSERT parent ( id, somedata ) values( 1, 'NATKEY' )

In Connection A run this...

BEGIN TRAN

 

UPDATE parent

    SET somedata = '12345'

where id = 1

In Connection B run this...

BEGIN TRAN

 

INSERT child ( parent_id ) VALUES( 1 )

Connection B now blocks, but why? You are only INSERT’ing into the table ‘child’! The foreign key constraint is checking that the parent_id you are inserting into ‘child’ actually exists in the table ‘parent’. Not exactly obvious what is blocking unless you dig around (through profiler, IO STATS and the query plan).

Profiler Trace and FK behaviour for Clustered Index

Above is the new profiler trace, notice we have only 2 pages (109 and 120) in our locking condition.

Use DBCC PAGE again to determine what pages belong to what, you will see that (in my example) page 109 belongs to the table ‘parent’ and has an IndexId of 1, 120 has an IndexId of 0 and belongs to the table ‘child’.

We are actually blocking because of the exclusive row lock (7 – KEY) on page 109 the parent table – why?

The difference between a table with a clustered index on it and one without (a heap) is that the data row is part of the clustered index, the data row is at the bottom (leaf) level of the index hence the reason we only ‘use’ or ‘lock’ the single page, page 109 in the clustered index example above, whereas in a heap the data row and index are separate hence we have two pages – page 41 and 120.

The reason the heap does not block is because the data on page 41 (the index page) is not being updated (another good reason to use surrogate keys – but that’s another blog entry) so SQL Server can just use the Intent Update lock to state you can read it but don’t you dare update it (or delete).

That’s the behaviour, and it’s the default behaviour within SQL Server; I always recommend using a clustered index because of benefits elsewhere, so – how do we get round this blocking behaviour?

Couple of methods, the first is to disable (not remove) your foreign key constraints, this means that the lookup query is not performed, however, it does open your database to missing data; however – should that ever happen? If you thoroughly QA your application with the constraints on then any possible breach of foreign key will have been trapped and the argument could be successfully put to disable the constraints, well – on a closed system i.e. one that accepts no data imports (I suppose you could do checks there, also, there is no reason not to reenable constraints when loading data – it’s just the hit you take while SQL Server checks existing data, which can be got round).

I’m surprised setting the database options READ_COMMITTED_SNAPSHOT nor ALLOW_SNAPSHOT_ISOLATION do not help. I must admit, I did expect one of these options to ‘fix’ the behaviour and give the last good committed value – but no - the INSERT still blocks.

Ok, more on the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION that Erland Sommarskog just pointed out - these database options allow you to read old data, for instance READ_COMMITTED_SNAPSHOT would give the last committed value; for referential integrity that is no good - it could allow rows in that the other connection has just deleted! In the case of the Heap, if you Connection B had done a DELETE or UPDATE'd the referenced column then the Heap would block as well.

So, I guess the only way round this is to disable the constraints - an option if your system is suffering from blocking or excessive reads caused by FK lookups.

Also, check out Hugo Kornelis blog entry on the FK and SNAPSHOT ISOLATION -> http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/21/Snapshot-and-integrity-part-1.aspx

 

Don't get caught out with this mess!

My client has 3 servers, 2 use database mirroring (one a principal, the other a mirror) and the third is log shipped to.

Installing the windows update on the third server worked fine and was reasonably quick, the other two - well, let's just say after 10 minutes I was getting concerned! It is not a server problem because the slowness was consistent across both servers so it's obviously some feature or something I'm using - but, do not get caught out - this hot fix is not just a tools fix!

Both servers are HP and run x64 standard of SQL 2005.

I use Database Mirroring and Service Broker on these boxes, one box (the one
that took over 10 minutes) has a second \DEV instance.

So, if you are going to do this hotfix - then allow a good window of downtime.

When will the SP2 nightmare ever end?

And still no public word or hotfix from Microsoft on the database consistency check maint plan I blogged about weeks ago.

Tony.

Handmade Easter Chick and Chocolate Egg

Charity Auction

All proceeds from this auction will go to the The Stephanie Marks Appeal

http://cgi.ebay.co.uk/ws/eBayISAPI.dll?ViewItem&item=120102870149&ru=http%3A%2F%2Fsearch.ebay.co.uk%3A80%2Fsearch%2Fsearch.dll%3Ffrom%3DR40%26satitle%3D120102870149%26fvi%3D1

 

I'm meeting up with some other SQL / IT folks if anybody fancies joining us they are quite welcome.

I will be in here from about 6pm onwards -> http://maps.google.co.uk/maps?q=EC3V+9BJ&spn=0.005,0.02&hl=en, the pub is called the Red Lion and has some good and cheap (£2.50 a pint!) real Ales (http://www.beerintheevening.com/pubs/s/12/12775/Red_Lion/Monument).

My mobile 0796 816 0362.

Tony.

 

Role Title: Reporting Analyst                     Job Family: Banking/Financial/Technical         

circa £25K p.a.

 

Department: Collections               Reports To: Collections Director                                      

Purpose of Role:  A hands-on role within the Collections team, individuals at this level must be totally proficient in all aspects of reporting design using Crystal Reports and Microsoft Excel. They will be involved in the design and delivery of regular and ad-hoc reports. They will work closely with the business to translate business requirements and reporting solutions. From time to time, this individual may be required to undertake assignments not directly covered by this role profile as the needs of the business demand, as part of their personal development plan or to enhance the departmental capability.

 

Key Accountabilities

 

·         Working with business and development teams, reporting solutions and guidance for other members of the team and take responsibility for the implications and impact of design decisions.

 

·         Accurately estimating duration and effort required to complete reporting tasks.

 

·         Supplying innovative ideas and suggestion for improvement in working practices, tools and technologies to be used by the team.

 

·         Ensuring that the Collections Director is kept informed of progress, issues and difficulties in an efficient manner.

 

·         Understand the needs of the business to ensure reports are developed into working systems that fit business requirements in terms of supportability, scalability and stability.

 

Essential Skills/Qualifications:

 

·         A good academic background with a degree and at least 2 years reporting experience.

·         The ability to work closely with management and clients.

·         Strong analytical and problem solving skills.

·         Solid working knowledge of the following technologies:

·         Crystal Reports with extensive and in-depth knowledge of totalling, sub totals, formulas, parameters and table linking.

·         Microsoft Excel

·         Report design and layout

·         Microsoft SQL Database

·         Ability to plan own time efficiently with a focus on delivery of tasks whilst making themselves available to others as required.

·         Good communication skills, both written and verbal.

 

Desirable Skills/Qualifications:

 

·         Experience working on bespoke software solutions or products in an environment close to the business users

·         Familiarity with the following:

·         Experience of call centre environments

 

 

 

Email me at tonyrogerson@torver.net if you are interested and I'll forward onto my client.

 

 

If you are splitting a CSV that just contains numbers, for instance surrogate key id’s that you are passing in because of a multiple select checkbox or something and you know the values range then why split the string? It’s simpler than that – no splitting required, just use dynamic SQL and an IN coupled with a numbers table...

Create the numbers table, note – if you are just passing in the surrogate key id of say your individual table then you don’t even need a numbers table – just use the id column on the individual table...

create table numbers (

      number      int    not null primary key clustered

 

)

 

declare @i int

 

set @i = 1

 

while @i <= 32767

begin

      insert numbers ( number ) values( @i )

      set @i = @i + 1

 

end

go

 

create proc csv_to_set

      @csv varchar(max)

as

begin

      set @csv = replace( @csv, '''', '''''' )

 

      if object_id( 'tempdb..#csvset' ) is null

      begin

            print '#csvset must already exist before this proc is called'

--          create table #csvset (

--                csv_value smallint not null

--                )

      end

      else

      begin

            insert #csvset ( csv_value )

                  exec( '

                              select number

                              from numbers

                              where number in ( ' + @csv + ' )

                              ' )

 

            if @@rowcount <> ( LEN( @csv + ',' ) - LEN( REPLACE( @csv + ',', ',', '' ) ) )

            begin

                  print cast( ( LEN( @csv + ',' ) - LEN( REPLACE( @csv + ',', ',', '' ) ) ) as varchar(50) )

 

                  raiserror( 'A value exists in the CSV that is not in the numbers table', 16, 1 )

                  truncate table #csvset

 

                  return

 

            end

 

      end

end

 

The procedure is run using the example below, unfortunately because you can’t put dynamic SQL in a function then you can only do this in a stored procedure (a table valued function would remove the need for the # table).

I’ve also put a check in there that compares the @@ROWCOUNT (inserted rows) against how many values are in the CSV, if there is a mismatch then an error is thrown, but by doing this check you also prevent duplicate values being entered on the @csv, basically the IN clause does an implicit SELECT DISTINCT on your @csv removing the duplicate values – this is a bad thing if you are entering data and can have duplicates but it’s a good thing if you just want the unique values.

--    Create results table

create table #csvset (

      csv_value   smallint not null

      )

 

--    Split the string up

exec csv_to_set '55, 99, 1212'

 

--    Results

select *

from #csvset

 

drop table #csvset

Ever had the need to pass in a list of values to a stored procedure (like an array), may be the middle tier passing in a set of pools for a race, passing a set of emails associated with an event.

If you listen to Joe Celko (some links at the bottom), self proclaimed “SQL Guru” you’d be coding one parameter per passed value, for instance passing in a set of emails you’d have @email0001, @email0002 right through to @emailnnnn . He feels and arguments strongly against people who dare suggest or god forbid use a single parameter, @email_csv or @email_xml and pass in a CSV (Comma Separated Value) or XML to the procedure.

I just had to write this up, I'm getting real tired of this guy stating x, y and z when x, y, z only works in the class room and if anybody actually did this in a real application they'd end up in a real mess - if only he'd do some research before opening his mouth, but hey - when you are as 'Guru' as he is perhaps you don't need to - may be one day....

Well here’s the proof just how wrong Celko is.

Test

CPU

Reads

Writes

Duration
(secs)

1,000 Parameters

345,984

45,298

33

350

1,000 Proc Generation Cost

109

7,334

27

>1

CSV – Number Table

378,047

33,768,734

1,652

384

CSV – Dynamic SQL

27,266

40,006

0

27

Note: Figures from SQL Profiler captured on a machine dedicated to this test.

This test is as vanilla as it gets, in reality the figures for the 1,000 parameter approach would be significantly worse because of interaction with other SQL within the procedure. Imagine this out in a real system with many concurrent connections etc... There are better ways of doing the array than using the number table, dynamic SQL will always be the best performer and in 2005 security can be locked down.

This is before maintainability again for which he strongly argues that maintaining the 1,000 parameter approaches would cost less than using CSV or XML! Can you imagine the client/middle tier side code to that – the amount of additional memory required for the command object and all those parameter classes?

Practicalities around maintaining the 1,000 parameter stored procedure, even on my 4GB Dual Core machine with a fast AMD processor Management Studio takes over a minute to open the thing for edit.

Anyway, I’ll let you decide – here is the SQL that proves the results above, this was run on SQL 2005 SP2.

create database csvtosp

go

use csvtosp

go

 

create table numbers (

      number      smallint    not null primary key clustered

)

declare @i int

set @i = 1

while @i <= 32767

begin

      insert numbers ( number ) values( @i )

      set @i = @i + 1

end

go

 

create table somedata (

      avalue      int   not null primary key clustered

      )

 

--    populate

set nocount on

declare @i int

set @i = 1

while @i <= 1000

begin

      insert somedata ( avalue ) values( @i )

      set @i = @i + 1

 

end

go

 

 

--    We will pass CSV but use the numbers to split the string up

create proc getdata_csv

      @csv varchar(max)

as

      DECLARE @c int

      DECLARE @csv_extracted TABLE ( input smallint not null )

 

      INSERT @csv_extracted ( input )

      SELECT SUBSTRING( ',' + @csv + ',', n1.Number + 1,

                    CHARINDEX(',', ',' + @csv + ',', n1.Number + 1) - n1.Number - 1)

      FROM   Numbers n1

      WHERE  n1.Number <= len(',' + @csv + ',') - 1

        AND  substring(',' + @csv + ',', n1.Number, 1) = ','

 

      SELECT @c = count(*)

      FROM @csv_extracted e

            INNER JOIN somedata d ON d.avalue = e.input

 

 

go

 

--    We will use dynamic SQL, note the replace on ' to make sure there are no open ended ' to allow injection

create proc getdata_dynamic

      @csv varchar(max)

as

      set @csv = replace( @csv, '''', '''''' )

      exec( '     DECLARE @c int

 

               select @c = count(*)

               from somedata

               where avalue in ( ' + @csv + ' )'

            )

go

 

--    Create the 1,000 parm query; best this way because the actual proc once created is over 15KBytes of text!

dbcc freeproccache

go

 

declare @i int

set @i = 2

 

drop proc getdata_parms

 

declare @sql varchar(max)

set @sql = 'create proc getdata_parms @p1 int'

 

declare @sql2 varchar(max)

set @sql2 = 'exec getdata_parms @p1=1'

 

declare @in_list varchar(max)

set @in_list = '1'

 

while @i <= 1000

begin

      set @sql = @sql + ', @p' + cast( @i as varchar(4) ) + ' int'

      set @sql2 = @sql2 + ', @p' + cast( @i as varchar(4) ) + ' = ' + cast( @i as varchar(4) )

      set @in_list = @in_list + ', @p' + cast( @i as varchar(4) )

      set @i = @i + 1

 

end

 

set @sql = @sql + ' as declare @c int; select @c = count(*) from somedata where avalue in ( ' + @in_list + ' )'

 

print @sql

print @sql2

print @in_list

 

exec( @sql )

 

dbcc freeproccache

dbcc dropcleanbuffers

 

set @i = 1

while @i <= 10000

begin

      exec( @sql2 )

      set @i = @i + 1

end

 

print '---------------------------------------------------'

print '---------------------------------------------------'

go

 

dbcc freeproccache

dbcc dropcleanbuffers

 

declare @i int

set @i = 1

while @i <= 10000

begin

      exec getdata_csv '1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 295, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446, 447, 448, 449, 450, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 473, 474, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 489, 490, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500, 501, 502, 503, 504, 505, 506, 507, 508, 509, 510, 511, 512, 513, 514, 515, 516, 517, 518, 519, 520, 521, 522, 523, 524, 525, 526, 527, 528, 529, 530, 531, 532, 533, 534, 535, 536, 537, 538, 539, 540, 541, 542, 543, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 560, 561, 562, 563, 564, 565, 566, 567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581, 582, 583, 584, 585, 586, 587, 588, 589, 590, 591, 592, 593, 594, 595, 596, 597, 598, 599, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630, 631, 632, 633, 634, 635, 636, 637, 638, 639, 640, 641, 642, 643, 644, 645, 646, 647, 648, 649, 650, 651, 652, 653, 654, 655, 656, 657, 658, 659, 660, 661, 662, 663, 664, 665, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 680, 681, 682, 683, 684, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 696, 697, 698, 699, 700, 701, 702, 703, 704, 705, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719, 720, 721, 722, 723, 724, 725, 726, 727, 728, 729, 730, 731, 732, 733, 734, 735, 736, 737, 738, 739, 740, 741, 742, 743, 744, 745, 746, 747, 748, 749, 750, 751, 752, 753, 754, 755, 756, 757, 758, 759, 760, 761, 762, 763, 764, 765, 766, 767, 768, 769, 770, 771, 772, 773, 774, 775, 776, 777, 778, 779, 780, 781, 782, 783, 784, 785, 786, 787, 788, 789, 790, 791, 792, 793, 794, 795, 796, 797, 798, 799, 800, 801, 802, 803, 804, 805, 806, 807, 808, 809, 810, 811, 812, 813, 814, 815, 816, 817, 818, 819, 820, 821, 822, 823, 824, 825, 826, 827, 828, 829, 830, 831, 832, 833, 834, 835, 836, 837, 838, 839, 840, 841, 842, 843, 844, 845, 846, 847, 848, 849, 850, 851, 852, 853, 854, 855, 856, 857, 858, 859, 860, 861, 862, 863, 864, 865, 866, 867, 868, 869, 870, 871, 872, 873, 874, 875, 876, 877, 878, 879, 880, 881, 882, 883, 884, 885, 886, 887, 888, 889, 890, 891, 892, 893, 894, 895, 896, 897, 898, 899, 900, 901, 902, 903, 904, 905, 906, 907, 908, 909, 910, 911, 912, 913, 914, 915, 916, 917, 918, 919, 920, 921, 922, 923, 924, 925, 926, 927, 928, 929, 930, 931, 932, 933, 934, 935, 936, 937, 938, 939, 940, 941, 942, 943, 944, 945, 946, 947, 948, 949, 950, 951, 952, 953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 966, 967, 968, 969, 970, 971, 972, 973, 974, 975, 976, 977, 978, 979, 980, 981, 982, 983, 984, 985, 986, 987, 988, 989, 990, 991, 992, 993, 994, 995, 996, 997, 998, 999, 1000'

      set @i = @i + 1

end

 

print '---------------------------------------------------'

print '---------------------------------------------------'

go

 

dbcc freeproccache

dbcc dropcleanbuffers

 

declare @i int

set @i = 1

while @i <= 10000

begin

      exec getdata_dynamic '1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263, 264, 265, 266, 267, 268, 269, 270, 271, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 290, 291, 292, 293, 294, 295, 296, 297, 298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361, 362, 363, 364, 365, 366, 367, 368, 369, 370, 371, 372, 373, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 400, 401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411, 412, 413, 414, 415, 416, 417, 418, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429, 430, 431, 432, 433, 434, 435, 436, 437, 438, 439, 440, 441, 442, 443, 444, 445, 446, 447, 448, 449, 450, 451, 452, 453, 454, 455, 456, 457, 458, 459, 460, 461, 462, 463, 464, 465, 466, 467, 468, 469, 470, 471, 472, 473, 474, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 487, 488, 489, 490, 491, 492, 493, 494, 495, 496, 497, 498, 499, 500, 501, 502, 503, 504, 505, 506, 507, 508, 509, 510, 511, 512, 513, 514, 515, 516, 517, 518, 519, 520, 521, 522, 523, 524, 525, 526, 527, 528, 529, 530, 531, 532, 533, 534, 535, 536, 537, 538, 539, 540, 541, 542, 543, 544, 545, 546, 547, 548, 549, 550, 551, 552, 553, 554, 555, 556, 557, 558, 559, 560, 561, 562, 563, 564, 565, 566, 567, 568, 569, 570, 571, 572, 573, 574, 575, 576, 577, 578, 579, 580, 581, 582, 583, 584, 585, 586, 587, 588, 589, 590, 591, 592, 593, 594, 595, 596, 597, 598, 599, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630, 631, 632, 633, 634, 635, 636, 637, 638, 639, 640, 641, 642, 643, 644, 645, 646, 647, 648, 649, 650, 651, 652, 653, 654, 655, 656, 657, 658, 659, 660, 661, 662, 663, 664, 665, 666, 667, 668, 669, 670, 671, 672, 673, 674, 675, 676, 677, 678, 679, 680, 681, 682, 683, 684, 685, 686, 687, 688, 689, 690, 691, 692, 693, 694, 695, 696, 697, 698, 699, 700, 701, 702, 703, 704, 705, 706, 707, 708, 709, 710, 711, 712, 713, 714, 715, 716, 717, 718, 719, 720, 721, 722, 723, 724, 725, 726, 727, 728, 729, 730, 731, 732, 733, 734, 735, 736, 737, 738, 739, 740, 741, 742, 743, 744, 745, 746, 747, 748, 749, 750, 751, 752, 753, 754, 755, 756, 757, 758, 759, 760, 761, 762, 763, 764, 765, 766, 767, 768, 769, 770, 771, 772, 773, 774, 775, 776, 777, 778, 779, 780, 781, 782, 783, 784, 785, 786, 787, 788, 789, 790, 791, 792, 793, 794, 795, 796, 797, 798, 799, 800, 801, 802, 803, 804, 805, 806, 807, 808, 809, 810, 811, 812, 813, 814, 815, 816, 817, 818, 819, 820, 821, 822, 823, 824, 825, 826, 827, 828, 829, 830, 831, 832, 833, 834, 835, 836, 837, 838, 839, 840, 841, 842, 843, 844, 845, 846, 847, 848, 849, 850, 851, 852, 853, 854, 855, 856, 857, 858, 859, 860, 861, 862, 863, 864, 865, 866, 867, 868, 869, 870, 871, 872, 873, 874, 875, 876, 877, 878, 879, 880, 881, 882, 883, 884, 885, 886, 887, 888, 889, 890, 891, 892, 893, 894, 895, 896, 897, 898, 899, 900, 901, 902, 903, 904, 905, 906, 907, 908, 909, 910, 911, 912, 913, 914, 915, 916, 917, 918, 919, 920, 921, 922, 923, 924, 925, 926, 927, 928, 929, 930, 931, 932, 933, 934, 935, 936, 937, 938, 939, 940, 941, 942, 943, 944, 945, 946, 947, 948, 949, 950, 951, 952, 953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 966, 967, 968, 969, 970, 971, 972, 973, 974, 975, 976, 977, 978, 979, 980, 981, 982, 983, 984, 985, 986, 987, 988, 989, 990, 991, 992, 993, 994, 995, 996, 997, 998, 999, 1000'

      set @i = @i + 1

end

 

Links to Celko’s rants...

http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/1407cc57fb65d1ac/1006816b3f75e845?lnk=st&q=celko+%221000+parameters%22&rnum=9&hl=en

http://www.google.co.uk/search?hl=en&q=celko+%221000+parameters%22&meta=

Ester, March 2003 - March 2007

A good and funny friend who kept me company in my office while working from home passed away this morning after battling with an unknown (symptoms of stasis) illness.

Every morning, even at the ludicrous times I leave the house in the morning she'd appear jumping up on the sofa wanting my crust from my toast.

She was quite definitely the boss in our little warren here in Harpenden - always finding a way through my patio fence and onto my garden where she would be delighted to run rings round me while I tried to chase her back onto the patio - its these moments where you really see the personality of the bunny come out.

One of the most memorable (of many) funny things she did was when she run into the house from the patio clutching a 2 foot length of nettle in her mouth - very funny.

Another funny thing that shows her intelligence or cunning is her ability to realise child gates wheren't fully closed and how she used her nose and teeth to open them to get into rooms she shouldn't be in and I'd have to chase around getting her our.

Where ever you are now Ester I hope you feel you had a good life and you'll be sadly (very sadly) missed.

For those who reached here from googling Rabbit Stasis I do have advice that has actually saved both my bunnies in the past; always keep an emergency kit of Metacam (helps relax and give pain relief so the bunny has a chance) and Metacloprimide (helps get the gut going again) handy because 9 times out of 10 the vets won't be open and time is precious once you discover your rabbit has stasis (stops eating, lethargic and stops doing there business). I sort of resigned myself to the fact I might lose one of the bunnies this year because of the staggering amount that my other bunny Rosie is moulting - which you need to keep on top of because that's the cause folks!

Quick Tip about BULK INSERT and using FIRSTROW – the BATCHSIZE parameter makes performance suck and you’ll never guess why!

What do you think FIRSTROW does? According to Books Online it “Specifies the number of the first row to load.”, so baring that in mind what does BATCHSIZE do? According to BOL it “Specifies the number of rows in a batch; each batch is copied to the server as one transaction.”

Given a data file with 100,000 rows in it and you just want the last two rows you’d expect SQL Server to read data up-to and including row 99,998 and then start writing for just the last two rows you want (99,999 and 100,000) – well it doesn’t.

In a nutshell specifying a BATCHSIZE of 1 and FIRSTROW of 99999 will cause SQL Server to write 1.2GBytes of data on the LDF and MDF files only to write just a few Kbytes of actual data! The repro example is below.

Note, this is on SQL Server 2005 (could be 2000 as well but I've not tested it).

CREATE DATABASE BUlkInsert

go

USE BulkInsert

go

CREATE TABLE Test (

    somedata    char(4096)  not null

)

go

SET NOCOUNT ON

DECLARE @i int

SET @i = 1

WHILE @i <= 100000

BEGIN

    INSERT Test values( cast( @i as char(10) ) )

 

    SET @i = @i + 1

 

END

GO

 

sp_spaceused Test

GO

Now create the data file we will import in a moment..

bcp bulkinsert..test out testbulk.dat -n -T –S

TRUNCATE TABLE Test

CHECKPOINT

GO

SET NOCOUNT ON

 

SELECT * FROM sys.dm_io_virtual_file_stats( db_id(), null )

 

BULK INSERT Test

    FROM 'C:\users\tonyrogerson\testbulk.dat'

    WITH (

        BATCHSIZE = 1,

        FIRSTROW = 99999,

        CODEPAGE = 'raw',

        DATAFILETYPE = 'native',

        TABLOCK    )

 

SELECT * FROM sys.dm_io_virtual_file_stats( db_id(), null )

This will give you two result sets

database_id file_id sample_ms   num_of_reads         num_of_bytes_read    io_stall_read_ms     num_of_writes        num_of_bytes_written io_stall_write_ms    io_stall             size_on_disk_bytes   file_handle
----------- ------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------
26          1       336123593   3937                 867188736            1976212              84840                1486749696           2379713              4355925              4520607744           0x00000A60
26          2       336123593   96                   815104               700                  192953               935902208            712195               712895               40894464             0x00000928

 

database_id file_id sample_ms   num_of_reads         num_of_bytes_read    io_stall_read_ms     num_of_writes        num_of_bytes_written io_stall_write_ms    io_stall             size_on_disk_bytes   file_handle
----------- ------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------
26          1       336967453   3968                 867442688            1976278              185633               2312601600           2488036              4464314              6562185216           0x00000A60
26          2       336967453   96                   815104               700                  301099               1458697216           843031               843731               40894464             0x00000928

If we look at the num_of_bytes_written columns we can see the following IO...

MDF (file id 1)    787Mbytes

LDF (file id 2)      498Mbytes

Ouch! To read just two rows of data has caused 1.2GBytes of write activity.

Remove BATCHSIZE=1 and the problem goes away, instead of taking 13minutes it now takes 48 seconds and has the following IO characteristics:

MDF (file id 1)    16KBytes

LDF (file id 2)      77KBytes

Somewhat of a difference in performance eh! The only probably with removing BATCHSIZE is that the rows we are inserting are done as one complete transaction which if there are many then you can end up with a large transaction log.

Note, setting recovery model to Simple has no effect.