I think that is a very per-use case question.

For example, the cost to host a data warehouse is going to be different than a highly OLTP workload.

And there are a...

If I am understanding you correctly, you want to know the second largest record.  If so, one way you could do this is to do a nested SELECT TOP query. ...

From my understanding both IDX1 and IDX2 would use the exact same space.  They are holding the exact same data, just in potentially a different order.

If there is any compression...

I think I can help with that one.

IDX1 would have the 3 columns with the data ordered by OrderID, then ItemID, and finally Status.  If there were more columns in...

Looks like someone asked about this on the MSDN forums and got a few answers:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/196b4586-1338-434d-ba8c-49fa3c9bdeeb/server-collation-sqllatin1generalcp1cias-versus-latin1generalcias

The important bits from the above forum post (the whole thing is a good read mind...

Alternate to triggers, but similar idea, you could implement CDC (change data capture).

If the database table is for a custom-built app, you could also implement audit history like that on...

How many rows are in the table?

I am pretty sure that your WHERE clause is the problem, not the ROW_NUMBER portion.  The query, without the WHERE doesn't need to do...

If you load up extended events or profiler, you can see what is being run against the database.

My guess (without seeing anything) is that your shared dataset doesn't have the...

Just to confirm - you don't have any triggers on the table, right?  Those can cause all sorts of surprises like that.

William - to address both replies (Jeffery and sterling3721, correct me if I am misrepresenting):

1 - the new driver MAY have some optimizations in it that cause it to give...

My guess - your queries don't need to use all of the cores OR your SQL isn't configured to use all of the cores when going parallel... which is best...

I imagine you could build up a DLL that could handle it and do some magic on the SQL side, but it is not going to be a quick operation.

Alternately,...

I am not 100% sure, but I am about 95% sure that that is not possible and would be a HUGE performance hit to go that route anyways.  A better...

Locks are a required part of SQL Server.  You wouldn't want 2 queries updating a single piece of data at the exact same time.  Much better for them to run...

Just to confirm - you are NOT reindexing anything during that job step, right?

I just found this post:

https://www.sqlservercentral.com/forums/topic/sql-job-fails-giving-message-2528

which says that message 2528:

"The message 2528 is what DBCC Reindex always outputs"