Vastly different query run-times on different servers 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.
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/