Given 2 SQL Server Instances where the second instance is configured with a RAMDisk for tempdb and the following test case.

-- Create source data
select top(1000000) * /* ~10 cols */ into #t1 from SomeData;

Then measure total runtime for these cascading selects;

-- Benchmark  
select * into #t4 from #t1;
select * into #t5 from #t4;
select * into #t6 from #t5;

The runtimes for me came out the same ~(15s vs ~15s). One CPU maxes out for the entire test period.

Is there a way to speed those queries across CPUs (is that tempdb file partitioning)?

Requested Data via comments:

The 1 CPU is the SQL Server 2012 process on a 12 CPU server circa 2012.
Machine: 24GMb RAM, HardDrives: 10kRpm x 2 stripe,  
RamDisk; Any popular, they are 100x to 1000x faster.
DataSource: Anything reasonable, increase # rows until stressed.
  • 3
    Seems to me that ten columns of integers is a different copying proposition than ten blob columns containing 2GB each ;c) – Paul White 9 Aug 3 '13 at 8:10
  • The last action was to refactor the numerous comments into the question as that was biggest complaint. I believe both answers are descent and inline at this stage. – crokusek Aug 5 '13 at 16:43

If you asking if SELECT INTO can use parallelism when writing, the answer is currently "no".

Parallel SELECT INTO is being added to the product in SQL Server 2014. It is functional in Community Technical Preview 1, but performance testing is not encouraged (or valid) on pre-release software. Parallel SELECT INTO does not require multiple files or file groups.

Unless your ten columns are spectacularly wide (no definition in the question!) or your CPUs particularly slow, or your RAM disk driver particularly inefficient, fifteen seconds seems quite a long time to write three copies to tempdb on RAM disk.

There again, you don't say which RAM disk product you are using, or whether/how you have analysed which process is consuming the CPU.

In versions of SQL Server up to and including 2012, the performance of a SELECT INTO is limited to the performance of a single core. Adding files to tempdb will not allow the work to be spread across multiple logical processors.

Achieving multi-threaded writing to a temporary table in 2012 involves using SSIS or a custom CLR component using the unsafe permission set and the SqlBulkCopy interface. The temporary table would have to be global (to be visible to multiple connections) or a regular user table.


Apparently not.

Case 1: Logical Files were added (total 4 db + 4 log), all on the RAMDisk. Shrunk, Restarted instance. Still showing a single CPU maxing out and ~15s. All files growing bigger.

Case 2: Logical files: 1 db + 1 log on RAMDisk, 1 + 1 on HardDisk (10krpm * 2x stripe). Same results.

So it appears no matter how fast an I/O sub-system is, a serial query from a single temp table must in the end go through a single CPU. Tempdb only allows one FileGroup. (Do multiple FileGroups in non-TempDb still bottleneck into 1 CPU?)

Concluding that, for the context of the question, adding multiple files per FileGroup within TempDb may only help when the I/O layer is relatively slower than a single CPU's throughput.

Not the answer you're looking for? Browse other questions tagged or ask your own question.