6

I have an ASP.NET 4 project using SQL Server 2016 on Windows Server 2012. This is a dev environment with me as the only user. When I run the project in IIS8 and use Visual Studio 2015's Debug -> "Attach to Process" it connects to my w3wp process just fine. However, in Task Manager I can instantly see that the process "SQL Server Windows NT - 64 bit" jumps to a CPU usage of >20% even tough there are no queries. I repeated this several times and it's definitely related to VS-debugging. When I stop debugging it goes back to 0% for a couple of seconds but then jumps back to 20% and stays there.

The problem is that in this state even trivial SQL queries are awfully slow or produce a timeout. So debugging or even opening any DB-backed page of the project becomes nearly impossible.

The only way to relief SQL-Server and make the site usable again is to close the entire Solution in Visual Studio.

Is Visual Studio starting some (unwanted!) hidden SQL debugging when general debugging starts? If so, how can I disable this?

AndreasS
  • 315
  • 3
  • 12
  • `However, in Task Manager I can instantly see that the process "SQL Server Windows NT - 64 bit" jumps to a CPU usage of >20% even tough there are no queries` are you using developer edition of sql server – TheGameiswar Jul 02 '17 at 04:21
  • No, it's a regular install of SQL EXPRESS 2016. – AndreasS Jul 02 '17 at 05:35
  • All the free editions are also used to collect telemetry to improve product ,which may be one of the causes for cpu hike even when there are no queries – TheGameiswar Jul 02 '17 at 06:56
  • @Andreas Steidle, Whether this web project was deployed to the IIS or just debug the local web project? I just met this error before if the server is taking too much load due to IIS, maybe you could change application pools in your IIS and divide the applications running on them, it can help you to reduce your server load. Other reference: https://stackoverflow.com/questions/22944986/constant-sql-server-80-cpu-utilization – Jack Zhai Jul 03 '17 at 07:31
  • @TheGameiswar: Collect telemetry to a point that renders the product totally unusable? Hard to believe even on a free product... – AndreasS Jul 03 '17 at 17:52
  • i dont see that rendering a product unusable,i just pointed out case of cpu usage in instances even when there are no queries running – TheGameiswar Jul 03 '17 at 17:56
  • you can run sp_who2 from Adam Machanic and log it into a table for further analysis – TheGameiswar Jul 03 '17 at 17:57
  • @JackZhai-MSFT: Second sentence of my question: This is a dev environment with me as the only user!!! There IS NO LOAD on this machine. From what I can see VS-debugging is the culprit: If I turn it on it's bad. If I close the VS solution everything is instantly back to normal. Reproduced this about ten times... – AndreasS Jul 03 '17 at 17:58
  • Used sp_who2. There are 2-4 sessions with SPID >50 and non-sa logins. When I start debugging in VS these user processes do not change (no queries yet). The only difference I notice is that even sp_who2 now takes ~4sec to run. Again, once I close the solution in VS it executes almost instantly again. – AndreasS Jul 03 '17 at 21:29
  • @Andreas Steidle, Maybe you could analyze the CPU usage for your app code: https://msdn.microsoft.com/en-us/library/dn971856.aspx. – Jack Zhai Jul 05 '17 at 05:48
  • In SSMS do the following. Right Click your Server -> Reports -> Standard Reports -> Performance -> Top Queries by CPU Time. Does any of those queries pop out? – Jason Geiger Jul 05 '18 at 19:45
  • 1
    @Jason: Thanks for your suggestion. The problem is gone since I added more memory to the machine. Running VS-Debugging took up so much memory that SQL-Server obviously did some eternal swapping... And SQL Server 2016 seems to be more sensible here than its predecessor. All symptoms pointed towards a performance problem when it really was a memory problem. For me problem is solved. – AndreasS Jul 06 '18 at 22:21

1 Answers1

0

Here is the compiled answer based on above comments above...

The issue was with not enough system memory. This lead to using a lot of, slow, virtual memory that brought the system to a halt. This is a valid answer and should serve as a note for the future if somebody else has this issue.

SQL Server is "supposed" to return memory to the OS but in practice it acts like a greedy little troll. I always set it to use always have at least 2GB available to the OS. The above issue was caused by low memory. Limiting other programs memory access or increasing the available system memory should fix your problem.

Jason Geiger
  • 1,912
  • 18
  • 32