CODEWITHSUNDEEP
sql-serverperformancesql-server-2008temp-tables
Elroy Flynn
Elroy Flynn

Reputation: 3218

Why one I/O per insert into temp table? sql server

In a simple test program I start a transaction, create a #temp table and two indexes, insert into the table a bunch of rows, and commit.

Watching task manager I/O Writes for Sql Server, I see that there is 1 disk write per table insert. This surprises me because #temp tables are not recoverable, so there's no need for writing or logging unless there is memory pressure, and even if it needed to be logged, I'd expect a minimal number of log write operations, not 1 per insert. Instead, with 20,000 inserts, I get 20,000 I/Os.

The engine has lots of memory and no pressure from other apps.

Is it possible to reduce the number of I/Os here?

Here's the gist of the code (disposes, 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();

Upvotes: 3

Views: 753

Answers (2)

Elroy Flynn
Elroy Flynn

Reputation: 3218

I was measuring with Task Managet "I/O Writes" per Sql Server, but that counter includes ALL I/O including network. See comments above, thanks Martin.

Upvotes: 0

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

Yes, by batching multiple inserts in a single transaction. Each transaction requires at least one log file write -- this is true for tempdb as it is for any other database because, despite not being recoverable, operations on tempdb still require consistency and durability for the time they're required. It's a common misconception that tempdb is "all in memory" or "does not require I/O". SQL Server does have some optimizations to reduce tempdb I/O (like caching temporary tables so they don't need to be recreated as often) but the basics of transactional I/O still apply.

By batching multiple inserts in a single transaction, you reduce the number of sequential writes that need to be waited on. If you want truly minimal logging, use bulk inserts. Incidentally, you will also get better performance by creating the indexes after all the inserts, not before (unless the inserts are somehow dependent and need lookups).

Upvotes: 1

Related Questions