Performance degradation in SQL Server Management Studio 2012 vs. SQL 2008 R2 - by vs2015junkie

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 779898 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/24/2013 11:40:47 AM
Access Restriction Public


When connecting to a SQL Server 2008 R2 database using the tcp: protocol (such as by connecting to a remote database server), expanding the Databases folder in Management Studio takes significantly longer when the target database server contains hundreds of databases (such as vs. the load time in SQL Server 2008 R2 Management Studio.
Sign in to post a comment.
Posted by Microsoft on 3/29/2013 at 1:26 PM
Hello vs2010junkie. Based on our explanations given here (and the lack of subsequent reported difficulties), we are marking this item as resolved. Please let us know if you have any subsequent issues. -Walter A Jokiel, Program Manager, SQL Server (
Posted by Charles [MSFT] on 3/19/2013 at 5:21 PM
Hello vs2010junkie,

The issue is likely due to the AUTO_CLOSE property being set to true for your databases. If you disable this you should see a large improvement in load times for the database list (and overall speed improvements as well!)

See for more information

Given that you have many databases I'd recommend running the following query, which will execute the ALTER DATABASE query on each non-system database in your server.

EXECUTE sp_MSforeachdb
IF (''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model''))

Please let us know if this solves your issue.

-Charles Gagnon (
Posted by Microsoft on 3/13/2013 at 10:44 AM
Hello vs2010junkie. As per your email, we have reproduced this situation using SQL 2008 R2 Express. To help us investigate further, please run the following command on your database and copy the results to us. SELECT @@VERSION -Walter A Jokiel, Program Manager, SQL Server (
Posted by Microsoft on 3/12/2013 at 8:41 AM
Hello vs2010junkie. Thank you for reporting this problem to us. However, we have been unable to reproduce the behavior locally using SSMS 2012 RTM on a 500 database server, all databases created through 2008R2 RTM. We recommend you try this scenario again with a clean installation of SSMS 2012. We will be closing the issue as a “no repro.” If a fresh installation of SSMS 2012 still faces this issue, please get back in touch with us and we will assist you. -Walter A Jokiel, Program Manager, SQL Server (
Posted by Microsoft on 3/7/2013 at 10:11 AM
Hello vs2010junkie. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you. -Walter A Jokiel, Program Manager, SQL Server (