SQL Management Studio медленно работает, тормозит. Как решить проблему

Проблема: 

 SQL Server ManagementStuido (SSMS) тормозит при открытии

Необходимо проверить следующее:

    Проанализированы журналы MS SQL Server и Windows Server –  

    Проанализированы индексы (фрагментация и т д) – добавлены недостающие и удалены неиспользуемые

    Проанализированы запросы – улучшены некоторые запросы

    Проанализированы задания в SQL Agent – не удалось задачи привязать к проблеме торможений

    Проанализированы задания в Планировщике заданий – не удалось задачи привязать к проблеме торможений

    Profiler тоже выдавал следствие, а не причину торможений.

    Проведена проверка на взаимоблокировки – не было выявлено долгих блокировок вообще  возможно что-то с дисками. 

1 отключить Intellisense?

Технология IntelliSense - 

https://docs.microsoft.com/ru-ru/sql/ssms/scripting/intellisense-sql-server-management-studio?view=sql-server-ver15

Настройка IntelliSense (среда SQL Server Management Studio)

https://docs.microsoft.com/ru-ru/sql/ssms/scripting/configure-intellisense-sql-server-management-studio?view=sql-server-ver15 

Изменение параметров технологии Transact-SQL IntelliSense

    В меню Сервис выберите команду Параметры.

    Раскройте список Текстовый редактор, затем список Transact-SQL, после чего выберите IntelliSense.

    Снимите флажки параметров IntelliSense, которые не требуются.

    Чтобы изменить размер скрипта, при котором отключаются функции IntelliSense, выберите размер из списка Максимальный размер скрипта .

2 отключить все надстройки  

3 Почистил файлы .tmp в папке users\local\temp

4 Вы можете настроить IE больше не «Проверка отзыва сертификата издателя» в дополнительных настройках.

(см. вышеупомянутые блоги 1 и 2 )

Shut down SSMS.

Go into IE, select Tools|Internet Options|Advanced

If “Check publisher’s certificate revocation” under the security node is checked, then uncheck it.

Disable checks for certificate revocation. You can do this using Internet Explorer by opening the Internet Options dialog, going to the Advanced Page, and then un-checking the "Check for publisher's certificate revocation" checkbox. There are fraudulently signed binaries in the wild that can make virus-infected applications look like they were published by Microsoft. Disabling this check should probably not be done on machines with internet access.

5  В момент "тормозов" проанализировать текущие ожидания:

См. Скрипт 

Источник sys.dm_os_waiting_tasks   sys.dm_exec_sessions

6  В момент зависания очищать процедурный кэш:

DBCC FREEPROCCACHE

https://docs.microsoft.com/ru-ru/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Удаляет все элементы из кэша планов, удаляет заданный план из кэша планов с помощью указания дескриптора плана или дескриптора SQL либо удаляет все записи кэша, связанные с указанным пулом ресурсов.

Очистка всех планов из кэша планов

DBCC FREEPROCCACHE WITH NO_INFOMSGS; 

Чистка всего процедурного кэша для всей базы данных каждый час через SQL Agent:

--очищаем кэш по id БД

DBCC FLUSHPROCINDB(@db_id);

MS SQL Server съедает всю выделенную ей оперативную память (через Диспетчер задач).  

освобождается кэш не всегда вовремя. Проблему с кэшем удалось решить с помощью программы EmptyStandbyList.exe

https://www.downloadcrew.com/article/34150-empty_standby_list

7  Запретите загрузку помощи из сети: "Tools" -> "Options" -> "Help" -> "Online" укажите "Try local first, not online".

Отключите проверку аннулированных сертификатов в "Internet Explorer" -> "Tools" -> "Internet Options" -> "Advanced" и снять «галки» с Check for server certificate revocation и Check for publisher's certificate revocation

Отключите отображение экрана-заставки при открытии среды SQL Server Management Studio. Правой кнопкой мыши по ярлыку SSMS и в свойствах добавьте параметр "nosplash".

Определите значения переменных %vsspv_visualstudio_dir% и %vsspv_vs_install_directory% , либо в реестре явно укажите пути для параметров DefaultSettingsDirectory и LastResetSettings ( HKCU\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\Profile ). 

Например:
"%USERPROFILE%\My Documents\SQL Server Management Studio\Projects"
и
"%USERPROFILE%\Мои документы\SQL Server Management Studio\Common7\IDE\Profiles\sqlshell.vssettings"
соответственно.

8 Решение состояло в том, чтобы очистить историю MRU и другие настройки от моего профиля..

    Закройте SSMS 2012

    В проводнике откройте "% AppData%\Microsoft\SQL Server Management Studio"

    Переименуйте папку "11.0" на что-то еще, например "11.0.old"

    Открыть SSMS 2012

Вы увидите, что ваш список MRU был очищен. Затем вы сможете повторно ввести свои учетные данные и использовать SSMS как обычно.

Если все работает, вы можете удалить переименованную папку "11.0.old".

 В противном случае удалите новую папку "11.0" , которая была создана, и переименуйте "11.0.old" обратно в "11.0" .

автозавершение в базах данных. 

Каждый раз, когда вы расширяете или обновляете список базы данных, сервер должен пробуждать базы данны.

SELECT name, is_auto_close_on

FROM master.sys.databases AS dtb 

WHERE is_auto_close_on = 1 

ORDER BY name

правой кнопкой мыши на экземпляре базы данных в проводнике объектов

 → Свойства клика → Нажмите "Параметры" в левой навигационной панели в окне свойств базы данных → Измените значение свойства "Автозакрытие" на "False" в правой панели, как показано в снимок ниже:

-- В момент "тормозов" проанализировать текущие ожидания:
select  wt.*
from
 sys.dm_os_waiting_tasks wt join
 sys.dm_exec_sessions s on s.session_id = wt.session_id
where
 s.is_user_process = 1;

-- 2. Проанализировать какие индексы рекомендуется создать,
-- используя DMV - sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details.
--В качестве критерия берите значение столбца avg_user_impact из sys.dm_db_missing_index_group_stats.
--Чем больше значение, тем полезнее группа предлагаемых индексов.
--Так же необходимо учесть значения в user_seeks и user_scans - чем они меньше, тем реже будут использоваться эти индексы.

SELECT TOP 10 *
FROM sys.dm_db_missing_index_group_stats
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

--  система предлагает создать эти индексы
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

-- ==  система предлагает создать эти индексы == Всё разобрался. Добавил индексы кое какие и все летает) Всем спасибо)

--  автозавершение в базах данных
SELECT name, is_auto_close_on
FROM master.sys.databases AS dtb
WHERE is_auto_close_on = 1
ORDER BY name