sql server 2012 性能分析工具 sqlserver2019性能

概述

  SQL Server长期以来一直以nchar，nvarchar和ntext数据类型的形式支持Unicode字符，这些字符仅限于UTF-16。可以将UTF-8数据放入nchar和nvarchar列中，但这 通常很乏味，即使 在SQL Server 2014 SP2中添加了通过BCP和BULK INSERT的UTF-8支持之后。最终结果是要支付Unicode的存储和内存需求，因为即使部分或全部为ASCII，仍然必须将所有数据存储为Unicode。

  在SQL Server 2019中，有新的UTF-8排序规则，可让您节省存储空间，同时仍可享受兼容性和原生存储UTF-8数据的好处。与Unicode压缩类似（但不完全相同），您只需为实际需要该字符的字符支付额外的存储空间。但是，实际的存储影响是什么？这如何影响内存授予和查询性能？

测试过程

  各种归类，代码页和UTF格式都有许多含义。我觉得专家可以撰写20个部分的文章系列，但仍然没有完成。实际上，所罗门·鲁兹基（Solomon Rutzky）已经写了很多有关这些主题的文章，最近 一篇有关SQL Server 2019中对UTF-8支持的文章 –这表明您可能不应该使用此功能，并且您应该专注于使用UTF-8列的排序规则应该主要是关于兼容性，而不是存储空间或性能。

  因为我知道尽管有 Solomon的建议，人们仍然会使用它，所以我只想专注于特定的UTF-8排序规则，以及与传统Unicode列中存储的UTF-16数据相比，空间和内存要求有何不同。我将比较压缩与不压缩以及列值的各种百分比（以及表中行的百分比）与非ASCII数据的比较。

   首先，让我们看一下一个表，该表包含具有三个不同排序规则的列，并查看当我们向其中插入数据时的外观。我为该查询拍摄了屏幕截图，因为我知道其中一些Unicode字符在到达您的设备时无法很好地转换：

共有三列，第一列使用标准Latin1_General归类，第二列包含具有补充字符（SC）的Latin1_General，第三列使用新的Latin1_General UTF-8归类。我分别插入了希腊字符，亚洲字符和表情符号（当然是加拿大国旗！），然后再插入一些其他ASCII字符。这是每个值的LEN（）和DATALENGTH（）的结果：

   显然，您可以看到长度基本相同，唯一的不同是表情符号在第一次排序时需要四个字节（请参阅 Greg Low的这篇文章，以了解为什么这是字节而不是字符）。但是，使用UTF-8归类时，实际存储几乎总是相同或更低（再次，除了一个例外，这次亚洲字符需要一个额外的字节）。我为您省去了一个悬念：通过行和页面压缩以及类似的#temp表，所有结果都是相同的。

另外，上面的代码示例中的注释表明，即使目标类型是varchar，您仍然需要在字符串文字上使用N前缀。原因是SQL Server将首先尝试解释字符串的值，如果N不存在，则部分Unicode数据会丢失。

尝试这个：

DECLARE @t TABLE (t varchar(10) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
INSERT @t(t) VALUES('h'),(N'h');
SELECT t FROM @t;

t
----
?
h

在玩这个游戏的过程中，我还发现了另一种现象，可能与排序规则完全无关，但仍然很有趣。当使用Unicode字符串的varbinary表示形式时（例如一堆poo表情符号，0x3DD8A9DC），可以根据语句中的其他内容来不同地解释它们。在此示例中，我要执行三个不同的批处理：

（1）直接插入varbinary值；

（2）直接插入值，并在单独的语句中，将值转换为nvarchar后插入；

（3）将值和转换后的值插入同一条语句中：

DECLARE @t TABLE (t varchar(10) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
INSERT @t(t) VALUES(0x3DD8A9DC);
SELECT t FROM @t;
GO -- 1
 
DECLARE @t TABLE (t varchar(10) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
INSERT @t(t) VALUES(0x3DD8A9DC);
INSERT @t(t) VALUES(CONVERT(nvarchar(10),0x3DD8A9DC));
SELECT t FROM @t;
GO -- 2
 
DECLARE @t TABLE (t varchar(10) COLLATE Latin1_General_100_CI_AI_SC_UTF8);
INSERT @t(t) VALUES(0x3DD8A9DC),(CONVERT(nvarchar(10),0x3DD8A9DC));
SELECT t FROM @t;
GO -- 3

结果让我感到困惑：

在使用不同的语句执行插入的情况下，两个解释都正确。但是，当使用VALUES（）将两行插入在一起时，两者都以某种方式转换为nvarchar。涉及VALUES（）的行为，可能与归类无关，但在以后的技巧中，我将不得不对此进行研究。同时，如果要将脚本从一种形式更改为另一种形式，请注意这一点。

回到原来的调查；如果我们大规模尝试该怎么办？我编写了一个脚本，该脚本为一堆表生成CREATE TABLE语句，这些表具有用于校对，压缩和实际存储多少非ASCII数据的各种设置。具体来说，这将创建81个表，这些表具有以下组合：

• 压缩（行，页，无）；
• 排序规则（Latin1_General_100_CI_AI，Latin1_General_100_CI_AI _SC和Latin1_General_100_CI_AI _SC_UTF8）；
• 包含UTF-8数据的行百分比（0％，50％，100％）；和，
• 每行是UTF-8数据的字符数（0个字符，25个字符和50个字符）：

CREATE TABLE #cmp(cmp varchar(4));
INSERT #cmp VALUES('ROW'),('PAGE'),('NONE');
CREATE TABLE #coll(coll varchar(8));
INSERT #coll VALUES(''),('_SC'),('_SC_UTF8');
CREATE TABLE #row(rowconf varchar(9));
INSERT #row VALUES('0  % UTF8'),('50 % UTF8'),('100% UTF8');
CREATE TABLE #char(charconf varchar(7));
INSERT #char VALUES('0 UTF8'),('25 UTF8'),('50 UTF8');
SELECT N'CREATE TABLE dbo.' + QUOTENAME(N'UTF8Test' + coll.coll + N'_' 
  + cmp.cmp + N'_' + rowconf + N'_' + charconf) + N'
(
    id int IDENTITY(1,1) NOT NULL,
    the_column ' + CASE coll.coll WHEN '_SC_UTF8' THEN N'' ELSE N'n' END + N'varchar(512)' END 
    + N' COLLATE Latin1_General_100_CI_AI' + coll.coll + N',
    CONSTRAINT ' + QUOTENAME(N'pk_UTF8Test_' + coll.coll + N'_' + cmp.cmp 
    + N'_' + rowconf + N'_' + charconf) + N' PRIMARY KEY CLUSTERED(id) 
    WITH (DATA_COMPRESSION = ' + cmp.cmp + N')
);' FROM #cmp AS cmp, #coll AS coll, #row AS rowconf, #char AS charconf;

该脚本产生81行输出，并具有如下表定义（当然，它们不是漂亮的脚本）：

CREATE TABLE dbo.[UTF8Test_ROW_0  % UTF8_0 UTF8]
(
    id int IDENTITY(1,1) NOT NULL,
    the_column nvarchar(200) COLLATE Latin1_General_100_CI_AI,
    CONSTRAINT [pk_UTF8Test__ROW_0  % UTF8_0 UTF8] PRIMARY KEY CLUSTERED(id)
      WITH (DATA_COMPRESSION = ROW)
);
CREATE TABLE dbo.[UTF8Test_SC_ROW_0  % UTF8_0 UTF8]
(
    id int IDENTITY(1,1) NOT NULL,
    the_column nvarchar(200) COLLATE Latin1_General_100_CI_AI_SC,
    CONSTRAINT [pk_UTF8Test__SC_ROW_0  % UTF8_0 UTF8] PRIMARY KEY CLUSTERED(id)
      WITH (DATA_COMPRESSION = ROW)
);
CREATE TABLE dbo.[UTF8Test_SC_UTF8_ROW_0  % UTF8_0 UTF8]
(
    id int IDENTITY(1,1) NOT NULL,
    the_column varchar(200) COLLATE Latin1_General_100_CI_AI_SC_UTF8,
    CONSTRAINT [pk_UTF8Test__SC_UTF8_ROW_0  % UTF8_0 UTF8] PRIMARY KEY CLUSTERED(id)
      WITH (DATA_COMPRESSION = ROW)
);

… 78 more tables …

 

复制，粘贴，执行，现在您有81个表，可以生成INSERT语句以类似的方式进行填充。这里涉及更多逻辑，因此脚本更加丑陋-我们希望在每个表中插入10,000行，但是这些行是部分或全部填充（或未填充）Unicode数据的值的混合。我在这里有加拿大国旗，并在该位置添加了注释，以防它无法在您的浏览器中正确显示：

DECLARE @sql nvarchar(max) = N'SET NOCOUNT ON;';
SELECT @sql += N'
WITH n AS (SELECT n = 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)
INSERT dbo.' + QUOTENAME(N'UTF8Test' + coll.coll + N'_' + cmp.cmp 
  + N'_' + rowconf + N'_' + charconf) + N'(the_column) SELECT b FROM (SELECT 
  b = REPLICATE(N''🇨🇦'',' + LEFT(charconf.charconf,2) + N')
  -----------------^ Canada flag is here
  + REPLICATE(N''.'',' + RTRIM(50-LEFT(charconf.charconf,2)) + N')) AS a
  CROSS APPLY (SELECT TOP (' + CONVERT(varchar(11),CONVERT(int,10000 
  * LEFT(rowconf.rowconf,3)/100.0)) + N') n FROM n) AS b OPTION (MAXRECURSION 10000); 
WITH n AS (SELECT n = 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)
INSERT dbo.' + QUOTENAME(N'UTF8Test' + coll.coll + N'_' + cmp.cmp 
  + N'_' + rowconf + N'_' + charconf) + N'(the_column) SELECT b FROM (SELECT 
  b = REPLICATE(N''.'',50)) AS a 
  CROSS APPLY (SELECT TOP (' + CONVERT(varchar(11),10000-CONVERT(int,10000 
  * LEFT(rowconf.rowconf,3)/100.0)) + N') n FROM n) AS b OPTION (MAXRECURSION 10000);'
FROM #cmp AS cmp, #coll AS coll, #row AS rowconf, #char AS charconf;
PRINT @sql;
--EXEC sys.sp_executesql @sql;

打印不会显示所有脚本（除非您具有 SSMS 18.2或使用本文所述的其他 措施），而是成对的insert语句。每对中的第一对代表包含UTF-8数据的行，第二对代表不包含数据的行：

WITH n AS (SELECT n = 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)
INSERT dbo.[UTF8Test_ROW_0  % UTF8_0 UTF8](the_column) SELECT b FROM (SELECT 
  b = REPLICATE(N'🇨🇦',0 )
  ----------------^ Canada flag is here
  + REPLICATE(N'.',50)) AS a
 CROSS APPLY (SELECT TOP (0) n FROM n) AS b OPTION (MAXRECURSION 10000); 
WITH n AS (SELECT n = 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)
INSERT dbo.[UTF8Test_ROW_0  % UTF8_0 UTF8](the_column) SELECT b FROM (SELECT 
  b = REPLICATE(N'.',50)) AS a 
 CROSS APPLY (SELECT TOP (10000) n FROM n) AS b OPTION (MAXRECURSION 10000);

在第一个示例中，我们希望0％的行包含UTF-8数据，并且希望任何行内的0个字符包含UTF-8数据。这就是为什么我们不插入包含加拿大国旗的行，也不插入10,000行（包含50个句点）的原因。（我承认50个周期的压缩会受到不公平的压缩，但是更具代表性的数据更难以自动化，而GUID则相反。）

如果我们从脚本后面的示例中选取一个任意示例，我们可以看到行的分布方式不同–一半的行包含UTF-8数据，而那些行包含25个Unicode字符和25个句点：

WITH n AS (SELECT n = 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)
INSERT dbo.[UTF8Test_ROW_50 % UTF8_25 UTF8](the_column) SELECT b FROM (SELECT 
  b = REPLICATE(N'🇨🇦',25)
  ----------------^ Canada flag is here
  + REPLICATE(N'.',25)) AS a
 CROSS APPLY (SELECT TOP (5000) n FROM n) AS b OPTION (MAXRECURSION 10000);
WITH n AS (SELECT n = 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)
INSERT dbo.[UTF8Test_ROW_50 % UTF8_25 UTF8](the_column) SELECT b FROM (SELECT 
  b = REPLICATE(N'.',50)) AS a 
  CROSS APPLY (SELECT TOP (5000) n FROM n) AS b OPTION (MAXRECURSION 10000);

如果您确信我不会炸毁您的磁盘，请更改以下内容：

PRINT @sql;
--EXEC sys.sp_executesql @sql;

--PRINT @sql;
EXEC sys.sp_executesql @sql;

然后执行它。在我的系统上，这花费了20到40秒的时间，数据和日志文件分别为400 MB和140 MB（从相当标准的AdventureWorks示例数据库开始）。

现在，我们准备进行抽查和分析！首先，让我们确保所有表的行数均正确：

SELECT t.name, p.rows
  FROM sys.tables AS t
  INNER JOIN sys.partitions AS p
  ON t.object_id = p.object_id
  WHERE t.name LIKE N'UTF8%';
-- 81 rows, all with 10,000 rows

然后我们可以对任何我们希望会有差异的表进行检查：

SELECT TOP (2) * FROM dbo.[UTF8Test_ROW_50 % UTF8_50 UTF8] ORDER BY id;
SELECT TOP (2) * FROM dbo.[UTF8Test_ROW_50 % UTF8_50 UTF8] ORDER BY id DESC;
SELECT TOP (2) * FROM dbo.[UTF8Test_SC_UTF8_ROW_50 % UTF8_25 UTF8] ORDER BY id;
SELECT TOP (2) * FROM dbo.[UTF8Test_SC_UTF8_ROW_50 % UTF8_25 UTF8] ORDER BY id DESC;

果然，我们看到了我们期望看到的结果（这对排序规则没有任何满足，只是证明我的脚本做了我认为会做的事情）：

现在，存储空间如何？我想看看页面分配DMV，sys.dm_db_database_page_allocations，尤其是相对比较。我从模板中提取了以下简单查询：

SELECT t.name,PageCount = COUNT(p.allocated_page_page_id) 
FROM sys.tables AS t CROSS APPLY 
sys.dm_db_database_page_allocations(DB_ID(), t.object_id, 1, NULL, 'LIMITED') AS p
WHERE t.name LIKE N'UTF8%'
GROUP BY t.name
ORDER BY PageCount DESC;

 

我将输出移到Excel中，几乎任意地将其分为三列。左侧的列是每个需要100页以上的表，而右侧的列是每个使用页压缩的表。中间一栏是包含81或89页的所有内容。现在，我可能已经堆叠了甲板以便于压缩，因为任何给定页面上的所有值都可能是相同的。这意味着压缩所涉及的页数可能比真实世界中更多的数据要少得多。但这确实表明，在给定相同数据的情况下，页面压缩是绝对均衡器。剩下的是一团糟，没有实际可观察的趋势，除了说明当更多数据是Unicode时，页数会增加，而不管排序规则如何（而且大部分情况下，

性能如何？在这种情况下，我通常关心的事情-除了必须在扫描中读取的页面数之外-是要分配的内存授权，尤其是对于具有排序的查询。持续时间也总是让人感兴趣的，但是我总是觉得自由记忆比耐心更稀缺。我编写了一个脚本来生成针对每个表运行的查询，共10次：

DECLARE @sql nvarchar(max) = N'DBCC FREEPROCCACHE;
GO
';
;WITH x AS (SELECT name FROM sys.tables WHERE name LIKE N'UTF8%')
SELECT @sql += N'
SELECT TOP 1 c FROM (SELECT TOP 9999 the_column FROM dbo.' 
  + QUOTENAME(name) + ' ORDER BY 1) x(c);
GO 10'
FROM x;
PRINT @sql;

在这种情况下，我使用PRINT输出（复制并粘贴到新窗口中）而不是sys.sp_executesql，因为后者不能接受GO 10之类的命令 。运行查询后，我转到sys.dm_exec_query_stats检查内存授予和查询持续时间。我本可以单独分析这82个查询，但是我决定简单地按排序规则和压缩将它们分组。我运行的查询：

WITH x AS 
(
  SELECT coll = CASE WHEN t.name LIKE '%SC_UTF8%' THEN 'UTF8'
      WHEN t.name LIKE '%_SC%' THEN 'SC' ELSE '' END,
    comp = CASE WHEN t.name LIKE N'%_PAGE_%' THEN 'Page'
      WHEN t.name LIKE N'%_ROW_%' THEN 'Row' ELSE 'None' END,
      max_used_grant_kb,max_ideal_grant_kb,max_elapsed_time
  FROM sys.dm_exec_query_stats AS s
  CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS st
  INNER JOIN sys.tables AS t
  ON st.[text] LIKE N'SELECT TOP%' + t.name + N'%'
  WHERE t.name LIKE N'UTF8%'
)
SELECT coll, comp, 
  max_used_grant = AVG(max_used_grant_kb*1.0),
  ideal_grant    = AVG(max_ideal_grant_kb*1.0),
  max_time       = AVG(max_elapsed_time*1.0) 
FROM x GROUP BY coll,comp
ORDER BY coll, comp;

这产生了两个有趣的图表。第一个显示UTF-8数据的内存授予量略小：

不幸的是，第二个图表显示UTF-8查询的平均持续时间高出50％或更多：

摘要

  新的UTF-8归类可以提供存储空间方面的好处，但是如果使用页面压缩，则该好处并不比旧归类更好。尽管内存授予量可能会略低，从而可能允许更多的并发性，但是这些查询的运行时间却明显更长。经过一小段调查之后，我不会说有明显的情况，我会急于改用UTF-8归类。