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.
