Tuesday, February 3, 2009

SQL Management Studio - Speed-up database list load

Recently, we came across a seemingly strange issue where SQL Management Studio 2008 errored-out when trying to connect to a server with a limited permission login. Studio 2005 allowed us in without a problem.

After scrawling through blogs and forums galore, I finally discovered that it was because Studio 2008 was trying to read the 'Collation' property of each and every database on the server, and was crashing on the very first one, thus not letting me view any databases at all.

In order to stop this failing, we needed to view 'Object Explorer Details' (F7) on the 'Databases' folder, and remove the column header 'Collation'. This fixed the problem and now allowed us to view all databases, and then open the one we were permitted to.

Following up on this, on a hunch, I decided to see what happen when we un-check all other columns (database properties) except for name. Up till now, refreshing (and initially loading) a server's list of databases took painfully long... those extra seconds that kill us developers!

As expected and to my delight, with all those properties not being requested the database list loaded almost instantly on click. Better still, Studio 2008 remembers the column settings so that hereon in, only looks to load the name of the database initially, and speeds up the process every time we now open SQL Management Studio.

1 comment:

Rob said...

Thanks for the tip!