Resource_Semaphore

Question

我有一个由dev团队创建的存储过程，它将详细信息插入到审计表中。它在插入值之后创建一个表变量，然后插入到实际表中。应用程序产生了大量并发连接，因此这些表变量的创建和销毁都是通过并发连接进行的。这个过程变得非常缓慢，主要等待的是resource_semaphore。问题是，尽管引擎最大内存为30 GB，内存最少为20 GB，但它从未使用超过4GB的内存.基本上，内存未使用，但在resource_semaphore等待时会阻塞。即使是表变量的声明也要花费很多时间(如果我只选择声明X作为表变量，则需要几秒钟的时间执行)。确实，sproc使用了一个执行字符串操作的函数。我的雇主说，完全相同的代码和shema在完全相同的硬件上运行，在oracle上没有问题，并希望我让sql server执行同样的操作。我找不到至少迫使sql引擎占用最小内存的脚本，并且不会再次陷入其中。贝娄是程序和函数。

CREATE PROCEDURE [dbo].[CreateAuditTrailDetailBulk]
(
    @auditTrailId INT,
    @tableNameArray NVARCHAR(MAX),
    @fieldNameArray NVARCHAR(MAX),
    @oldValueArray NVARCHAR(MAX),
    @newValueArray NVARCHAR(MAX),
    @oldValueTextArray NVARCHAR(MAX),
    @newValueTextArray NVARCHAR(MAX)
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @AuditTrailDetail TABLE
    (
      AuditTrailId int  NOT NULL, 
      TableName nvarchar(100) NOT NULL,
      FieldName nvarchar(2000) NOT NULL,
      OldValue nvarchar(max) NULL,
      NewValue nvarchar(max) NULL,
      OldValueText nvarchar(max) NULL,
      NewValueText nvarchar(max) NULL
    )

    INSERT INTO @AuditTrailDetail (AuditTrailId, TableName, FieldName, OldValue, NewValue, OldValueText, NewValueText)
    SELECT 
        @auditTrailId AS AuditTrailId,
        tn.item AS TableName,
        fn.item AS FieldName,
        NULLIF(ov.item, 'NULL') AS OldValue,
        NULLIF(nv.item, 'NULL') AS NewValue,
        NULLIF(ovt.item, 'NULL') AS OldValueText,
        NULLIF(nvt.item, 'NULL') AS NewValueText
    FROM 
        (SELECT rn, item FROM dbo.StringSplit_xml_nulltest1(@tableNameArray, '||')) tn
        INNER JOIN (SELECT rn, item FROM dbo.StringSplit(@fieldNameArray, '||')) fn ON fn.rn = tn.rn
        INNER JOIN (SELECT rn, item FROM dbo.StringSplit(@oldValueArray, '||')) ov ON ov.rn = tn.rn
        INNER JOIN (SELECT rn, item FROM dbo.StringSplit(@newValueArray, '||')) nv ON nv.rn = tn.rn
        INNER JOIN (SELECT rn, item FROM dbo.StringSplit(@oldValueTextArray, '||')) ovt ON ovt.rn = tn.rn
        INNER JOIN (SELECT rn, item FROM dbo.StringSplit(@newValueTextArray, '||')) nvt ON nvt.rn = tn.rn

    INSERT INTO dbo.AuditTrailDetail (AuditTrailId, TableName, FieldName, OldValue, NewValue, OldValueText, NewValueText)
    SELECT AuditTrailId, TableName, FieldName, OldValue, NewValue, OldValueText, NewValueText FROM @AuditTrailDetail
END

GO


CREATE FUNCTION [dbo].[StringSplit](
    @input VARCHAR(8000) -- List of delimited items
  , @delimiter VARCHAR(8000) = ',' -- delimiter that separates items
)
RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
    DECLARE @item VARCHAR(8000)

    WHILE CHARINDEX(@delimiter,@input,0) <> 0
        BEGIN

            SELECT
                @item=RTRIM(LTRIM(SUBSTRING(@input,1,CHARINDEX(@delimiter,@input,0)-1))),
                @input=RTRIM(LTRIM(SUBSTRING(@input,CHARINDEX(@delimiter,@input,0)+LEN(@delimiter),LEN(@input))))

            IF LEN(@item) > 0
                INSERT INTO @List SELECT @item
            END

            IF LEN(@input) > 0
                INSERT INTO @List SELECT @input -- Put the last item in

            RETURN
    END

GO

Answer 1

在您的情况下，等待类型的原因可能是由以下类型的查询引起的不正确的统计.

内部连接(选择rn，item FROM dbo.StringSplit(@fieldNameArray，‘而今“) fn ON fn.rn = tn.rn内连接(选择rn，item FROM dbo.StringSplit(@oldValueArray，’而今”) ov ON ov.rn = tn.rn

SQLServer如何执行查询

必需内存:运行排序和散列连接所需的最小内存。调用它是必需的，因为如果没有可用的内存，查询将无法启动。SQL server使用此内存创建内部数据结构来处理排序和散列连接。

我认为SQLServer由于缺乏统计数据而编写了错误的计划，并且选择了一个需要大量内存资源的联接类型。

若要解析this..try，请将查询更改为。

Insert into #sometemp
SELECT rn, item FROM dbo.StringSplit(@fieldNameArray, '||')

当您像上面这样插入临时表并在之后进行连接时，SQL将能够猜测统计信息，从而选择一个不涉及大量内存分配的适当计划。