主页 / user-137410

jericzech's questions

Martin Hope
jericzech
Asked: 2021-09-10 05:08:15 +0800 CST
  • 3

那些使用优秀 sp_HumanEvents 的人，也许是作者本人，请帮助我理解我缺少的东西（由于我的愚蠢）。

#1

监控阻塞时，必须设置阻塞进程阈值（以秒为单位），否则阻塞进程事件不会被触发。这与@blocking_duration_ms 参数有何关联？

示例：阻塞进程阈值 ID 设置为 10 秒 @blocking_duration_ms 保留默认值 = 500 毫秒

#2

当我想在服务器重新启动时连续且独立地将结果记录到表中时，建议我使用代理作业和一个示例，该示例设置一个名称为sp_HumanEvents: 10 second Check In 但在周日午夜重复运行的计划。当 SQL Server 代理启动更合适时不会自动启动它吗？

sql-server performance-tuning
Martin Hope
jericzech
Asked: 2019-10-17 12:31:37 +0800 CST
  • 1

为什么 MS SQL Server 在这种情况下拒绝使用支持的过滤索引？

-- demo data
CREATE TABLE #Test (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
    ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID()
    ,Col2 NVARCHAR(20) NOT NULL DEFAULT N''  -- !!
    );

WITH
    L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
    L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
    L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
    L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
    L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
    L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
INSERT INTO #Test(Col2)
SELECT TOP 100000 N''
FROM Nums;

INSERT INTO #Test(Col2)
VALUES(N'ABC');

-- FILTERED index to support filter predicate of a query
CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N'';

-- just checking statistics
DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered')

-- condition on variable = index scan :-(
DECLARE @Filter NVARCHAR(20) = N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

在此处输入图像描述

使用文字时，一切都按预期进行。

-- condition on literal value - index seek + key lookup :-)
SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

在此处输入图像描述

sql-server execution-plan
Martin Hope
jericzech
Asked: 2019-10-11 09:19:05 +0800 CST
  • 2

我想知道是否有任何方法可以重写查询中的 EXPRESSION（只是表达式，而不是整个查询）以短路 THEN 阶段的无用评估？

演示数据：

CREATE TABLE #Docs (
    ID INT NOT NULL
    ,DocType TINYINT NOT NULL
    );

CREATE TABLE #DocsItems (
    IDDocs INT NOT NULL
    ,Amount NUMERIC(19,6)
    );

INSERT INTO #Docs(ID, DocType) VALUES(1,1),(2,1),(3,2),(4,2),(5,2),(6,2);
INSERT INTO #DocsItems(IDDocs,Amount) VALUES(3,50.),(3,25.),(3,33.),(4,44.),(4,123.),(6,11.);

主题查询：

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN D.DocType <> 1 THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

查询计划： 在此处输入图像描述

如果我将查询（故意）重写为：

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN 2 = 1 /* rewrite*/  THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1

该计划按照它应该/可以/将在原始查询中的方式进行： 在此处输入图像描述

sql-server performance
Martin Hope
jericzech
Asked: 2019-06-06 13:26:35 +0800 CST
  • 1

我想向社区寻求解决这种僵局情况的帮助： 死锁图

<deadlock-list>
 <deadlock victim="process17392264e8">
  <process-list>
   <process id="process17392264e8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="4449" ownerId="394509589" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:41.320" XDES="0x18ed9516f0" lockMode="IX" schedulerid="4" kpid="8348" status="suspended" spid="143" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:41.310" lastbatchcompleted="2019-06-05T12:06:41.310" lastattention="1900-01-01T00:00:00.310" clientapp="Helios Orange" hostname="???" hostpid="508" loginname="???" isolationlevel="read committed (2)" xactid="394509589" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
    <executionStack>
     <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
         FROM TabDavka D 
           INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
         WHERE D.IDDilce=@IDKusovni     </frame>
     <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x010007000f5ba906f09b4b6f0700000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1     </frame>
    </executionStack>
    <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120234, 39008, 1
SELECT @ret    </inputbuf>
   </process>
   <process id="process15bf6ce8c8" taskpriority="5" logused="0" waitresource="OBJECT: 7:2018106230:0 " waittime="6020" ownerId="394493191" transactionname="user_transaction" lasttranstarted="2019-06-05T12:06:39.750" XDES="0x16fb190ea0" lockMode="IX" schedulerid="1" kpid="3940" status="suspended" spid="83" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2019-06-05T12:06:39.750" lastbatchcompleted="2019-06-05T12:06:39.750" lastattention="1900-01-01T00:00:00.750" clientapp="Helios Orange" hostname="???" hostpid="14924" loginname="???" isolationlevel="read committed (2)" xactid="394493191" currentdb="7" currentdbname="XXXXXX" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128568">
    <executionStack>
     <frame procname="XXXXXX.dbo.hp_ZdvojeniKonstrukceATech" line="73" stmtstart="6876" stmtend="7250" sqlhandle="0x03000700efbf9d27f8924d01e9a8000001000000000000000000000000000000000000000000000000000000">
UPDATE CZ SET IntPermanentniZmena=0 
         FROM TabDavka D 
           INNER JOIN TabCZmeny CZ ON (CZ.ID=D.ZmenaOd AND CZ.IntPermanentniZmena=1) 
         WHERE D.IDDilce=@IDKusovni     </frame>
     <frame procname="adhoc" line="2" stmtstart="44" stmtend="152" sqlhandle="0x0100070009fde604c031f1451a00000000000000000000000000000000000000000000000000000000000000">
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1     </frame>
    </executionStack>
    <inputbuf>
DECLARE @ret integer
EXEC @ret=hp_ZdvojeniKonstrukceATech 120073, 39047, 1
SELECT @ret    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
    <owner-list>
     <owner id="process15bf6ce8c8" mode="S"/>
     <owner id="process15bf6ce8c8" mode="IX" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process17392264e8" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="2018106230" subresource="FULL" dbid="7" objectname="XXXXXX.dbo.TabCzmeny" id="lock134d414780" mode="S" associatedObjectId="2018106230">
    <owner-list>
     <owner id="process17392264e8" mode="S"/>
     <owner id="process17392264e8" mode="IX" requestType="convert"/>
    </owner-list>
    <waiter-list>
     <waiter id="process15bf6ce8c8" mode="IX" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>
 </deadlock>
</deadlock-list>

它位于同一资源（表）上，并且是由从两个会话调用的同一过程引起的。

程序的简化流程是这样的：

SELECT @Variable = Column FROM Table WITH (HOLDLOCK)
…
UPDATE Table SET...

我将不胜感激任何建议如何防止这种死锁情况？

sql-server locking
Martin Hope
jericzech
Asked: 2018-09-08 10:13:45 +0800 CST
  • 25

假设我们有一个这样的表定义：

CREATE TABLE MyTab (
    ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
    ,GroupByColumn NVARCHAR(10) NOT NULL
    ,WhereColumn DATETIME NULL
    )

和这样的过滤非聚集索引：

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab 
    (GroupByColumn)
WHERE (WhereColumn IS NULL)

为什么这个索引没有“覆盖”这个查询：

SELECT 
    GroupByColumn
    ,COUNT(*)
FROM MyTab
WHERE WhereColumn IS NULL
GROUP BY GroupByColumn

我得到这个执行计划：

在此处输入图像描述

KeyLookup 用于 WhereColumn IS NULL 谓词。

这是计划：https ://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

sql-server performance
Martin Hope
jericzech
Asked: 2018-08-16 10:43:27 +0800 CST
  • 1

关于 SQLOS 的执行模型（RUNNING 状态、RUNNABLE 队列、WAITER 列表），当当前正在对 RAM 中的页面进行逻辑读取时，任务的状态是什么？

如果是 WAITER 列表，最普遍的等待类型是什么？

我能以某种方式测量这种操作所花费的时间吗？

我知道很多逻辑读取会减慢您的查询速度，很多表/索引扫描（已经驻留在缓冲池中）会减慢您的查询速度 - 我只想知道它们如何出现在统计/dmv 中或如何将其与其他区别开来“经典”等待类型。

sql-server performance
Martin Hope
jericzech
Asked: 2018-01-23 02:39:33 +0800 CST
  • 2

在迁移到 SQL Server 2017（从 SQL Server 2008）后PAGELATCH，我看到PAGEIOLATCH和等待的增加。WRITELOG虽然我可以解释增加PAGEIOLATCH和WRITELOG（我们有其他磁盘 - HDD 而不是 SSD），但不知道为什么PAGELATCH也会增加。

它们有关系吗？

我周围看到的不是，原因PAGELATCH属于内存，而其他属于IO。

sql-server waits
Martin Hope
jericzech
Asked: 2017-12-16 07:58:05 +0800 CST
  • 4

你有没有解释，为什么查询优化器在这个例子中选择不同的索引和模式？

/* crete objects and data for testing */

-- table
CREATE TABLE #Test (
    ID INT IDENTITY PRIMARY KEY
    ,CustNo INT NULL
    ,CustNo2 INT NULL
    );

-- populate with data
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO #Test (CustNo)
SELECT TOP (1000) n FROM Nums ORDER BY n;

-- index on CustNo
CREATE NONCLUSTERED INDEX IX__#Test__CustNo ON #Test (CustNo ASC);

/* running test */

-- variables
DECLARE @Step INT = 1;
DECLARE @FindNo INT = 5;

-- #1 - uses CX index scan
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo);

-- #2 - uses NCX index seek
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo)
OPTION (RECOMPILE);

-- #3 - uses NCX index seek
IF @Step = 1
    SELECT CustNo
    FROM #Test
    WHERE CustNo = @FindNo;
sql-server t-sql