Make SSMS start faster

A couple of years ago, I blogged about some ways to make Management Studio start faster. With the latest builds of SQL Server 2012, it seems to be slower than ever, at least when starting SSMS for the first time after a reboot or install. So I've asked for them to do something in the background on Windows start-up to cover whatever initialization costs have to be paid on first launch. This is not something I expect to happen in this release, and perhaps it will be better by the time RTM comes around. But if you find it slow as well, in addition to voting, please post – in the comments on the Connect item – your machine config and how long it takes between clicking on the shortcut and being able to work. With enough stats in there they can probably extrapolate how much time is being lost waiting for the application to load. 🙂

#714652 : SSMS : Background helper / pre-loader

Let me opt out of product updates during setup

I am a big fan of streamlining slipstreaming (say that 5 times fast) – in other words, letting SQL Server setup check for product updates *before* installation, rather than the current manual process of cobbling together a setup package with a service pack and/or a cumulative update. In SQL Server 2012 we finally have this. Unfortunately, it is currently not an option; so, if your machine is not connected to the Internet for whatever reason, it hangs indefinitely on that step, trying desperately to go find those updated files. This needs to be an option so that you can proceed, even if you want to install RTM (e.g. for testing purposes) or you can't get to the Internet (now or ever). Sadly most software companies think that high speed Internet is enjoyed by all, when in fact a lot of folks can't get it or intentionally block their servers from any kind of external access.

#714661 : Scan for Product Updates hangs on isolated system

Add a time limit option to index reorg

Greg Low had an interesting idea to add a time limit to index reorg. Since the most work you'll ever lose upon cancelling is the reorganization of a single page, unlike a rebuild which has to roll back ALL of the work, it is a common practice to do a little bit of reorg at a time. But it is quite tedious to set up some kind of watchdog or to wait and kill the process manually. I can see how the syntax could work – a simple TIME_LIMIT option with the same input format as WAITFOR DELAY. So, for example, if you wanted to allow this reorg to work for up to 45 minutes and then stop:

 ALTER INDEX foo 
  ON dbo.bar
  REORGANIZE WITH (TIME_LIMIT = '00:45:00');

The Connect item is currently closed as won't fix, and I think that's partially because it's had only 2 votes in over a year. Please vote and add a comment indicating why this could be useful in your environment:

#611158 : Index Reorganize time limit

Gosh, I wish I could re-use that CTE

Several folks have filed suggestions to make it easier to work with the same query multiple times. Two of the popular ideas are temporary views and module-level table expressions. The current workaround of dumping data into a #temp table has significant overhead, and can still lead to errors if the same joins and where clauses need to be repeated over and over again. I like Erland Sommarskog's module-level table expressions idea best, but temporary views have a strong use case as well. I'm hoping to see some more comments and use cases on these items so that Microsoft can evaluate

#343067 : Module-level table expressions

#640863 : Please allow creation of temporary views

#714617 : [T-SQL] : Please provide temporary views

Truncate a table with foreign keys

We all know that you can't truncate a table that has foreign keys, but why should that be the case if all the referencing tables are empty (or have nullable referencing columns that are all NULL)? They've closed this one as won't fix, but if you can see the value in this for your environment, please add a comment indicating your use case, as they seem more than willing to revisit it…

#312074 : Permit TRUNCATE TABLE when referencing tables are empty