Vojtěch Dohnal's questions

我需要更频繁地刷新 SSMS 中的“消息”窗格，有没有办法在没有 GO 语句的情况下实现这一目标？

我的查询是：

 DECLARE @Deleted_Rows INT @Deleted_Rows = 1;
 DECLARE @Deleted_Rows_Total bigINT = 0;
 DECLARE @t1 DATETIME;
 DECLARE @t2 DATETIME;

 WHILE (@Deleted_Rows > 0)
   BEGIN
      SET @t1 = GETDATE();
      DELETE TOP (100000)  FROM  PenData WHERE Cas < '20200101'
      SET @Deleted_Rows = @@ROWCOUNT;
      SET @t2 = GETDATE();

      SET @Deleted_Rows_Total = @Deleted_Rows_Total +@Deleted_Rows
      PRINT DATEDIFF(SECOND,@t1,@t2)
      PRINT @Deleted_Rows_Total

      --SELECT @Deleted_Rows AS smazano, @Deleted_Rows_Total AS celkem, DATEDIFF(SECOND,@t1,@t2) AS elapsed_s, GETDATE() as CAS
 END

我有一个表，其中包含有关AutoData具有组合聚集键Cas(DateTime) + GCom(Car ID) 的汽车的历史数据。一条记录包含各种指标，如燃油油位、车辆状态等。

表中一辆车的各个记录之间的间隔AutoData是不规则的，有时是 120 秒，有时是几秒，有时是几小时等。我需要对记录进行规范化查看，以便每 30 秒显示一条记录。

我有以下脚本：

DECLARE @GCom int = 2563,
    @Od DateTime2(0) = '20170210', 
    @Do DateTime2(0) = '20170224'    

--Create a table with intervals by 30 seconds
declare @temp Table ([cas] datetime2(0))
INSERT @temp([cas])
SELECT d
FROM
(
  SELECT
      d = DATEADD(SECOND, (rn - 1)*30, @Od)
  FROM 
  (
      SELECT TOP (DATEDIFF(MINUTE, @Od, @Do)*2) 
          rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM
          sys.all_objects AS s1
      CROSS JOIN
          sys.all_objects AS s2
      ORDER BY
          s1.[object_id]
  ) AS x
) AS y;

--Create temp table
CREATE TABLE #AutoData (
    [Cas] [datetime2](0) NOT NULL PRIMARY KEY,
    [IDProvozniRezim] [tinyint] NOT NULL,
    [IDRidic] [smallint] NULL,
    [Stav] [tinyint] NOT NULL,
    [Klicek] [bit] NOT NULL,
    [Alarm] [bit] NOT NULL,
    [MAlarm] [tinyint] NOT NULL,
    [DAlarm] [bit] NOT NULL,
    [Bypass] [bit] NOT NULL,
    [Lat] [real] NULL,
    [Lon] [real] NULL,
    [ObjemAktualni] [real] NOT NULL,
    [RychlostMaxV1] [real] NOT NULL,
    [RychlostV2] [real] NOT NULL,
    [Otacky] [smallint] NOT NULL,
    [Nadspotreba] [real] NOT NULL,
    [Vzdalenost] [real] NOT NULL,
    [Motor] [smallint] NOT NULL
)

--Populate the temp table selecting only relevant AutoData records
INSERT INTO #AutoData
SELECT [Cas]
      ,[IDProvozniRezim]
      ,[IDRidic]
      ,[Stav]
      ,[Klicek]
      ,[Alarm]
      ,[MAlarm]
      ,[DAlarm]
      ,[Bypass]
      ,[Lat]
      ,[Lon]
      ,[ObjemAktualni]
      ,[RychlostMaxV1]
      ,[RychlostV2]
      ,[Otacky]
      ,[Nadspotreba]
      ,[Vzdalenost]
      ,[Motor]
FROM AutoData a 
WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do

--Select final data
SELECT t.cas, ad.malarm, ad.IDProvoznirezim, ad.Otacky, ad.motor, ad.objemAktualni, ad.Nadspotreba 
FROM @temp t
OUTER APPLY (
SELECT TOP 1 stav, malarm, otacky,motor, objemAktualni, Nadspotreba, IDProvoznirezim  FROM #AutoData a
                     WHERE DATEDIFF(SECOND, a.cas, t.cas)<=CASE WHEN Motor>120 THEN Motor ELSE 120 END 
                     AND DATEDIFF(SECOND,  a.cas, t.cas)>-30 
                     ORDER BY CASE WHEN DATEDIFF(SECOND, a.cas, t.cas)>0 THEN DATEDIFF(SECOND, a.cas, t.cas) ELSE (DATEDIFF(SECOND, a.cas, t.cas)*-1) +120 END
) ad

DROP TABLE #AutoData

起初，我尝试使用只有一个表变量@temp 将条件WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do放在最后一个选择中来编写脚本。该脚本需要 39 秒才能执行。

当我使用#AutoData临时表在临时表中预加载数据子集时，如上面的脚本所示，它下降到 5 秒。

然后我尝试使用表变量@AutoData而不是#AutoData- 但它又花了更长的时间 - 22秒。

@temp对于这个例子，表有 40320 条记录，#AutoData表有 1904 条记录。但令人惊讶的是，仅使用#temp表而不是@temp变量会使执行再次变慢。

我很惊讶地看到使用或不使用临时表/变量的这种差异。显然，SQL Server 本身无法优化 OUTER APPLY 子句的内部。

但是为什么使用表变量和临时表会有这么大的区别呢？有没有其他方法可以知道，使用什么而不只是尝试它？

带有临时表#AutoData的执行计划：

https://www.brentozar.com/pastetheplan/?id=B1y2x2Zcg

带有变量@AutoData 的执行计划：

https://www.brentozar.com/pastetheplan/?id=r1rAZnbqx

我对这个小问题的想法不多了：

我需要直接在用户计算机上对 LocalDB 数据库执行查询。我不想在那里安装任何东西，只安装了 .NET (4.5.2) 和 SQL Server 2014 Express LocalDB。

我不想将数据库文件复制到我的计算机并稍作修改再返回，我只需要直接在该计算机上运行一两个查询。

是否有任何便携式命令行工具可以让我这样做？SQLCMD 不可用，因为它需要安装 ODBC 驱动程序，SSMS 也需要安装。

我使用 SSMS 2014 修改了 SQL Server 2008 上的维护计划，特别是重组索引任务 - 我为此任务添加了另外两个数据库。

当我想检查 T-SQL 是否正常时，View T-SQL按钮显示错误：

未知属性 IsMemoryOptimized

可以（应该？）以某种方式修复吗？

在安装了 SQL Server LocalDB 2014 的机器上，我正在寻找 SQLCMD.EXE 文件，但在任何地方都找不到。有没有办法单独安装？

不幸的是，我不是机器的本地管理员，所以我不喜欢 MSI ......

找到此链接https://www.microsoft.com/en-us/download/confirmation.aspx?id=36433，但不能使用 MSI ...也许复制可执行文件和 dll 可以解决问题。

从另一台安装了 SSMS 的计算机复制文件 SQLCMD.EXE、BATCHPARSER.DLL 和 SQLCMD.RLL 后，C:\Program Files\Microsoft SQL Server\110\Tools\Binn 我仍然收到错误：

Microsoft SQL Server Native Client 11.0：未找到数据源名称且未指定默认驱动程序。

运行sqlcmd -S np:\\.\pipe\LOCALDB#F8040C8F\tsql\query。

我需要跑

ALTER TABLE ... 
ALTER COLUMN ... COLLATE Czech_CI_AS ...

对于数据库中的多个列。所有这些表都有TIMESTAMP用于数据库同步的列。

SQL Server 2008 R2 Std. 上ALTER TABLE仅更改COLLATION一列而没有其他内容的命令会增加列的值吗？TIMESTAMP

基于这些问题和给出的答案：

SQL 2008 Server - 性能损失可能与非常大的表有关

具有历史数据的大型表分配了过多的 SQL Server 2008 Std。内存 - 其他数据库的性能损失

我在数据库 SupervisionP 中有一个表，定义如下：

CREATE TABLE [dbo].[PenData](
    [IDUkazatel] [smallint] NOT NULL,
    [Cas] [datetime2](0) NOT NULL,
    [Hodnota] [real] NULL,
    [HodnotaMax] [real] NULL,
    [HodnotaMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IDUkazatel] ASC,
    [Cas] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]

它包含 cca 2.11 亿行。

我运行以下语句：

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24
SELECT min(cas) from PenData p WHERE IDUkazatel=25
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;


SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25 
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

结果显示在这里：

第三个 SELECT 还将更多数据加载到 SQL Server 内存缓存中。

为什么第三个 SELECT 比前两个 SELECT（16 毫秒）慢得多（8.5 秒）？如何使用 OR 提高第三个选择的性能？我想运行以下 SQL 命令，但在我看来，在这种情况下，创建游标和运行单独的查询比单个选择要快得多。

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM  ...)

编辑

正如大卫建议的那样，我将鼠标悬停在粗箭头上：

基于这个问题和给出的答案：

SQL 2008 Server - 性能损失可能与非常大的表有关

我发现了 SQL Server 2008 的内存分配问题。

基本上，我们在 1 个 SQL Server 上有 3 个数据库（EkDB cca 300MB、SupervisionP cca 8 GB 和 Tarmac42 cca 42 GB），并且内存中有一个非常大的表。内存消耗是这样的：

几乎 6 GB 仅由一个表 PenData 分配，大约有 2.11 亿行。

该表定义如下：

CREATE TABLE [dbo].[PenData](
    [IDUkazatel] [smallint] NOT NULL,
    [Cas] [datetime2](0) NOT NULL,
    [Hodnota] [real] NULL,
    [HodnotaMax] [real] NULL,
    [HodnotaMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IDUkazatel] ASC,
    [Cas] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]

该表包含许多未使用或很少使用的历史记录，并且始终使用这样的条件访问数据

 WHERE IDUkazatel=@a AND Cas BETWEEN @b AND @c

从来没有这个条件。数据以每小时一次的批量插入方式插入，数据库每天增长约 10 MB。

我曾尝试删除一些记录（8300 万条），然后运行 ​​DBCC SHRINKDATABASE - 目前看起来很有希望，但第二天 SupervisionP 再次占用了 5-6 GB。

我找不到 SQL Server 为这张表分配这么多内存的任何原因。问题是，其他数据库缓存的数据会定期卸载，并且在数据再次加载到内存之前，对它们的查询运行速度较慢。

编辑

我忘了提到它是 SQL Server 的标准版......所以分区是不可能的。我确实考虑过它，但我没有发现它仅适用于企业版。

编辑 2

我浏览了所有的存储过程，我必须承认有几个 SQL 命令可以访问比我想象的更多的行，即

SELECT @minCas=MIN(cas) FROM PenData WHERE IDUkazatel=@IDUkazatel 

SELECT @StazenoDoReal=MAX(cas) from PenData p INNER JOIN Ukazatel u ON u.IDUkazatel=p.IDUkazatel WHERE u.IDZapisovac=@IDZapisovac 

SELECT  TOP 365   DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.PenData.Cas)) AS Den, MAX(dbo.PenData.Hodnota) AS MaxHodnota
FROM         dbo.Zapisovac INNER JOIN
                      dbo.VyrobniLinka ON dbo.Zapisovac.IDVyrobniLinka = dbo.VyrobniLinka.IDVyrobniLinka INNER JOIN
                      dbo.Ukazatel ON dbo.Zapisovac.IDZapisovac = dbo.Ukazatel.IDZapisovac INNER JOIN
                      dbo.PenData ON dbo.Ukazatel.IDUkazatel = dbo.PenData.IDUkazatel
WHERE     /*(dbo.PenData.Cas >= @Od) AND (dbo.PenData.Cas <= @Do) AND*/ (dbo.Zapisovac.IDVyrobniLinka = @IDVyrobniLinka AND dbo.Zapisovac.IDTypZapisovace!=4)
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.PenData.Cas)), ISNULL(dbo.Ukazatel.MinHodnotaProvoz, 10)
HAVING MAX(dbo.PenData.Hodnota)>ISNULL(dbo.Ukazatel.MinHodnotaProvoz, 10) OR MAX(dbo.PenData.Hodnota) IS NULL
ORDER BY Den DESC

SELECT MIN from all records 是否需要一直读取所有记录？我想是的，也许是愚蠢的问题......

编辑 3

所以我做了以下测试。我已经在测试服务器上恢复了 SupervisionP 数据库。恢复后它只占用了 1 MB 的缓存。然后我运行这个简单的查询：

  SELECT MIN(cas) FROM PenData

PenData 的主聚集键是如上所示的复合键 - 包含 Time + IndicatorID。因此，我认为对于 SQL Server 而言，在聚集索引中查找第一条或最后一条记录可能是相对容易的任务。但是内存使用量急剧增长到 1285 MB（测试服务器的总可用内存为 3.5 GB）。查询正在执行聚集索引扫描。

当我做：

 SELECT MIN(cas)  FROM PenData WHERE IDUkazatel=10

然后它是聚集索引搜索，内存保持在 1 MB - 它非常快而且还可以。

当我运行时：

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM Ukazatel WHERE IDZapisovac=10)

或者

 SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25 OR IDUkazatel=26 OR IDUkazatel=97 OR IDUkazatel=97 OR IDUkazatel=98

那么它需要很长时间并且内存分配急剧上升。然而，执行相同操作的以下命令会立即执行，并且不会分配内存！

SELECT min(cas) from PenData p WHERE IDUkazatel=24 
SELECT min(cas) from PenData p WHERE IDUkazatel=25
...

编辑 5

我已经提出了一个新问题，因为这个命令似乎是问题的原因

SELECT @StazenoDoReal=MAX(cas) from PenData p INNER JOIN Ukazatel u ON u.IDUkazatel=p.IDUkazatel WHERE u.IDZapisovac=@IDZapisovac 

与单独的 SELECT 相比，使用 OR 条件的索引查找要慢得多

我们有 SQL Server 2008，上面运行着 3 个活动数据库。

• DB1 - cca 400 MB 大小
• DB2 - cca 8 GB 大小
• DB3 - cca 42 GB 大小 - 但大部分记录根本没有使用

在 DB2 中我们有一张这张表

CREATE TABLE [dbo].[PenData](
    [IndicatorID] [smallint] NOT NULL,
    [Time] [datetime2](0) NOT NULL,
    [Value] [real] NULL,
    [ValueMax] [real] NULL,
    [ValueMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IndicatorID] ASC,
    [Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

该表本身占用大约 8 GB 并有 283 029 812 条记录。该表中的绝大多数记录都是历史记录，很少或从不访问。但是一小部分最近的记录被大量使用，并且每小时都有许多新记录被插入到这个表中。

问题是我们最近观察到 DB3 中的性能问题。尽管 DB2 和 PenData 的性能还可以。

我的问题是：

1.PenData 表的大小是否是服务器整体性能的重要因素？这么多未使用的表记录如何影响服务器分配的内存？

2. 如果我从非常大的表 PenData 中删除一半的记录，我能否在服务器（在 DB3 中）上获得显着的性能提升？

3.当我没有访问Activity monitor的权限时，有什么工具可以监控性能吗？

编辑

我非常害怕地看到（使用答案中提供的脚本）PenData 表占用了整个 SQL Server 内存的 60-70%（相对较低的 cca 6 GB）。我不确定为什么，因为这是我自己编写的应用程序，我看不出任何原因，为什么这个表中的这么多行仍缓存在内存中。在我试图查看有多少 PenData 仍缓存在内存中之前，运行 SELECT COUNT(*) FROM PenData 也是我的错误。

我在这张表中省略了一个外键，所以我在这里展示它：

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Indicator] FOREIGN    KEY([IndicatorID])
REFERENCES [dbo].[Indicator] ([IndicatorID])

我已经使用 PenData 的 SET RECORDCOUNT 100000 以 100 000 条记录为一组删除了数百万条记录。现在它有 211 120 425 条记录。我已经运行了 DBCC SHRINKDATABASE (PenData, 20) - 只有在这之后 PenData 的内存消耗才显着减少。

其他数据库的性能和内存消耗变得更好。

但是一天后 PenData 表再次占据了几乎所有的内存......

编辑

我在一个存储过程中更改了一个 SQL 命令，现在一切都很完美，数据库 SupervisionP 仅占用 184 MB 的缓存！在这里查看详细信息

与单独的 SELECT 相比，使用 OR 条件的索引查找速度要慢得多

谢谢你的帮助。