We have a job that populates about 450,000 rows nightly in an update/insert job via a stored proc nightly. The .Net app pulls in data from a noSQL DB via an API call then pushes it out via a stored proc into SQL Server. Last night, it took 10x as long as normal.
I was trying to diagnose if it's a SQL Server issue or something in between. Here is what I checked:
-Plan Cache did the plan change from last night? There was just 1 in the cache and the cache was not under pressure so it's highly unlikely that it threw away the old plan. I have older plans sitting around but I did not explicitly check the create date on the plan.
-PerfMon metrics were all normal. CPU was about 20%, lots of free memory, no real diskIO issues (nothing above 50ms)
-started a extended events session on the spid several times for 2 minute internvals. The results consistently came back with about 5-6 seconds of total wait stats for SQL Server for a 120 second interval.
The figures were:
-I saw 0 locking or blocking anywhere on the spid and saw nothing in wait stats showing any real signs of locking outside of the pagelatch for 2.8 seconds.
-Sometimes we have network issues sending the results to the web server but we have had almost no wait stats on network on these queries.
-SOS_SCHEDULER_YIELD was the top wait stat, but CPU usage and thread count was low. There was no thread contention. These stored procedures just conduct update/inserts without major complexities and the code hasn't changed lately so I don't think it's a matter of not having enough GHZ.
again, this is over a 120 second interval.
It seems like the API might be returning data slowly to the .Net app. The .Net app doesn't really hold the data in a short term storage space so we're not sure how fast it's pulling in data then pushing it out. I'd like to further troubleshoot this by having us pull a subset or the entire set of the data into a flat file and then push it out to another test server to see how long each step takes.
Does the community feel I have sufficiently tested to claim 'this is not a ms sql server issue'? I hate making such claims without fully checking everything and want to help the team figure out the issue.
Thanks in advance.