Sql-server – SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index

sql serversql server 2014

I'm running a 350GB database on my PC with ~40 million rows.

SQL Server 2014, Win7, AMD 8350 @ 4.8GHZ, 16 GB of RAM, and a 500 GB
SSD (database is hosted on it's own 500 GB SSD, with a throughput of
500MB/500MB read/write).

The database is not being updated, I'm just analyzing/reading it. With the creation of a few indexes, any join, count(*) etc, takes less than 1 minute, which is ok for my purposes. I have been running some queries (after running a single join query, 40-50 times, it becomes slow) on the data, and now calls that took 1 minute, are still running 20 minutes later.

I keep a careful eye on system resources, and I can see the SSD kick in when the query starts, it reads for 20-30 seconds, then it reads at 121kB/second for the next 20 minutes. This is not a CPU problem, or disk problem. I am limited with my amount of RAM, however the calls run fine when I first loaded the database, now, nothing runs, 25 minutes later.

Effectively I cannot query the database anymore, any call takes excessively long even a basic SELECT statement. I have tried rebuilding the indexes and updating statistics, but no difference.

I don't have a lot of experience on this so it's entirely possible that my SQL query is incorrect, in which case I'd expect an error, or for it to finish executing with 0 results, but neither occurs.

What I am trying to do is count all instances of a 'TypeID', in the 5 seconds prior to a time based on the table ACALLS.

SELECT ACALLS.StartTime, ACALLS.Time, ACALLS.ServerIP, ACALLS.SRVR,   ACALLS.calls, ACALLS.TOKEN, COUNT(TypeID) as ExecRate
FROM ACALLS
INNER JOIN MAINVIEW ON 
MainView.TimeStamp BETWEEN ACALLS.StartTime and DATEADD(ss,-5,ACALLS.StartTime)

WHERE DATEPART(hour,MainView.TimeStamp) BETWEEN 10 and 13 and 
CAST(MainView.TimeStamp as date) = '2015-12-09' and
MainView.TypeID = '123456789'
GROUP BY Acalls.STartTime, ACALLs.TIME, ServerIp,SRVR, ACALLS.CALLS, ACALLS.TOKEN
ORDER BY Acalls.StartTime

After running "Who is Running",
enter image description here
enter image description here

Best Answer

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 < @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 :).

Related Question