We have a Data Warehouse product which runs a number of ad-hoc (IE SQL Text, not stored procs or dynamic SQL) every night.

The build took significantly longer last night than previous nights and looking at the DW Product's log, I can see a number of INSERT INTO.... SELECT.... FROM statements that have taken longer (approx 10 queries each taken circa 30 mins longer than normal) than the previous night

I have taken a couple of these queries and tried to run them in SSMS and am still seeing the slow performance. I then updated stats on the tables of these queries which has made no difference.

When they are running, I run sp_BlitzFirst and can see the usual CXPACKET and LATCH_EX waits I am used to seeing on the server and nothing out of the ordinary.

The queries have never been the best performing and are in need of tuning but I can't see what would cause this seemingly random nose dive.

The build runs overnight when there is no other activity running. I have observed today and I could see no blocking (Although I wouldn't think it was down to blocking as they run slow in SSMS now)

I have overnight Perfmon traces and they are not showing anything unusual for environment either.

What could the problem be here? It sounds parameter sniffing-esque but these are ad-hoc queries. Nothing has changed on the server.

  • 1
    It sounds like plans might have changed. This is entirely possible as data grows and statistics update appropriately. However, unless you can provide before-and-after plans, we're stuck providing guesses to an overly-broad question, "what might go wrong with SQL Server?"
    – Forrest
    Commented Feb 25, 2019 at 18:05
  • Yes that's what I thought, the only way I suppose I could find out is to restore an old version of the database an see what plan it comes out with
    – SE1986
    Commented Feb 25, 2019 at 18:06
  • Seems weird it would happen to so many at once, I've never seen that (so far!) on this system
    – SE1986
    Commented Feb 25, 2019 at 18:07