question

APF avatar image
APF asked

Should I see a major difference in CPU usage (2008 R2 vs 2016)?

I've just copied a 30TB database from a SQL Server 2008 R2 installation on physical hardware to a SQL Server 2016 installation in a VMWare farm. I guess I'm not surprised it's slower. But can someone explain the difference in the "set statistics io on, time on" output though? It shows both tables involved are entirely in memory, but the CPU ticks are significantly higher on SQL Server 2016. (The query plans are identical.) They each have 24 cores - with maxdop set 8. I run this query on both: INSERT BLS.dbo.tDetail select DN as d, TN as t, 0 as v, fR as f, 0 as r, 0 as m, 0 as x from tTest where SID = 70967 The statistics for the 2008 R2 install show: SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. Table 'tDetail'. Scan count 0, logical reads 2815868, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tTest'. Scan count 24, logical reads 15573, physical reads 0, read-ahead reads 15, lob logical reads 0,, lob physical reads 0, lob read-ahead reads 0. (2802331 row(s) affected) (1 row(s) affected) SQL Server Execution Times: CPU time = 4181 ms, elapsed time = 4491 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. The statistics for the 2016 install show: SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 4 ms. Table 'tDetail'. Scan count 0, logical reads 2815868, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'tTest'. Scan count 24, logical reads 15573, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (2802331 row(s) affected) (1 row(s) affected) SQL Server Execution Times: CPU time = 9375 ms, elapsed time = 10672 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
querysql servercpu
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

See below (the high elapsed time is sending ~3 million rows to SSMS) On the 2008 R2 server, the stats output is: (2802331 row(s) affected) Table 'tTest'. Scan count 24, logical reads 15573, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 562 ms, elapsed time = 14062 ms. On the 2016 server, the stats output is: (2802331 row(s) affected) Table 'tTest'. Scan count 24, logical reads 15573, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 3157 ms, elapsed time = 37769 ms.
1 Like 1 ·
What's the query like without the INSERT? ie just the select.
0 Likes 0 ·
Are the settings identical on each server? Are the execution plans the same?
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.