Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit

Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit.

All tests are run twice to load data into memory and pre-compile the execution plan for the second run. All results shown are for the second run. For SQL Server 2008, the tables use the new Date data type in place of Date Time, and queries are modified to avoid conversion anomalies as noted below. Below is the total (sum) CPU time in milli-seconds to execute the 22 queries in sequence  for max degree of parallelism: 1, 2, 4, and 8.

                        DOP 1         DOP 2         DOP4          DOP 8

2000 RTM             534,912    663,848    656,232    697,794

2000 bld 2187      514,881    589,245    657,543    770,272

2005 RTM 32        463,526    444,479    456,567    498,623

2005 SP2 32        464,478    403,668    413,685    452,134

2005 RTM 64        379,363    377,570    394,962    474,200

2005 SP2 64        370,206    327,149    345,155    436,491

2008 RTM             375,136    324,264    343,250    410,220

 

Duration in milli-seconds to run 22 queries by max DOP.

                        DOP 1         DOP 2         DOP4          DOP 8

2000 RTM             553,900    293,411    191,552    149,568

2000 bld 2187      566,333    276,085    188,497    164,677

2005 RTM 32        480,839    237,933    134,644    84,721

2005 SP2 32        483,842    214,804    119,525    72,515

2005 RTM 64        379,563    194,199    107,409    65,094

2005 SP2 64        370,374    166,579    94,844      59,388

2008 RTM             375,135    171,390    94,028      56,795

 

Duration for SQL 2008 64-bit

1P D 2P D 4P D 8P D
Q1 50,013 26,317 12,591 7,159
Q2 504 268 150 107
Q3 16,296 5,186 3,158 1,902
Q4 19,232 5,288 3,452 2,340
Q5 21,648 16,150 8,371 5,120
Q6 1,845 929 496 312
Q7 17,397 4,369 2,388 1,376
Q8 5,734 6,765 3,628 1,849
Q9 48,361 22,034 11,335 6,372
Q10 15,281 5,822 3,595 2,425
Q11 4,423 1,238 657 600
Q12 9,363 4,828 4,356 2,365
Q13 21,699 11,310 5,751 2,967
Q14 2,146 1,033 547 334
Q15 1,368 970 521 249
Q16 6,599 3,615 2,018 1,848
Q17 1,243 521 294 213
Q18 50,909 27,945 15,439 9,365
Q19 2,096 1,093 607 378
Q20 841 430 255 165
Q21 69,191 22,064 12,826 8,337
Q22 8,946 3,213 1,592 1,010
375,135 171,390 94,028 56,795

 

1P D 2P D 4P D 8P D
Q1 64,761 32,553 16,428 8,344
Q2 504 295 158 106
Q3 14,733 4,782 3,003 1,989
Q4 17,506 5,338 3,747 2,519
Q5 19,716 7,376 4,654 3,159
Q6 1,609 893 471 309
Q7 15,855 5,472 3,306 2,403
Q8 5,225 2,391 1,333 2,147
Q9 44,611 23,291 12,213 7,222
Q10 13,989 6,384 3,934 2,754
Q11 4,093 1,192 669 495
Q12 8,166 4,497 4,022 1,714
Q13 25,830 13,566 7,521 4,260
Q14 2,060 1,020 526 352
Q15 1,358 1,931 1,139 235
Q16 6,476 3,476 2,429 1,215
Q17 1,012 524 291 199
Q18 46,954 26,156 13,896 9,209
Q19 2,133 1,172 623 450
Q20 830 446 253 172
Q21 64,231 20,850 12,536 9,087
Q22 8,722 2,972 1,692 1,049
370,374 166,579 94,844 59,388

 

 

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s