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

The Get-SCDWJob cmdlet is slow or times out

· 52CE 我爱测

Fixes an issue in which the Get-SCDWJob cmdlet is slow or times out when the fn_OrchestrationJobHistoryView function runs for a long time.

This article helps you fix an issue in which the Get-SCDWJob cmdlet is slow or times out when the fn_OrchestrationJobHistoryView function runs for a long time.

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

Symptoms

When there is too much process activity by Microsoft SQL Server that involves the fn_OrchestrationJobHistoryView function, the Get-SCDWJob cmdlet takes more than several seconds to run. In a worst-case scenario, the Get-SCDWJob cmdlet times out after 30 minutes and generates a message that resembles the following:

get-scdwjob : The requested operation timed out.

Cause

This issue occurs when the following conditions are true:

  • The fn_OrchestrationJobHistoryView function from the DWStagingAndConfig data source is called.
  • The function has a string that contains thousands of process IDs.

In this situation, the function must be optimized to significantly improve performance so that the loop processes only the process IDs that are of interest before the loop is entered instead of doing this after the loop is entered.

Resolution

To resolve this issue, follow these steps:

  1. Start SQL Server Management Studio.

  2. Expand the DWStagingAndConfig database, expand Programmability, expand Functions, and then expand Table-valued Functions.

  3. Locate the dbo.fn_OrchestrationJobHistoryView function.

  4. Right-click the function, and then click Modify.

  5. Locate the following line in the code:

    INSERT INTO @tempProcessTable(value) SELECT value from dbo.fn_ParseIntListInOrder(@ProcessIdList)

  6. Add two hyphens to the beginning of this line, as follows:

    -- INSERT INTO @tempProcessTable(value) SELECT value from dbo.fn_ParseIntListInOrder(@ProcessIdList)

  7. Add the following line:

    INSERT INTO @tempProcessTable(value) SELECT id FROM [Infra].[OrchestrationProcessView] WHERE id IN (SELECT value from dbo.fn_ParseIntListInOrder(@ProcessIdList)) AND (CategoryName not in ('Deployment','Execution'))

  8. To apply the changes, click Execute. If you accidentally change more than intended, close the query window without clicking Execute. If the function is currently being used, execution will be delayed. Execution of change takes 1 second or less unless the function is currently being used.

More information

The Get-SCDWJob cmdlet is not the only entity that calls the fn_OrchestrationJobHistoryView function. In the most severe case, the running time decreased from more than 30 minutes to less than 2 seconds.

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)

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

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