1

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:

Pagelatch_EX: 2800ms

SOS_SCHEDULE_YIELD: 1499ms

writelog: 182ms

Network_IO: 2ms

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

  • What did you use to store all these metrics from the time the slowdown occurred? – Reaces Dec 3 '14 at 19:24
  • extended events on the query SPID to record query wait stats. Windows PerfMon for disk/cpu/memory, and queried the plan cache. – Ali Razeghi Dec 3 '14 at 21:17
  • My point was actually, it looks like you were very thorough. But by the way your post was written it seemed to me like you took statistics, after the slowdown had passed. Which might not be a proper indicator. – Reaces Dec 3 '14 at 21:24
  • ah i gotcha, thanks. These were gathered live during the issue. It seems like it wasn't a SQL Server issue now that the other temas got to investigate it. – Ali Razeghi Dec 3 '14 at 21:32
  • 1
    You could try to have the Stored Proc pull data from SQL server itself into SQL Server (or use a CSV file as the source). That way, you could prove that once the noSQL is removed, things run fast again. – Thomas Kejser Dec 9 '14 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.