大网数据广告活动
启动与系统组件

Service Manager console becomes slow after SQL Server upgrade

· 52CE 我爱测

System Center Service Manager console becomes slow after you upgrade to SQL Server 2014 or a later version.

This article helps you fix an issue in which Service Manager console becomes slow after you upgrade to Microsoft SQL Server 2014 or a later version.

Original product version:   System Center 2016 Service Manager, System Center 2012 R2 Service Manager
Original KB number:   4056822

Symptom

After you upgrade to SQL Server 2014 or a later version of the program, System Center Service Manager console becomes slow.

Additionally, after you add a user to the Administrators role, the performance for that user is normal.

Cause

The issue occurs because Service Manager queries are designed to run against SQL Server 2012 Cardinality Estimator.

Resolution

First, check the current compatibility level by using the following SQL query:

Use ServiceManager
Select Name, Compatibility_Level from sys.databases Where name = 'ServiceManager'
  • If the current compatibility level is 110, this article doesn't apply to your system.
  • If the current compatibility level is higher than 110, use one of the following methods to improve performance.

Method 1: Change the compatibility level of the ServiceManager database to 110 (SQL Server 2012)

To do this, use the following query:

ALTER DATABASE ServiceManager
SET COMPATIBILITY_LEVEL = 110

Method 2: Set the existing instance of SQL Server to run with trace flag 9481 to use SQL Server 2012 Cardinality Estimator

To do this, following these steps:

  1. Open SQL Server Configuration Manager and select SQL Server Services.
  2. Right-click the instance of SQL Server that you use, select Properties, and then select the Startup Parameters tab.
  3. Type -T9481 in the text box, and then select Add.

Note: If you have other databases running in the same instance of SQL Server, the performance of the other databases may be affected.

Method 3: Change the ServiceManager stored procedure p_UserRoleSelectAccessToMultipleEntities by adding OPTION (QUERYTRACEON 9481)

To do this, use the following query:

IF (0 = @ReturnUserRoleInfo)
 BEGIN
       INSERT INTO #BaseManagedEntityWithUserRoleInstanceScope
       SELECT T.[BaseManagedEntityId], T.[UserRoleId], NULL AS [ScopeId]
       FROM
       (
            SELECT E.[BaseManagedEntityId], E.UserRoleId
            FROM #BaseManagedEntityWithUserRoleOperationTypeScope E
            INNER JOIN dbo.[RecursiveMembership] as RM
            ON E.[BaseManagedEntityId] = RM.[ContainedEntityId]
            INNER JOIN #UserRoleWithScope AS L
            ON E.[UserRoleId] = L.[UserRoleId]
            INNER JOIN dbo.[UserRoleGroupType] AS URIS
            ON (URIS.[GroupId] = RM.[ContainerEntityId] OR (URIS.[GroupId] IS NULL))
            AND (URIS.TypeId IS NULL)
            AND (URIS.[ScopeType] = 2 OR URIS.[ScopeType] = 8)
            AND (URIS.[ScopeId] = L.[ScopeId])
            UNION ALL
            SELECT E.[BaseManagedEntityId], E.UserRoleId
            FROM #BaseManagedEntityWithUserRoleOperationTypeScope E
            INNER JOIN dbo.[RecursiveMembership] as RM
            ON E.[BaseManagedEntityId] = RM.[ContainedEntityId]
            INNER JOIN dbo.[TypedManagedEntity] AS TMECONTAINER
            ON RM.[ContainerEntityId] = TMECONTAINER.BaseManagedEntityId
            INNER JOIN dbo.[DerivedManagedTypes] AS DMTCONTAINER
            ON TMECONTAINER.[ManagedTypeId] = DMTCONTAINER.DerivedTypeId
            INNER JOIN #UserRoleWithScope AS L
            ON E.[UserRoleId] = L.[UserRoleId]
            INNER JOIN dbo.[UserRoleGroupType] AS URIS
            ON  (URIS.TypeId = DMTCONTAINER.[BaseTypeId])
            AND (URIS.TypeId IS NOT NULL)
            AND (URIS.[ScopeType] = 2 OR URIS.[ScopeType] = 8)
            AND (URIS.[ScopeId] = L.[ScopeId])
       ) AS T OPTION (QUERYTRACEON 9481)

Note: This method doesn't fix any other SQL queries that may have also been configured to work with SQL Server 2012 Cardinality Estimator.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 04/08/2024
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。