8

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

15
  • I suspect this has to do with the statistics on the tables and the types of processing done for the joins. You should look at the execution plans for the queries: nested loop joins can be a sign of problems. Feb 1, 2016 at 16:50
  • Have you tried WITH RECOMPILE?
    – SonicTheLichen
    Feb 1, 2016 at 16:56
  • 3
    Post an actual execution plan of a surprisingly slow query. The simpler the query the better.
    – usr
    Feb 1, 2016 at 17:27
  • 1
    Have you updated statistics on all tables? Have your rebuilt all indexes? Information on sp_whoisactive: link
    – TT.
    Feb 1, 2016 at 17:29
  • 2
    Those wait stats show a lot of IO but we don't know what proportion of the query is IO and what is CPU. Perform the tests that I recommended as post the results.
    – usr
    Feb 1, 2016 at 18:28

2 Answers 2

2

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

1

create a composite non clustered index on mainview(typeid, timestamp).

change your "where" on mainview so that you're not using a function against the mainview columns. this may require you to precalculate these values as variables prior to running the query if you need these to be more dynamic.

WHERE MainView.TimeStamp BETWEEN '2015-12-09 10:00' and '2015-12-09 13:00'

create a nonclustered index on ACALLS.StartTime.

change the join to ACALLS to be

WHERE ACALLS.StartTime BETWEEN DATEADD(ss,-5,MainView.TimeStamp) AND MainView.TimeStamp

from my understanding, that will handle your logic and joins at a fairly high performance and get you away from the IO.

my best guess as to what you're running into is that your data is being flushed from cache and/or tempdb is spilling to disk every now and then, so the best solution I've typically found is to write the queries better to just limit the tempdb and memory usage and the underlying problems go away.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.