原创-关于SSMS右键菜单很卡的问题

原创文章，引用时请注明出处！
本来是自己发的一篇帖子，等了许久都没人回答，于是自己琢磨了半天，终于搞定。干脆写成博文，分享一下经验。

现象：
本地装了SQLServer2012，在SSMS中连接本地DB，选择表或者视图右键打开菜单时特别卡。有时候得花16-20秒（偶尔慢的时候卡1分多）才能出来。
菜单出现后反复右键显示菜单就很快，能立即显示。而过一阵子再右键打开菜单又会很慢，卡16-20秒出来。
用SSMS连服务器DB查看菜单很快，不论什么时候几乎都是一瞬间显示。
用SSMS连同事DB查看菜单，卡的时候2-3秒，不卡的时候一瞬间显示。（同事电脑相同配置，安装程序也几乎一样）

调查：
用SQLServerProfile跟踪发现，每次右键菜单时都会触发一个SQL:BatchStarting事件，里面会执行一段SQL用来取得一些系统或者用户的设定信息。而这一段SQL会很卡，直到SQL:BatchCompleted完成需要16-20秒。
SQL如下：

declare @HkeyLocal nvarchar(18)

        declare @ServicesRegPath nvarchar(34)

        declare @SqlServiceRegPath sysname

        declare @BrowserServiceRegPath sysname
        ....(略)
SELECT

@SmoAuditLevel AS [AuditLevel],

ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],

(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],

ISNULL(@SmoMailProfile,N'') AS [MailProfile],

@BackupDirectory AS [BackupDirectory],

@SmoPerfMonMode AS [PerfMonMode],

ISNULL(@InstallSqlDataDir,N'') AS [InstallDataDirectory],

CAST(@@SERVICENAME AS sysname) AS [ServiceName],

@ErrorLogPath AS [ErrorLogPath],

@SmoRoot AS [RootDirectory],

CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],

@@MAX_PRECISION AS [MaxPrecision],

CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],

SERVERPROPERTY(N'ProductVersion') AS [VersionString],

CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],

CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],

CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],

CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],

convert(sysname, serverproperty(N'collation')) AS [Collation],

CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],

CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],

@LogPath AS [MasterDBLogPath],

@MasterPath AS [MasterDBPath],

SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile],

SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog],

SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],

SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],

SERVERPROPERTY(N'CollationID') AS [CollationID],

SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],

SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],

SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],

SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],

SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],

SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],

SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],

@ServiceStartMode AS [ServiceStartMode],

ISNULL(@ServiceAccount,N'') AS [ServiceAccount],

CAST(@NamedPipesEnabled AS bit) AS [NamedPipesEnabled],

CAST(@TcpEnabled AS bit) AS [TcpEnabled],

ISNULL(@InstallSharedDirectory,N'') AS [InstallSharedDirectory],

ISNULL(suser_sname(sid_binary(ISNULL(@SqlGroup,N''))),N'') AS [SqlDomainGroup],

case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state  where target_query_expression_with_id like 'Server%' ) then 1 else 0 end AS [PolicyHealthState],

@FilestreamLevel AS [FilestreamLevel],

ISNULL(@FilestreamShareName,N'') AS [FilestreamShareName],

-1 AS [TapeLoadWaitTime],

CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled],

SERVERPROPERTY(N'HADRManagerStatus') AS [HadrManagerStatus],

ISNULL(@cluster_name, '') AS [ClusterName],

ISNULL(@quorum_type, 4) AS [ClusterQuorumType],

ISNULL(@quorum_state, 3) AS [ClusterQuorumState],

SUSER_SID(@ServiceAccount, 0) AS [ServiceAccountSid],

CAST(

        serverproperty(N'Servername')

       AS sysname) AS [Name],

CAST(

        ISNULL(serverproperty(N'instancename'),N'')

       AS sysname) AS [InstanceName],

CAST(0x0001 AS int) AS [Status],

0 AS [IsContainedAuthentication],

CAST(null AS int) AS [ServerType]

把这一段SQL单独拿到SSMS中执行，通常会卡16-20秒(偶尔慢的时候卡1分多)连续执行则很快。CPU，内存都正常。
通过分析

SUSER_SID(@ServiceAccount, 0) AS [ServiceAccountSid]

这一句很慢，单独执行也很慢，但连续执行就很快。
注：因为我的SQLServer的NT Service\MSSQLSERVER账户由于安全原因，被Windows系统禁用，所以无法启动Server，而用的LocalSystem账户启动的，所以@ServiceAccount显示为’LocalSystem’。而在同事机子上也是’LocalSystem’账户启动的Server，执行这一句，慢的时候只2-3秒。
刚开始怀疑跟缓存执行计划和数据有关，可是查了半天，似乎看不出什么有价值的东西。

最后在一个网站上找到一篇1999年的短文，当中有这么一段描述：
Changes that Microsoft made to the SQL Server security model have caused performance slowdowns for queries that use SQL Server 6.5 system functions to access a server user ID (SUID).
意思是由于安全模块的改变，导致有些基于使用SQLServer6.5中的访问服务器SUID的系统函数的查询变得很慢。

于是一下就怀疑到我这个SUSER_SID上来了，SUSER_SID虽然是SQLServer7.0以后推荐的方式，会不会也有同样的毛病呢？

尝试停掉服务器，从本地系统账户切换到本机域用户重新启动服务器，果然变得很快，几乎一瞬间菜单就出来了。

为了证明是SUSER_SID起了效果，拿两个用户分别执行多次，就是一个快一个慢。而且很长时间内多次确认菜单都是一瞬间显示，根本不用等2~3秒。
而同事虽然也是几乎同样的配置，但我俩机型还是有差异，最主要装的Win10系统还是有差异的，一些系统设置的地方界面也有不一样。可能可以解释为什么LocalSystem账户在他那边查询的比较快吧。

所以建议大家不要使用LocalSystem账户启动服务器。否则有可能会遇到SSMS右键菜单经常卡死的现象。