0

I've got two databases setup on two different SQL Servers. One is 2012 and one is 2016, but aside from that, they have the same CPUs, same core count, and same RAM. They're both VMs in the same location and all tests were run from the same client.

I created the tables and loaded them using the exact same method (SQLBulkCopy in .NET) and then applied the same CLUSTERED indexes to various fields on the tables (based on usage). I ran a query that is reasonably complex and returns about 200K records from a table that contains 4mil records and is JOINed to a few other tables that contain 10K (or so) records.

On the 2012 Server, my run-times (in ms) are:

21772
22864
23209

This seems pretty much about what I'd expect. But on the 2016 Server, the run-times are:

132440
122569
120030

Removing the CLUSTERED index on the largest table and replacing it with a NONCLUSTERED index results in:

72096
71042
69958

I'm looking for ideas on where to start looking for why the run-times would be so vastly different on two servers that are so similar. The run-times on 2012 are well within reason, but on 2016 they're untenable.

  • 2
    What did the query plan on those servers say? – Martheen Jun 4 at 22:47
  • Did you restore the same database from the 2012 server onto 2016? Have you done anything else since, like updated statistics, changed the compatibility level to 130, ... – Aaron Bertrand Jun 5 at 3:20
  • 1
    You're going to need to compare server settings between the two. Both at the OS and in SQL Server. Almost every time someone says "the servers are identical," they are not. – Grant Fritchey Jun 5 at 11:59
2

Many reasons are affected to your result i try to explain some important of :

Note

At First it's important to know how config your VMs many options are important like : reserved CPU ? Or reserve Memory Or Share it. How many Vms in your host and when you run your queries how many workloads your host has ? and many other reason like hypervisor config hypervisor queue and etc ... Of course it's too complex for explaining by few rows you can read this: https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/techpaper/sql_server_virtual_bp.pdf

Cardinality Estimation (CE)

After SQL Server 2012 CE is Change and use new algorithm it's one of most factor you have to pay attention when you want to check SQL Server 2012 Vs SQL Server 2016

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-ver15

But I explain about some important factor in SQL Server

CPU

CPU is very important, and config Maxdop and Cost threshold for parallelism are affected to your result.

and many things related to cpu ...

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver15

Memory

The Other important factor you have to check is memory , are your queries cash , limitation of memory and etc

and many things related to RAM ...

Disk I/O

I/O is very important in different way you can look at :

One side is what your disk IOPS is ? Or how many VMDK you are? how you config Filegroups in Vmdks and ...

the other side is How many Page IO you need to do your work in each server (related to check execution paln and Statistics , i will explain it)

TempDB

how do you config your Tempdb by how many files (it's can related to CPU) or where your TempDB is locate

https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/

Log File

how do you config your Log File How many VLF Generate What size of Log growing and ...

MDF/NDF File Config

how do you config your Data File growing ... , FileGroups and etc

Do you config Instant File Initialization https://www.sqlshack.com/an-overview-of-instant-file-initialization-in-sql-server/

and many other reason of course ...

After ALL ...

Statistics

It's very important to check you Execution Plan (DO NOT COMPARE COST , CHECK WITCH OPERAND USE) in each server and how many page IO you need to complete your action.

check your statistics are update.

when you want to compare some scenario like yours, has baseline is very helpfull to get correct result ,test your senarios a lot and find a baseline to compare

Finally when you use Non-Cluster instead of Cluster Index if i want to explain shortly : Cluster Index is structure of your data (actually is your data in B+Tree ...) when you remove it your table now is Heap and don't have Tree structure and don't need to sort , or other IO need , for this reason and many other reasons ... your bulk insert faster than before https://www.sqlshack.com/clustered-index-vs-heap/

| improve this answer | |
  • 1
    Thanks, this is quite thorough. It ends up that the Compatibility Level (discussed in your link about Cardinality Estimation) was the major factor. Changing it to 110 on SQL Server 2016 (so they both ran like 2012) brought the run-times on 2016 from 120 sec down to 45. I'm frankly shocked that a simple setting like that would have such a huge impact, especially since you'd think the newer version would be better. Further tweaking (moving to a new disk, indexes, etc) brought the run-times on part (mid 20 secs). – WATYF Jun 5 at 15:52
  • @WATYF your welcome – Sayadian Jun 5 at 21:29
0

Cardinality estimation is changed in SQL Server 2016. You can try two things:

  • To set Legacy Cardinality Estimation to True (I'm not sure if this feature is available in SQL Server 2016, it is in 2019 for sure)
  • To change Compatibility Level of the database to 110 (SQL Server 2012)

This is quick fix, but far away from real solution. You need to tune your database to use full potential of newer version of SQL Server and then to switch it back to highest compatibility level or disable legacy cardinality.

| improve this answer | |

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