Management Studio freezing after writing a few lines of code

Question

Post reply

Management Studio freezing after writing a few lines of code

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 1

richardmgreen1 - Wednesday, April 5, 2017 6:08 AM

I've just had it freeze again, and managed to run the sp_who2 command using sqlcmd.

Looking at the output file, I can't see anything in the BlkBy column.

As far as I could see, there's nothing else running on this server (i.e. no-one else is running anything).

::edit::
Just turned off the Auto-Complete from APEX and (so far) SSMS is behaving itself.

I like having that option as it completes the table name and gives it an alias.
If, as current evidence suggests, it is the APEX software, is there any freeware out there that will do the same?
All I want it something that will complete table names, give them aliases and (this would be a big bonus) allows me to select fields from a table (after typing in the alias) using tick-boxes.

I've noticed similar things with Red-Gate's SQL Prompt. It is not a free tool, but I do like the auto complete with it. But if I leave things running for long periods of time and have a lot of active connections in Object Explorer and/or SQL queries, it slows things down pretty good.
What I've found helps is to use task manager to kill "RedGate.Client.Service.exe". Means that all of the SQL Prompt features break in SSMS, but I get SSMS to behave properly again. Usually around the time I am getting that slowdown/stalling in SSMS, I am also noticing a lot of my queries are throwing "outOfMemoryException" messages even when I have available memory.
I'd start by adding more RAM to your system (if possible) and looking at the minimum requirements for all of the tools. I believe SSMS requires 1 GB of memory (but I could be wrong), plus any objects that APEX looks at would be stored in memory. I wouldn't be surprised if adding more RAM delayed or stopped the problem entirely.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Mr. Brian Gale SSC-Insane Points: 24363 More actions · Answer 2

richardmgreen1 - Wednesday, April 5, 2017 8:23 AM

I've never noticed any issues using mis-matched ssms and SQL versions.
I just find I needed to remember that certain commands don't exist in previous versions (like CAST(XXX as DATE) in 2005).

Have you tried using the SSIS catalog in SSMS 2016 to put a dtsx into a SQL Server 2012 SSIS Catalog before? It will upload properly but as soon as you go to execute it you get version errors. Even using SSMS 2016 to browse the 2012 SSIS Catalog is horrendously slow compared to browsing it in the matching version. Or even exporting an ispac from SSMS 2016 connected to a SQL Server 2012 SSIS catalog will export it for SSIS 2016 not 2012.
That is the only problem I have really noticed. Well, that and the auto-complete from higher versions sometimes tells you to put things in your query that don't work (mostly with windowing functions).
Or if SSMS is older than the SQL version. I've had a lot of fights with that before where a query will either fail to run or give incorrect results.

But as long as you are not using "new" features or depricated features, most things will work as expected in mismatched versions (as long as the version of SSMS is NEWER than the SQL version).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.