Ross Bush's questions

当我“内联”变量时，下面语句中的 @SQL 变量总是被截断。如果我发送变量并打印，它不会截断到 4000，但是，我无法将发送的变量绑定到 USE [@DatabaseName] 语句中。如何解决。

这是可行的，但是数据库名称在 USE 语句中是硬编码的

DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
'
USE [MyDatabase]
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

这将引发异常，并显示消息“数据库‘@DatabaseName’不存在。请确保正确输入名称。”

DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
'
USE [@DatabaseName]
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

这会将查询截断为 NVARCHAR(4000)

DECLARE @DatabaseName NVARCHAR(MAX) = 'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
'
USE ['+@DatabaseName+']
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) ='@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

也尝试使用 N'string'，但没有成功

DECLARE @DatabaseName NVARCHAR(MAX) = N'MyDatabase'
DECLARE @Sql NVARCHAR(MAX)=
N'
USE ['+@DatabaseName+']
PRINT @DatabaseName
SELECT
...<20,000 more chars
'

DECLARE @params NVARCHAR(MAX) =N'@DatabaseName NVARCHAR(MAX)'
EXEC sp_executesql @SQL, @params,@DatabaseName

我想我明白为什么USE [@Variable]不起作用，而我不知道如何解释为什么USE ['+@Variable+']当两个变量都最大时会被截断到 4000，除非 sql server 正在强制关闭sp_executesql。

无论如何，有谁知道有什么解决方法可以实现这一点。我基本上想使用大于 4000 个 nchars 的 SQL 语句动态定位任何数据库。

我正在尝试使用 ms-replication 解决问题，但我不确定它是否最适合当前的问题。

问题：需要复制所有初始插入和所有更新，同时忽略对订阅的删除。本质上，目标数据库会继续增长，并且数据永远不会被删除，即使在源端删除了记录。能够在源端恢复而不破坏目标将是一个很大的优势。

我可以通过 ms-replication 在所有表项目上设置忽略删除并将表准备模式设置为不执行任何操作来实现大部分工作。这将处理在恢复期间维护数据并在目标处保留已删除的记录，但是，如何处理在源处恢复后会导致 PK 违规的插入。我找不到任何记录在案的方法来“在插入期间忽略现有记录”，即使存在黑客攻击，它是否会大大降低复制的性能？

注意：我应该提到，支持备份并不意味着回到很久以前的某个时间点，它只是意味着如果发生硬故障，需要使用最新的日志链恢复源数据库。最多需要处理两个小时的插入。这可能可以通过手动删除目标中不在源中的记录来处理，但是，我试图完全避免这种情况。

除了尝试通过 BCP 加载数据之外，我对快照过程的基础不太熟悉。如果表准备模式不执行任何操作，是否有选项可以选择跳过快照的“批量加载”阶段，然后在应用快照后像往常一样使用日志读取器进行读取？

使用事务复制，加密数据库（TDE 加密）是否可以毫无问题地复制到未加密目标？

有一个发布者-->分销商-->订阅者的设置，并且在这个系统中所有内容都已经配置好了，添加几个表和重新捕捉表的一些简单测试似乎运行良好。

问题是每个数据库有超过 6K 篇文章，多租户系统中有很多数据库。该系统已经运行了 20 多年，几乎所有数据库都积累了堆积物和无法编译的视图或存储过程。每次恢复数据库和/或需要重新发布数据库时，我们无法指向并单击；因此，正在测试以下内容。

我知道这可以编写脚本，但我们正在遵循此建议来重新捕捉表格。

初步测试

• 创建包含 1 篇文章 tblAccountingBatch 的订阅。
• 运行下面的脚本来添加所有具有 PK 的表和所有兼容的文章。
• 重新运行快照代理并获取新表。

结果

• 用户界面显示所有~5K 篇文章均处于活动状态并在子版块中被选中。
• 快照运行并为初始表 tblAccountingBatch 生成 1 篇文章。
• EXEC sp_helpsubscription 仅显示一个表 - tblAccountingBatch。
• 我尝试使用 sp_addarticle 编写脚本来添加 UI 中勾选的另一个文章，但出现错误，提示该文章已被添加。

有谁知道断开连接可能在哪里吗？快照似乎看不到创建子版块后添加的文章。

我碰到了这个 KB，它描述了一些非常相似的东西；然而，DBA 说这些补丁已经应用于测试环境。

订阅创建后添加所有有效文章的脚本

SET NOCOUNT ON

USE VP_UPS_V05
GO

DECLARE @PublicationName NVARCHAR(200) = 'UPS_PUB'

EXEC sp_changepublication @publication = @PublicationName, @property = 'allow_anonymous' , @value = 'false' 
EXEC sp_changepublication @publication = @PublicationName, @property = 'immediate_sync' , @value = 'false' 


IF(CHARINDEX('VP_',DB_NAME()) = 0 OR CHARINDEX('_V05',DB_NAME()) =0 ) BEGIN
    RAISERROR('This script can only be applied against a Client database', 16, 1)
    RETURN
END


DECLARE @Debug BIT = 0

DECLARE @FullName nvarchar(1000);
DECLARE @ShortName nvarchar(300)
DECLARE @ObjectType nvarchar(300)
DECLARE @HasPrimaryKey BIT
DECLARE @Sql nvarchar(1000);
DECLARE @Result int;


DECLARE @Exceptions TABLE(ShortName NVARCHAR(300), FullName NVARCHAR(300),ObjectType NVARCHAR(300), Result INT, ErrorMessage NVARCHAR(MAX))


DECLARE @IgnoredTables TABLE (TableName NVARCHAR(100))
INSERT INTO @IgnoredTables VALUES
    ('tblAccountingBatch')
    

DECLARE ObjectCursor CURSOR FOR
SELECT 
    ShortName,LongName,ObjectType,HasPrimaryKey
FROM
(
    SELECT  
        OBJECT_NAME(o.object_id) ShortName , 
        QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)) LongName, 
        CASE WHEN type_desc = 'USER_TABLE' THEN CASE WHEN  OBJECTPROPERTY(o.object_id,'tablehasprimaryKey') = 1 THEN 1 ELSE 0 END ELSE 0 END AS HasPrimaryKey,
        type_desc AS ObjectType,
        CASE 
            WHEN type_desc='USER_TABLE' THEN 0
            WHEN type_desc='VIEW' THEN 1 
            WHEN type_desc='SQL_SCALAR_FUNCTION' THEN 2
            WHEN type_desc='SQL_TABLE_VALUED_FUNCTION' THEN 3
            WHEN type_desc='SQL_INLINE_TABLE_VALUED_FUNCTION' THEN 4
            WHEN type_desc='SQL_TRIGGER' THEN 5
            WHEN type_desc='SQL_STORED_PROCEDURE' THEN 6
            ELSE
                7
        END AS ProcessOrder
    FROM 
        sys.objects o
        LEFT OUTER JOIN @IgnoredTables IgnoredTable ON type_desc = 'USER_TABLE' AND IgnoredTable.TableName = OBJECT_NAME(o.object_id)
    WHERE 
        type_desc IN ('USER_TABLE','SQL_STORED_PROCEDURE','SQL_TRIGGER','SQL_SCALAR_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','VIEW')    
        AND 
        ISNULL(OBJECTPROPERTY(o.object_id, 'IsSchemaBound'), 0) = 0
        AND
        SCHEMA_NAME(o.schema_id)='dbo'
        AND
        IgnoredTable.TableName IS NULL
        AND
        ((type_desc = 'USER_TABLE' AND OBJECT_NAME(o.object_id) LIKE 'tbl%')OR(type_desc<>'USER_TABLE'))
)AS X 
Order BY ProcessOrder, ShortName;

--RETURN

OPEN ObjectCursor;

DECLARE @TotalObjects INT = @@CURSOR_ROWS
DECLARE @CurrentObject INT = 0

FETCH NEXT FROM ObjectCursor INTO @ShortName, @FullName, @ObjectType, @HasPrimaryKey

DECLARE @IsError BIT = 0
DECLARE @CompileResult BIT = 0
DECLARE @ErrorMessage NVARCHAR(MAX)

DECLARE @InsertCommand NVARCHAR(300)
DECLARE @DeleteCommand NVARCHAR(300)
DECLARE @UpdateCommand NVARCHAR(300)
DECLARE @ArticleType NVARCHAR(300)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentObject = @CurrentObject + 1
    PRINT 'Processing Object '+CAST(@CurrentObject AS VARCHAR(10))+'/'+CAST(@TotalObjects AS VARCHAR(10))+' - '+@ObjectType+' '+@ShortName
     
    SET @IsError = 0
    SET @ErrorMessage = ''

    IF(@ObjectType = 'USER_TABLE') BEGIN 
        IF(@HasPrimaryKey = 0 ) BEGIN
            SET @IsError = 1
            SET @ErrorMessage = 'Missing Primary Key'   
        END     
    END ELSE BEGIN      
        SET @Sql = N'EXEC sp_refreshsqlmodule ''' + @FullName + '''';       
        BEGIN TRY
            EXEC @CompileResult = sp_executesql @Sql;
            IF @CompileResult <> 0 RAISERROR('Failed', 16, 1);
        END TRY
        BEGIN CATCH
            SET @IsError = 1
            SET @ErrorMessage = ERROR_MESSAGE()             
        END CATCH
    END

    IF(@IsError = 0 AND @Debug=0) BEGIN
        BEGIN TRY           
            IF(@ObjectType = 'USER_TABLE') BEGIN

                SET @InsertCommand = 'CALL sp_MSins_dbo_'+@ShortName
                SET @DeleteCommand = 'CALL sp_MSdel_dbo_'+@ShortName
                SET @UpdateCommand = 'CALL sp_MSupd_dbo_'+@ShortName

                exec sp_addarticle 
                    @publication = @PublicationName, 
                    @article = @ShortName, 
                    @source_owner = 'dbo', 
                    @source_object = @ShortName, 
                    @type = 'logbased', 
                    @description = null, 
                    @creation_script = null, 
                    @pre_creation_cmd = 'drop', 
                    @schema_option = 0x000000000803509F, 
                    @identityrangemanagementoption = 'manual', 
                    @destination_table = @ShortName, 
                    @destination_owner = 'dbo', 
                    @vertical_partition = 'false', 
                    @ins_cmd = @InsertCommand, 
                    @del_cmd = @DeleteCommand, 
                    @upd_cmd = @UpdateCommand
            END ELSE IF (@ObjectType IN ('SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION','SQL_STORED_PROCEDURE','SQL_TABLE_VALUED_FUNCTION','VIEW')) BEGIN
                IF(@ObjectType='SQL_STORED_PROCEDURE') 
                    SET @ArticleType = 'proc schema only'
                ELSE IF(@ObjectType = 'VIEW')   
                    SET @ArticleType = 'view schema only'
                ELSE
                    SET @ArticleType ='func schema only'
                    
                exec sp_addarticle 
                    @publication =@PublicationName, 
                    @article = @ShortName, 
                    @source_owner = 'dbo', 
                    @source_object = @ShortName, 
                    @type = @ArticleType, 
                    @description = null, 
                    @creation_script = null, 
                    @pre_creation_cmd = N'drop', 
                    @schema_option = 0x0000000008000001, 
                    @destination_table = @ShortName, 
                    @destination_owner = 'dbo'

            END
        END TRY
        BEGIN CATCH
            SET @IsError = 1
            SET @ErrorMessage = 'Could Not Add Article to Replication - '+ERROR_MESSAGE()  
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        END CATCH
    END 
    
    IF(@IsError = 1)  
        INSERT INTO @Exceptions SELECT  @ShortName, @FullName, @ObjectType, 0, @ErrorMessage
        
    FETCH NEXT FROM ObjectCursor INTO @ShortName, @FullName, @ObjectType, @HasPrimaryKey
END

CLOSE ObjectCursor;
DEALLOCATE ObjectCursor; 

SELECT * FROM @Exceptions

我需要能够迭代所有表并发出TRUNCATE命令，但是我不希望循环因阻塞而停止。本质上，我正在寻找最佳方法来确定SCH-M在发出截断命令之前是否可以获得锁定。

Windows Server 2016 Datacenter 10.0 上的 Microsoft SQL Server 2019 (RTM-CU26) - 15.0.4365.2 (X64) 标准版（64 位）

有一个表似乎在不断更新。还有一个存储过程定期运行并遍历表，并尝试使用以下命令截断数据分区：

TRUNCATE TABLE [dbo].[Table] WITH (PARTITIONS (10))

循环过程总是锁定在同一个表上，即使在要删除的分区中没有更新数据，不断更新的表也会失败。

调用进程无法更新。除了修改查询之外，是否有其他技巧可以将更新绑定到分区，或者强制不更改分区不被阻止/锁定？

页面锁定会导致分区截断失败吗？例如，两个分区是否可以共享一个页面，而尝试使用分区（2）删除会失败，因为某个进程正在更新分区 1 中的数据，而分区 2 恰好与分区 1 共享一个页面？

CREATE TABLE [dbo].[Table]
(
      [TableID] INT NOT NULL
      ...
    , [SourceID] NVARCHAR(4000) NOT NULL
    , CONSTRAINT [PK_Table_TableID] PRIMARY KEY NONCLUSTERED([TableTableID] ASC , [SourceID] ASC) ON psSourceID(SourceID)
    , CONSTRAINT [CIX_Table_TableID] UNIQUE CLUSTERED ([SourceID] ASC, [TableID] ASC) ON psSourceID(SourceID)
)
ON psSourceID(SourceID)
GO

ALTER TABLE [dbo].[Table] SET (LOCK_ESCALATION = AUTO)
GO

该TRUNCATE命令被正在对表运行更新的会话阻止。在较低的环境中，经过多次尝试后，阻止过程从未发生过，因此假设只会WITH PARTITION按分区放置元数据锁，而不是整个表。

我不喜欢触发器和动态 sql，但是，我正在使用的东西需要两者。

CREATE TRIGGER [dbo].[GenerateDynamicFormItemViews] ON [dbo].[tblFormItems] AFTER INSERT, UPDATE
AS
BEGIN   
    SET NOCOUNT ON;
    DECLARE @DatabaseName NVARCHAR(100)
    DECLARE @FormItemID INT

    DECLARE db_cursor CURSOR FOR
    SELECT SourceID,FormItemID from Inserted

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID

    WHILE @@FETCH_STATUS = 0  
    BEGIN  

        BEGIN TRY    
            EXEC spAddEditFormItemView @FormItemID, @DatabaseName WITH RESULT SETS NONE
        END TRY    
        BEGIN CATCH 
            INSERT INTO AdminErrorLog(SourceID, ErrorNumber, ErrorState, ErrorSeverity, ErrorProcedure, ErrorLine, ErrorMessage, ErrorDateTime) 
            SELECT @DatabaseName, ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE()
        END CATCH     

        FETCH NEXT FROM db_cursor INTO @DatabaseName, @FormItemID
    END

    CLOSE db_cursor  
    DEALLOCATE db_cursor
END 

这就是正在发生的事情。

1. AWS DMS 复制任务正在批量复制 10,000 个数据。复制的实现是一个黑匣子，但连接上似乎存在嵌套事务。
2. EXEC spAddEditFormItemView 调用一个存储过程，该存储过程执行动态 sql 并在一条记录上出错。
3. CATCH 块永远不会被执行
4. 该错误永远不会进入 AdminErrorLog。
5. AWS Batch 出错并且从未提交，整个任务失败。
6. 在扩展事件中，捕获以下 SQL 错误。这是导致任务失败的错误。我读到了有关命令既不能处于提交或回滚状态的情况，但确实没有完全理解这个概念。

消息：当前事务无法提交，并且无法支持写入日志文件的操作。回滚事务。

严重程度：16

任何人都可以解释为什么 catch 块没有被捕获以及为什么客户端应用程序得到（我的猜测是）sql 异常并回滚所有内容。我的猜测是动态 sql 异常的处理方式不同，并且隐式触发事务正在回滚而不是捕获错误。另外，有谁知道避免传播异常的方法吗？

我敢打赌最安全的解决方案是修改触发器以将 sql 命令填充到表中，然后让 sql 代理作业查找每分钟左右运行的命令。

编辑-添加重新创建的过程：在 SSSM 中：

EXEC [spAddEditFormItemView] 30032,'VP_BENCHMARKING_V05'

命令成功完成。

完成时间：2023-11-27T16:11:18.1762097-05:00

在触发器中，它会强制回滚并显示可怕的错误消息。

ALTER PROCEDURE [dbo].[spAddEditFormItemView] ( 
    @FormItemID int,
    @Schema nvarchar(100)
)
AS
    DECLARE @SQL NVARCHAR(MAX) = 'e2e2e2e2e'
    BEGIN TRY   
        EXEC (@SQLCommand)      
    END TRY
    BEGIN CATCH
        DECLARE @X INT
    END CATCH

编辑：我现在可以在 SSMS 中复制。

有一种场景，必须将 175+ 个数据库组合成一个大型云数据库，这应该在多个环境、开发、测试、阶段生产等中得到支持。目前，主键基于客户端标识符，即该数据来自的数据库。在这种情况下，将基于同一列使用基于行的安全性。一些较大的表最终将达到接近 10 亿行。

问题是，特别是在较低的环境中，恢复很常见。为了及时截断大量数据，我一直在研究基于客户端标识符（即“COKE”|“PEPSI”等）的分区，这也是所有表的PK。这将允许将数据移入和移出，并可能更有效地建立索引。客户端密钥可以用于分区功能吗？我有在右侧使用基于日期的分区的经验，但是，不确定通过在两个现有分区之间添加新分区来更改分区功能的可管理性或可能性如何？

也许这可以通过为客户端使用顺序标识符来解决，并且随着新客户端的加入，它们会依次获得下一个 ID 并在右侧添加一个新分区？按客户端名称分区在 Sql Server 中是否实用。

多租户、多数据库系统中有N个表结构相同的数据库。希望将这些数据库中的一个或多个表复制到 OLAP 数据库中的一个更大的表中，我假设它可以工作。

-使用事务复制-

1. 为发布者处的所有表文章重新创建 PK，包括标识数据库的新添加字段。

2. 将选项“当文章存在时”设置为不删除和使用行过滤器（包括数据库标识符）。

3. 使用唯一数据库标识符为每个表添加行过滤器。

我的问题是，鉴于上述情况，如果为发布者创建新快照，是否会删除订阅者的陈旧数据，并且只针对该发布者？恐怕这就是删除表和重新创建的目的：/

简单来说，如果我有

如果重新初始化名为 Database007 的发布的新快照。是否会删除 Database007 订阅中的所有数据并为 Database007 重新水化，或者我是否会遇到 PK 违规。

我也一直在研究 CDC，但是，这似乎不支持 N-1 复制方案。另外，请随时抛出任何其他想法。

我正在研究一个会变得非常大的 DW 报告表。为简单起见，我将显示该表如下：

BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL

每个查询都将使用公司名称在数据分区（而不是物理分区）内进行查询。

由于该表可能包含超过十亿行，并且每个公司的数据分布非常均匀，因此按公司查询应该尽可能快。我正处于设置一些测试的阶段，但在这样做之前，我想我会问一下，看看这是否会浪费时间。

我的想法是确定如果每个公司的数据分区通过聚集索引在磁盘上彼此相邻放置，那么数据检索是否会比仅使用非聚集索引覆盖 CompanyName 更快。

示例 1：这是 IDENTITY 列是 PK 但不是 CLUSTERED 的变体。CompanayName 和 TableID 结合起来形成聚集索引，因此数据将按公司在磁盘上排序。

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY NONCLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [CLUSTERED_ByCompanyName_TableID] ON [dbo].[BigTable]
(
    [CompanyName] ASC,
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

这是创建具有覆盖索引的表的传统方式。

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ByCompanyName] ON [dbo].[BigTable]
(
    [CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

有谁知道在使用第一个示例而不是第二个示例时是否会有任何性能改进？

编辑：我倾向于在公司中使用聚集索引。如果行需要唯一引用，TableID 只是用作 PK 的自动增量字段。我觉得聚集索引搜索/扫描比索引扫描/搜索更快。

我希望您可以轻松地根据 companyid 之类的东西进行分区或分片。

基本查询的形式为

SELECT
    SUM(FieldA) OVER (PARTITION BY ...) a,
    COUNT(1) OVER (PARTITION BY...) b
    ...       
FROM
    BigTable 
WHERE
    CompanyName = 'NABISCO'
GROUP BY
   ....
ORDER BY
   ....

进行以下查询：

DECLARE @X VARCHAR(200) = '1,2,3,4'

SELECT
    *,
    dbo.aUserDefinedScalarFunction(4) AS ScalarValue
FROM
   MyTable T
   INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID
WHERE 
  (T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))
  AND
  (T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

我通常为上述条件创建索引#tempTables WHERE，我发现它在大型数据集上表现更好。但是，我仍然无法找到以下问题的明确答案：

1. 查询分析器会将 aUserDefinedScalarFunction(4) 优化为 ScalarValue 还是针对每条记录进行评估？

2. INNER JOIN dbo.aUserDefineTableFunction(@X) 会在临时表中实现一次，还是会为每条记录执行？该函数返回表（不是表变量）。

3. SELECT [value] FROM STRING_SPLIT(@X,',') 的结果是否得到优化，还是针对每次比较进行评估？

4. SELECT Value FROM dbo.MySplitterFunction(@X) 的结果是否得到优化或在每次比较期间进行评估？

我正在尝试在 Azure 管道中执行使用 Microsoft.SqlServer.Management.IntegrationServices 的 PowerShell 脚本。成功调用 SSIS 包，但是尝试远程连接时包失败。我觉得这与双跳有关，但不知道如何解决这个问题。

构建服务器（域\服务用户）--> 集成服务器（域\服务用户）--> 数据库服务器（域\服务用户）

注意：Domain\ServiceUser 有权访问该实例上的 SSISDB 和远程数据库。

My Project Import:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

为了帮助诊断问题是否与安全相关。我正在使用下面的过程来尝试调用包。当我这样做时，我收到以下错误：

--EXECUTE AS LOGIN  = 'Domain\ServiceUser'  
Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name='ProjectFolder',
    @project_name='ProjectFolderImport',
    @use32bitruntime=False,
    @reference_id=Null
EXEC [SSISDB].[catalog].[start_execution] @execution_id

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
Msg 6522, Level 16, State 1, Procedure start_execution_internal, Line 0 [Batch Start Line 0]
A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal": 
System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
System.Data.SqlClient.SqlException: 

当我取消注释 EXECUTE AS 时，我得到以下信息：

Cannot execute as the server principal because the principal "Domain\ServiceUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

我知道包可以与数据库服务器建立远程连接，因为当我创建一个任务来执行包并选择“运行为”SSIS 包执行代理用户“SSIS_Exec”时，它工作正常。我需要弄清楚如何让我的代码或存储过程执行类似于作为 SSIS_Exec 运行的操作。

我通常会避免像斑块这样的游标操作，但是，我遇到了第一个问题，即使用游标优于查询。所以我被迫使用它。

我创建了一个精心设计的报告存储过程，供许多用户使用。我使用游标迭代列表并将数据插入@TEMP_TABLE，最后选择临时表作为结果集。

游标的使用如下：

DECLARE HIGHLIGHTS_REPORT_CURSOR CURSOR LOCAL FOR
SELECT RowID, UserID,GradeID,ClassID,MenuSetID,CurrentSequence FROM @DATA   
OPEN HIGHLIGHTS_REPORT_CURSOR
FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID,@UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
WHILE(@@FETCH_STATUS=0)BEGIN
    ...
    FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID, @UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
END
CLOSE HIGHLIGHTS_REPORT_CURSOR
DEALLOCATE HIGHLIGHTS_REPORT_CURSOR

所以一切都很好，报告通过了 QA，没有问题，直到基于用户的增加现在我收到偶尔发送给我的异常日志，并出现以下错误：

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlExceptionexception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

我可以 100% 肯定地说没有另一个同名游标。和之间没有错误的CLOSE\DEALLOCATE CURSOR调用。BEGINEND

我了解到 SQL ServerGLOBAL在安装期间将“默认游标”数据库属性默认为。由于报告程序是唯一使用游标的 SP，我已将“默认游标”更改为LOCAL.

那没有帮助:(也许“默认光标”是一个需要重新启动的设置。我没有重新启动，而是使用了声明的LOCAL属性。CURSOR

这确保了我的光标在 SP 范围内，但仍然出现上述错误？？？

我开始认为这是多个连接同时访问 SP 的并发问题。这可以解释零星的行为以及为什么在 QA 期间没有发现这一点。

是否有可能两个连接几乎同时调用该过程，其中一个连接DEALLOCATE HIGHLIGHTS_REPORT_CURSOR恰好在第二个连接命中FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO块之前命中？

我假设使用 LOCAL 确保每个连接都有其副本，但事实并非如此？有任何想法吗？

更新。这很奇怪，我将三个异常消息堆叠为一个。我想知道快速连续调用同一个 sp 的客户端连接是否会导致类似的情况。

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

更新 2让我认为这是用户并发问题的部分是这些错误总是以两个为一组的事实。我同时收到来自两个不同用户的异常报告，其中包含上述错误。

在 SQL Server 2008 中，我上周创建了一个 Sql Server Agent Alert 来监视死锁事件。警报调用一个没有计划的作业，发送通过 WMI SELECT * FROM DEADLOCK_GRAPH 获得的 xml 死锁图标记。该作业调用存储图形的 SP 并发送电子邮件。

我一直在监视 Sql Server Waits，发现在我实施上述警报后，BROKER_TASK_STOP 和 SQLTRACE_WAIT_ENTRIES 的等待类别急剧上升。这些值从不被关注到分别占总等待时间的 42.59% 和 5.11%。

我应该担心吗？我在某处读到，高 BROKER_TASK_STOP 可能不是真正的问题。

我们最近将服务器升级到 SQL Server 2008 R2。服务器一直在运行 SQL Server 2005。我们正在使用

• 事务复制
• 生产服务器上的管理数据仓库和数据收集器

我们开始在应用程序日志中看到死锁错误。这在 SQL 2005 中没有发生。

在报告数据库上，我可以使用“ad-hoc”查询复制死锁，并在活动表上进行内部连接和表扫描。但是，我可以运行需要几分钟才能完成的报告过程，而不会出现任何问题。

sql 2008 对 sql 2005 的死锁更敏感吗？我将开始撤消我们已经实施和测试的一些更改。在我走这条路之前，我想看看其他人是否注意到升级到 2008 R2 时死锁增加了。

我已经通过 sql server 2008 r2 中的数据收集器监控文件增长两周了。该数据库以每天 35 (MB) 左右的速度持续增长。数据库尚未达到 2 GB 的初始大小。

数据库文件自动增长设置为 5MB，我想尝试不同的方法，所以我正在寻找建议和/或评论。

每周日晚上 1:30 有一个调优任务运行。该任务将：

• 检查数据库完整性
• 缩小日志文件——（这没关系，因为日志模式很简单）
• 收缩数据库
• 重组索引
• 重建索引
• 更新统计
• 清理历史

我想在每周调整计划中再添加两个步骤：

1. 如果已用空间达到某个阈值或总大小，则将数据库文件增加 500 MB。
2. 如果已用空间达到总大小的某个阈值，则将日志文件增加 250 MB（收缩后）。

通过将增长负担放在离线时间，我希望通过减少重负载期间的自动增长事件数量来获得性能。

我有两个与自动增长文件有关的问题。

• 放置文件增长步骤的最佳位置是在当前步骤之前还是之后？
• 如果我使用ALTER DATABASE|MODIFY FILE来增长文件，那么我如何确定是否SpaceUsedInFile >= (TotalFileSpace-@AllowanceThreshold)？