Why one IO per insert to temp table? sql server
In a simple test program, I start a transaction, create a #temp table and two indexes, insert a bunch of rows into the table, and commit.
Observing Task Manager I / O Manager for Sql Server, I can see that there is 1 write to disk per table. This surprises me because #temp tables are not recoverable, so there is no need to write or log if there is no memory pressure, and even if required for logging, I expect a minimum number of log writes, not 1 per insert. Instead, with 20,000 inserts, I get 20,000 I / Os.
The engine has a lot of memory and no pressure from other applications.
Can I reduce the number of I / O operations here?
Here's the gist of the code (removed, etc. removed for brevity)
var conn = new SqlConnection("my connection string");
conn.Open();
tran = conn.BeginTransaction();
var cmd = new SqlCommand("create table #sqltt(Context int, intValue int, stringValue varchar(100))", conn, tran))
cmd.ExecuteNonQuery();
// repeat the above sqlcmd pattern to create two indexes on the table
// then
cmd = new SqlCommand("INSERT INTO #sqltt (Context, intValue) VALUES ('-1', @intValue)", conn, tran))
var parm = cmd.CreateParameter();
parm.DbType = DbType.Int32;
parm.Direction = ParameterDirection.Input;
parm.ParameterName = "intValue";
for (var i = 0; i < HOWMANY; i++)
{
parm.Value = i;
cmd.ExecuteNonQuery();
}
tran.Commit();
conn.Close();
source to share
Yes, by combining multiple attachments into one transaction. Each transaction requires at least one log file entry - this is true for tempdb as it is for any other database, because even though it is not recoverable, tempdb operations still require consistency and durability within the required time. It is a common misconception that tempdb is "all in memory" or "no I / O required". SQL Server has some optimizations to reduce tempdb I / O (like caching temporary tables so they don't have to be recreated as often), but the basics of transactional I / O still apply.
By combining multiple attachments in a single transaction, you reduce the number of consecutive records to wait. If you want really minimal registration, use volumetric inserts. By the way, you will also get better performance by creating indexes after all inserts, and not before (unless the inserts are somehow dependent and need to be searched).
source to share