0

When you work on a SQL file in SSMS, then SSMS is opening a sql-connection, and during the time that you work on that SQL-file, no matter how many times you execute statements, it always uses the same connection - never closes it.

Now I'm writing a web-interface, basically a SSMS replacement, because that thing is horribly slow.

Now if I want to replicate this functionaliy, I can put the connection into a static ConcurrentDictionary, which I can access from a cookie/parameter, that's not a problem.

I was wondering WHY does SSMS choose to do it this way ?
Why does it not just close&reopen the connection ?
I don't think performance can be the reason there, so - is there any ?

The only reason I can think of is that this way you can work with temporary tables and don't have to use global-temporary tables.
This might be useful if you need to analyze data, where queries are slow.
But I don't think this can be the reason - one could just use global temp-tables instead...

Are there any GOOD reasons to do the connection-handling this way ?

Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
  • In my experience, opening a new connection every time you want to run an ad-hoc query can be painfully slow, depending on your connection type and other factors. – Robert Harvey Aug 23 '18 at 17:50
  • 1
    Troubleshooting would be one... i.e. if your SPID changed all the time filtering our own out would be annoying when looking at some issues. Also, in multiple batches this could cause an issue... like setting the DB to single user mode, then you get a new SPID and aren't the single user anymore and thus, can't access the DB. There's also remote DAC which can only be used by a single user at a time which may come into play here. – S3S Aug 23 '18 at 17:54
  • Not only that, but global temp tables are not the only session specific data. I frequently use SSMS to run multiple statements as separate batches within a transaction. [DAC](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-2017) and other SPID-specific roles like `ALTER DATABASE SET SINGLE_USER` also require a single connection per query window. – Mitch Aug 23 '18 at 17:54
  • It's a tool that is primarily focussed in the development/debugging end of the spectrum. As such, it gives *you* the tools to manage your connections. Each query window (unless you switch connections) represents a *session*. But you're going to prototype/experiment/adapt. Things not normally done in a "production" session but *expected* in this kind of environment – Damien_The_Unbeliever Aug 23 '18 at 17:55
  • My more frequent complaint is that SSMS *doesn't* hold connections open. Try browsing SQL Server Object Browser with a RTT of 400ms. It is genuinely painful since each click can take one to three TCP three-way handshakes + all of the TDS setup. You burn 30 seconds waiting for the "Databases" list to load easily. Same story with intellisense - opens and closes dozens of connections for no apparent reason. – Mitch Aug 23 '18 at 17:57
  • 1
    Also, "because that thing is horribly slow" - in what *context* is it both noticeably slow and worth doing something about? And what makes you think you'll be able to outperform a fairly bare-bones layering atop basic connectivity? – Damien_The_Unbeliever Aug 23 '18 at 17:57
  • 1
    +1 @Damien_The_Unbeliever, where you think you have a better solution than SQL Server Operations Studio, any of the Redgate plugins for SSMS, or the plugins for Atom/VS Code. I don't mean to dissuade you if you are doing it for fun, but there are many prebuilt solutions here. Opening new connections is only "fast" with connection pools enabled. Connection pools bring issues in correctness for database administration tools - see [Isolation level leaks across pooled connections](https://stackoverflow.com/questions/9851415/sql-server-isolation-level-leaks-across-pooled-connections). – Mitch Aug 23 '18 at 17:59
  • @Damien_The_Unbeliever: Sometimes it takes over 15 seconds to create a simple select script from a table. Sometimes it takes longer than that to open a db. Sometimes it' actually quite fast. And quality is crap: insert scripts include computed columns, and generated inserts aren't standard compatible. Scripting data takes ages, and creates a lousy insert script. Creating schema scripts have the wrong dependency order. And so on, and so forth. But the epic slowness is really the most annoying. They simply fail at everything. Every day in every way... – Stefan Steiger Aug 27 '18 at 06:48

0 Answers0