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.