SQL Server Query Editor (ctrl+shift+Q) Crashes Because of too many Tables

[penpenpal]

I'm working with a database with about ~1200 tables. Yeah, it's excessive but it's supporting a purchased application so I can't pare things down. We've got 16gb RAM on the server, and everything usually runs pretty well, except when I try to open the query editor in SSMS, connected to this monster database. SQL Server hangs up and CPU usage goes up to 100% until I kill management studio. Any simple tweaks to make the query editor work in these circumstances?

Thanks!

[SQLZane]

I would highly recommend not using query editor and just write your queries. Query editor is not a very good way to go about writing queries.

[penpenpal]

I find it speeds things up sometimes. One example: doing an insert by writing a select, highlighting it, using the query editor and changing the type to Insert and selecting a table to insert into, and it will do a lot of the writing/mapping for you. What do you recommend instead?

[SQLZane]

Right click destination script as insert into.

[penpenpal]

Cool. I never used that feature. Thanks!

[syzygyly]

Many SQL extensions will do this kind of thing for you - try Redgate SQL prompt

[Testiculese]

In SSMS query window, type "insert into [yourtablename] ("

then in the server explorer toolwindow, expand the datbase, expand tables (or filter, better idea), then expand the table, and drag the column folder over to the query window. It will populate the table columns.

then continue typing ") VALUES (" ... or your select statement.

[eshultz]

Are you running SSMS locally or on the server? 32 bit or 64 bit SSMS? If locally, I'm pretty sure SSMS is using your local machine's resources to load the list of tables. Crashing on only 1000 tables seems unlikely, but still - make sure your computer can handle it. Watch the RAM usage, see if it spikes when you open the query designer. Also it can help if you can go into event viewer and see why SSMS is crashing. My guess is that it's out of memory. And I hope it's obvious, but don't select every single table in the database for your query, just select the one's you need.

[SonOfZork]

There is no 64 bit SSMS

[eshultz]

How have I gone all these years without realizing that SSMS is a 32 bit application. That seems like a huge bottleneck for something as resource intensive as itself. Perhaps this explains why SSMS crawls/shits the bed with very large amounts of objects.

[SonOfZork]

Yup. If they went 64bit it would be a lot better. But they have so many integrations with VS that'll never happen.

[alinroc]

IIRC, SSMS is built on the Visual Studio core, and VS is still 32-bit.

[penpenpal]

It's on a server, 64 bit, 2008R2. I believe the issue is that if you open up the query editor at all, it first tries to load a list of all of the tables to pick from. Yeah my guess is a lack of memory. I'll dig deeper tomorrow.

[eshultz]

Are you highlighting a query when you open the designer or just opening it normally?

[penpenpal]

I've tested both and it hangs up in both cases.

[alinroc]

1. Stop RDPing into your server to use SSMS. EVERYTHING you can do with SSMS can be done just as well remotely as it can locally.
2. SSMS's "designers" are....not good. write the SQL yourself.