SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index
You have a non-SARGable query - even if you have good indexes, you're not using them with that query.

First, a knee-jerk reaction to pageiolatch_sh is reading pages from disk into the buffer; you don't have enough RAM for the data it's trying to pull.

Second, you need to look at the execution plan and its use of indexes - or the lack thereof.

Quit using functions in your joins and your WHERE, and only get the data you absolutely need to start with.


"BETWEEN ACALLS.StartTime and DATEADD(ss,-5,ACALLS.StartTime)" - get rid of that DATEADD in the BETWEEN.


If this is a read only, reporting database, then create a reporting table with just the data you need, and then put compound indexes on it as required.  Use that one to get the primary keys/other unique keys of the ACALLS rows you need, then get the rest of the ACALLS data later.

WHERE DATEPART(hour,MainView.TimeStamp) BETWEEN 10 and 13 and 
CAST(MainView.TimeStamp as date) = '2015-12-09'


same thing - get rid of the CAST - change '2015-12-09' to one or two @parameters of the right data type for MainView.TimeStamp >= @StartTimestamp AND MainView.TimeStamp &lt; @EndTimestamp
and get rid of that DATEPART by restricting the @StartTimestamp and @EndTimestamp to include your hours criteria as well.



Perhaps load up a #temp table with just the primary/unique keys of rows that meet those MainView criteria before the join.

Hmm... also, if Mainview is a complex view, go straight to the base tables to load up that #temp table

Don't forget to use Profiler to check and see if adding (compound if need be) indexes on the #temp or other staging table is a net gain or a net loss :).