Daniel Björk's questions

我试图找到有关何时以及谁缩小了 ldf 文件的信息，但我无法在任何地方找到该信息。

有人能指出我正确的方向吗？

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4266.0 (X64) 
    Sep 26 2012 17:08:07 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

当我在没有OPTION (RECOMPILE). 如果不重新编译，运行需要 3-4 分钟，重新编译需要大约 15-20 秒。

我无法更改查询，我已经更新了所有统计信息并重建了所有索引。只有 1 个索引建议，改进了 9.4%。

我努力了：

-- clear all plans in cache
DBCC FREEPROCCACHE

-- Clear Buffer pool
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO

我还能做什么？我无法编辑查询，因此OPTION (RECOMPILE)对我来说不是有效的解决方案。查询可能会不时发生一些变化，因此“计划指南”不起作用？

请注意，这不是实际查询。实际查询由 Dynamics AX 执行，因此使用api_cursor. 我从中撬出了这个sp_cursorprepare并手动填写了参数。

DECLARE @p1 AS BIGINT = 5637144576
DECLARE @p2 AS NVARCHAR(32) = N'1003'
DECLARE @p3 AS NVARCHAR(32) = N'posp%'
DECLARE @p4 AS BIGINT = 5637144576
DECLARE @p5 AS NVARCHAR(32) = N'sv'
DECLARE @p6 AS NVARCHAR(32) = N'posp'
DECLARE @p7 AS BIGINT = 5637144576
DECLARE @p8 AS NVARCHAR(32) = N'1003'
DECLARE @p9 AS NVARCHAR(32) = N'posp'
DECLARE @p10 AS BIGINT = 5637144576
DECLARE @p11 AS NVARCHAR(32) = N'posp'
DECLARE @p12 AS BIGINT = 5637144576
DECLARE @p13 AS NVARCHAR(32) = N'1003'
DECLARE @p14 AS BIGINT = 5637144576
DECLARE @p15 AS NVARCHAR(32) = N''
DECLARE @p16 AS NVARCHAR(32) = N'sv'
DECLARE @p17 AS INT = 0
DECLARE @p18 AS INT = 0
DECLARE @p19 AS INT = 0
DECLARE @p20 AS INT = 0
DECLARE @p21 AS BIGINT = 5637144576
DECLARE @p22 AS NVARCHAR(32) = N'1003'
DECLARE @p23 AS INT = 2
DECLARE @p24 AS BIGINT = 5637144576
DECLARE @p25 AS NVARCHAR(32) = N'1003'
DECLARE @p26 AS BIGINT = 5637144576
DECLARE @p27 AS INT = 1
DECLARE @p28 AS INT = 101


select @p1, @p2, @p5, @p6, @p7

SELECT * 
FROM   ( 
                       SELECT          Row_number() OVER(ORDER BY t2.NAME,t2.itemid) AS rownumber,
                                       t2.itemid                                     AS f1, 
                                       t2.product                                    AS f2, 
                                       t2.NAME                                       AS f3, 
                                       t2.recid                                      AS f4, 
                                       t3.unitid                                     AS f5, 
                                       t3.recid                                      AS f6, 
                                       t4.recid                                      AS f7, 
                                       t5.recid                                      AS f8, 
                                       t6.recid                                      AS f9, 
                                       t7.recid                                      AS f10, 
                                       t7.instancerelationtype                       AS f11 
                       FROM            retailitemname T2 
                       CROSS JOIN      inventtablemodule T3 
                       LEFT OUTER JOIN inventtable T4 
                       ON              ((( 
                                                                                       t4.partition=@P1)
                                                       AND             ( 
                                                                                       t4.dataareaid=@P2))
                                       AND             (( 
                                                                                       t4.itemid LIKE @P3 ESCAPE '\' )
                                                       AND             ( 
                                                                                       t2.itemid=t4.itemid)))
                       LEFT OUTER JOIN ecoresproducttranslation t5 
                       ON              (( 
                                                                       t5.partition=@P4) 
                                       AND             ((( 
                                                                                                       t5.languageid=@P5)
                                                                       AND             ( 
                                                                                                       FREETEXT(t5.NAME,@P6)))
                                                       AND             ( 
                                                                                       t2.product=t5.product)))
                       LEFT OUTER JOIN inventtable t6 
                       ON              ((( 
                                                                                       t6.partition=@P7)
                                                       AND             ( 
                                                                                       t6.dataareaid=@P8))
                                       AND             (( 
                                                                                       FREETEXT(t6.namealias,@P9))
                                                       AND             ( 
                                                                                       t2.itemid=t6.itemid)))
                       LEFT OUTER JOIN ecoresproduct t7 
                       ON              (( 
                                                                       t7.partition=@P10) 
                                       AND             (( 
                                                                                       FREETEXT(t7.searchname,@P11))
                                                       AND             ( 
                                                                                       t2.product=t7.recid)))
                       WHERE           (((( 
                                                                                                       t2.partition=@P12)
                                                                       AND             ( 
                                                                                                       t2.dataareaid=@P13))
                                                       AND             (( 
                                                                                                       t2.partition#2=@P14)
                                                                       OR              ( 
                                                                                                       t2.partition#2 IS NULL)))
                                       AND             ((( 
                                                                                                       t2.languageid=@P15)
                                                                       OR              ( 
                                                                                                       t2.languageid=@P16))
                                                       AND             (((( 
                                                                                                                                       t4.recid<>@P17)
                                                                                                       OR              (
                                                                                                                                       t5.recid<>@P18))
                                                                                       OR              (
                                                                                                                       t6.recid<>@P19))
                                                                       OR              ( 
                                                                                                       t7.recid<>@P20))))
                       AND             ((( 
                                                                                       t3.partition=@P21)
                                                       AND             ( 
                                                                                       t3.dataareaid=@P22))
                                       AND             (( 
                                                                                       t3.moduletype=@P23)
                                                       AND             ( 
                                                                                       t2.itemid=t3.itemid)))
                       AND             EXISTS 
                                       ( 
                                              SELECT 'x' 
                                              FROM   retailitemcategory t8 
                                              WHERE  (((( 
                                                                                 t8.partition=@P24)
                                                                   AND    ( 
                                                                                 t8.dataareaid=@P25))
                                                            AND    ( 
                                                                          t8.partition#2=@P26))
                                                     AND    ( 
                                                                   t2.itemid=t8.itemid))) )t1
WHERE  (( 
                     t1.rownumber>=@P27) 
       AND    ( 
                     t1.rownumber<@P28))
                     OPTION (RECOMPILE)

计划

• 慢速执行计划（3-4 分钟）
• 快速执行计划（15-20 秒）
• MAXDOP 0（1分钟）

再次提醒：请注意这些是我创建的查询的计划，不一定是应用程序实际使用游标时使用的计划。

版本：

微软 SQL Server 2014 - 12.0.2000.8 (X64)
2014 年 2 月 20 日 20:04:26
版权所有 (c) 微软公司
Windows NT 6.1（内部版本 7601：Service Pack 1）（管理程序）上的开发人员版（64 位）

MAXDOP设置为 1，因为它是 AX 2012 (R3 CU10) 数据库。我们不希望任何大报告或作业阻塞所有处理器。不知道您可以在计划中看到更多索引建议。但是慢的只有一个建议而快的有 2.MAXDOP 0速度更快但仍然不如重新编译那么快。

当您通过实时服务在 AX 中搜索渠道数据库中不存在的产品时，AX 查询将运行。此搜索包含在 AX 2012 R3 零售版中。没有定制。

目的是优化 SQL Server 上的进程。该查询完全按照它应该做的去做，但如果不重新编译，它就会太慢。它使用了错误的查询计划。如果它使用正确的查询计划，它会很快。所以应该不需要在 AX 中做任何更改。

optimize for ad hoc workloads被激活。

我有一个不会在生产服务器上运行但在测试服务器上运行的查询。查询计划似乎无法编译或其编译的查询计划非常糟糕。我已经使用全扫描更新了所有统计信息并重建了所有相关表的索引但没有成功，在任何表中都没有那么多行。我无法更改查询，因为它是 AOS (AX 2012) 创建的。我应该怎么做才能按原样使用查询？

询问

DECLARE @P1 AS BIGINT = 5637144576
DECLARE @P2 AS NVARCHAR(4) = N'1003'
DECLARE @P3 AS INT = 212
DECLARE @P4 AS BIGINT = 5638885273
DECLARE @P5 AS BIGINT = 5637144576
DECLARE @P6 AS INT = 865
DECLARE @P7 AS BIGINT = 5637144576
DECLARE @P8 AS BIGINT = 5637144576

SELECT     t1.balance01, 
           t1.recid, 
           t2.amountcur, 
           t2.dataareaid, 
           t2.recid, 
           t3.recid, 
           t3.voucher, 
           t3.accountnum, 
           t3.approved, 
           t3.closed, 
           t3.dataareaid, 
           t4.party, 
           t4.dataareaid, 
           t4.recid 
FROM       spectrans T1 
CROSS JOIN custtransopen T2 
CROSS JOIN custtrans T3 
CROSS JOIN 
           ( 
                      SELECT     virt.id AS dataareaid , 
                                 t4.accountnum, 
                                 t4.party, 
                                 t4.partition, 
                                 t4.recid 
                      FROM       custtable T4 
                      INNER JOIN virtualdataarealist VIRT 
                      ON         t4.dataareaid = virt.virtualdataarea 
                      UNION ALL 
                      SELECT     t4.dataareaid , 
                                 t4.accountnum, 
                                 t4.party, 
                                 t4.partition, 
                                 t4.recid 
                      FROM       custtable T4 
                      INNER JOIN dataarea DAT 
                      ON         ( 
                                            t4.dataareaid = dat.id 
                                 AND        dat.isvirtual = 0)) T4 
WHERE      (( 
                                 t1.partition=@P1) 
           AND        ((( 
                                                       t1.speccompany=@P2) 
                                 AND        ( 
                                                       t1.spectableid=@P3)) 
                      AND        ( 
                                            t1.specrecid=@P4))) 
AND        (( 
                                 t2.partition=@P5) 
           AND        ((( 
                                                       t1.refcompany=t2.dataareaid) 
                                 AND        ( 
                                                       t1.reftableid=@P6)) 
                      AND        ( 
                                            t1.refrecid=t2.recid))) 
AND        (( 
                                 t3.partition=@P7) 
           AND        ( 
                                 t2.refrecid=t3.recid 
                      AND        ( 
                                            t2.dataareaid = t3.dataareaid) 
                      AND        ( 
                                            t2.partition = t3.partition))) 
AND        (( 
                                 t4.partition=@P8) 
           AND        ( 
                                 t3.accountnum=t4.accountnum 
                      AND        ( 
                                            t3.dataareaid = t4.dataareaid) 
                      AND        ( 
                                            t3.partition = t4.partition)))

• 如果我使用来自测试服务器的执行计划，它将在不到一秒的时间内执行。
• 我也试过OPTION (RECOMPILE)了DBCC FREEPROCCACHE
• 没有阻塞

PROD 和 TEST 之间的差异表“sys.configurations”：

PROD    TEST    description
    30  0   Blocked process reporting threshold
    1   0   Enable or disable Database Mail XPs
    1   0   Sets the FILESTREAM access level
    1   2   maximum degree of parallelism
    230000  22528   Maximum size of server memory (MB)
    1   0   Dedicated Admin Connections are allowed from remote clients

环境细节

PROD：Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 版权所有 (c) Microsoft Corporation Enterprise Edition：Windows NT 6.3（Build 9600：）上基于核心的许可（64 位）

TraceFlag   Status  Global  Session
1117    1   1   0
1118    1   1   0
1224    1   1   0
2371    1   1   0
2505    1   1   0
3226    1   1   0
4199    1   1   0

测试：Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) o​​n Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

TraceFlag   Status  Global  Session
1117    1   1   0
1224    1   1   0
2371    1   1   0
2505    1   1   0
3226    1   1   0
4199    1   1   0

兼容级别：两者均为 120。

数据详情

产品

SELECT COUNT(*) FROM SPECTRANS -- 4601
SELECT COUNT(*) FROM CUSTTRANSOPEN -- 14162
SELECT COUNT(*) FROM CUSTTRANS -- 137127
SELECT COUNT(*) FROM CUSTTABLE -- 35617
SELECT COUNT(*) FROM VIRTUALDATAAREALIST -- 3
SELECT COUNT(*) FROM DATAAREA -- 5

预估执行计划： http: //pastebucket.com/326386
统计- http://pastebucket.com/326459

测试

 SELECT COUNT(*) FROM SPECTRANS -- 10753
 SELECT COUNT(*) FROM CUSTTRANSOPEN -- 7150
 SELECT COUNT(*) FROM CUSTTRANS -- 77342
 SELECT COUNT(*) FROM CUSTTABLE -- 36297
 SELECT COUNT(*) FROM VIRTUALDATAAREALIST -- 3
 SELECT COUNT(*) FROM DATAAREA -- 5 

实际执行计划： http: //pastebucket.com/326387
统计- http://pastebucket.com/326458

当我尝试从客户端连接到服务器的 SQL Express 实例时，我遇到了一个问题。服务器记录错误：

SSPI 握手失败，错误代码为 0x8009030c，在建立具有集成安全性的连接时状态为 14；连接已关闭。原因：AcceptSecurityContext 失败。Windows 错误代码指示失败的原因。登录尝试失败
[CLIENT: 192.168.245.50]

我登录的帐户是 sysadmin，并且对它尝试连接的数据库拥有所有可能的权限，但仍然失败。但是，如果我添加试图连接到本地管理员组的用户，它就可以工作。这不是一个好的解决方案，我不希望所有用户都是服务器上的本地管理员。为什么用户需要是本地管理员？

而且我认为 SPN 设置正确。

它都在同一个域中并使用域帐户。Windows 身份验证用于登录。
与 DC 的连接应该没有问题。使用命名实例名称（如果域用户是本地管理员，则它有效）。

从 SQL 错误日志中记录：

03/09/2015 15:22:47，登录，未知，登录失败。登录来自不受信任的域，不能与 Windows 身份验证一起使用。[客户端：192.168.245.50] 03/09/2015 15:22:47，登录，未知，错误：18452 严重性：14 状态：1。03/09/2015 15:22:47，登录，未知，SSPI 握手失败建立具有集成安全性的连接时出现错误代码 0x8009030c 状态 14；连接已关闭。原因：AcceptSecurityContext 失败。Windows 错误代码指示失败的原因。登录尝试失败 [CLIENT: 192.168.245.50] 03/09/2015 15:22:47，登录，未知，错误：17806 严重性：20 状态：14。

从尝试连接的客户端登录：

LSRetailPosis.POS.Program: System.Data.SqlClient.SqlException (0x80131904): 登录失败。登录来自不受信任的域，不能与 Windows 身份验证一起使用。在 System.Data.ProviderBase.DbConnectionPool.TryGetConnection（DbConnection owningObject，UInt32 waitForMultipleObjectsTimeout，Boolean allowCreate，Boolean onlyOneCheckConnection，DbConnectionOptions userOptions，DbConnectionInternal＆connection）在 System.Data.ProviderBase.DbConnectionPool.TryGetConnection（DbConnection owningObject，TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 次重试，DbConnectionOptions userOptions，DbConnectionInternal oldConnection，DbConnectionInternal& 连接）在 System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal（DbConnection outerConnection，DbConnectionFactory connectionFactory，TaskCompletionSource 1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 次重试，DbConnectionOptions userOptions）在 System.Data.SqlClient.SqlConnection.TryOpenInner（TaskCompletionSource）1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 次重试）在 System.Data.SqlClient.SqlConnection.Open() 在 LSRetailPosis.Settings.Database.CheckDatabaseExistence() 在 LSRetailPosis.Settings.Database.CheckDatabaseSettings() 在 LSRetailPosis.Settings.ApplicationSettings.InitializeSettings(String[] args) 在LSRetailPosis.POS.Program.InitializeAndLoadPOS(String[] args, Boolean owned, SplashScreenForm splashScreenForm) 在 LSRetailPosis.POS.Program.Main(String[] args) ClientConnectionId:d57c7fd9-4a47-41b7-af07-ed93627d2930。

当我们对包含大约 15gb 数据的表的聚集索引进行重建并且数据大小缩小到 5gb 时，怎么会这样？删除了什么样的“数据”？

数据大小我的意思是 DBCC sp_spaceused 的“数据”列

在聚集索引上重建之前：

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    39169656 KB 15857960 KB 22916496 KB 395200 KB

---

在聚集索引上重建后：

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    29076736 KB 5867048 KB  22880144 KB 329544 KB

用于重建的 TSQL：

USE [DAX5TEST]
GO
ALTER INDEX [I_212RECID] ON [dbo].[LEDGERJOURNALTRANS] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE, FILLFACTOR = 85 )
GO