0

This question already has an answer here:

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.

marked as duplicate by MDCCL, LowlyDBA, Max Vernon sql-server Feb 25 at 19:33

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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 Feb 25 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 – SEarle1986 Feb 25 at 18:06
  • Seems weird it would happen to so many at once, I've never seen that (so far!) on this system – SEarle1986 Feb 25 at 18:07