6

I have a weird problem with SQL Server Management Studio, version 11.0.2100.60. When I was writing some query, sometimes the CPU gets hogged, and RAM usage keeps increasing. Usually closing and reopening SSMS will solve the problem, but it is quite annoying if I have to do this multiple times.

How to solve this issue?

enter image description here

[UPDATE]: I ran the PerfView to analyze the CPU usage. But I'm not quite sure how to troubleshoot from there:

PerfView CPU Stacks, showing ssms using a lot of CPU resource: enter image description here

Drilling down Ssms. Looks like this ntdll is taking the most resource: enter image description here

GC Heap Net Mem (Coarse Sampling): enter image description here

Improve this question
7
  • its not really unusual for monolithic tools like Visual Studio or SMSS to take up a GB or more of ram, especially if you leave them open for days or weeks at a time as I do. for the CPU, are you typing SQL code, or do you have designer windows open? have you used the "edit top 200" or anything like that which keeps connections open for long periods?
    – Frank Thomas
    Commented Apr 5, 2017 at 4:01
  • I'm not opening SSMS for days, I open only like few hours. I was editing a stored procedure, no other windows open.
    – rcs
    Commented Apr 5, 2017 at 5:34
  • Even when I close all the query tabs, the problem still persists. Needs to close the entire SSMS and reopen.
    – rcs
    Commented Apr 5, 2017 at 5:47
  • use ETW to trace CPU and memory usage
    – magicandre1981
    Commented Apr 5, 2017 at 15:13
  • have you analyzed the CPU/memory usage with xperf/WPR/WPA?
    – magicandre1981
    Commented Apr 11, 2017 at 14:40

2 Answers 2

Reset to default
2

The problem is here with Intelligence. I had a same problem today. After opening a 60kb of SQL file in SSMS, it is taking nearly 2GB of my RAM.

Anyway I figured it out. This is because the incomplete code or Joins written in that query window. If I comment out that incomplete code snippet it became normal. Check for the joins which are not closed ON condition etc or comment it out until you finish writing it.

You can see that in below pictures, memory observations before and after commenting incomplete code. It came to 128MB from 1.2GB

With Incomplete code

enter image description here

After commenting code enter image description here

Improve this answer
3
  • For my case, after I wait for some time, the memory usage will come back to normal. But this is frustrating as I have to waste my time. So I decided to turn off the Intellisense instead.
    – rcs
    Commented Nov 30, 2017 at 8:44
  • Working without intelligence is so pathetic.. its ok
    – Shakeer Mirza
    Commented Nov 30, 2017 at 9:34
  • 1
    Smaller, more modular files will also help.
    – jerrylagrou
    Commented Feb 14, 2018 at 21:21
1

I managed to bring it down from 2.5 GB to 150KB by disabling Auto Recover.

Auto-Recover Setting

Before Before

After After

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .