Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 779898
Opened: 2/24/2013 11:40:47 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
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 (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)
Sign in to post a workaround.