It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion.

There are 2 possible base reasons for your slow down.

1. You upgraded your system and rebooted it
2. You load a bunch of data in it

Let's take a look at part No. 1

It might be that your SQL Server configuration might be broken. This can cause serious problems regarding your Server speed and the disc usage.

Please check in the first instance your basic server settings. Those basic settings are max server memory, affinity I/O mask, affinity mask and max degree of parallelism. You may need to enable the advanced options using show advanced options.

Here is a complete script:

-- enable advanced options
EXEC sp_configure 'show advanced options',1
-- apply configuration
RECONFIGURE
-- how much memory can the sql server allocate?
EXEC sp_configure 'max server memory'
-- which cpu is used to run I/O operations
EXEC sp_configure 'affinity I/O mask'
-- which cpus can run processes?
EXEC sp_configure 'affinity mask'
-- how many threads can work on one query part?
EXEC sp_configure 'max degree of parallelism'

Compare the result with your documented values in your installation steps. Are they still the same?

It may have many reasons why your server behaves so strange. I would normally bet, that your max server memory is just wrong. This will cause your SQL Server permanently swapping data pages. He can't hold everything in his memory. This means he need to read the pages from the disc, update it, write it instantly back. If another update comes along and uses the same page for an update, it can't be read from the memory. Instead the server needs to read it again from the disc. Just swapping...

Another problem can be a to high affinity on disc or processes. If you used a shared Server (SQL Server + other services) with a dedicated disc for SQL Server (which may be a rare case, but it could be), this could be your problem. Your server normally used to have for example 3 cpus for processes and one for I/O. The other 12 cpus are used for other services. In this case your affinity mask is wrong and uses for example an automatic configuration. This means your Server uses all the 16 cores for processes and I/O dynamically. If you have huge processes running, they can put a huge load on the disc, which it may not handle. But in fact, I don't believe that this is your case. It would be faster (even if just a bit) if this would apply, but your case is a slow down.

Another problem may be a too high degree of parallelism. Which means you have too many threads idling on one partial of a query. This could also cause a huge slow down if the parallelism don't work as expected. But this won't describe your high I/O in total.

Now let's take a look at part No. 2 too

You load a bunch of rows into your system. Even if this is a regular job, it could raised a limit in which your query plans escalate. It could be even the case that your insert in combination with SQL Server produces this behavior.

Yοu mentioned that you already tried to migrate your indices to another disc, which seems to help. This can be the happened just to the fact that you split the load on two different discs.

It may be that your indices were fractured, that your plans were fractured or that your statistics are just outdated.

1. lets check the statistics last update You can do this manually over the interface for each single statistic element. Which would be a pain. Or you can try this code:

SELECT name AS indexname,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes

This will give you a complete information over each index (and heap) and the statistics behind them. Even if you run sp_updatestats it doesn't mean that the statistics were updated. The part when an update is quite tricky, even if you run sp_updatestats or even if auto update statistics is enabled, the statistics won't be updated just in time. Here are some edge points, when an update is needed/generated:

• An empty table gets one or more rows
• A table with more than 500 rows updates 20% + 500 additional rows and an insert happened afterwards
• When 500 rows were changed in a table that holds less than 500 rows

This means, your statistics may be outdated even if you run the update.

You can take a look at the query above. If you find some pretty old statistics in some tables, you may want to run an manual statistic update for this table:

UPDATE STATISTICS dbo.YourBadTable WITH FULLSCAN

After that, you may want to give your server a kick in the ass to throw away all old plans.

DBCC FREEPROCCACHE 

If you just want to clean all caches, you might want to run this instead:

DBCC FREESYSTEMCACHE ('ALL')

This will clean up all caches, not just the plan cache. I would normally warn, to use this on a production server in production phase. But as your server don't work currently, you can't harm them too much. It might slow down for some seconds maybe 1-2 minutes as he needs to rebuild all caches, but after that he should run with the correct plans.

Another reason can be totally fragmented indices. This can be checked on the whole server using this statement:

SELECT * 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)

If the fragmentation is very high, you might need to reorganize (fragmentation < 20%) or totally rebuild (>20%) it. This may take more pressure on your disc and cause trouble. On the other hand, if the indices are that bad, it probably would help in the end more than it harms.

Beside those two reasons, there still may be a third problem

It maybe that your server is configured probably, you haven't changed any code in this time, just added a few rows. All statistics are updated and all caches are rebuild. All your indices are reorganized in the way you need them, but still - nothing works. It just might be that you reached the limit of available memory in your processes. Maybe you need more. You can simply check if there is any process which tries to get more memory than you have.

You can check this using this command:

SELECT * FROM sys.dm_exec_query_memory_grants

It will provide you a list of all sessions which consuming memory. There might be some query which is still waiting to get memory. Those queries can be easily filtered. All sessions where granted_memory_kb IS NULL. These are sessions which requested memory but don't get it. Another thing can be an granted memory which may be to low. You can compare the columns requested_memory_kb with granted_memory_kb. Requested shows how much memory the process needs to run optimal while granted shows up the memory which is enable for the process. If a process needs 2GB to run but only gets 2MB... you might get it on your own. ;-)

Another way is to check the RESSOURCE_SEMAPHORE:

SELECT * FROM sys.dm_exec_query_resource_semaphore

You can take a look at the waiter_count and the grantee_count. If the waiter is above 0, you have pressure on your memory, which may cause swapping and may cause the disc pressure seen by you in the perfmon.

Since you are running Microsoft SQL Server 2014 - 12.0.2000, the very first RTM build including the new Cardinality Estimator I would strongly suggest you try updating to one of the latest CU's.

As stated in this blog post on msdn

You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix.

SQL Server 2014 Service Pack 1 made various fixes on new Cardinality Estimator (new CE). The release notes also documents the fixes.

You already have TF 4199 active so the improvements should become active automatically.

I would strongly advise you to update and use the new Cardinality Estimator but with all the more recent fixes instead of disabling the new CE alltogheter.

If you still have issues with this single query you can add a plan guide that says OPTION (QUERYTRACEON 9481) for this query alone.