SQL Studio Management - Expanding Databases muito lento.

SQL Studio Management - Expanding Databases muito lento.

Em um de nossos clientes durante a implementação, identificamos uma situação atípica,uma performance muito lenta, abaixo do aceitável. Em atividades simples, apenas dentro do SQL Server Studio Management. Notamos que simples ações, como por exemplo de expandir o menu Databases.

Demorou em torno de 2 a 3 minutos, para exibir o nó de Bancos existentes.

Não me lembro exatamente quando começou. Mas eu acho que já fazem vários meses.
Era fácil demais ignorar, já que eu poderia usar o alt-tab e fazer outras atividades enquanto aguardava o atraso.

Hoje decidi pesquisá-lo e concluímos em duas opções:

Opção 1) Ocorre porque alguns bancos têm a opção de auto_close ativado;
Isso faz com que o servidor SQL tenha que inicializar cada banco de dados, antes de poder renderizá-lo no Nó menu de bancos. Isso cria um atraso muito significativo, quando você tem vários bancos de dados configurados para auto_close.

Uma maneira rápida de corrigir todos eles de uma só vez, quando possível ....
É configurar o modo de recuperação para = simple (qualquer coisa mais é inútil no ambiente de desenvolvimento), use este script:

USE MASTER

Declare

    @isql varchar(2000),

    @dbname varchar(64)

    declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

    open c1

    fetch next from c1 into @dbname

    While @@fetch_status <> -1

        begin

        select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

        select @isql = replace(@isql,'@dbname',@dbname)

        print @isql

        exec(@isql)

        select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'

        select @isql = replace(@isql,'@dbname',@dbname)

        print @isql

        exec(@isql)

        select @isql='USE @dbname checkpoint'

        select @isql = replace(@isql,'@dbname',@dbname)

        print @isql

        exec(@isql)

        fetch next from c1 into @dbname

        end

    close c1

    deallocate c1

Opção 2) Um configuração pré definida, do proprio SQL Studio gerou problemas não apenas em expandir databases. Mas em todas as ações executadas dentro do Management.

No Management Studio, no Menu (Tools)Ferramentas, selecione (Options) Opções e clique em "Designers".  Há uma opção chamada "Override connection string time-out value for table designer updates:"

Valor de tempo limite, da cadeia de conexão para atualizações de designer de tabela:
Transaction time-out after: modifique para 0 seconds

Este segunda opção foi a mais eficiente em diversos clientes, principalmente em ambiente de produção.Quando não é possível modificações em modo de recuperação. Não há relação entre as opções mas foram alternativas funcionais.

Sugestões e explicações mais detalhadas estamos a disposição.
Fontes referencias: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190181(v=sql.105)