Search code examples
sql-serverperformancesql-server-2008temp-tables

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();

Solution

  • 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.