SQL Prompt freezes SQL Server Management Studio (SSMS) with select * statements

I'm not sure if others are facing this issue, but when I have multiple "Select top 10 *" statements at the top of my T-SQL file, SQL prompt doesnt like it and starts to slow down my SSMS instance any time I try to click off the top of the script.

When I say "performance issue" I'm talking about at lest 30-90 seconds of lag time and sometimes my SSMS actually completely freeze and I have to go into Windows Task manager and End Task.

Any and all optimization help greatly appreciated!!

sitedesigner March 08, 2019 22:41

Comments

2 comments

Have you checked the rudimentary performance tips here: https://documentation.red-gate.com/sp9/managing-sql-prompt-behavior/managing-connections-and-memory?

In case management Studio is using too much memory from https://documentation.red-gate.com/soc7/troubleshooting/error-messages/system-outofmemoryexception

You could try disabling code analysis or try disabling suggestions - each separately - to try and narrow down the cause.

Are they cross database or instance (i.e. linked server) queries? Are there lots of objects in the targets of these?

Russell D March 11, 2019 12:43

0
Specifically, what I was looking for was going to
SSMS > SQL Prompt > "Enable Code Suggestions" and unchecking that box, which can also be done with the shortcut "CTRL + SHIFT + A".

Thanks for the help! This, combined with reducing the check for all databases (per your first link) helped improve my performance tremendously!.

Cheers,
David

sitedesigner March 11, 2019 16:26

0

Add comment

Please sign in to leave a comment.

How can we help you today?

SQL Prompt freezes SQL Server Management Studio (SSMS) with select * statements

Comments

Add comment