Sir Swears-a-lot's questions

我刚刚构建了 2 个新的 Win/SQL 2022 服务器。一切顺利，我可以连接 SSMS。

然后我将两个 SQL 引擎服务更改为使用域帐户（我的标准做法），我可以在本地连接，但无法再通过 SSMS 远程连接并出现以下错误。

The target principal name is incorrect.  Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

当 SQL 作为本地服务运行时，它可以注册一个 SPN。但是当它作为域帐户运行时它不能。（我已经在两台服务器上重复了几次）。我还尝试使用域管理员帐户运行该服务。

我已经按照自动和手动设置 SPN的MS 说明进行操作。而且都没有解决问题。

还有其他人遇到过这个吗？任何意见或建议将不胜感激。

编辑：我已经尝试过 Kerberos Config Mgr 工具，但它无法远程或本地连接，它返回错误：

10/01/2023 11:36:10 am Error: Connect to SQLWMI failed \root\Microsoft\SqlServer\ComputerManagement System.Management.ManagementException: Invalid namespace

我也试过运行 SQL 修复。

启动后SQL日志显示如下警告：

SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/server.domain ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

我正在尝试查询如下所示的 xml。

<wfs:FeatureCollection xmlns:gml="http://www.opengis.net/gml" xmlns:wfs="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <gml:featureMember>
    <SiteList>
      <Site>123 ABC Road</Site>
      <Location>
        <gml:Point>
          <gml:pos>-38.987654321 177.12345678</gml:pos>
        </gml:Point>
      </Location>
    </SiteList>
  </gml:featureMember>
</wfs:FeatureCollection>

这是我的查询。我需要网站 & gml:pos (lat/Long)

select 
    xml.xmldata.value('(wfs:FeatureCollection/gml:featureMember/SiteList/Site)[1]', 'varchar(250)') as [SiteName],  
    xml.xmldata.value('(wfs:FeatureCollection/gml:featureMember/SiteList/Location/gml:Point/gml:pos)[1]', 'varchar(250)') as [Location]
    from  #xml t
    cross apply t.yourXML.nodes('//wfs:FeatureCollection/gml:featureMember/SiteList') xml (xmldata)

我收到以下错误：

Msg 2229, Level 16, State 1, Procedure dbo.DW_sp_Extract_Sites, Line 102 [Batch Start Line 2]
XQuery [#xml.yourXML.nodes()]: The name "wfs" does not denote a namespace.

谁能告诉我我做错了什么？任何帮助或建议将不胜感激。我一直在与 xquery 语法作斗争。

我有一个显示水位数据的折线图。

该图有一个工具提示，当您将鼠标悬停在上面时会显示值。见下文。

我的问题是记录流显示的小数位数不同。我希望它们都显示为小数点后 3 位。

所有三个值都使用浮点数据类型存储在 SQL 中。警告和截止级别值是单个值（可以是 1、2 或 3 位小数，但显示为 3 位小数），其中记录的流量是存储到小数点后 8-10 位的可变时间序列，但仅显示作为小数点后 2 位。（我想要 3 个。）

我看不到任何数据类型被更改或值已四舍五入的地方。我已经删除并重新添加了时间序列，并更改了出现的顺序，但没有区别。我什至对 SQL 中的数据进行了四舍五入，以查看 PowerBi 是否在嗅探数据以确定格式。

我发现的所有网络搜索结果都在谈论缺乏格式控制，但并不是说它们不一致或看起来不同。

我错过了什么？

我想从 SSAS 中检索每个 OLAP 多维数据集或数据库的现有连接字符串。数据将用于控制/审计报告。

我发现 Powershell 允许我像目录一样钻入 SSAS，我可以看到我在追求什么，但不知道如何查询它。

在 Powershell 中，我可以深入到这里：

PS SQLSERVER:\SQLAS\LAP123\Default\databases\EnvironmentalAnalysisService\Datasources> 

我可以看到我想要的细节：

Name                      Isolation     Max Connections Connection String                       
----                      ---------     --------------- -----------------                       
Environmental Data Mart   ReadCommitted              10 Provider=SQLNCLI11.1;Data               
                                                        Source=LAP123;Integrated                
                                                        Security=SSPI;Initial                   
                                                        Catalog=EnvironmentalDataMart

在这种情况下服务器；LAP123 和数据库：EnvironmentalDataMart

如何从 Powershell 中获取我想要的项目，例如查询？

我刚刚将 2018 年 8 月版的 PowerBI 报表服务器安装到带有 SSRS 2016 的同一台 Win 2012 服务器上。我可以让它运行，但它与带有 URL 保留的 SSRS 冲突。

我已将它们配置为使用不同的虚拟目录和端口，但 SSRS 不断中断。

• SSRS：http://ServerName:80/Reports
• PBIRS：http://ServerName:8080/PBIReports

我错过了什么？

编辑：我有 SSRS 2017 和 PBIRS 在我的笔记本电脑上同时工作。两者都在端口 80 上，但具有不同的虚拟目录。

我正在尝试使用我已授予凭据的 SQL 身份验证帐户发送带有附件的电子邮件。

存储的凭据中使用的登录名是有权从共享中读取的域帐户。

EXEC msdb.dbo.sp_send_dbmail 
    @recipients = '[email protected]', 
    @profile_name = 'Alarms', 
    @subject = 'Test with Attachment', 
    @body = '*** Alarms ***' , 
    @file_attachments = '\\Servername\Share\Images\TestImage.png'

但是当我使用 SQL 登录时，出现以下错误：

消息 22051，级别 16，状态 1，第 26 行无法模拟客户端连接安全上下文。附加文件需要集成的客户端登录

我错过了什么？我如何测试凭据是否已正确应用？这仅适用于本地共享吗？

编辑：我授予我的 SQL 登录 Sysadmin 权限，它现在可以与本地共享和远程共享一起使用。那么系统管理员有什么特权可以让它工作呢？

我正在尝试使用 HammerDb 3.0 获取基准来比较我的服务器。

我可以毫无问题地创建所需的 Db、模式、数据和项目。我可以执行测试并看到它正在运行。

当脚本完成时，它会给我一个 NOPM 编号，但说 TPM 为零。当它运行时，事务计数器也不会绘制任何图形。

这是已知问题还是常见问题？

我的曲线由 X/Y 点（数组？）组成，存储在具有相同分隔符的字符串中。每行有 1 条曲线。下面是一个简单的例子：

0,0,1,1,2,1.9,2.9,2.8,3.6,3.5

我想将每条曲线提取到 #table 中，以便我可以编辑它们并最终将它们放回去。

X,Y
0,0
1,1
2,1.9
2.9,2.8
3.6,3.5

一条曲线可能有十几个到数百个值，并且这些点可能没有顺序。

最干净/最简单的方法是什么？

我有一个带有 XML 列的表。我需要插入各种节点，然后使用子查询中的值设置这些节点。

设置：

use tempdb

CREATE TABLE [dbo].[Sites](
    [SiteID] [int] IDENTITY(1,1) NOT NULL,
    [SiteInfo] [xml] NULL,
    [InVal] Varchar(50) NULL)

insert into [dbo].[Sites] ([InVal]) select 'ABC' union select 'DEF' union  select 'GHI'

update [dbo].[Sites] SET [SiteInfo] = '<SiteInfo />'

update [dbo].[Sites] SET [SiteInfo].modify('insert <SiteID/> into (/SiteInfo[1])') 

这是我最简单形式的更新。

update Sites SET [SiteInfo].modify('replace value of (/SiteInfo/@SiteID)[1] with sql:column("InVal")') 

我已经尝试过单例和非单例节点

最终结果最好不要是单例。即类似的东西：

<SiteInfo>
  <SiteID>ABC</SiteID>
</SiteInfo>

我的任务是每天改进第一个报表连接的性能。类似于这个帖子。

我们有一个简单的 SSRS 报告作为登录页面。它具有指向后续报告的超链接。

我努力了：

• 在早上 6:30执行powershell 重启脚本。
• 将报告缓存配置为在重新启动后每 10 分钟按计划刷新一次
• 添加订阅。
• 安排一个powershell 脚本来导出报告。

似乎问题在于 SSRS 以某种方式对待这些连接的方式与真实用户通过 Web 浏览器打开报告的方式不同。即使报表已运行，它的处理方式似乎也与用户通过门户点击它的方式不同。所以......第一个用户遇到了很大的延迟。

使用 Internet Explorer 中的 F12 调试功能，我设法重现并捕获了浏览器所看到的内容。SSRS 只用了 4 秒来生成和返回报告，但是在浏览器中从尝试打开 URL 到正在呈现的屏幕需要 122 秒。2分钟！！！难怪用户抱怨。

查看我的 SSRS 日志文件（位于：C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles），SSRS appdomain 似乎正在重新启动或重新加载。

下面的日志摘录显示

1. 计划在 08:00:19 和 08:10:19 刷新缓存
2. 随后是我的报告请求，大约 8:15:01
3. 我直到大约 8:17:06 才收到报告

日志文件：

library!WindowsService_0!1bdf8!08/24/2017-08:10:19:: i INFO: Call to CleanBatch()
library!WindowsService_0!1bdf8!08/24/2017-08:10:20:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, etc etc 
library!WindowsService_0!1bdf8!08/24/2017-08:10:20:: i INFO: Call to CleanBatch() ends
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:01:: i INFO: Registering AppDomain: type='ReportServer'; id='3'; Name='ReportServer_MSSQLSERVER_0-1-131479929006508632'
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:01:: i INFO: Creating a new AppDomainLifeCycleManagementEntry for AppDomain id '3'
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:01:: i INFO: Appdomain STARTED: id='3'; name='ReportServer_MSSQLSERVER_0-1-131479929006508632'
appdomainmanager!ReportServer_0-1!adb0!08/24/2017-08:15:08:: i INFO: RS authentication mode is 4; effective ASP.NET authentication mode is Windows. vdir=/ReportServer.
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:08:: i INFO: Appdomain:3 ReportServer_MSSQLSERVER_0-1-131479929006508632 initialized (#1).
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:08:: i INFO: SetInitialized AppDomain id '3': initialized = 1
webserver!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Reporting Web Server started
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: SQLDump flags: ReferencedMemory, AllThreads, SendToWatson
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: MiniDump flags: DataSegs, UnloadedModules, ProcessThreadData
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Dump on: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogExceptionMicrosoft.ReportingServices.
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Do not dump on: System.Threading.ThreadAbortExceptionSystem.Web.UI.ViewStateExceptionSystem.OutOfMemoryExceptionSystem.
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Minidump location: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\Logfiles
runningjobs!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds
runningjobs!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds
resourceutilities!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Reporting Services starting SKU: Developer
ui!ReportServer_0-1!adb0!08/24/2017-08:16:52:: i INFO: Installed SKU: Developer edition
ui!ReportServer_0-1!adb0!08/24/2017-08:16:52:: i INFO: Registering with Software Usage Metrics Product:SQL Server 2016, Role:Microsoft SQL Server Reporting Services,  etc etc
library!ReportServer_0-1!adb0!08/24/2017-08:16:52:: i INFO: Call to GetItemTypeAction(/CIA). User: MYDOMAIN\prm.
library!ReportServer_0-1!adb0!08/24/2017-08:16:53:: i INFO: Catalog SQL Server Edition = Developer
crypto!ReportServer_0-1!adb0!08/24/2017-08:16:54:: i INFO: Initializing crypto as user: NT Service\ReportServer
crypto!ReportServer_0-1!adb0!08/24/2017-08:16:54:: i INFO: Exporting public key
crypto!ReportServer_0-1!adb0!08/24/2017-08:16:54:: i INFO: Importing existing encryption key
library!ReportServer_0-1!adb0!08/24/2017-08:17:02:: i INFO: Entering StreamRequestHandler.ExecuteCommand - Command = StyleSheet
library!ReportServer_0-1!adb0!08/24/2017-08:17:02:: i INFO: Exiting StreamRequestHandler.ExecuteCommand - Command = StyleSheet (success)
library!ReportServer_0-1!adb0!08/24/2017-08:17:06:: i INFO: Call to GetItemTypeAction(/CIA). User: MYDOMAIN\prm.
library!ReportServer_0-1!adb0!08/24/2017-08:17:06:: i INFO: RenderForNewSession('/CIA')
library!ReportServer_0-1!adb0!08/24/2017-08:17:07:: i INFO: Using folder C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\blahblahblah

由于到目前为止我尝试过的各种解决方法似乎都不起作用......
在真正的用户尝试运行报告之前，如何强制 SSRS 执行此操作？

我只需要调试一个让我彻底困惑的问题。

我们的开发数据仓库上的 ETL 转换流程在每天成功运行数月后刚刚失败。相同的 SSIS 作业调用相同的存储过程，具有相同的表架构、索引和数据在生产中运行良好。

此步骤通常需要不到 2 分钟。今天，4 小时后，这项工作还没有完成，但也没有失败。没有报告错误。SQL 日志中没有任何内容，sp_who2也没有显示任何阻塞。

• 这是查询执行良好时估计计划的链接。
• 这是查询未完成时的估计计划的链接。

该作业截断一个临时表，然后插入大约 600,000 行数据。ETL 进程对表具有独占访问权。当我检查时，我能看到的只是 waits on CXPACKET。

我已将故障追溯到一个唯一的聚集索引。

该表在标识列上有一个非聚集主键（见下文）

CREATE TABLE [dbo].[Transform_JobCosting_Transaction](
    [ETL_TransformKey] [int] IDENTITY(1,1) NOT NULL,
    [TransactionId] [varchar](255) NOT NULL,
    [KeyType] [varchar](255) NOT NULL,
    [FinancialYear] [varchar](255) NOT NULL,
    [Job] [varchar](255) NOT NULL,
    [Subjob] [varchar](255) NOT NULL,
    [AnalysisCode] [varchar](255) NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL,
    [etc] [varchar](255) NOT NULL
     CONSTRAINT [PK_Transform_JobCosting_Transaction] PRIMARY KEY NONCLUSTERED 
(
    [ETL_TransformKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

问题聚集索引是：

CREATE UNIQUE CLUSTERED INDEX [IDX_Unique] ON [dbo].[Transform_JobCosting_Transaction]
(   [FinancialYear] ASC,
    [KeyType] ASC,
    [TransactionId] 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) ON [PRIMARY]
GO

有一个第三个非聚集索引根本不影响插入。

我们两个人已经为此工作了 4 个小时。我已经删除并重新添加了索引 20-30 次尝试不同组合的选项。

摘要：聚集索引块插入。非集群工作正常。

我们尝试过：

• 服务器重新启动没有任何区别。
• 表被截断并从 SSMS 手动运行 sp 没有差异。（有 SA 权限）
• 重建索引没有帮助。
• 如果我删除聚集索引，则插入工作。
• 在上面插入之后，我可以添加聚集索引而不会出错。
• 如果将索引删除并重新添加为非聚集索引，则它可以工作。
• 我检查了数据，它是由这 3 个字段分组的唯一的。
• 更改索引以使其不唯一并没有任何区别。
• 添加/删除with tablock提示没有帮助。
• 我尝试在插入之前对数据进行排序，但没有任何区别。

运行：Windows Server 2012 R2 Standard 6.3 上的 Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Developer Edition（64 位）

任何想法或建议将不胜感激。

当我尝试处理我的 OLAP 多维数据集时，我的 SSIS ETL 过程中出现间歇性故障。

SQL 日志中没有错误消息，并且当时没有其他重要的运行。我可以在事件日志中找到的唯一错误消息是：

在传输层中遇到错误。

SSIS 和 SSAS 是 SQL 都在同一台服务器上本地运行，所以我根本不相信它与网络相关。

已部署相同的软件包并成功运行了数周。如果我们手动连接到它处理的多维数据集没有任何问题。

Windows Server 2012 R2 6.3（内部版本 9600：）在 VMWare 64gb 内存上。

Microsoft SQL Server 2016 企业版 (SP1-CU1) - 13.0.4411.0 SQL 最大内存 = 48gb

我的直觉是，这更像是一个 SSIS 问题而不是 SSAS 问题。

有人遇到过这种情况么？任何人都可以推荐解决方案或解决方法吗？是否可以获得额外的调试信息？

每次我尝试在查询存储中查看缺失的索引详细信息时，都会收到以下错误：

DocumentFrame（SQL 编辑器）

程序位置：Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ShowPlan.ShowPlanControl.OnMissingIndexDetails(Object sender, EventArgs a) at System.ComponentModel.Design.MenuCommand.Invoke() at Microsoft.SqlServer.Management.UI.VSIntegration .Editors.ShowPlan.ShowPlanControl.Microsoft.VisualStudio.OLE.Interop.IOleCommandTarget.Exec（Guid& guidGroup，UInt32 nCmdId，UInt32 nCmdExcept，IntPtr vIn，IntPtr vOut）

有其他人遇到这种情况吗？这是我可以修复的东西还是一个错误？

SSMS 版本 13.0.16106.4

这有点令人沮丧，因为我无法查看创建索引建议的结尾。

我正在尝试跨标准偏差和平均值等一系列数据计算各种统计计算。

数据来自许多不同的“数据源”。这些都保存在一个维度中。

我尝试使用此示例STDDEVP中所示的函数，该函数对度量进行操作。

但结果是错误的。我认为这是错误的，因为它处理的是总数而不是原始数据。[Sum of Values] 是对事实表中的数据求和的标准聚合度量。

按照格雷格提供的链接，我现在正在使用这个：

CREATE MEMBER CURRENTCUBE.[Measures].[Standard Deviation]
 AS  (([Measures].[Sum of Values]^2 - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values])/[Measures].[Count of Values])^0.5),
FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Fact TS Data'; 

这给了我这个：

这仍然是不正确的。

使用 SQL 如果我使用STDDEV以下查询计算 Air Temp：

  SELECT stdevp([Value])
  FROM [EnvironmentalDataMart].[DDS_HILLTOP].[factTimeSeries]
  where [DETL_DataSourceSurrogateKey] = 78

我得到：6.13770334742149

如何让 MDX 查询计算STDDEV我的维度中的每个数据源？

我也尝试过使用“无聚合”的 Measure，但它没有返回任何内容。

我正在基于气象数据构建一个 SSAS OLAP 多维数据集。其中一些数据可以汇总（例如降雨），而有些则不能（例如风向）。

我想要总计，但仅限于有意义的数据源。（其中插值方法 = 3。）

我试过用这个添加范围：

SCOPE ([Measures].[Total]); 
SUM([Data Source].[Interpolation Method].&[3],[Measures].[Total]); 
END SCOPE; 

并得到警告：

无效的查询语法。应有有效的 MDX 或 DAX 查询

和：

在 MDX 脚本中遇到一个或多个错误

我错过了什么？

我正在尝试在 SQL Server 2016 上开始使用 R 服务。我在 youtube 上关注 MS 的这个示例。

2 分 22 秒进入视频 John 显示了对 library(RODBC) 的引用......我没有。所以我从 CRAN 下载了 RODBC 驱动程序

根据文档：

RODBC 安装简单，CRAN 提供适用于 Windows 的二进制分发版

但它没有提供有关如何安装它的更多详细信息。它提到了一个ODBC 驱动程序管理器，但仅此而已。

所以..我如何安装 RODBC？

我是否将其解压缩到特定路径？安装它是否需要实用程序或工具？我运行脚本吗？

我有一个更新语句，它使用子查询来过滤记录。我的输入表在 varchar 字段中包含日期，其中一些无效。

CREATE TABLE [dbo].[input](
    [id] int,
    [START_DATE] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(1, NULL) ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(2, '') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(3, '01 JUL 0201') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(4, '01 JUL 2016') ;

在示例数据中，只有记录 4 有效。

我的查询：

select a.[id] 
FROM (
    select [id], convert(datetime, [START_DATE],106) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0     
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < getdate()

我玩过各种条款来排除垃圾：

• len > 0 排除空值和 ''。
• 子串寻找合理的年份。
• isdate 再次检查有效日期。

我确信还有其他更有效的方法可以做到这一点，但关键是子查询会执行并正确返回有效记录。在本例中，id = 4。有效日期为 2016 年 7 月 1 日。

外部查询现在应该只是将转换后的日期与今天的日期进行比较。但是我得到：

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

为什么？我错过了什么？

内部查询已经过滤并转换了我正在评估的值。

有没有更好的方法来过滤此列表以用于更新查询？

我刚刚将我们的数据仓库升级到 SQL 2016。我在查询存储中看到了一些非常有趣的图表（我喜欢这个功能！）。下面是我见过的最奇怪的例子。同一查询的 22 个计划。

这让我开始考虑 ETL 流程的性能调整、临时表的优缺点以及如何影响执行计划的行为。

我的 ETL 过程使用了许多存储过程，它们混合使用标准和临时 #tables 作为临时表。#tables 通常使用一次然后删除。有些只有几千行。有些是数百万。SSMS 建议缺少索引，但在较小的表上，它们是否会产生足够的差异以值得努力添加它们？更好的统计数据就足够了吗？

我刚刚阅读了有关临时表统计信息的 Brent Ozar 博客文章，以及 Paul White 关于存储过程中的临时表的文章

它说统计信息是在查询#table 时自动创建的，然后可能会被优化器使用。

我的问题是：在#table 上创建索引有什么意义或好处。和/或：是否值得在查询中使用之前显式更新统计信息作为存储过程中的一个步骤，因为它们只使用一次。

额外的步骤和开销值得吗？它会导致明显更好或不同的执行计划吗？

我目前正在将我们的数据仓库从 SQL 2012 升级到 SQL 2016。我的旧 DW 和新 DW 并行运行。

我的 ETL 流程（由第三方在 SSIS 中开发的框架）在 2012 年成功运行了 2 年多，但在 2016 年失败了。到目前为止，数据库和 ETL 流程是相同的。

两台服务器都是在 VMWare 上运行的虚拟机。旧服务器是 Win 2008，具有 24Gb 的 RAM。SQL 2012 标准。最大内存设置为 16Gb。新服务器是 Win 2012，具有 64Gb 的 RAM。SQL 2016 开发。最大内存设置为 50Gb。新 DW 正在运行 v13.0.1601.5 RTM 开发人员版（64 位）。

在运行我的 ETL 过程时，使用 SQL 合并到维度或事实表的加载步骤失败并出现以下错误。

全文：

描述: SQL Server 断言: 文件: , line=951 断言失败 = 'IS_OFF (BUF_MINLOGGED, m_buf->bstat) || pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage ()'。此错误可能与时间有关。如果重新运行语句后错误仍然存​​在，请使用 DBCC CHECKDB 检查数据库的结构完整性，或重新启动服务器以确保内存中的数据结构没有损坏。

按照建议，我运行了 DBCC，没有发现任何错误。我也重新启动了 SQL。然后我重新启动了 ETL 过程并得到了同样的错误。

我对这个错误的搜索表明这是SQL 2008、2012 和 2014 中的已知错误，并在后续的修补程序和累积更新中修复。所以我有点惊讶地看到它在 2016 年重新出现。

我发现的链接说，如果数据库处于简单或批量日志恢复模式，它会在尝试插入时影响 SSIS。（我在简单恢复模式下运行）

建议的解决方法是将 Db 恢复模式更改为 FULL。我已经尝试过了，它可以工作，但它不是数据仓库的解决方案。

2016年有没有人遇到过这种情况？

任何人都可以建议替代解决方法吗？

更新：

26/7/2016：我应用了关键更新 KB3164398 (v13.0.1708.0)，但问题仍然存在。

27/7/2016：我已应用累积更新 CU1 KB3164674 (v13.0.2149.0)。

2016 年 3 月 8 日：我们最小的立方体在一夜之间发生错误。CU1 没有解决这个问题。今天我报告了 MS Connect 上的错误，并且我还记录了与 Microsoft 的支持电话。

2016 年 12 月 8 日：MS-Support 最初做出了回应，但回应是“我们没有解决方案”。支持人员将与他的同事讨论并回复我。8天后我没有他的消息。

虽然我没有“修复”，但我们确实找到了适合我们的解决方法。请参阅我发布的答案。

2016 年 9 月 29 日。我上周申请了CU2。在星期四，我们不小心运行了旧版本的合并，它再次失败并出现同样的错误。所以.. CU2 也没有修复它。

23/1/2017：我应用了2016 SP1 CU1，我相信这已经解决了这个问题。特别是 KB3205964

我们最近将我们的 ERP 系统从 IBM Universe 转换为 SQL Server。应用程序性能通常是可以容忍的，但偶尔会降级到糟糕。

我们在具有 32 Gb RAM 和 SQL Server 2012 的 VMWare 上的 Win Server 2012 上运行数据库。SQL Max Memory 设置为 27Gb。db 服务器仅托管此数据库，不执行任何其他功能。总数据库大小约为 110Gb。该应用程序有它自己的专用服务器。

供应商已广泛使用 CLR 来移植代码（超过 36,000 个标量函数）。我了解单个 CLR 在应用程序 OLTP 上下文中运行正常，但由于逐行而不是基于集合的操作而尝试执行批量作业时，不能很好地扩展。很好……很酷……继续前进。

我运行了Brent Ozar 的脚本，该脚本将高可用内存标识为需要查看的内容，以及每个查询的大量执行计划。供应商建议向服务器添加更多内存，但这让我很恼火，因为应用程序似乎没有使用现在的内存。

我感兴趣的是整体的 SQL 性能和行为。我看到一系列症状表明某些事情不正确，但我无法确定。就像服务器拒绝运行一样。它决心走路。

非常粗略地说，在我看来，大约 10Gb 的内存被数据库用于缓存，大约 11GB 是免费的，大约 3.5Gb 用于计划缓存，其余的我无法解释。而且我有点不确定一些定义，例如免费、保留和被盗等。它们是否重复计算？

活动监视器显示：

当我运行此查询时：

-- what's happening inside my buffer pool?
SELECT counter_name, instance_name, mb = cntr_value/1024.0
  FROM sys.dm_os_performance_counters 
  WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
  OR (instance_name = N'' AND counter_name IN 
       (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)', 
        N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)', 
        N'Log Pool Memory (KB)', N'Free Memory (KB)')
  ) ORDER BY mb DESC;

我得到：

+--------------------------------+---------------------+----------+
|          Counter_name          |    instance_name    |    mb    |
+--------------------------------+---------------------+----------+
| Free Memory (KB)               |                     |  11,732  |
| Stolen Server Memory (KB)      |                     |   5,426  |
| Lock Memory (KB)               |                     |      59  |
| Log Pool Memory (KB)           |                     |       4  |
| Optimizer Memory (KB)          |                     |       2  |
| Connection Memory (KB)         |                     |       2  |
| Cursor memory usage            | TSQL Global Cursor  |       1  |
| Cursor memory usage            | TSQL Local Cursor   |       0  |
| Cursor memory usage            | API Cursor          |     -    |
| Granted Workspace Memory (KB)  |                     |     -    |
+--------------------------------+---------------------+----------+

当我运行此查询时：

-- which db's are using memory and how much. 
SELECT
    (CASE WHEN ([database_id] = 32767)
        THEN N'Resource Database'
        ELSE DB_NAME ([database_id]) END) AS [DatabaseName],
    COUNT (*) * 8 / 1024 AS [MBUsed],
    SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty]
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id];

我得到：

+-------------------+----------+---------+
|   DatabaseName    |  MBUsed  | MBEmpty |
+-------------------+----------+---------+
| ERP               |  10,764  |    626  |
| master            |       2  |    -    |
| model             |     -    |    -    |
| msdb              |      11  |      3  |
| Resource Database |      16  |      5  |
| tempdb            |      41  |     13  |
+-------------------+----------+---------+

当我运行此查询时：

SELECT TOP (12) Type, Name, pages_kb,
       Virtual_Memory_reserved_kb, Virtual_Memory_committed_kb
FROM   sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

我得到：

+---------------------------+-----------------------+----------+----------------------------+-----------------------------+
|           Type            |         Name          | pages_kb | Virtual_Memory_reserved_kb | Virtual_Memory_committed_kb |
+---------------------------+-----------------------+----------+----------------------------+-----------------------------+
| MEMORYCLERK_SQLBUFFERPOOL | Default               | 11224968 |                   12999744 |                      640296 |
| CACHESTORE_SQLCP          | SQL Plans             |  3519552 |                          0 |                           0 |
| CACHESTORE_CLRPROC        | ClrProcCache          |   110232 |                          0 |                           0 |
| CACHESTORE_OBJCP          | Object Plans          |   100776 |                          0 |                           0 |
| USERSTORE_DBMETADATA      | ERP_Live              |    93856 |                          0 |                           0 |
| USERSTORE_SCHEMAMGR       | SchemaMgr Store       |    87544 |                          0 |                           0 |
| CACHESTORE_PHDR           | Bound Trees           |    73464 |                          0 |                           0 |
| MEMORYCLERK_SOSNODE       | SOS_Node              |    62456 |                          0 |                           0 |
| OBJECTSTORE_LOCK_MANAGER  | Lock Manager : Node 0 |    60792 |                     131072 |                      131072 |
| MEMORYCLERK_SQLCLR        | Default               |    40992 |                    6327292 |                      429408 |
| MEMORYCLERK_SQLSTORENG    | Default               |    28472 |                       9472 |                        9472 |
| MEMORYCLERK_SQLQUERYEXEC  | Default               |    20904 |                          0 |                           0 |
+---------------------------+-----------------------+----------+----------------------------+-----------------------------+

1. 看来我有 11Gb 的“可用内存”。这真的可以免费使用吗？为什么 SQL 不使用它？

2. 在我看来，我的 ERP 系统只使用了大约 10Gb 或大约 1/3 的可用内存。（感觉不对。）如何鼓励我的应用程序更有效地使用内存

3. MEMORYCLERK_SQLCLR 已保留 6.03Gb 内存。这是 CLR 的正常行为吗？他们什么时候保留内存？它们何时被编译/注册/执行？他们曾经发布它吗？这是在“空闲内存”中吗？（由斯鲁茨基回答）

4. Re: 大量执行计划会刷新缓存有帮助吗？

5. 我可以使用任何功能来影响上述行为吗？还是我只需要接受这就是应用程序的工作方式。

6. 我如何解释服务器上实际持有或使用内存的内容。

其他人要求的查询

这些：

SELECT type,
       SUM(pages_kb)/1024 AS [Memory utilized in MB],
       SUM(awe_allocated_kb)/1024 AS [Memory allocated though Windows API]
FROM   sys.dm_os_memory_clerks
GROUP BY type
ORDER BY [Memory utilized in MB] DESC;

SELECT * FROM sys.dm_os_process_memory;

返回：

+----------------------------------+-----------------------+-------------------------------------+
|               type               | Memory utilized in MB | Memory allocated though Windows API |
+----------------------------------+-----------------------+-------------------------------------+
| MEMORYCLERK_SQLBUFFERPOOL        |                  4417 |                                   0 |
| CACHESTORE_SQLCP                 |                  3437 |                                   0 |
| CACHESTORE_CLRPROC               |                   120 |                                   0 |
| USERSTORE_DBMETADATA             |                   100 |                                   0 |
| CACHESTORE_OBJCP                 |                    99 |                                   0 |
| USERSTORE_SCHEMAMGR              |                    76 |                                   0 |
| CACHESTORE_PHDR                  |                    72 |                                   0 |
| MEMORYCLERK_SOSNODE              |                    64 |                                   0 |
| OBJECTSTORE_LOCK_MANAGER         |                    59 |                                   0 |
| MEMORYCLERK_SQLCLR               |                    38 |                                   0 |
| MEMORYCLERK_SQLSTORENG           |                    26 |                                   0 |
| MEMORYCLERK_SQLQUERYEXEC         |                    14 |                                   0 |
| MEMORYCLERK_SQLGENERAL           |                    10 |                                   0 |
| OBJECTSTORE_SNI_PACKET           |                     9 |                                   0 |
| CACHESTORE_SYSTEMROWSET          |                     8 |                                   0 |
| USERSTORE_TOKENPERM              |                     7 |                                   0 |
| MEMORYCLERK_XE                   |                     6 |                                   0 |
| MEMORYCLERK_SQLLOGPOOL           |                     4 |                                   0 |
| CACHESTORE_SEHOBTCOLUMNATTRIBUTE |                     3 |                                   0 |
| MEMORYCLERK_SQLOPTIMIZER         |                     2 |                                   0 |
| MEMORYCLERK_SQLQERESERVATIONS    |                     2 |                                   0 |
| MEMORYCLERK_SQLCONNECTIONPOOL    |                     1 |                                   0 |
| OBJECTSTORE_LBSS                 |                     1 |                                   0 |
| CACHESTORE_STACKFRAMES           |                     0 |                                   0 |
| MEMORYCLERK_SQLHTTP              |                     0 |                                   0 |
+----------------------------------+-----------------------+-------------------------------------+

+---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+
| physical_memory_in_use_kb | large_page_allocations_kb | locked_page_allocations_kb | total_virtual_address_space_kb | virtual_address_space_reserved_kb | virtual_address_space_committed_kb | virtual_address_space_available_kb | page_fault_count | memory_utilization_percentage | available_commit_limit_kb | process_physical_memory_low | process_virtual_memory_low |
+---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+
|                  28571952 |                         0 |                          0 |                   137438953344 |                          77358808 |                           28786620 |                       137361594536 |       1014012259 |                            99 |                   3734268 |                           0 |                          0 |
+---------------------------+---------------------------+----------------------------+--------------------------------+-----------------------------------+------------------------------------+------------------------------------+------------------+-------------------------------+---------------------------+-----------------------------+----------------------------+

这个：

SELECT COUNT(*) AS [NumCachedObjects],
       CONVERT(BIGINT, SUM(CONVERT(BIGINT, size_in_bytes)) / 1024.0) AS [CachedKBytes],
       ISNULL(cacheobjtype, '<-- Totally Total') AS [CacheObjType],
       ISNULL(objtype, '<-- TOTAL') AS [bytes]
FROM   sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype WITH ROLLUP;

回报：

+------------------+--------------+-------------------+-----------+
| NumCachedObjects | CachedKBytes |   CacheObjType    |   bytes   |
+------------------+--------------+-------------------+-----------+
|             3882 |        62112 | CLR Compiled Func | Proc      |
|             3882 |        62112 | CLR Compiled Func | <-- TOTAL |
|                3 |           24 | CLR Compiled Proc | Proc      |
|                3 |           24 | CLR Compiled Proc | <-- TOTAL |
|               50 |         4168 | Compiled Plan     | Adhoc     |
|            26911 |      3416232 | Compiled Plan     | Prepared  |
|              101 |        99584 | Compiled Plan     | Proc      |
|                5 |         1656 | Compiled Plan     | Trigger   |
|            27067 |      3521640 | Compiled Plan     | <-- TOTAL |
|               17 |          136 | Extended Proc     | Proc      |
|               17 |          136 | Extended Proc     | <-- TOTAL |
|               16 |          536 | Parse Tree        | Check     |
|                3 |           24 | Parse Tree        | Default   |
|              313 |        20632 | Parse Tree        | UsrTab    |
|              535 |        52520 | Parse Tree        | View      |
|              867 |        73712 | Parse Tree        | <-- TOTAL |
|            31836 |      3657624 | <-- Totally Total | <-- TOTAL |
+------------------+--------------+-------------------+-----------+

这个：

SELECT * FROM sys.dm_clr_appdomains;

回报：

+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+ 
| appdomain_address  | appdomain_id | appdomain_name                           | creation_time           | db_id | user_id | state              | strong_refcount | weak_refcount | cost      | value    | compatibility_level | total_processor_time_ms | total_allocated_memory_kb | survived_memory_kb | 
+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+ 
| 0x00000003DECEC200 | 16           | ERP       .CLRExtensionUser[runtime].111 | 2016-07-13 10:51:23.370 | 5     | 5       | E_APPDOMAIN_SHARED | 1               | 3236          | 130810392 | 11534336 | 110                 | 15                      | 112020591                 |                206 | 
+--------------------+--------------+------------------------------------------+-------------------------+-------+---------+--------------------+-----------------+---------------+-----------+----------+---------------------+-------------------------+---------------------------+--------------------+