BUG: V9.0.1.3179 - Out-of-Memory Exception multiple times, SSMS did crazy things

PDinCA

With the default of "Enable Code Analysis" checked, SQL Prompt causes SSMS OOM Exceptions, repeatedly.

The worst occurrence:

1. Pasted a table-name in a tab that I'd been using for sp_blitzindex, and F5. It ran for the table I was expecting.
2. I looked at another tab, which wasn't visible (i.e., it wasn't in another vertical group - all tabs were in the original group and no split-screen operations of any kind had or were in use). That tab showed TWO pastes of a 3-line comment from several minutes before. The tab had been saved, and it had only one comment block at save-time.
3. Going back to the sp_blitzindex tab, the table name had reverted on its own
4. Any subsequent attempt to activate a tab, or save anything, or anything at all did not get the typical Management Studio is Busy message - it was just completely unresponsive.

The log file appears to state, by this, "ERROR RedGate.SQLPrompt.CommonUI.Utils.ErrorDialog - Report error dialog UI shown", that the dialog to report an error to Red Gate using {sa} was active - not on top, it wasn't... and no evidence of it. (Windows 8.1).

SSMS is 17.3. I had about 14 tabs open (screen is very wide, and I can see all the tab headings). Connected to the usual Dev server, on which I had connected to 5 databases including master. This isn't at all unusual - I'm often connected to 3 or 4 instances, so 8 databases of prompt objects is more like my norm. I also explicitly set SSMS to use Large Memory, via "Large Address Aware.exe". Since doing that when 17.3 came out, I don't get OOM Exceptions until I've retrieved millions of result-set rows over the wire, which doesn't happen very often.

Did I change any Code Smells rules? Yes. I turned off ST008.

Enable Code Analysis is OFF! So far, so good...

Log file attached.

UPDATE: Dec 7th
Turned OFF for over a day and ZERO SSMS incidents. STRONGLY suggest this "feature" is delivered INACTIVE as a default.

Michael Clark

Hi @PDinCA,

Thanks for getting in touch. We're really sorry to hear that out of memory exceptions stopped you using code analysis. We are trying to improve code analysis and your feedback is really valuable.

I can see from the log file you uploaded that you are using SQL Prompt 9.0.1.3179. That version already had some improvements to issues around out of memory, however it doesn't look like it was enough to handle your scripts/connections.

Do you have other SSMS plugins installed?

How large are your tabs? What is the size of C:\Users\<USERNAME>\AppData\Local\Red Gate\SQL Prompt 9\SavedTabs.db?

Best,
Michael

PDinCA

Hi Michael

Thanks for the reply.

db is at 87.7MB, and includes an 11M row resultset as I write.

Other plug-ins, yes, and have had them for almost as long as SQP: ApexSQL Refactor, SentryOne Plan Explorer.

Our database object size may be considered to be on the large size, and with each connection, master is a given, but usually one of these large databases is retrieved for SQP. Raw SELECT COUNT(*) from sys.objects gives 26,237; columns is 52,855; sysconstraints is 11,104, if that's any kind of guide.

I have "Manual Refresh" on, BTW.

Happy to give you any other stats, logs, etc.

I'm now on 9.0.2.3223

Jessica R

Thanks for those details @PDinCA!

I'm trying to reproduce this and while I haven't gotten SSMS to actually crash yet, from some testing it seems that Code Analysis may add less overhead in the latest version, v9.0.4.3408- while I continue testing, can you kindly update to this version to confirm that the issue is still present there for you?

PDinCA

Turned "it" on in SSMS 2017 v17.5 - SQP 9.1.0.4138. I'm now restarting SSMS because it became unresponsive - utterly unresponsive. You can guess what's being PERMANENTLY turned OFF!

Jessica R

I'm so sorry to hear that @PDinCA!

Can I just check something - would you be willing to re-enable it one more time and disable Options->Text Editor->Transact-SQL->Intellisense, to see if the problems persist? (the native Intellisense has clashed with Prompt in the past due to excessive memory usage, and we've just had another case where disabling it helped)

PDinCA

A solid "maybe" on that one, Jessica. I updated to the latest SQP 9.1.1.4243, but I don't have any bandwidth at this time to tinker while fighting performance issues faced by a Tier-1 Client.

Sorry to disappoint, but it will be a few weeks (the issues are MANY!)

Jessica R

No worries @PDinCA, I'm sorry you're experiencing these issues in the first place!

One of the releases since you first posted (v9.0.8.3873 on Jan 31st, 2018) addressed some Code Analysis issues- it supports larger scripts without freezing the SSMS/VS UI, by only showing the first 50,000 issues of code analysis- so it's unfortunate that hasn't helped!

I am curious to see if disabling Intellisense has any effect- please do let us know once you have a chance to test.