IO block Size

I have read an interested blog post about the IO block Size.

Here it is .

Also, important information can be found here (please keep in mind it’s a sql server 2008 version).

The conclusions are:

• transaction log flushes – 512 Bytes UP TO 60KB
• data file IO
  • checkpoint – 8KB to 1MB
  • lazy writer -8KB to 1MB
  • reading – 8*8KB to 1MB
  • read ahead – 128KB to 512KB
  • bulk loads – 256KB
• Backup/restore – 1MB

S

Insert

I’ve wrote about parallel insert in a older post. This is an update to it.

Starting with sql server 2014, the engine allowed parallelism of the SELECT …INTO newTable operations.

Starting with sql server 2016, the engine allowed parallelism of the INSERT … SELECT operations, but in some conditions :

• use of TABLOCK : INSERT table (TABLOCK) …SELECT
• compatibility level of db  = >= 130.

 

But , there some conditions that disable parallelism :

• presence of a clustered index or any additional non-clustered indexes
• IDENTITY column

See more here

 

 

S