insert, update & delete performance joe chang [email protected]
TRANSCRIPT
![Page 2: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/2.jpg)
Insert, Update and DeleteInsert, Update and Delete
IUD BasicsMulti-row Inserts
Logical IO count
IUD Operations and Indexes
IUD Operations and Foreign Keys
![Page 3: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/3.jpg)
Insert Plan – 1 RowInsert Plan – 1 Row
Insert Table(…)Values(…)
No indexes other than primary key
No foreign keys
![Page 4: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/4.jpg)
Insert – I/O CostInsert – I/O Cost
Note: difference in I/O cost
Insert I/O cost dependsOn number of row in table!
0 & 1 row
> 300 rows
![Page 5: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/5.jpg)
Insert Plan I/O Cost versus RowsInsert Plan I/O Cost versus Rows
0.010
0.012
0.014
0.016
0.018
1 10 100rows
Inse
rt I
/O C
ost
0.0100
0.0101
0.0102
0.0103
0.0104
0.0105
10 100 1000 10000rows
Inse
rt I
/O C
ost
320 r/p
99 r/p
I/O cost
![Page 6: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/6.jpg)
Insert – Clustered IndexInsert – Clustered Index
Clustered index more or less same as Table
> 320 rows
![Page 7: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/7.jpg)
Insert Plan Cost & Logical I/OInsert Plan Cost & Logical I/O
Logical I/O count1st rowTable 'MIC_01'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0.2nd rowTable 'MIC_01'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0.Row ~65,000Table 'MIC_01'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0.
Insert Cost FormulaI/O: 0.010068378 to 0.016756756CPU: 0.00000100 per rowTotal: 0.010070635 to 0.016759014
Plan cost independent of indexes at low row counts
![Page 8: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/8.jpg)
Disk SettingDisk Setting
Enable Write Caching andEnable Advanced Performancehas large impact on log writeperformance
1) Write to disk, continue after confirmation
2) Write to disk, continue immediately
Settings vary fromWindows 2000 ServerWindows XPWindows Server 2003
![Page 9: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/9.jpg)
INSERT & Physical DiskINSERT & Physical Disk
Each standalone INSERT statement must be matched to 1 or more write I/Os to transaction log, may or may not result in write to data,
SQL Server may consolidate transaction log entries from separate threads (Process ID or SPIDs) into a single I/O on the transaction log file
Log writes for statements inside BEGIN/COMMIT TRANSACTION are consolidated?
![Page 10: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/10.jpg)
BEGIN/COMMIT TRANBEGIN/COMMIT TRAN
Which is faster and more efficient?
WHILE @I < 100,000 BEGIN INSERT Table(…) VALUES (@I, …) SET @I = @I + 1END
BEGIN TRANSACTION WHILE @I < 100,000 BEGIN INSERT Table(…) VALUES (@I, …) SET @I = @I + 1 ENDCOMMIT TRANSACTION
A
B
![Page 11: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/11.jpg)
Update Update
UPDATE N1N SET Value = 'ABC123456D‘ WHERE ID = 1
UPDATE MXN SET ID9 = 1 WHERE ID = 1 Non integer values
No Compute Scalar for Updates to Clustered Index
![Page 12: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/12.jpg)
Table Update – Index SeekTable Update – Index Seek
Same as plain Index Seek
![Page 13: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/13.jpg)
Table Update – CS & TopTable Update – CS & Top
CPU: 0.0000001 / row
CPU: 0.0000001 / row
![Page 14: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/14.jpg)
Table UpdateTable Update
![Page 15: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/15.jpg)
Clustered Index UpdateClustered Index Update
Single component, but numbers don’t add up
![Page 16: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/16.jpg)
Update Plan CostUpdate Plan Cost
Same cost structure as Insert plus additional Index Seek cost(I/O costs depend on Table density and row count)
Clustered IndexI/O: 0.010068378CPU: 0.00000100 per rowTotal: 0.016477678
TableIndex Seek: 0.0064081Compute Scalar: 0.0000001Top: 0.0000001Table Update I/O: 0.010071216
CPU: 0.00000100Total: 0.016480517
Index Seek cost implied?
![Page 17: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/17.jpg)
DeleteDelete
![Page 18: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/18.jpg)
DeleteDelete
![Page 19: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/19.jpg)
Multi-row InsertsMulti-row Inserts
Compare two separate Insert statements:
INSERT N1C(ID,Value) VALUES (321,'TYI539087J')INSERT N1C(ID,Value) VALUES (322,'TYI539087J')
With statement below
INSERT N1C(ID,Value) SELECT 321,'TYI539087J‘UNION ALL SELECT 322,'TYI539087J'
![Page 20: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/20.jpg)
Multi-row Inserts – Union AllMulti-row Inserts – Union All
INSERT N1C(ID,Value) SELECT 321,'TYI539087J‘UNION ALL SELECT 322,'TYI539087J'
![Page 21: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/21.jpg)
Multi-row InsertsMulti-row Inserts
![Page 22: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/22.jpg)
Multi-row InsertsMulti-row Inserts
2 rows
CPU: 2XI/O: same
![Page 23: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/23.jpg)
Multi-row InsertsMulti-row Inserts
![Page 24: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/24.jpg)
Multi-row SelectMulti-row Select
SELECT @Value1 = CASE ID WHEN @ID1 THEN VALUE ELSE @Value1 END, @Value2 = CASE ID WHEN @ID2 THEN VALUE ELSE @Value2 END
FROM M2C WHERE ID IN (@ID1,@ID2)
SELECT @Value1 = VALUE FROM M2C WHERE ID = @ID1SELECT @Value2 = VALUE FROM M2C WHERE ID = @ID2
Plan Cost is lower than 2 separate selects, but actual performance is worse!
![Page 25: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/25.jpg)
Multi-row DeleteMulti-row Delete
DECLARE @ID1 int, @ID2 int SELECT @ID1 = 1, @ID2 = 49999
DELETE MIC WHERE ID IN (@ID1,@ID2)
Has not been tested!
![Page 26: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/26.jpg)
IUD with Additional IndexesIUD with Additional Indexes
IUD ops may need to modify indexesInsert & Delete – always
Update – only if modified value is in index
Plan costs for low row counts Not dependent on indexes
Counter intuitive, but plan not impacted
IUD w/larger row counts Plan depends on indexes
![Page 27: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/27.jpg)
Inserts with indexes - I/O countInserts with indexes - I/O count
Index depth: Clustered 2, Nonclustered 1
No indexes other than primary keyTable 'MIC'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0.
1 Nonclustered indexTable 'MIC'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0.
2 Nonclustered indexesTable 'MIC'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0.
2 I/O for Clustered Index (Index Depth 2) 1 I/O for each nonclustered index at Index Depth 1
![Page 28: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/28.jpg)
Insert with Select QueryInsert with Select Query
Primary key – clustered, and 1 nonclustered index
Up to ~500 rows
INSERT MIC(…) SELECT … FROM M2C
> ~505 rowsSELECTINSERT
![Page 29: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/29.jpg)
Multiple IndexesMultiple Indexes
![Page 30: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/30.jpg)
Update w/IX, large row countUpdate w/IX, large row count
600 rows
![Page 31: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/31.jpg)
Update multiple IX, large row countUpdate multiple IX, large row count
One for each index excluding PK
![Page 32: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/32.jpg)
Spool & SequenceSpool & Sequence
Spool I/O 0.008752485 + 0.0074975/pageSpool CPU 0.00000040 + 0.000000360/row
Sequence CPU 0.0000020/row
![Page 33: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/33.jpg)
Delete w/Index large row countDelete w/Index large row count
505 rows
1 NC Index
2 NC Indexes
![Page 34: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/34.jpg)
Foreign KeysForeign Keys
ALTER TABLE [dbo].[M2C] ADD CONSTRAINT [FK_M2C_M2D] FOREIGN KEY ( [ID2] ) REFERENCES [dbo].[M2D] ( [ID] )
ON DELETE NO ACTION ON UPDATE NO ACTION
![Page 35: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/35.jpg)
Insert w/Foreign Key ConstraintInsert w/Foreign Key Constraint
INSERT M2C (…) VALUES (50001,…)
Statistics IO:Table 'M2D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.Table 'M2C'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0.
Index depth 2, both tables
FK
PK
![Page 36: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/36.jpg)
Insert FK details
![Page 37: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/37.jpg)
Delete w/FK ConstraintDelete w/FK Constraint
DELETE M2D WHERE ID = 50001
Statistics IO:Table 'M2C'. Scan count 1, logical reads 507, physical reads 0, read-ahead reads 0.Table 'M2D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
506 leaf level pages
FK
PK
![Page 38: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/38.jpg)
Delete–FK & Table Scan comparedDelete–FK & Table Scan compared
FK
PK
![Page 39: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/39.jpg)
Delete – Reference Table ScanDelete – Reference Table Scan
Unusually low cost
Expected cost for 506 pages,50,000 rows
From Delete op FK Reference
From normal Table scan
![Page 40: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/40.jpg)
Index on Foreign KeyIndex on Foreign Key
CREATE INDEX IX_M2C_ID2 ON M2C(ID2)
INSERT M2C (…) VALUES (50001,…)Statistics IO:Table 'M2D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.Table 'M2C'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0.
DELETE M2C WHERE ID = 50001Statistics IO:Table 'M2C'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0.
![Page 41: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/41.jpg)
Delete with Indexed Foreign KeyDelete with Indexed Foreign Key
DELETE M2D WHERE ID = 50001
Statistics IO:Table 'M2C'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.Table 'M2D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
FK
PK
![Page 42: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/42.jpg)
Update with Foreign KeyUpdate with Foreign Key
Update Primary Key table
Update Foreign Key table
FK
PK
FK
PK
![Page 43: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/43.jpg)
Query Cost ModelQuery Cost Model
Actual Query Costs in CPU-Cycles
Stored Procedure Cost = RPC cost (once per procedure)+ Type cost (once per procedure?)+ Query cost (once per query)
Query – one or more components
Component Cost = + Component base cost+ Additional row or page costs
![Page 44: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/44.jpg)
INSERT CharacteristicsINSERT Characteristics
0
2,000
4,000
6,000
8,000
1 2 4 6 8 10Threads
RP
C/s
ec
Single row INSERTClustered index, no other indexesNo Foreign Keys2x2.4GHz server
050,000
100,000150,000200,000250,000300,000350,000400,000450,000
1 2 4 6 8 10Threads
CP
U-C
ycle
s
0%10%20%30%40%50%60%70%80%90%
Mea
n C
PU
Uti
l.
CPU-Cycles
CPU Util
Net CPU-cycles cost – excludes RPC cost
![Page 45: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/45.jpg)
Clustered, Heap, Non-Clust.Clustered, Heap, Non-Clust.
0
100,000
200,000
300,000
400,000
500,000
1 2 4 6 8 10Threads
CP
U-C
ycle
s
Clustered
Heap
Non Clustered
Single row INSERT 1) Clustered index 2) Heap with no indexes 3) Heap with 1 non-clustered index
Log write consolidation?
Context switch reduction?
![Page 46: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/46.jpg)
INSERT – Multiple RowsINSERT – Multiple Rows
0
5,000
10,000
15,000
20,000
25,000
1 2 4 6 8 10Insert Statements per Stored Proc
RPC/sec
Inserts/sec
Multiple single row INSERT statements per stored proc8 threads
0
5,000
10,000
15,000
20,000
25,000
30,000
35,000
1 2 4 6 8 10
Insert rows per Stored Proc
RPC/sec
Insert rows/sec
Multiple rows per INSERT statement (UNION ALL)8 threads
![Page 47: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/47.jpg)
IUD Cost StructureIUD Cost Structure
P4/Xeon* Notes
RPC cost 240,000 Higher for threads, owner m/m
Type Cost 130,000 once per procedureIUD Base 170,000 once per IUD statementSingle row IUD 300,000 Range: 200,000-400,000
Multi-row InsertCost per row 90,000 cost per additional row
*Use Windows NT fibers on
INSERT, UPDATE & DELETE cost structure very similarMulti-row UPDATE & DELETE not fully investigated
![Page 48: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/48.jpg)
INSERT Cost StructureINSERT Cost Structure
Index and Foreign Key not fully explored
Early measurements:
50-70,000 per additional index50-70,000 per foreign key
![Page 49: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/49.jpg)
IUD SummaryIUD Summary
Consolidate IUD statements where possible
Large impact on performance
Verify impact of BEGIN/COMMIT TRAN
REPEATABLE READ & SERIALIZABLE not tested
Index & Foreign Key overheadSome cost on IUD for each index
Most app 90% Read, 10% Write?
Is FK required for data integrity?
![Page 50: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/50.jpg)
Test TablesTest Tables
CREATE TABLE [dbo].[M2C] ([ID] [int] NOT NULL ,[ID2] [int] NOT NULL ,[ID3] [int] NOT NULL ,[ID4] [int] NOT NULL ,[ID5] [int] NOT NULL ,[ID6] [int] NOT NULL ,[GroupID] [int] NOT NULL ,[CodeID] [int] NOT NULL ,[Value] [char] (10) NOT NULL ,[randDecimal] [decimal](9, 4) NOT NULL ,[randMoney] [money] NOT NULL ,[randDate] [datetime] NOT NULL ,[seqDate] [datetime] NOT NULL
) ON [PRIMARY]
50,000 rowsIndex depth 299 row per page506 pages
![Page 51: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/51.jpg)
Test DataTest Data
DECLARE @I int, @rowCnt int, @p int, @sc1 int, @dv1 intSELECT @I = 1, @rowCnt = 50000, @p = 100, @sc1 = 10SELECT @dv1 = @rowCnt/@sc1WHILE @I <= @RowCnt BEGIN INSERT M2C (ID, ID2, ID3, ID4, ID5, ID6, GroupID, CodeID,
Value, randDecimal, randMoney, randDate, seqDate) VALUES ( @I, @I, 1 + (@I-1)*@p/@rowCnt + ((@I-1)*@p)%@rowCnt, @I/4, @I/10, (@I-1)%(320) + 1, (@I-1)/@sc1 + 1, (@I-1)%(@dv1) + 1, CHAR(65+26*rand())+CHAR(65+26*rand())+CHAR(65+26*rand())+CONVERT(char(6), CONVERT(int,100000*(9.0*rand()+1.0)))+CHAR(65 + 26*rand()), 10000*rand(), 10000*rand(), DATEADD(hour,120000*rand(),'1990-01-01'), DATEADD(hour,3*@I,'1990-01-01') ) SET @I = @I+1END
![Page 52: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/52.jpg)
Test Data SequencesTest Data Sequences
WHILE loop variable @I: 1,2,3,…
Function Sequence(@I-1)/10 + 1 increments every 10 rows(@I-1)%(10) + 1 10 distinct values repeating
1,2,3,4,5,6,7,9,10,1,2,3
![Page 53: Insert, Update & Delete Performance Joe Chang jchang6@yahoo.com](https://reader035.vdocuments.us/reader035/viewer/2022062408/56649eb75503460f94bc0eac/html5/thumbnails/53.jpg)
LinksLinks
www.sql-server-performance.com/joe_chang.asp
SQL Server Quantitative Performance AnalysisSQL Server Quantitative Performance AnalysisServer System ArchitectureServer System ArchitectureProcessor PerformanceProcessor PerformanceDirect Connect Gigabit NetworkingDirect Connect Gigabit NetworkingParallel Execution PlansParallel Execution PlansLarge Data OperationsLarge Data OperationsTransferring StatisticsTransferring StatisticsSQL Server Backup Performance with Imceda LiteSpeedSQL Server Backup Performance with Imceda LiteSpeed