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

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

Closed

as By Design Help for as By Design

Type:	Bug
ID:	779898
Opened:	2/24/2013 11:40:47 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 tcp:Mandrake.arvixe.com) vs. the load time in SQL Server 2008 R2 Management Studio.

Details (expand)

English

SQL Server 2012 SP1

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows Server 2012 Datacenter

US English

1. Connect to a remote database server running SQL Server 2008 R2 using the tcp protocol (such as tcp:Mandrake.arvixe.com) using SQL Server 2012 SP1 Management Studio
2. Open/expand the databases folder after establishing a successful connection to the database server
3. Time how long it takes to display the total list of databases
4. Repeat steps 1-3 using SQL Server 2008 R2 Management Studio
5. Notice that the time to load the Databases tree in SQL Server 2008 R2 Management Studio is significantly shorter than in SQL Server 2012 Management Studio.

Databases tree in SQL Server 2012 Management Studio takes significantly longer to load than in SQL Server 2008 R2 Management Studio.

Databases tree in SQL Server 2012 Management Studio should take the same time or preferably even less time to load than in SQL Server 2008 R2 Management Studio.

X64

Other (e.g. VM Ware, specify in Description)

0 attachments

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 (wajokiel@microsoft.com)

Posted by Microsoft 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 http://technet.microsoft.com/en-us/library/bb402929.aspx 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''))
EXECUTE (''ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT'')
'

Please let us know if this solves your issue.

-Charles Gagnon (chgagnon@microsoft.com)

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 (wajokiel@microsoft.com)

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 (wajokiel@microsoft.com)

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 (wajokiel@microsoft.com)