AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / user-136766

Learning_DBAdmin's questions

Martin Hope
Learning_DBAdmin
Asked: 2022-02-18 03:43:52 +0800 CST

不可预知的缓慢和表假脱机(懒惰阅读)

  • 0

我在 Stackoverflow 数据库上进行测试,以找出 SQL Server 不推荐在执行计划中使用索引的可能情况,但是如果我们引入一个,它将有很大帮助!

对于 Group by、Order by Clause 和聚合函数(计数函数 - 表的最小副本),它是否很容易。我写了一个随机查询,其中我知道引入支持性索引肯定会有所帮助,但是缺少索引推荐只会在连接条件下而不是在 order by 子句上。

查询如下:

select top 100 Location from Users U join Badges B
on B.UserId = U.Id
order by Location desc

引入了以下索引以提高性能:

create index Location on Users(Location)
go
create index UsersId on Badges(UserId)
go

优化器使用的索引与上述查询的预期一致:

使用索引查询执行计划

逻辑读取和时间统计如下:

使用索引查询统计信息

现在,我想用 Location 列的 Users 表上的索引和 Badges (UserId) 表上的索引来测试性能,这里的性能变得很糟糕(大约需要 7 分钟):

仅在用户表上建立索引

逻辑读取和时间统计如下:

逻辑读取和时间统计

用户表中的索引被大量使用,从执行计划和逻辑读取中可以明显看出,但是执行聚集索引扫描和表假脱机(惰性假脱机)会导致大部分问题。

以上测试均在 SQL Server 2019 上以 SQL Server 2016 兼容模式(130)进行。

如果有人可以就潜在问题提出建议,那将有很大帮助。

这里还要注意一点,当这两个表中的任何一个都没有非聚集支持索引时,相同的查询会在 9 秒内完成。下面是执行计划:

没有 NC 索引的执行计划

逻辑读取和时间统计:

没有 NC 索引的逻辑读取和时间统计

出于测试目的,我将兼容性级别更改为 2019(150),令我惊讶的是 - 之前的查询仅在用户(位置)表上而不在徽章表上具有索引,在 2 秒内完成,这在 SQL Server 2016 中需要 7 分钟兼容性(130)模式:

SQL Server 2019 执行计划

逻辑统计和时间统计:

逻辑统计和时间统计

在 2019 兼容模式下,Parallelism 之前的所有算子都是批处理模式。

在这方面的任何输入都将帮助我理解这种行为。

sql-server sql-server-2019
  • 1 个回答
  • 173 Views
Martin Hope
Learning_DBAdmin
Asked: 2020-12-02 00:04:09 +0800 CST

需要关于 Indexaphobia 的建议:具有高影响的高价值缺失指数。当索引已经存在时

  • 0

当我已经有索引以及如何解决它时,我试图理解这个特定表的缺失索引的真正含义。

我有一张经常使用的桌子,大约 2.5GB。由于它被大量使用,有点犹豫要创建不是非常需要的索引(值得商榷)。该表之前是堆的,最近将主键从非集群更改为集群后更改为表。

当我使用数据库名称或此表运行 sp_blitzindex 时,结果如下:

Blitzindex 的结果

大多数情况下,它建议在 APT_ID 列上创建索引,并包括建议 LOGID、RECEIVE_TIME 和其他一些列。如果我们看表定义,主键定义在 LOGID 和 RECEIVE_TIME。而且我们在 APT_ID 列上有一个 NC 索引。

表 DDL 如下:

CREATE TABLE [dbo].[TXN_LOG](
    [LOGID] [int] IDENTITY(1,1) NOT NULL,
    [RECEIVE_TIME] [varchar](15) NOT NULL,
    [APT_ID] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    .
    .
    .
    [ERROR_CODE] [varchar](20) NULL,
    [MESSAGE_ID] [varchar](40) NULL,
    [END_POINT_ID] [varchar](50) NULL,
    [NODE_ID] [varchar](40) NULL,
    [TIMEOUT_NETWORK_ID] [int] NULL,
    [TXN_SUMMARY] [numeric](1, 0) NULL,
    [D_FLAG] [numeric](1, 0) NULL,
     CONSTRAINT [PKTXN_LOG] PRIMARY KEY CLUSTERED 
    (
        [LOGID] ASC,
        [RECEIVE_TIME] ASC
    ))
GO

此表上的 NC 索引为:

CREATE NONCLUSTERED INDEX [IDX_TXN_LOG_1] ON [dbo].[TXN_LOG]
(
    [APT_ID] ASC
)
INCLUDE([RECEIVE_TIME]) 
GO

当前使用的索引似乎不支持 NC 索引,因为写入次数高于读取次数。

索引的使用

对于在问题中附加图像表示诚挚的歉意。感谢我能否就此获得一些专家建议。提前致谢。

版本:Microsoft SQL Server 2014 (SP3-GDR) (KB4532095) - 12.0.6118.4 (X64) 2019 年 12 月 12 日 21:46:15 版权所有 (c) Microsoft Corporation Enterprise Edition:Windows NT 上基于内核的许可(64 位) 6.3(内部版本 9600:)(管理程序)

sql-server index
  • 2 个回答
  • 362 Views
Martin Hope
Learning_DBAdmin
Asked: 2020-11-03 23:42:11 +0800 CST

在堆上引入新的非聚集索引后面临死锁

  • 1

我面临一个奇怪的问题,其中我分析了一个每 3 分钟运行一次的存储过程,并且给 CPU 带来了负载。我发现有许多选择语句是此过程的一部分,并且它们都在进行全表扫描(读取表中的所有页面)。因此,我在测试环境中对它们进行了测试,并使用非聚集索引来支持它。相关供应商也确认了这一点,他们同意进行更改。我昨天将它们部署到生产环境并检查了这些查询的逻辑读取,并在新索引之后进行了交叉检查,验证它具有积极影响并且逻辑读取下降了 1/10。

部署此索引后,每 3 分钟运行一次的程序立即开始失败。手动执行以检查问题,发现它每次都会导致死锁,除了像 1 小时内的 1 或 2 次。我完全不知道为什么索引会导致死锁?理想情况下,索引应该解决死锁,但它恰恰相反。

我指的表是一个堆,没有聚集主键,而是有非聚集主键。我已得到各方同意从 NC 更改为集群,但是它通过 PK-FK 关系与多个表链接,并且需要停机时间,因此它目前处于暂停状态。

我已经捕获了死锁图,并且还设置了 sp_blitzlock。应用程序查询和这个过程之间似乎发生了死锁,但是我不明白这个索引是如何导致它的,以及当我回滚这个索引时,它工作顺利并且没有死锁。

死锁图如下:

Sentry 中的死锁图

<deadlock>
  <victim-list>
    <victimProcess id="processef645b848" />
  </victim-list>
  <process-list>
    <process id="processef645b848" taskpriority="0" logused="0" waitresource="PAGE: 6:1:965 " waittime="3661" ownerId="303318514" transactionname="INSERT" lasttranstarted="2020-11-02T12:18:12.793" XDES="0x31fbb78e0" lockMode="S" schedulerid="1" kpid="8564" status="suspended" spid="1246" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-02T12:18:00.500" lastbatchcompleted="2020-11-02T12:18:00.500" lastattention="1900-01-01T00:00:00.500" clientapp="SQLAgent - TSQL JobStep (Job 0x417A2365E91D1647B2C225CA23D84860 : Step 1)" hostname="DB_Server" hostpid="3628" loginname="SQL_Agent_Login" isolationlevel="read committed (2)" xactid="303318514" currentdb="7" currentdbname="DB_Name_1" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtend="2888" sqlhandle="0x02000000c984e814ec51be0d03e3852ee0b755da518273d00000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="DB_Name_1.dbo.Procedure_Name" line="171" stmtstart="13412" stmtend="13482" sqlhandle="0x03000700151ddc58cd73c00013ac000001000000000000000000000000000000000000000000000000000000">
EXECUTE (@EXEC_IMMEDIATE_VAR)    </frame>
        <frame procname="adhoc" line="1" sqlhandle="0x01000700d070832b90a421810300000000000000000000000000000000000000000000000000000000000000">
EXEC Procedure_Name 'DB_User_Application'    </frame>
      </executionStack>
      <inputbuf>
EXEC Procedure_Name 'DB_User_Application'   </inputbuf>
    </process>
    <process id="processe1435468" taskpriority="0" logused="996" waitresource="PAGE: 6:1:88348 " waittime="3841" ownerId="303318578" transactionname="user_transaction" lasttranstarted="2020-11-02T12:18:13.007" XDES="0x255f023b0" lockMode="IX" schedulerid="1" kpid="4120" status="suspended" spid="1271" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-11-02T12:18:13.017" lastbatchcompleted="2020-11-02T12:18:13.007" lastattention="1900-01-01T00:00:00.007" clientapp="Vendor_Name" hostname="APP_Server_Name" hostpid="1296" loginname="DB_User_Application" isolationlevel="read committed (2)" xactid="303318578" currentdb="6" currentdbname="DB_Name_2" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="720" stmtend="1560" sqlhandle="0x020000001fdba70fc3e8a833920a732fe1c19b282e28ac1c0000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="adhoc" line="1" stmtend="1190" sqlhandle="0x02000000f228391fab59e520bc237b6a919f53ced0b1ac290000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
update Deadlock_Table set Error_Code = '000',TIMEOUT_NETWORK_ID=NULL , var32_32='XXXX', var32_15='000', var32_22='1', var32_04='IB', var32_05='XXX', var256_01='Processed OK', var32_09='XXX', var32_14='048', var64_01='XXXXX', var32_06='02112020121802', var32_03='XXX', var32_10='XXXX', var32_07='XXXX', var32_02='XXX', var32_01='XXX', Node_Id='APP_Server_Name', Message_Id='XXXX', End_Point_Id='XXXXX' where Log_Id=XXXXX and Receive_Time='XXXXX'   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="965" dbid="6" subresource="FULL" objectname="DB_Name_2.dbo.Deadlock_Table" id="lock4d151f900" mode="IX" associatedObjectId="72057594079739904">
      <owner-list>
        <owner id="processe1435468" mode="IX" />
      </owner-list>
      <waiter-list>
        <waiter id="processef645b848" mode="S" requestType="wait" />
      </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="88348" dbid="6" subresource="FULL" objectname="DB_Name_2.dbo.Deadlock_Table" id="lock4e1da7d00" mode="S" associatedObjectId="72057594079739904">
      <owner-list>
        <owner id="processef645b848" mode="S" />
      </owner-list>
      <waiter-list>
        <waiter id="processe1435468" mode="IX" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>

下面是表的 DDL:

CREATE TABLE [dbo].[Deadlock_Table](
    [Log_id] [int] IDENTITY(1,1) NOT NULL,
    [Receive_time] [varchar](15) NOT NULL,
    [A] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    [VAR32_03] [varchar](32) NULL,
    [VAR32_04] [varchar](32) NULL,
    [VAR32_05] [varchar](32) NULL,
    [VAR32_06] [varchar](32) NULL,
    [VAR32_07] [varchar](32) NULL,
    [VAR32_08] [varchar](32) NULL,
    [VAR32_09] [varchar](32) NULL,
    [VAR32_10] [varchar](32) NULL,
    [VAR32_11] [varchar](32) NULL,
    [VAR32_12] [varchar](32) NULL,
    [VAR32_13] [varchar](32) NULL,
    [VAR32_14] [varchar](32) NULL,
    [VAR32_15] [varchar](32) NULL,
    [VAR32_16] [varchar](32) NULL,
    [VAR32_17] [varchar](32) NULL,
    [VAR32_18] [varchar](32) NULL,
    [VAR32_19] [varchar](32) NULL,
    [VAR32_20] [varchar](32) NULL,
    [VAR32_21] [varchar](32) NULL,
    [VAR32_22] [varchar](32) NULL,
    [VAR32_23] [varchar](32) NULL,
    [VAR32_24] [varchar](32) NULL,
    [VAR32_25] [varchar](32) NULL,
    [VAR32_26] [varchar](32) NULL,
    [VAR32_27] [varchar](32) NULL,
    [VAR32_28] [varchar](32) NULL,
    [VAR32_29] [varchar](32) NULL,
    [VAR32_30] [varchar](32) NULL,
    [VAR32_31] [varchar](32) NULL,
    [VAR32_32] [varchar](32) NULL,
    [VAR32_33] [varchar](32) NULL,
    [VAR32_34] [varchar](32) NULL,
    [VAR32_35] [varchar](32) NULL,
    [VAR32_36] [varchar](32) NULL,
    [VAR32_37] [varchar](32) NULL,
    [VAR32_38] [varchar](32) NULL,
    [VAR32_39] [varchar](32) NULL,
    [VAR32_40] [varchar](32) NULL,
    [VAR32_41] [varchar](32) NULL,
    [VAR32_42] [varchar](32) NULL,
    [VAR32_43] [varchar](32) NULL,
    [VAR32_44] [varchar](32) NULL,
    [VAR32_45] [varchar](32) NULL,
    [VAR32_46] [varchar](32) NULL,
    [VAR32_47] [varchar](32) NULL,
    [VAR32_48] [varchar](32) NULL,
    [VAR32_49] [varchar](32) NULL,
    [VAR32_50] [varchar](32) NULL,
    [VAR32_51] [varchar](32) NULL,
    [VAR32_52] [varchar](32) NULL,
    [VAR32_53] [varchar](32) NULL,
    [VAR32_54] [varchar](32) NULL,
    [VAR32_55] [varchar](32) NULL,
    [VAR32_56] [varchar](32) NULL,
    [VAR32_57] [varchar](32) NULL,
    [VAR32_58] [varchar](32) NULL,
    [VAR32_59] [varchar](32) NULL,
    [VAR32_60] [varchar](32) NULL,
    [VAR32_61] [varchar](32) NULL,
    [VAR32_62] [varchar](32) NULL,
    [VAR32_63] [varchar](32) NULL,
    [VAR32_64] [varchar](32) NULL,
    [VAR64_01] [varchar](64) NULL,
    [VAR64_02] [varchar](64) NULL,
    [VAR64_03] [varchar](64) NULL,
    [VAR64_04] [varchar](64) NULL,
    [VAR64_05] [varchar](64) NULL,
    [VAR64_06] [varchar](64) NULL,
    [VAR64_07] [varchar](64) NULL,
    [VAR64_08] [varchar](64) NULL,
    [VAR64_09] [varchar](64) NULL,
    [VAR64_10] [varchar](64) NULL,
    [VAR64_11] [varchar](64) NULL,
    [VAR64_12] [varchar](64) NULL,
    [VAR64_13] [varchar](64) NULL,
    [VAR64_14] [varchar](64) NULL,
    [VAR64_15] [varchar](64) NULL,
    [VAR64_16] [varchar](64) NULL,
    [VAR64_17] [varchar](64) NULL,
    [VAR64_18] [varchar](64) NULL,
    [VAR64_19] [varchar](64) NULL,
    [VAR64_20] [varchar](64) NULL,
    [VAR64_21] [varchar](64) NULL,
    [VAR64_22] [varchar](64) NULL,
    [VAR64_23] [varchar](64) NULL,
    [VAR64_24] [varchar](64) NULL,
    [VAR64_25] [varchar](64) NULL,
    [VAR64_26] [varchar](64) NULL,
    [VAR64_27] [varchar](64) NULL,
    [VAR64_28] [varchar](64) NULL,
    [VAR64_29] [varchar](64) NULL,
    [VAR64_30] [varchar](64) NULL,
    [VAR64_31] [varchar](64) NULL,
    [VAR64_32] [varchar](64) NULL,
    [VAR128_01] [varchar](128) NULL,
    [VAR128_02] [varchar](128) NULL,
    [VAR128_03] [varchar](128) NULL,
    [VAR128_04] [varchar](128) NULL,
    [VAR128_05] [varchar](128) NULL,
    [VAR128_06] [varchar](128) NULL,
    [VAR128_07] [varchar](128) NULL,
    [VAR128_08] [varchar](128) NULL,
    [VAR128_09] [varchar](128) NULL,
    [VAR128_10] [varchar](128) NULL,
    [VAR128_11] [varchar](128) NULL,
    [VAR128_12] [varchar](128) NULL,
    [VAR128_13] [varchar](128) NULL,
    [VAR128_14] [varchar](128) NULL,
    [VAR128_15] [varchar](128) NULL,
    [VAR128_16] [varchar](128) NULL,
    [VAR256_01] [varchar](256) NULL,
    [VAR256_02] [varchar](256) NULL,
    [VAR256_03] [varchar](256) NULL,
    [VAR256_04] [varchar](256) NULL,
    [VAR256_05] [varchar](256) NULL,
    [VAR256_06] [varchar](256) NULL,
    [VAR256_07] [varchar](256) NULL,
    [VAR256_08] [varchar](256) NULL,
    [VAR512_01] [varchar](512) NULL,
    [VAR512_02] [varchar](512) NULL,
    [VAR512_03] [varchar](512) NULL,
    [VAR512_04] [varchar](512) NULL,
    [VAR1024_01] [varchar](1024) NULL,
    [VAR1024_02] [varchar](1024) NULL,
    [E] [varchar](20) NULL,
    [M] [varchar](40) NULL,
    [E] [varchar](50) NULL,
    [N] [varchar](40) NULL,
    [TN] [int] NULL,
    [T] [numeric](1, 0) NULL,
    [D] [numeric](1, 0) NULL,
 CONSTRAINT [XPKtable_name] PRIMARY KEY NONCLUSTERED 
(
    [Log_id] ASC,
    [Receive_time] ASC
)

目前,除了第三列(比如 A)的主键之外,它只有一个索引。

我提出的索引如下:

CREATE NONCLUSTERED INDEX [IX_Deadlock_Table] ON [dbo].[Deadlock_Table]
(
 var32_02 ,
 D,
 T
 ) 
GO

感谢您阅读如此冗长的问题,非常感谢您在解决此僵局方面的投入。万一我错过了一些重要的细节,请将它们放在评论部分,我会添加它们。

更新语句的应用查询计划 --> https://www.brentozar.com/pastetheplan/?id=r14x3TCOD

在游标内运行的查询是多个插入语句,它们基本上是涉及该死锁表与其他表连接的选择语句,它们如下:

INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,null,null,null,var32_07,null,var32_02,var32_01,var32_20,Coalesce(null,var32_10,var32_11,var32_21),var32_21,null,null,Coalesce(var32_31,var32_12,var32_13),null,null,null,null,null,convert(varchar(2),N),null,1,1,null,null,null,null,null,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,var32_17,var32_19,var32_18,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =4 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var32_02 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 4) AND var32_02 NOT IN ('0044')
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,var64_12,var64_02,var32_42,var32_20,var32_45,null,null,null,null,convert(varchar(2),N),var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,NULL,NULL,NULL,NULL,var64_22,var32_30,var64_23,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =10 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 10)
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,var64_12,var64_02,var32_42,var32_20,var32_45,null,null,null,null,var32_62,var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,var1024_02,NULL,NULL,NULL,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =11 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 11)
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,var32_25,var32_10,var32_04,var32_24,null,var64_19,var32_14,var32_18,var64_11,      case when var64_19 = '64' then var64_10 else var64_12 end as TO_ACCOUNT_NUM, var64_02,var32_42,var32_20,var32_45,null,null,null,null,var32_62,var32_35,1,1,null,var32_43,var32_38,var32_39,null,var32_27,NULL,NULL,var32_19,NULL,NULL,var32_22,var1024_02,var64_17,var64_21,var64_20,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =12 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1))       AND (var64_19 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 12)      or  var64_19 in (SELECT B.CODE_NAME FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 12))
INSERT INTO Table_Name   (SELECT LOG_ID, RECEIVE_TIME, N, E, CASE WHEN TRAN_SUMMARY = 1 AND DIRTY_FLAG = 1 THEN 1 ELSE 0 END,null,null,null,var32_44,null,var32_02,var32_46,null,null,null,null,null,null,null,null,null,null,null,convert(varchar(2),N),null,1,1,null,null,null,null,null,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'system','system' FROM deadlock_table A, ADPTR       B WHERE A.ADPTR_ID = B.ADPTR_ID AND       B.N =13 AND (A.TRAN_SUMMARY = 0  OR (A.TRAN_SUMMARY = 1 AND A.DIRTY_FLAG = 1)) AND var32_02 IN (SELECT B.CODE_ID FROM TRN_CODE A , GETCODEMAPPING B WHERE A.TRAN_CODE = B.CODE_NAME AND B.N  = 13)

没有我的索引的第一个选择语句的查询计划(以其当前形式) - https://www.brentozar.com/pastetheplan/?id=BkDnpbktw

创建索引后选择语句的查询计划 - https://www.brentozar.com/pastetheplan/?id=r1SCeGktD

以下是索引创建前后的逻辑读取:

Table 'deadlock_table'. Scan count 5, logical reads 326203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'deadlock_table'. Scan count 58, logical reads 12055, physical reads 0, read-ahead reads 23, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL 版本

版本:Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition:Windows NT 6.3 (Build 9600:) 上基于内核的许可(64 位)(管理程序) )

sql-server deadlock
  • 1 个回答
  • 395 Views
Martin Hope
Learning_DBAdmin
Asked: 2020-02-03 23:13:33 +0800 CST

begin tran 而不是在 SQL 作业中使用 db_name 来执行存储过程

  • 2

我昨天定义了一个 SQL 作业,用于在早上的特定时间执行存储过程。这是一个临时且紧急的要求,因此我即时开发了这个程序,并安排它每天早上运行。

我应该只是在作业步骤中调用该过程并在高级选项卡中定义输出文件。我必须在作业步骤中使用以下命令:

use db_name
go
exec sp_name
go

而不是上面,我错误地写了如下:

begin tran
go
exec sp_name
go

这个 sql 作业没有失败,但是它没有完成它的预期工作。

输出文件包含以下内容:

作业“Job_Name”:第 1 步,“Step_name”:开始执行 2020-02-03 06:10:00

这似乎是正确的,但我想知道上面可以保持一个开放的交易并可能导致另一个问题。

我执行了sp_whoisactive(感谢 Adam Machanic 先生)并且没有看到从那时起运行的任何会话,并且在任何表上都没有阻塞,因为基础过程在将数据插入表时有 tablock 表提示。

有没有办法找到这个 sql 作业实际发生了什么,为什么它没有完成它的预期工作,以及是否有办法找到它是否仍在后台运行并且可以提交或回滚。我还运行 sp_who2 以查看是否有任何与此作业相关的 SPID,但找不到任何东西。

运行以下查询以检查所有作业的状态,它显示我的作业成功:

USE MSDB
SELECT name AS [Job Name]
         ,CONVERT(VARCHAR,MAX(DATEADD(S,(run_time/10000)*60*60 /* hours */  
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113))),100) AS [Time Run]
         ,CASE WHEN enabled=1 THEN 'Enabled'  
               ELSE 'Disabled'  
          END [Job Status]
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'  
          END [Job Outcome]
FROM   sysjobhistory SJH  
JOIN   sysjobs SJ  
ON     SJH.job_id=sj.job_id  
WHERE  step_id=0  
AND    DATEADD(S,  
  (run_time/10000)*60*60 /* hours */  
  +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
  + (run_time - (run_time/100) * 100)  /* secs */,  
  CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-20,GetDate())  
  group by name, CASE WHEN enabled=1 THEN 'Enabled'  
               ELSE 'Disabled'  
          END 
         ,CASE WHEN SJH.run_status=0 THEN 'Failed'
                     WHEN SJH.run_status=1 THEN 'Succeeded'
                     WHEN SJH.run_status=2 THEN 'Retry'
                     WHEN SJH.run_status=3 THEN 'Cancelled'
               ELSE 'Unknown'  
          END 

非常感谢任何帮助或输入。

版本:Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1 (X64) 2019 年 5 月 29 日 20:05:27 版权所有 (c) Microsoft Corporation Enterprise Edition:Windows NT 上基于内核的许可(64 位) 6.3(内部版本 9600:)(管理程序)

sql-server jobs
  • 1 个回答
  • 209 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-07-09 04:59:28 +0800 CST

SQL Server 的 Log 文件夹中的 HKEngineEventFile

  • 1

如本问题所述,我们的数据库中存在不一致问题。现在已修复相同的问题,并且数据库正在运行,没有任何问题。

我注意到 SQL 服务在过去 3 天内多次重启(根据错误日志),这次重启只用了 1 到 2 秒,令我惊讶的是,没有人注意到这一点,而这发生在工作时间。这是一个生产数据库并且被大量使用,这意味着每秒有 1-3 个事务命中数据库。

我记录了重新启动服务的时间或最近几天,并注意到同时(每次)都会创建一个名为“ HkEngineEventFile_0_XXXX ”的文件,大小为 68KB。试图用不同的工具打开它,但没有得到任何信息。

HKEngineEvent_0_XXX

有人可以帮助我理解这一点,而且我还需要了解这是否是服务的真正重启或其他原因。

以下是 SQL 错误日志中的条目:

2019-07-07 16:51:22.32 Server      Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) 
    Sep  7 2018 01:37:51 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

2019-07-07 16:51:22.32 Server      UTC adjustment: 3:00
2019-07-07 16:51:22.32 Server      (c) Microsoft Corporation.
2019-07-07 16:51:22.32 Server      All rights reserved.
2019-07-07 16:51:22.32 Server      Server process ID is 7916.
2019-07-07 16:51:22.32 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2019-07-07 16:51:22.32 Server      Authentication mode is MIXED.
2019-07-07 16:51:22.32 Server      Logging SQL Server messages in file 'E:\DB_SQL12.ABC\DB_SQL\Log\ERRORLOG'.
2019-07-07 16:51:22.32 Server      The service account is 'Organization_Name\Service_Account'. This is an informational message; no user action is required.
2019-07-07 16:51:22.32 Server      Registry startup parameters: 
     -d E:\DB_SQL12.ABC\DB_SQL\DATA\master.mdf
     -e E:\DB_SQL12.ABC\DB_SQL\Log\ERRORLOG
     -l E:\DB_SQL12.ABC\DB_SQL\DATA\mastlog.ldf
2019-07-07 16:51:22.32 Server      Command Line Startup Parameters:
     -s "ABC"
2019-07-07 16:51:22.55 Server      SQL Server detected 2 sockets with 4 cores per socket and 4 logical processors per socket, 8 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2019-07-07 16:51:22.55 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2019-07-07 16:51:22.55 Server      Detected 24575 MB of RAM. This is an informational message; no user action is required.
2019-07-07 16:51:22.55 Server      Using conventional memory in the memory manager.
2019-07-07 16:51:22.72 Server      Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2019-07-07 16:51:22.77 Server      The maximum number of dedicated administrator connections for this instance is '1'
2019-07-07 16:51:22.78 Server      This instance of SQL Server last reported using a process ID of 6672 at 7/7/2019 4:50:49 PM (local) 7/7/2019 1:50:49 PM (UTC). This is an informational message only; no user action is required.
2019-07-07 16:51:22.78 Server      Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2019-07-07 16:51:22.80 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2019-07-07 16:51:22.80 Server      Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2019-07-07 16:51:22.83 spid7s      Starting up database 'master'.
2019-07-07 16:51:22.88 Server      CLR version v4.0.30319 loaded.
2019-07-07 16:51:22.89 spid7s      14 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2019-07-07 16:51:22.90 spid7s      0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2019-07-07 16:51:22.90 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2019-07-07 16:51:22.96 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2019-07-07 16:51:23.01 spid7s      CHECKDB for database 'master' finished without errors on 2019-07-05 21:45:01.463 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:23.01 spid7s      Resource governor reconfiguration succeeded.
2019-07-07 16:51:23.01 spid7s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2019-07-07 16:51:23.01 spid7s      Audit: Server Audit: 65536, Initialized and Assigned State: START_FAILED
2019-07-07 16:51:23.01 spid7s      Audit: Server Audit: 65536, Initialized and Assigned State: STARTED
2019-07-07 16:51:23.01 spid7s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2019-07-07 16:51:23.03 spid7s      SQL Trace ID 1 was started by login "sa".
2019-07-07 16:51:23.03 spid7s      Server name is 'Organization_Name-Service_Account\ABC'. This is an informational message only. No user action is required.
2019-07-07 16:51:23.03 spid7s      The NETBIOS name of the local node that is running the server is 'Organization_Name-MWDB01'. This is an informational message only. No user action is required.
2019-07-07 16:51:23.06 spid15s     A self-generated certificate was successfully loaded for encryption.
2019-07-07 16:51:23.09 spid15s     Server is listening on [ 192.168.100.170 <ipv4> 1433].
2019-07-07 16:51:23.09 spid15s     Started listening on virtual network name 'Organization_Name-Service_Account'. No user action is required.
2019-07-07 16:51:23.09 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\ABC ].
2019-07-07 16:51:23.09 spid15s     Server named pipe provider is ready to accept connection on [ \\.\pipe\$$\Organization_Name-Service_Account\DB_SQL$ABC\sql\query ].
2019-07-07 16:51:23.10 spid15s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2019-07-07 16:51:23.10 Server      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.
2019-07-07 16:51:23.18 Logon       Error: 18456, Severity: 14, State: 38.
2019-07-07 16:51:23.18 Logon       Login failed for user 'ABCuser'. Reason: Failed to open the explicitly specified database 'Organization_Name_DB2'. [CLIENT: 192.168.100.151]
2019-07-07 16:51:23.23 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ DB_SQLSvc/Organization_Name-Service_Account.Organization_Name.COM:ABC ] for the SQL Server service. Windows return code: 0x2098, state: 20. 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.
2019-07-07 16:51:23.23 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ DB_SQLSvc/Organization_Name-Service_Account.Organization_Name.COM:1433 ] for the SQL Server service. Windows return code: 0x2098, state: 20. 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.
2019-07-07 16:51:23.40 Logon       Error: 18456, Severity: 14, State: 38.
2019-07-07 16:51:23.40 Logon       Login failed for user 'DB2user'. Reason: Failed to open the explicitly specified database 'Organization_Name_DB2'. [CLIENT: 192.168.100.151]
2019-07-07 16:51:23.45 spid16s     A new instance of the full-text filter daemon host process has been successfully started.
2019-07-07 16:51:23.49 spid19s     Starting up database 'msdb'.
2019-07-07 16:51:23.49 spid20s     Starting up database 'Organization_Name_DB1'.
2019-07-07 16:51:23.49 spid22s     Starting up database 'Organization_Name_DB2'.
2019-07-07 16:51:23.50 spid21s     Starting up database 'Organization_Name_ABC'.
2019-07-07 16:51:23.50 spid23s     Starting up database 'DB3'.
2019-07-07 16:51:23.50 spid24s     Starting up database 'DBA_Maintenance'.
2019-07-07 16:51:23.50 spid10s     Starting up database 'DB_SQLsystemresource'.
2019-07-07 16:51:23.50 spid25s     Starting up database 'DB4'.
2019-07-07 16:51:23.53 spid10s     The resource database build version is 12.00.6024. This is an informational message only. No user action is required.
2019-07-07 16:51:23.60 spid25s     2 transactions rolled forward in database 'DB4' (10:0). This is an informational message only. No user action is required.
2019-07-07 16:51:23.72 spid7s      0 transactions rolled back in database 'DB4' (10:0). This is an informational message only. No user action is required.
2019-07-07 16:51:23.72 spid10s     Starting up database 'model'.
2019-07-07 16:51:23.75 spid24s     47 transactions rolled forward in database 'DBA_Maintenance' (9:0). This is an informational message only. No user action is required.
2019-07-07 16:51:23.81 spid20s     1001 transactions rolled forward in database 'Organization_Name_DB1' (5:0). This is an informational message only. No user action is required.
2019-07-07 16:51:23.93 spid7s      0 transactions rolled back in database 'DBA_Maintenance' (9:0). This is an informational message only. No user action is required.
2019-07-07 16:51:23.93 spid7s      Recovery is writing a checkpoint in database 'DBA_Maintenance' (9). This is an informational message only. No user action is required.
2019-07-07 16:51:23.97 spid19s     1934 transactions rolled forward in database 'msdb' (4:0). This is an informational message only. No user action is required.
2019-07-07 16:51:24.06 Server      Software Usage Metrics is disabled.
2019-07-07 16:51:24.08 spid10s     CHECKDB for database 'model' finished without errors on 2019-07-05 21:45:02.243 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:24.08 spid10s     Clearing tempdb database.
2019-07-07 16:51:24.14 spid7s      0 transactions rolled back in database 'Organization_Name_DB1' (5:0). This is an informational message only. No user action is required.
2019-07-07 16:51:24.14 spid7s      Recovery is writing a checkpoint in database 'Organization_Name_DB1' (5). This is an informational message only. No user action is required.
2019-07-07 16:51:24.24 spid10s     Starting up database 'tempdb'.
2019-07-07 16:51:24.25 spid7s      0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required.
2019-07-07 16:51:24.25 spid7s      Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2019-07-07 16:51:24.36 spid10s     The tempdb database has 1 data file(s).
2019-07-07 16:51:24.36 spid30s     The Service Broker endpoint is in disabled or stopped state.
2019-07-07 16:51:24.36 spid30s     The Database Mirroring endpoint is in disabled or stopped state.
2019-07-07 16:51:24.36 spid19s     CHECKDB for database 'msdb' finished without errors on 2019-07-05 21:45:02.820 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:24.37 spid30s     Service Broker manager has started.
2019-07-07 16:51:24.40 spid25s     CHECKDB for database 'DB4' finished without errors on 2019-07-03 23:30:07.940 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:24.40 spid24s     CHECKDB for database 'DBA_Maintenance' finished without errors on 2019-07-04 07:58:21.313 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:24.43 spid20s     CHECKDB for database 'Organization_Name_DB1' finished without errors on 2019-07-03 23:30:44.193 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:24.55 spid52      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2019-07-07 16:51:24.55 spid52      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2019-07-07 16:51:24.56 spid52      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2019-07-07 16:51:24.81 spid23s     1391 transactions rolled forward in database 'DB3' (8:0). This is an informational message only. No user action is required.
2019-07-07 16:51:24.82 spid52      Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2019-07-07 16:51:24.82 spid52      Using 'xpsqlbot.dll' version '2014.120.6024' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2019-07-07 16:51:24.90 spid52      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2019-07-07 16:51:24.91 spid52      Using 'xpstar.dll' version '2014.120.6024' to execute extended stored procedure 'xp_sqlagent_notify'. This is an informational message only; no user action is required.
2019-07-07 16:51:24.92 spid7s      0 transactions rolled back in database 'DB3' (8:0). This is an informational message only. No user action is required.
2019-07-07 16:51:24.92 spid7s      Recovery is writing a checkpoint in database 'DB3' (8). This is an informational message only. No user action is required.
2019-07-07 16:51:24.94 spid23s     CHECKDB for database 'DB3' finished without errors on 2019-07-03 23:41:10.877 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:25.04 spid22s     Recovery of database 'Organization_Name_DB2' (6) is 0% complete (approximately 313 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2019-07-07 16:51:25.05 spid22s     Recovery of database 'Organization_Name_DB2' (6) is 0% complete (approximately 310 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2019-07-07 16:51:25.29 spid22s     5868 transactions rolled forward in database 'Organization_Name_DB2' (6:0). This is an informational message only. No user action is required.
2019-07-07 16:51:25.42 spid21s     82 transactions rolled forward in database 'Organization_Name_ABC' (7:0). This is an informational message only. No user action is required.
2019-07-07 16:51:25.49 spid7s      0 transactions rolled back in database 'Organization_Name_DB2' (6:0). This is an informational message only. No user action is required.
2019-07-07 16:51:25.49 spid7s      Recovery is writing a checkpoint in database 'Organization_Name_DB2' (6). This is an informational message only. No user action is required.
2019-07-07 16:51:25.51 spid22s     CHECKDB for database 'Organization_Name_DB2' finished without errors on 2019-07-03 23:30:45.717 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:25.62 spid7s      0 transactions rolled back in database 'Organization_Name_ABC' (7:0). This is an informational message only. No user action is required.
2019-07-07 16:51:25.62 spid7s      Recovery is writing a checkpoint in database 'Organization_Name_ABC' (7). This is an informational message only. No user action is required.
2019-07-07 16:51:25.64 spid21s     CHECKDB for database 'Organization_Name_ABC' finished without errors on 2019-07-03 03:31:59.657 (local time). This is an informational message only; no user action is required.
2019-07-07 16:51:25.64 spid7s      Error: 8355, Severity: 16, State: 1.
2019-07-07 16:51:25.64 spid7s      Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.
2019-07-07 16:51:25.64 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2019-07-07 16:54:38.88 spid57      ex_dump_if_requested: Exception raised, major=52, minor=42, state=9, severity=22, attempting to create symptom dump
2019-07-07 16:54:38.88 spid57      Using 'dbghelp.dll' version '4.0.5'
2019-07-07 16:54:38.89 spid57      **Dump thread - spid = 0, EC = 0x00000004D7695F10

版本:Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 版权所有 (c) Microsoft Corporation Enterprise Edition:Windows NT 6.3 上基于内核的许可(64 位)(内部版本 9600:)(管理程序)

sql-server event
  • 1 个回答
  • 1321 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-07-09 04:30:54 +0800 CST

SQL 日志文件夹中的 SQLDIAG 文件

  • 0

我们的数据库中几乎没有不一致的问题,这是由于数据库服务器(WSFC)的故障转移不成功而发生的,在这个问题中也提出了同样的问题,昨晚在删除并重新创建主键并将数据移动到另一个表后得到了解决。

在修复一致性错误之前创建了许多 SQLDump 文件,它不再被创建,但是我在日志文件夹中看到很少的 SQLDIAG 文件,使用 SSMS 打开相同的文件,它是一组带有名称和时间戳的事件,某处 state_desc 看起来很干净而某处未知或警告。

ServerName_InstanceName_SQLDIAG

事件名称和时间戳

有人可以帮助我理解这一点,如果它是一个需要解决的问题吗?

版本:Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 版权所有 (c) Microsoft Corporation Enterprise Edition:Windows NT 6.3 上基于内核的许可(64 位)(内部版本 9600:)(管理程序)

sql-server clustering
  • 1 个回答
  • 1326 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-07-04 23:33:02 +0800 CST

Checkdb 问题 - 关键数据库中两个表的一致性错误

  • 2

昨晚有网络活动,他们正在升级服务器交换机。整个网络出现故障,我们作为 DBA 已经准备好禁用数据库服务器上的所有作业以进行复制和备份,但是在活动期间,WSFC(Windows 服务器故障转移集群)之一启动了故障转移,似乎它没有完全成功. 这导致两个节点启动并运行数据库和两台服务器上的所有驱动器,而驱动器和 SQL 服务应该只在其中一个上。

以上导致许多数据库损坏,我在尝试清除损坏方面非常困难。从两个用户数据库开始,后来 tempdb 和 msdb 也损坏了。不得不重新启动 tempdb 的服务,但是对于从上次成功备份恢复的 msdb,一切似乎都恢复了正常运行。

之后,在所有数据库(系统和用户数据库)上执行 dbcc checkdb。系统数据库没有任何问题,但是其中一个用户数据库(关键)出现以下错误:

Command: DBCC CHECKDB ([User_DB_Critical]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY, MAXDOP = 2
Msg 8914, Level 16, State 1, Server DB_Cluster_Name, Line 1
Incorrect PFS free space information for page (1:1439286) in object ID 526624919, index ID 0, partition ID 72057594055753728, alloc unit ID 72057594056933376 (type In-row data). Expected value  95_PCT_FULL, actual value  80_PCT_FULL.
Msg 8951, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: table 'Job_Execution_Log_Table' (ID 526624919). Data row does not have a matching index row in the index 'PK289' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Server DB_Cluster_Name, Line 1
Data row (1:2224:6) identified by (HEAP RID = (1:2224:6)) with index values 'JOB_NAME = 'populate_Tran_details' and START_TIME = '2019-07-03 03:42:00.323' and HEAP RID = (1:2224:6)'.
Msg 8951, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: table 'Job_Execution_Log_Table' (ID 526624919). Data row does not have a matching index row in the index 'PK289' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Server DB_Cluster_Name, Line 1
Data row (1:1395530:49) identified by (HEAP RID = (1:1395530:49)) with index values 'JOB_NAME = 'populate_Tran_details' and START_TIME = '2019-07-03 03:41:13.480' and HEAP RID = (1:1395530:49)'.
Msg 8951, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: table 'Job_Execution_Log_Table' (ID 526624919). Data row does not have a matching index row in the index 'PK289' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Server DB_Cluster_Name, Line 1
Data row (1:1439286:43) identified by (HEAP RID = (1:1439286:43)) with index values 'JOB_NAME = 'populate_Tran_details' and START_TIME = '2019-07-03 03:45:00.890' and HEAP RID = (1:1439286:43)'.
Msg 8951, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: table 'Job_Execution_Log_Table' (ID 526624919). Data row does not have a matching index row in the index 'PK289' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Server DB_Cluster_Name, Line 1
Data row (1:1439286:44) identified by (HEAP RID = (1:1439286:44)) with index values 'JOB_NAME = 'populate_Tran_details' and START_TIME = '2019-07-03 03:48:00.473' and HEAP RID = (1:1439286:44)'.
Msg 8935, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: Object ID 1374679995, index ID 1, partition ID 72057594120962048, alloc unit ID 72057596467675136 (type In-row data). The previous link (1:1685287) on page (1:491016) does not match the previous page (1:1445099) that the parent (1:232830), slot 129 expects for this page.
Msg 8937, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: Object ID 1374679995, index ID 1, partition ID 72057594120962048, alloc unit ID 72057596467675136 (type In-row data). B-tree page (1:491016) has two parent nodes (0:1), slot 0 and (1:1591622), slot 138.
Msg 8977, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: Object ID 1374679995, index ID 17, partition ID 72057594121093120, alloc unit ID 72057596467806208 (type In-row data). Parent node for page (1:692096) was not encountered.
Msg 8979, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: Object ID 1374679995, index ID 17, partition ID 72057594121093120, alloc unit ID 72057596467806208 (type In-row data). Page (1:692097) is missing references from parent (unknown) and previous (page (1:1548068)) nodes. Possible bad root entry in system catalog.
Msg 8978, Level 16, State 1, Server DB_Cluster_Name, Line 1
Table error: Object ID 1374679995, index ID 1, partition ID 72057594120962048, alloc unit ID 72057596467675136 (type In-row data). Page (1:1623651) is missing a reference from previous page (1:491016). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'Job_Execution_Log_Table' (object ID 526624919).
CHECKDB found 0 allocation errors and 5 consistency errors in table 'Tran_details_Table' (object ID 1374679995).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'User_DB_Critical'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (User_DB_Critical).

桌子尺寸:

尺寸

我建议我的经理采用以下方法:

  1. 尝试找到当时插入的行,如果可能,从上面的两个表中删除它们。
  2. 如果步骤 1 不可行,则重建表上的所有索引。重建需要对表的独占访问权限。
  3. 如果重建不起作用 - 我们将需要删除并重新创建索引。这需要对表的独占访问。
  4. 如果第 3 步不起作用,我们将不得不进行修复重建选项。此选项要求整个数据库处于单用户模式——这意味着在此操作进行期间任何人都不应访问数据库。
  5. 如果第 4 步不起作用——我们将需要选择 repair_allow_data_loss 选项,这很耗时,并且可能会丢失存在一致性问题的数据。这再次要求数据库处于单用户模式,并且没有人应该访问数据库。

我在活动之前进行了数据库完整备份,但是活动计划在 7 月 3 日早上进行,由于所有数据库的问题,当我们所有数据库都没有损坏并且业务开始照常运行时,它变成了早上 6:30。对于 msdb 和一个用户数据库 - 我仅使用以前的备份进行恢复。我在 7 月 3 日下班后运行了 checkdb,这意味着数据库包含一整天的所有数据。因此,如果我们在活动前恢复 7 月 3 日备份,我们将丢失 7 月 3 日白天的所有数据,这对企业来说是不可接受的。

添加有关备份的更多详细信息-目前我正在使用 ola hallengren 脚本进行备份,并且昨晚成功运行了备份。以下是我用于备份的参数:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBA_Maintenance -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -One_Heavy_Database', @Directory = N'DB_Backup_Path', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @Compress = 'Y',  @LogToTable = 'Y'" -b

我正在使用验证和校验和标志来检查备份。差异备份每 2 小时安排一次,日志备份每 15 分钟运行一次(日志传送已配置,但现在已停止),到目前为止,没有任何备份失败或报告任何问题。

在重表上,聚集索引上出现 3 个一致性错误,非聚集索引上出现 2 个一致性错误。对于第一个表,即 Job_Execution_Log_Table 在非聚集索引上存在所有不一致。

我需要关于如何去做的建议,以及解决这个一致性问题的最有效和最省时的工作应该是什么。

目前我正在浏览 Paul Randal 的链接,并试图看看这是否是最好的选择。

编辑:我将备份从主服务器恢复到辅助服务器并运行 checkdb 并发现与主服务器报告的一致性错误相同。删除并重新创建非聚集索引,4 个一致性错误消失了,只剩下一个:

Incorrect PFS free space information for page (1:1439286) in object ID 526624919, index ID 0, partition ID 72057594055753728, alloc unit ID 72057594056933376 (type In-row data). Expected value  95_PCT_FULL, actual value  80_PCT_FULL.

尚未触及大表,因为它在聚集索引中存在问题。并且不知道如何解决这个 PFS 问题。

感谢您的建议。

版本:Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 版权所有 (c) Microsoft Corporation Enterprise Edition:Windows NT 6.3 上基于内核的许可(64 位)(内部版本 9600:)(管理程序)

sql-server sql-server-2012
  • 1 个回答
  • 514 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-07-01 01:19:41 +0800 CST

SQL Server在使用不同实例的端口号时忽略实例名称

  • 4

我最近看到一个奇怪的案例,仍然不知道背后的原因是什么。在我的一台开发服务器上,有两个 SQL 服务器实例正在运行——一个是默认实例,另一个是 UAT 实例。

最近我们对这两个实例的默认端口进行了更改,并与开发团队和其他利益相关者进行了沟通,以开始他们的更改并对其进行测试。为了不影响他们现有的应用程序,端口 1433 保留在默认实例上。因此,以下是要连接的服务器和端口的详细信息:

默认连接 --> Server_Name,35683

命名 (SIT) 实例的连接 --> Server_Name\SIT,35685

默认实例 TCP/IP 属性

命名实例 TCP/IP 属性

这两个实例都有相似的数据库,但是由于它们指向不同的环境,因此数据集不同。

一位高级开发人员将连接用作 Server_name\SIT,35683,这意味着实例和端口不匹配。他使用的是 SIT 实例,但是默认实例的端口,它正在将他连接到默认实例。

当我在他的办公桌前并且我们正在测试一些代码时发现了这一点,我注意到了这一点,因为我的更改没有反映在默认实例中,因为我在 SIT 实例中进行了更改。

有人可以解释这种行为以及如何解决这个问题吗?

sql-server instance
  • 1 个回答
  • 349 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-04-21 23:47:05 +0800 CST

数据库 tempdb 的日志不可用

  • 3

几个月以来我一直在努力解决这个问题,我说这不是数据库问题,并将此案例分配给存储和操作系统团队,然后他们将其分配给我。这个问题反复发生,没有任何明确的发生模式。

我检查了这里提出的相同问题,我可以说这不是数据库损坏的问题,因为我正在使用 Ola Hallengren 的脚本进行维护工作,并且每周对用户和系统数据库进行数据库完整性检查(checkdb),并且没有报告任何问题在那里面。

也为类似的问题访问了第二个链接,并且可以确认 tempdb 处于简单恢复状态。

我为数据和日志添加了一个额外的文件,这样如果一个文件不可用,另一个文件仍然可以访问,但是后来我知道 tempdb 的访问是顺序的,所以只有在第一个文件满时才会转到第二个文件:

临时数据库属性

每次发生此问题时,我都会在 Windows 应用程序日志中看到另一个错误,如下所示:

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

存储团队已将这些文件排除在防病毒扫描之外。

这里要注意的一件事 - 我检查了其他系统数据库及其文件位置,可以看到对于 master 和 model,数据和日志文件在同一个驱动器(E 驱动器)中,而对于 tempdb 和 msdb,数据在 E 驱动器和日志中文件在 G 盘,不知道这是否相关。

当作业触发或任何事件被触发时,是否有任何检查系统数据库的顺序?如果它是那个驱动器的问题,那么 msdb 也在同一个驱动器上。

这是一个集群服务器,数据库驱动器在两台服务器中共享,服务器用于共享点应用程序。

Server Version: Windows Server 2012 Standard
SQL Server: Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) 
    Jan  5 2018 22:11:56 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

我们剩下的唯一选择是重新启动服务或故障转移 - 这样做听起来不太好。

感谢您在这方面的任何帮助。

sql-server tempdb
  • 1 个回答
  • 1873 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-04-10 04:56:27 +0800 CST

死锁图和解释,避免的解决方案

  • 4

我支持基于供应商的应用程序,它充满了块和死锁。死锁主要涉及两个或三个进程,但我昨天注意到,它涉及 9 个 SPID。

有人可以帮助我理解这个死锁图以及如何避免这种情况的解决方案。

<deadlock><victim-list><victimProcess id="process2ff017c28"/><victimProcess id="process2f1538108"/><victimProcess id="process2f618d088"/><victimProcess id="process2f6d828c8"/><victimProcess id="process2f6d83848"/><victimProcess id="process2da9b5468"/><victimProcess id="process2efac7468"/><victimProcess id="process2efac7848"/></victim-list><process-list><process id="process2ff017c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4412" ownerId="284194209" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.323" XDES="0x2a785f800" lockMode="IX" schedulerid="1" kpid="9052" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194209" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2f1538108" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4411" ownerId="284107628" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:24:11.843" XDES="0xdacf54e0" lockMode="IX" schedulerid="1" kpid="7272" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.737" lastbatchcompleted="2019-04-08T15:30:58.727" lastattention="1900-01-01T00:00:00.727" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284107628" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2f618d088" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4620" ownerId="284193487" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:59.513" XDES="0x20df303b0" lockMode="IX" schedulerid="2" kpid="2088" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.527" lastbatchcompleted="2019-04-08T15:30:58.527" lastattention="1900-01-01T00:00:00.527" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284193487" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2f6d828c8" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4302" ownerId="284194269" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.743" XDES="0x25090c6d0" lockMode="IX" schedulerid="3" kpid="3140" status="suspended" spid="105" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.843" lastbatchcompleted="2019-04-08T15:30:58.843" lastattention="1900-01-01T00:00:00.843" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194269" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2f6d83848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4319" ownerId="284178892" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:38.450" XDES="0xb83c63b0" lockMode="IX" schedulerid="3" kpid="7372" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.827" lastbatchcompleted="2019-04-08T15:30:58.827" lastattention="1900-01-01T00:00:00.827" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178892" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2da9b5468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4300" ownerId="284174799" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:25:26.760" XDES="0x20d4683b0" lockMode="IX" schedulerid="3" kpid="5664" status="suspended" spid="97" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.847" lastbatchcompleted="2019-04-08T15:30:58.847" lastattention="1900-01-01T00:00:00.847" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284174799" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2efac7468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4394" ownerId="284192570" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:49.180" XDES="0x184e789f0" lockMode="IX" schedulerid="4" kpid="5348" status="suspended" spid="98" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.753" lastbatchcompleted="2019-04-08T15:30:58.753" lastattention="1900-01-01T00:00:00.753" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284192570" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2efac7848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4409" ownerId="284178168" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:23.063" XDES="0x2420aab80" lockMode="IX" schedulerid="4" kpid="4572" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.740" lastbatchcompleted="2019-04-08T15:30:58.737" lastattention="1900-01-01T00:00:00.737" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178168" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process><process id="process2efac7c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4413" ownerId="284177493" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:12.160" XDES="0xab9103b0" lockMode="IX" schedulerid="4" kpid="2448" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284177493" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0  where id= @P1    </inputbuf></process></process-list><resource-list><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2ff017c28" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f1538108" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f618d088" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d828c8" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d83848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2da9b5468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7c28" mode="IX" requestType="convert"/></waiter-list></objectlock></resource-list></deadlock>

隔离级别设置为数据库级别的已提交读快照。

当我在哨兵一号计划资源管理器中打开这个死锁图时,它很吓人。

死锁图哨兵计划浏览器

版本:Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 版权所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.3 (Build 14393:) (管理程序)

sql-server deadlock
  • 3 个回答
  • 2316 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-04-01 04:06:58 +0800 CST

积极的索引不足和缺少索引的数据

  • 3

我知道我的数据库上有很多阻塞,并且已尽力按供应商进行排序,因为此应用程序受他们支持并且尚未产生任何成功的结果。时不时地,我们遇到阻塞问题,这种阻塞变得如此严重,而且它们的设计如此糟糕,以至于整个门户都崩溃了,除非并且直到我杀死几个持有独占锁的 SPID(大部分)。

我已经使用 sp_blitzindex 快一年了,并且是 Brent Ozar 先生和团队提供的 First Responder Kit 的忠实粉丝。当我对该发生阻塞的数据库执行 sp_blitzindex 时,它显示 - “ Aggressive Under-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits ” 优先级为 10。我检查了URL列和还检查了其他相关页面,但无法获得太多帮助。

我知道这里列出的表索引不足,需要创建更多索引,但是当我使用以下命令在表级别单独运行这些对象的 sp_blitzindex 时,需要创建更多索引:

EXEC dbo.sp_BlitzIndex @DatabaseName='db1', @SchemaName='sch', @TableName='tab1';

我根本没有得到任何缺失的索引详细信息。所有现有索引都被利用并且读取计数小于写入计数,此处突出显示的唯一问题在主键的“锁定等待”列中。

我不知道需要创建哪个列索引。我还检查了 sp_blitzlock 以查看是否可以收集更多信息,这将有所帮助,但是我看到的所有信息都存在很多死锁,并且列出了相同的对象,这些对象被认为是激进的索引不足。

还通过在此特定数据库中将排序顺序作为“读取”和“持续时间”传递来检查 sp_blitzcache 的输出,但没有丢失索引请求。有警告说“未参数化查询”和“未参数化查询,非 SARGables”,这些计划涉及不同的表集。

非常感谢任何帮助。

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index
  • 1 个回答
  • 1475 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-03-26 04:53:57 +0800 CST

关于创建缺失索引的建议

  • 1

我一直在使用 sp_blitzindex,它非常有用(感谢 Brent Ozar 和团队)。我对我的数据库执行了这个过程,下面是对一个表的索引恐惧症组的发现:

sp_blitzindex 输出

下面是基础表的定义:

CREATE TABLE [dbo].[table_name](
    [L] [int] IDENTITY(1,1) NOT NULL,
    [R] [varchar](15) NOT NULL,
    [A] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    [VAR32_03] [varchar](32) NULL,
    [VAR32_04] [varchar](32) NULL,
    [VAR32_05] [varchar](32) NULL,
    [VAR32_06] [varchar](32) NULL,
    [VAR32_07] [varchar](32) NULL,
    [VAR32_08] [varchar](32) NULL,
    [VAR32_09] [varchar](32) NULL,
    [VAR32_10] [varchar](32) NULL,
    [VAR32_11] [varchar](32) NULL,
    [VAR32_12] [varchar](32) NULL,
    [VAR32_13] [varchar](32) NULL,
    [VAR32_14] [varchar](32) NULL,
    [VAR32_15] [varchar](32) NULL,
    [VAR32_16] [varchar](32) NULL,
    [VAR32_17] [varchar](32) NULL,
    [VAR32_18] [varchar](32) NULL,
    [VAR32_19] [varchar](32) NULL,
    [VAR32_20] [varchar](32) NULL,
    [VAR32_21] [varchar](32) NULL,
    [VAR32_22] [varchar](32) NULL,
    [VAR32_23] [varchar](32) NULL,
    [VAR32_24] [varchar](32) NULL,
    [VAR32_25] [varchar](32) NULL,
    [VAR32_26] [varchar](32) NULL,
    [VAR32_27] [varchar](32) NULL,
    [VAR32_28] [varchar](32) NULL,
    [VAR32_29] [varchar](32) NULL,
    [VAR32_30] [varchar](32) NULL,
    [VAR32_31] [varchar](32) NULL,
    [VAR32_32] [varchar](32) NULL,
    [VAR32_33] [varchar](32) NULL,
    [VAR32_34] [varchar](32) NULL,
    [VAR32_35] [varchar](32) NULL,
    [VAR32_36] [varchar](32) NULL,
    [VAR32_37] [varchar](32) NULL,
    [VAR32_38] [varchar](32) NULL,
    [VAR32_39] [varchar](32) NULL,
    [VAR32_40] [varchar](32) NULL,
    [VAR32_41] [varchar](32) NULL,
    [VAR32_42] [varchar](32) NULL,
    [VAR32_43] [varchar](32) NULL,
    [VAR32_44] [varchar](32) NULL,
    [VAR32_45] [varchar](32) NULL,
    [VAR32_46] [varchar](32) NULL,
    [VAR32_47] [varchar](32) NULL,
    [VAR32_48] [varchar](32) NULL,
    [VAR32_49] [varchar](32) NULL,
    [VAR32_50] [varchar](32) NULL,
    [VAR32_51] [varchar](32) NULL,
    [VAR32_52] [varchar](32) NULL,
    [VAR32_53] [varchar](32) NULL,
    [VAR32_54] [varchar](32) NULL,
    [VAR32_55] [varchar](32) NULL,
    [VAR32_56] [varchar](32) NULL,
    [VAR32_57] [varchar](32) NULL,
    [VAR32_58] [varchar](32) NULL,
    [VAR32_59] [varchar](32) NULL,
    [VAR32_60] [varchar](32) NULL,
    [VAR32_61] [varchar](32) NULL,
    [VAR32_62] [varchar](32) NULL,
    [VAR32_63] [varchar](32) NULL,
    [VAR32_64] [varchar](32) NULL,
    [VAR64_01] [varchar](64) NULL,
    [VAR64_02] [varchar](64) NULL,
    [VAR64_03] [varchar](64) NULL,
    [VAR64_04] [varchar](64) NULL,
    [VAR64_05] [varchar](64) NULL,
    [VAR64_06] [varchar](64) NULL,
    [VAR64_07] [varchar](64) NULL,
    [VAR64_08] [varchar](64) NULL,
    [VAR64_09] [varchar](64) NULL,
    [VAR64_10] [varchar](64) NULL,
    [VAR64_11] [varchar](64) NULL,
    [VAR64_12] [varchar](64) NULL,
    [VAR64_13] [varchar](64) NULL,
    [VAR64_14] [varchar](64) NULL,
    [VAR64_15] [varchar](64) NULL,
    [VAR64_16] [varchar](64) NULL,
    [VAR64_17] [varchar](64) NULL,
    [VAR64_18] [varchar](64) NULL,
    [VAR64_19] [varchar](64) NULL,
    [VAR64_20] [varchar](64) NULL,
    [VAR64_21] [varchar](64) NULL,
    [VAR64_22] [varchar](64) NULL,
    [VAR64_23] [varchar](64) NULL,
    [VAR64_24] [varchar](64) NULL,
    [VAR64_25] [varchar](64) NULL,
    [VAR64_26] [varchar](64) NULL,
    [VAR64_27] [varchar](64) NULL,
    [VAR64_28] [varchar](64) NULL,
    [VAR64_29] [varchar](64) NULL,
    [VAR64_30] [varchar](64) NULL,
    [VAR64_31] [varchar](64) NULL,
    [VAR64_32] [varchar](64) NULL,
    [VAR128_01] [varchar](128) NULL,
    [VAR128_02] [varchar](128) NULL,
    [VAR128_03] [varchar](128) NULL,
    [VAR128_04] [varchar](128) NULL,
    [VAR128_05] [varchar](128) NULL,
    [VAR128_06] [varchar](128) NULL,
    [VAR128_07] [varchar](128) NULL,
    [VAR128_08] [varchar](128) NULL,
    [VAR128_09] [varchar](128) NULL,
    [VAR128_10] [varchar](128) NULL,
    [VAR128_11] [varchar](128) NULL,
    [VAR128_12] [varchar](128) NULL,
    [VAR128_13] [varchar](128) NULL,
    [VAR128_14] [varchar](128) NULL,
    [VAR128_15] [varchar](128) NULL,
    [VAR128_16] [varchar](128) NULL,
    [VAR256_01] [varchar](256) NULL,
    [VAR256_02] [varchar](256) NULL,
    [VAR256_03] [varchar](256) NULL,
    [VAR256_04] [varchar](256) NULL,
    [VAR256_05] [varchar](256) NULL,
    [VAR256_06] [varchar](256) NULL,
    [VAR256_07] [varchar](256) NULL,
    [VAR256_08] [varchar](256) NULL,
    [VAR512_01] [varchar](512) NULL,
    [VAR512_02] [varchar](512) NULL,
    [VAR512_03] [varchar](512) NULL,
    [VAR512_04] [varchar](512) NULL,
    [VAR1024_01] [varchar](1024) NULL,
    [VAR1024_02] [varchar](1024) NULL,
    [E] [varchar](20) NULL,
    [M] [varchar](40) NULL,
    [E] [varchar](50) NULL,
    [N] [varchar](40) NULL,
    [TN] [int] NULL,
    [T] [numeric](1, 0) NULL,
    [D] [numeric](1, 0) NULL,
 CONSTRAINT [XPKtable_name] PRIMARY KEY NONCLUSTERED 
(
    [L] ASC,
    [R] ASC
)

根据这些缺失的索引详细信息,我计划创建具有以下定义的索引:

create nonclustered index table_name_incl(A,VAR32_02) include(L,R,E,T,D,VAR32_10,VAR32_18,VAR32_19,VAR32_20,VAR64_11,VAR64_02,VAR32_42,VAR32_39,VAR32_38,VAR32_35,VAR32_39,VAR32_24,VAR32_25,VAR32_27)

我根据所有 6 个缺失索引统计信息中这些列的出现次数得出了上述列。

正如您从表定义中看到的那样,这是一个堆并且在该表上没有聚簇索引。

感谢您的指导或任何帮助。

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
sql-server index
  • 3 个回答
  • 455 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-03-25 03:59:32 +0800 CST

SQL Server 2014 的 MAXDOP 设置

  • 8

我知道这个问题已经被问过很多次并且也有答案,但是我仍然需要关于这个主题的更多指导。

以下是来自 SSMS 的我的 CPU 的详细信息:

中央处理器

下面是 DB 服务器任务管理器中的 CPU 选项卡:

CPU 选项卡

我MAXDOP通过以下公式将设置保持在 2:

declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int
declare @MaxDOP int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);

select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes 
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64

IF @NoofNUMA > 1 AND @HTEnabled = 0
    SET @MaxDOP= @logicalCPUPerNuma 
ELSE IF  @NoofNUMA > 1 AND @HTEnabled = 1
    SET @MaxDOP=round( @NoofNUMA  / @physicalCPU *1.0,0)
ELSE IF @HTEnabled = 0
    SET @MaxDOP=@logicalCPUs
ELSE IF @HTEnabled = 1
    SET @MaxDOP=@physicalCPU

IF @MaxDOP > 10
    SET @MaxDOP=10
IF @MaxDOP = 0
    SET @MaxDOP=1

PRINT 'logicalCPUs : '         + CONVERT(VARCHAR, @logicalCPUs)
PRINT 'hyperthreadingRatio : ' + CONVERT(VARCHAR, @hyperthreadingRatio) 
PRINT 'physicalCPU : '         + CONVERT(VARCHAR, @physicalCPU) 
PRINT 'HTEnabled : '           + CONVERT(VARCHAR, @HTEnabled)
PRINT 'logicalCPUPerNuma : '   + CONVERT(VARCHAR, @logicalCPUPerNuma) 
PRINT 'NoOfNUMA : '            + CONVERT(VARCHAR, @NoOfNUMA)
PRINT '---------------------------'
Print 'MAXDOP setting should be : ' + CONVERT(VARCHAR, @MaxDOP)

我仍然看到与CXPACKET. 我正在使用以下查询来获取:

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

目前CXPACKET,我的服务器的等待率为 63%:

等待统计

我参考了多篇关于专家推荐的文章,也看了微软MAXDOP的建议;但是,我不确定这个的最佳值应该是多少。

我在这里发现了一个关于同一主题的问题,但是如果我接受 Kin 的建议,那么MAXDOP应该是 4。在同一个问题中,如果我们选择 Max Vernon,它应该是 3。

请提供您宝贵的建议。

版本:Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition:Windows NT 6.3 (Build 9600:) 上基于内核的许可(64 位)(管理程序) )

并行度的成本阈值设置为 70。CTfP 已设置为 70,在测试了从默认值分别为 25 和 50 的相同值之后。当它是 default(5) 并且MAXDOP为 0 时,等待时间接近 70% CXPACKET。

我sp_blitzfirst在专家模式下执行了 60 秒,下面是结果和等待统计的输出:

sp_blitzfirst

sql-server sql-server-2014
  • 4 个回答
  • 2598 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-03-22 01:11:23 +0800 CST

性能改进在生产中出错,在测试中运行良好

  • 4

我们有一个供应商支持的应用程序,并且有一段代码在过程中进行非常繁重的逻辑读取和耗时,因此我建议他们稍微调整查询以减少 IO 和时间,它在在数据和性能方面进行测试,但是它在生产中失败并返回不同的数据,我们不得不回滚更改。需要您的专家建议。

这已经在测试环境中对数据进行了 3 个多月的测试,我们从未遇到任何数据问题。部署后立即开始在生产中出现少量故障,并产生不一致的数据。

现有查询:

SELECT @Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10)) 
FROM table_name WITH(NOLOCK) 
WHERE s_mode='value'

建议查询:

SELECT @Ref = ref_clnt+1 FROM table_name WITH(NOLOCK) 
WHERE RefNo = (SELECT MAX(RefNo) FROM table_name WHERE s_mode = 'value')

表的DDL如下:

CREATE TABLE [dbo].[table_name](
    [RefNo] [dbo].[udt_RefNo] NOT NULL,
    [S_Mode] [varchar](10) NOT NULL,
    [ref_clnt] [varchar](50) NULL)
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED 
(
    [RefNo] ASC
)

仅提供查询中使用的定义中的那些列。

Udt_RefNo是用户定义的数据类型:

CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
GO

SQL Server 版本:Microsoft SQL Server 2014 (SP3) 版权所有 (c) Microsoft Corporation 企业版(64 位)。

非聚集索引覆盖列如下图:

CREATE NONCLUSTERED INDEX [ncidx_table_name_1] ON [dbo].[table_name]
(
    [S_Mode] ASC,
    [S_Status] ASC
)
INCLUDE (   [ref_clnt])

请按要求查找查询计划:

  • 上一个查询

  • 建议查询

执行计划 开启统计IO和时间后的Reads Number of Reads对比:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 2732, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 157 ms,  elapsed time = 161 ms.

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

供应商已返回建议作为

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

问题是——如何测试它们,因为它们似乎都适用于大多数情况,但仅在少数情况下失败。我不太了解该应用程序及其供应商支持的应用程序,因此无法获得太多细节,例如业务逻辑如何为底层过程工作。

sql-server sql-server-2014
  • 2 个回答
  • 199 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-03-18 01:17:31 +0800 CST

堆的碎片级别

  • 4

我目前正在使用 Ola Hallengren 先生提供的脚本来执行维护工作,最近我注意到有许多表(堆)碎片级别高得惊人,需要查看并采取措施。我在网站上查看了常见问题解答,似乎他的脚本不支持重建堆。我使用以下查询来查找碎片级别:

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and dbindexes.name is null
ORDER BY page_count desc, indexstats.avg_fragmentation_in_percent desc

我的应用程序得到供应商的支持,我一直在与他们沟通以将这些堆更改为表并创建聚集索引,但是它还没有产生任何有意义的结果,因为他们已经将主键定义为唯一的非聚集索引并且它也是一部分外键,因此在进行任何更改之前需要在多个级别进行更改。首先,我花了很多天来解释聚簇索引和带唯一索引的主键的区别。

我还经历了Brent Ozar 先生建议的调整,以更改 Ola Hallengren 先生为索引优化提供的脚本中的默认值,以提高效率,但是我没有找到堆重建的任何细节。

根据我的理解,堆的碎片可以通过此处描述的两种方式处理:

  1. 在表上创建聚簇索引并将其删除——这将清除所有碎片并重建所有非聚簇索引,但这会耗费时间和 I/O。
  2. 重建堆——这也将清除碎片并重建表重建的所有非聚集索引部分。

我不能选择选项 1,因为我不知道可以创建聚集索引的列,而且这可能比选项 2 花费的时间更长。

我正在寻找通过 Ola Hallengren 在脚本中实现选项 1 的可能性或处理此问题的替代方法。另外要补充的是,只有当堆的大小超过 10,000 页且碎片级别超过 80 时,我才想重建我的堆。

我使用的是 Microsoft SQL Server 2014 SP3 企业版。

作为一名 DBA - 我不喜欢在我的数据库中使用堆,但是因为它是供应商支持的应用程序,并且因为他们已经将主键定义为唯一索引并且这些键是外键,所以很难将它们更改为集群,因为参考以及停机时间的可能性。

编辑:我浏览了 Erik Darling 先生提供的链接,我可以确认我有很多堆在数据库中转发记录或删除。现在,我回到了我开始的地方,即这两个选项。正如我之前提到的,在我的场景中创建聚簇索引非常困难,并且考虑到复杂的外键结构,至少需要数月(乐观)并且可能会停机。需要有关重建堆和可能的副作用的建议。

sql-server sql-server-2014
  • 1 个回答
  • 1054 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-03-15 03:38:02 +0800 CST

分段还原、文件组和文件扩展名

  • 1

根据我的理解(到目前为止),任何给定的用户数据库都包含三种类型的文件——mdf、ndf 和 ldf。

MDF 代表元数据文件并存储数据定义和数据(在小数据库的情况下并且没有其他文件存在数据)。

NDF基本上是针对表数据存储数据的,我们可以根据文件组来决定这里可以分配哪个表。

LDF 代表日志数据文件。这存储 VLF(虚拟日志文件)

我的印象是,只有一个 .mdf 文件属于主文件组,n个 .ndf 文件属于主文件组或用户定义的文件组。

我正在创建一个数据库,并为所有这些文件组和扩展名 .mdf(错误地)创建了多个文件组,令我惊讶的是,现在我的数据库有三个属于不同文件组的 .mdf 文件,如下所示:

CREATE DATABASE [TEST_DBASE]
ON  PRIMARY 
( NAME = N'TEST_DBASE', FILENAME = N'E:\DB\TEST_DBASE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), 
 FILEGROUP [ACCNTS_FILEGROUP] 
( NAME = N'ACCNTS_FILE', FILENAME = N'E:\DB\ACCNTS_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), 
 FILEGROUP [LOAN_FILEGROUP] 
( NAME = N'LOAN_FILE', FILENAME = N'E:\DB\LOAN_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

这种情况下,如果只需要让Accounts表上线,我们应该如何进行分段恢复呢?

早些时候我们遵循以下步骤:

RESTORE DATABASE [TEST_DBASE] 
FILEGROUP = 'PRIMARY' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY, PARTIAL

RESTORE DATABASE [TEST_DBASE] 
FILEGROUP = 'ACCNTS_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with RECOVERY

上述步骤使 Accounts 表在线且可访问。

为了在稍后的某个时间点(非关键表)使贷款表联机,我们过去常常执行以下步骤:

BACKUP LOG [BANK_DATABASE] TO DISK = 'E:\BACKUPS\Backup_tail.trn' with FORMAT, NORECOVERY

RESTORE DATABASE [TEST_DBASE] 
FILEGROUP = 'LOAN_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with NORECOVERY
RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\Backup_tail.trn' with RECOVERY

在上述情况下,我担心的是 - 考虑到元数据文件现在分布在所有文件组中并且不仅仅属于主文件组这一事实,零碎恢复是否会有任何变化。

对于任何部分恢复,主文件组的恢复是强制性的,因为它包含所有元数据,但是如果所有文件现在都是 .mdf,它将如何工作?

sql-server restore
  • 1 个回答
  • 67 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-03-12 10:24:12 +0800 CST

数据和日志文件的数据库备份

  • 3

有人告诉我,对于数据文件备份在扩展级别操作,对于日志文件,备份在页面级别操作。

我知道数据文件的文件类型始终是“行数据”,并以范围(混合或统一范围)的形式存储,而日志以日志的形式存储,即 VLF(虚拟日志文件)。

请有人详细说明这个概念,因为我对备份如何区分数据和日志有点困惑。如果是完整备份,它将存储写入数据文件的所有已提交更改,对于差异 - 自上次从数据文件完全备份以来的所有更改。对于日志备份 - 所有已提交但未写入数据文件的更改。

感谢您对此的宝贵意见。

sql-server backup
  • 2 个回答
  • 548 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-03-12 04:16:39 +0800 CST

MSSQL 的拆分备份与 Sybase ASE 的条带备份

  • 0

我最近开始了解 MSSQL Server 中拆分备份的概念,而我一直在 Sybase ASE 中进行条带备份。

Sybase ASE 中的条带备份比正常备份更快,如果我们使用三个条带,那么 Sybase 将使用 3 个 CPU 核心(取决于核心数量),因为它将被分成那么多线程,无论存储位置如何都并行执行。

我想了解 MSSQL 拆分备份是否也是如此,我读到如果我们使用不同的位置进行拆分备份,那么备份将并行执行并且会更快:

拆分备份

但是,如果存储位置相同(在同一驱动器和文件夹中),则不会涉及额外的 IO,因此对性能影响不大。

sql-server backup
  • 1 个回答
  • 130 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-02-26 00:58:36 +0800 CST

查找 APL 堆表插入的来源和详细信息

  • 0

我一直在使用以下命令在 HP-UX(Itanium)上的 Sybase ASE 15.7(SP139)上运行系统监视器(sysmon):

sp_sysmon "00:10:00"
go

观察到插入次数在一天中的任何时间都非常高,无法找到它的来源和详细信息,例如表名、数据库名、程序名等。下面是清晨 sysmon 的示例输出,当时有空载:

Transaction Profile
-------------------

  Transaction Summary             per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Committed Xacts                  10.7           n/a        6390     n/a     

  Transaction Detail              per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Inserts
    Fully Logged
      APL Heap Table              58665.7        5508.5    35199448     100.0 %
      APL Clustered Table             0.0           0.0           7       0.0 %
      Data Only Lock Table           13.2           1.2        7918       0.0 %
      Fast Bulk Insert                0.0           0.0           0       0.0 %
      Fast Log Bulk Insert            0.0           0.0           0       0.0 %
    Minimally Logged
      APL Heap Table                  0.0           0.0           0       0.0 %
      APL Clustered Table             0.0           0.0           0       0.0 %
      Data Only Lock Table            0.0           0.0           0       0.0 %
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Rows Inserted             58679.0        5509.8    35207373     100.0 %

看起来 tempdb 中正在发生插入,但是这么多插入有点麻烦,感谢专家的建议。

sybase monitoring
  • 1 个回答
  • 43 Views
Martin Hope
Learning_DBAdmin
Asked: 2019-02-19 22:36:01 +0800 CST

数据库维护作业和备份计划

  • 0

我会要求专家就安排维护和备份工作提供建议。以下是我更改之前的场景:

  1. 数据库的完整备份计划在每天凌晨 12:30 运行。
  2. 差异备份计划在工作时间(上午 8 点到下午 6 点)每 2 小时运行一次,在非工作时间每 6 小时运行一次。
  3. 配置日志传送后,日志备份计划每 15 分钟运行一次。
  4. 索引优化作业(使用来自 Great Mr. Ola Hallengren 的脚本)在每周日凌晨 1:45 运行。

在上述场景中,我们曾经面临存储磁盘上的空间问题,因为在维护作业之前运行了完整备份,因此后续差异备份的大小越来越大,直到运行下一次完整备份。这促使我在维护作业后运行完整备份,我还在周中检查了碎片级别,并根据决定每周运行两次维护作业的值,以下是修改后的计划:

  1. 计划在所有日期的凌晨 12:30 运行数据库的完整备份,但星期日和星期二安排维护作业时除外。在周日和周二,完整备份在凌晨 2:30 进行。
  2. 差异备份计划在工作时间(上午 8 点到下午 6 点)每 2 小时运行一次,在非工作时间每 6 小时运行一次 - 没有变化。
  3. 日志备份计划每 15 分钟运行一次,因为已配置日志传送 - 无变化
  4. 索引优化作业(使用 Great Mr. Ola Hallengren 的脚本)每周日和周二凌晨 1:45 运行。

我现在面临的问题是维护工作后立即进行日志备份的大小,日志备份比数据库本身的完整备份大得多。不用说,日志备份被转移到辅助站点,然后上传到那里用于同步目的。这花费的时间比预期的要长,并且在日志传送警报被触发之间,因为主要和次要不同步。同样早些时候,日志备份更大,但比完整备份要小得多,并且从主服务器传输到辅助服务器所花费的时间要少得多。

我不太确定,如果这是一个有效的场景,其中更改(插入/更新/删除)在过去 3 天内如此庞大以至于维护作业创建了比完整备份更大的日志文件并且会逐渐稳定下来或者我应该安排两个周日和周二的完整备份(维护作业运行时)- 一个在凌晨 12:30,另一个在维护作业之后。

感谢您的建议。

sql-server backup
  • 2 个回答
  • 508 Views

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目

    • 12 个回答
  • Marko Smith

    如何让sqlplus的输出出现在一行中?

    • 3 个回答
  • Marko Smith

    选择具有最大日期或最晚日期的日期

    • 3 个回答
  • Marko Smith

    如何列出 PostgreSQL 中的所有模式?

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

    如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve