Iain Samuel McLean Elder's questions

我写了两个函数来回答七周内七个数据库中第 3 天的第一个作业问题。

创建一个存储过程，您可以在其中输入您喜欢的电影标题或演员姓名，它将根据演员出演的电影或类似类型的电影返回前五名建议。

我的第一次尝试是正确的，但速度很慢。返回结果最多可能需要 2000 毫秒。

CREATE OR REPLACE FUNCTION suggest_movies(IN query text, IN result_limit integer DEFAULT 5)
  RETURNS TABLE(movie_id integer, title text) AS
$BODY$
WITH suggestions AS (

  SELECT
    actors.name AS entity_term,
    movies.movie_id AS suggestion_id,
    movies.title AS suggestion_title,
    1 AS rank
  FROM actors
  INNER JOIN movies_actors ON (actors.actor_id = movies_actors.actor_id)
  INNER JOIN movies ON (movies.movie_id = movies_actors.movie_id)

  UNION ALL

  SELECT
    searches.title AS entity_term,
    suggestions.movie_id AS suggestion_id,
    suggestions.title AS suggestion_title,
    RANK() OVER (PARTITION BY searches.movie_id ORDER BY cube_distance(searches.genre, suggestions.genre)) AS rank
  FROM movies AS searches
  INNER JOIN movies AS suggestions ON
    (searches.movie_id <> suggestions.movie_id) AND
    (cube_enlarge(searches.genre, 2, 18) @> suggestions.genre)
)
SELECT suggestion_id, suggestion_title
FROM suggestions
WHERE entity_term = query
ORDER BY rank, suggestion_id
LIMIT result_limit;
$BODY$
LANGUAGE sql;

我的第二次尝试是正确和快速的。我通过将过滤器从 CTE 向下推到联合的每个部分来优化它。

我从外部查询中删除了这一行：

WHERE entity_term = query

我将此行添加到第一个内部查询中：

WHERE actors.name = query

我将此行添加到第二个内部查询中：

WHERE movies.title = query

第二个函数需要大约 10 毫秒才能返回相同的结果。

除了函数定义之外，数据库中没有什么不同。

为什么 PostgreSQL 会为这两个逻辑上等价的查询产生如此不同的计划？

第一个函数的EXPLAIN ANALYZE计划如下所示：

                                                                                       Limit  (cost=7774.18..7774.19 rows=5 width=44) (actual time=1738.566..1738.567 rows=5 loops=1)
   CTE suggestions
     ->  Append  (cost=332.56..7337.19 rows=19350 width=285) (actual time=7.113..1577.823 rows=383024 loops=1)
           ->  Subquery Scan on "*SELECT* 1"  (cost=332.56..996.80 rows=11168 width=33) (actual time=7.113..22.258 rows=11168 loops=1)
                 ->  Hash Join  (cost=332.56..885.12 rows=11168 width=33) (actual time=7.110..19.850 rows=11168 loops=1)
                       Hash Cond: (movies_actors.movie_id = movies.movie_id)
                       ->  Hash Join  (cost=143.19..514.27 rows=11168 width=18) (actual time=4.326..11.938 rows=11168 loops=1)
                             Hash Cond: (movies_actors.actor_id = actors.actor_id)
                             ->  Seq Scan on movies_actors  (cost=0.00..161.68 rows=11168 width=8) (actual time=0.013..1.648 rows=11168 loops=1)
                             ->  Hash  (cost=80.86..80.86 rows=4986 width=18) (actual time=4.296..4.296 rows=4986 loops=1)
                                   Buckets: 1024  Batches: 1  Memory Usage: 252kB
                                   ->  Seq Scan on actors  (cost=0.00..80.86 rows=4986 width=18) (actual time=0.009..1.681 rows=4986 loops=1)
                       ->  Hash  (cost=153.61..153.61 rows=2861 width=19) (actual time=2.768..2.768 rows=2861 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 146kB
                             ->  Seq Scan on movies  (cost=0.00..153.61 rows=2861 width=19) (actual time=0.003..1.197 rows=2861 loops=1)
           ->  Subquery Scan on "*SELECT* 2"  (cost=6074.48..6340.40 rows=8182 width=630) (actual time=1231.324..1528.188 rows=371856 loops=1)
                 ->  WindowAgg  (cost=6074.48..6258.58 rows=8182 width=630) (actual time=1231.324..1492.106 rows=371856 loops=1)
                       ->  Sort  (cost=6074.48..6094.94 rows=8182 width=630) (actual time=1231.307..1282.550 rows=371856 loops=1)
                             Sort Key: searches.movie_id, (cube_distance(searches.genre, suggestions_1.genre))
                             Sort Method: external sort  Disk: 21584kB
                             ->  Nested Loop  (cost=0.27..3246.72 rows=8182 width=630) (actual time=0.047..909.096 rows=371856 loops=1)
                                   ->  Seq Scan on movies searches  (cost=0.00..153.61 rows=2861 width=315) (actual time=0.003..0.676 rows=2861 loops=1)
                                   ->  Index Scan using movies_genres_cube on movies suggestions_1  (cost=0.27..1.05 rows=3 width=315) (actual time=0.016..0.277 rows=130 loops=2861)
                                         Index Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
                                         Filter: (searches.movie_id <> movie_id)
                                         Rows Removed by Filter: 1
   ->  Sort  (cost=436.99..437.23 rows=97 width=44) (actual time=1738.565..1738.566 rows=5 loops=1)
         Sort Key: suggestions.rank, suggestions.suggestion_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  CTE Scan on suggestions  (cost=0.00..435.38 rows=97 width=44) (actual time=1281.905..1738.531 rows=43 loops=1)
               Filter: (entity_term = 'Die Hard'::text)
               Rows Removed by Filter: 382981
 Total runtime: 1746.623 ms

第二个查询的EXPLAIN ANALYZE计划如下所示：

 Limit  (cost=43.74..43.76 rows=5 width=44) (actual time=1.231..1.234 rows=5 loops=1)
   CTE suggestions
     ->  Append  (cost=4.86..43.58 rows=5 width=391) (actual time=1.029..1.141 rows=43 loops=1)
           ->  Subquery Scan on "*SELECT* 1"  (cost=4.86..20.18 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
                 ->  Nested Loop  (cost=4.86..20.16 rows=2 width=33) (actual time=0.047..0.047 rows=0 loops=1)
                       ->  Nested Loop  (cost=4.58..19.45 rows=2 width=18) (actual time=0.045..0.045 rows=0 loops=1)
                             ->  Index Scan using actors_name on actors  (cost=0.28..8.30 rows=1 width=18) (actual time=0.045..0.045 rows=0 loops=1)
                                   Index Cond: (name = 'Die Hard'::text)
                             ->  Bitmap Heap Scan on movies_actors  (cost=4.30..11.13 rows=2 width=8) (never executed)
                                   Recheck Cond: (actor_id = actors.actor_id)
                                   ->  Bitmap Index Scan on movies_actors_actor_id  (cost=0.00..4.30 rows=2 width=0) (never executed)
                                         Index Cond: (actor_id = actors.actor_id)
                       ->  Index Scan using movies_pkey on movies  (cost=0.28..0.35 rows=1 width=19) (never executed)
                             Index Cond: (movie_id = movies_actors.movie_id)
           ->  Subquery Scan on "*SELECT* 2"  (cost=23.31..23.40 rows=3 width=630) (actual time=0.982..1.081 rows=43 loops=1)
                 ->  WindowAgg  (cost=23.31..23.37 rows=3 width=630) (actual time=0.982..1.064 rows=43 loops=1)
                       ->  Sort  (cost=23.31..23.31 rows=3 width=630) (actual time=0.963..0.971 rows=43 loops=1)
                             Sort Key: searches.movie_id, (cube_distance(searches.genre, suggestions_1.genre))
                             Sort Method: quicksort  Memory: 28kB
                             ->  Nested Loop  (cost=4.58..23.28 rows=3 width=630) (actual time=0.808..0.916 rows=43 loops=1)
                                   ->  Index Scan using movies_title on movies searches  (cost=0.28..8.30 rows=1 width=315) (actual time=0.025..0.027 rows=1 loops=1)
                                         Index Cond: (title = 'Die Hard'::text)
                                   ->  Bitmap Heap Scan on movies suggestions_1  (cost=4.30..14.95 rows=3 width=315) (actual time=0.775..0.844 rows=43 loops=1)
                                         Recheck Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
                                         Filter: (searches.movie_id <> movie_id)
                                         Rows Removed by Filter: 1
                                         ->  Bitmap Index Scan on movies_genres_cube  (cost=0.00..4.29 rows=3 width=0) (actual time=0.750..0.750 rows=44 loops=1)
                                               Index Cond: (cube_enlarge(searches.genre, 2::double precision, 18) @> genre)
   ->  Sort  (cost=0.16..0.17 rows=5 width=44) (actual time=1.230..1.231 rows=5 loops=1)
         Sort Key: suggestions.rank, suggestions.suggestion_id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  CTE Scan on suggestions  (cost=0.00..0.10 rows=5 width=44) (actual time=1.034..1.187 rows=43 loops=1)
 Total runtime: 1.410 ms

今天，七周内的七个数据库向我介绍了每个操作员的索引。

您可以通过创建text_pattern_ops运算符类索引来索引字符串以匹配先前查询的模式，只要这些值以小写形式索引即可。

CREATE INDEX moves_title_pattern ON movies (
    (lower(title) text_pattern_ops);

我们使用了，text_pattern_ops因为标题是文本类型。如果您需要索引 varchars、chars 或名称，请使用相关操作：varchar_pattern_ops、bpchar_pattern_ops和name_pattern_ops.

我发现这个例子真的很混乱。为什么这样做有用？

如果列是文本类型，在用作搜索值之前，其他类型（varchar、char、name）是否会转换为文本？

该索引与使用默认运算符的索引有何不同？

CREATE INDEX moves_title_pattern ON movies (lower(title));

我正在尝试为我的数据库中的一些存储过程生成使用情况报告。视图视图sys.dm_exec_procedure_stats包含我需要的一些内容，所以我决定从这里开始。

SQL Server 按对象 ID 键控过程元数据。我只知道过程名称，所以首先我必须使用OBJECT_ID函数将名称映射到 ID 。

有几个程序要检查，所以我想在处理它们之前把它们的名字放在一个临时表中。

我认为从临时表中的计算列中获取对象 id 会很可爱，所以我创建了一个这样的表：

USE msdb;
GO

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('dbo.sp_add_job');
INSERT INTO #procs (name) VALUES ('dbo.sp_start_job');

有了该表，获取执行计数的明显方法如下所示：

SELECT #procs.name, execution_count
FROM sys.dm_exec_procedure_stats AS procstats
INNER JOIN #procs ON #procs.[object_id] = procstats.[object_id]
ORDER BY execution_count DESC;

但它返回一个空的结果集！

此查询显示结果集为空的原因：

SELECT name, [object_id]
FROM #procs;

object_id 列是 NULL，所以内连接不会产生任何行：

name                 object_id
-------------------- -----------
dbo.sp_send_dbmail   NULL
dbo.sp_add_job       NULL
dbo.sp_start_job     NULL

这并不是临时表的计算列被破坏。如果我添加一个计算列来反转名称，当我从中选择时它会产生正确的值：

ALTER TABLE #procs ADD reverse_name AS REVERSE(name);

SELECT name, [object_id], reverse_name
FROM #procs;

结果：

name                 object_id   reverse_name
-------------------- ----------- ---------------------
dbo.sp_send_dbmail   NULL        liambd_dnes_ps.obd
dbo.sp_add_job       NULL        boj_dda_ps.obd
dbo.sp_start_job     NULL        boj_trats_ps.obd

为什么 OBJECT_ID 在这里返回 NULL？

我维护一个存档数据库，将历史数据存储在分区视图中。分区列是日期时间。视图下的每个表都存储了一个月的数据。

我们使用 datetime 列上的检查约束来约束每个表上的事件。这允许优化器限制为过滤事件日期时间列的查询搜索的表。

检查约束的名称是由 SQL Server 生成的，因此通过查看它们的名称很难知道它们的作用。

我希望约束名称具有“CK_TableName_Partition”的形式。

我可以使用此查询生成重命名脚本并从 sql_text 列复制数据。WHERE 子句匹配名称看起来像是由 SQL Server 生成的检查约束：

SELECT
  checks.name AS check_name,
  tabs.name AS table_name,
  skemas.name AS schema_name,
  cols.name AS column_name,
  N'
EXECUTE sys.sp_rename
  @objname = N''' + skemas.name + N'.' + checks.name + N''',
  @newname = N''CK_' + tabs.name + N'_Partition'',
  @objtype = ''OBJECT'';' AS sql_text
FROM sys.check_constraints AS checks
INNER JOIN sys.tables AS tabs ON
  tabs.object_id = checks.parent_object_id
INNER JOIN sys.schemas AS skemas ON
  skemas.schema_id = tabs.schema_id
INNER JOIN sys.columns AS cols ON
  tabs.object_id = cols.object_id AND
  cols.column_id = checks.parent_column_id
WHERE checks.name LIKE (
  N'CK__' + SUBSTRING(tabs.name, 1, 9) +
  N'__' + SUBSTRING(cols.name, 1, 5) +
  N'__' + REPLACE(N'xxxxxxxx', N'x', N'[0-9A-F]') COLLATE Latin1_General_BIN2
)
ORDER BY table_name;

输出如下所示：

check_name  table_name  schema_name column_name sql_text
CK__tbAcquisi__Acqui__5C4299A5  tbAcquisitions_201301   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__5C4299A5',  @newname = N'CK_tbAcquisitions_201301_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__76026BA8  tbAcquisitions_201302   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__76026BA8',  @newname = N'CK_tbAcquisitions_201302_Partition',  @objtype = 'OBJECT';
CK__tbAcquisi__Acqui__7D6E8346  tbAcquisitions_201303   Archive AcquisitionDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbAcquisi__Acqui__7D6E8346',  @newname = N'CK_tbAcquisitions_201303_Partition',  @objtype = 'OBJECT';
...
CK__tbRequest__Reque__60132A89  tbRequests_201301   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__60132A89',  @newname = N'CK_tbRequests_201301_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1392CE8F  tbRequests_201302   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1392CE8F',  @newname = N'CK_tbRequests_201302_Partition',  @objtype = 'OBJECT';
CK__tbRequest__Reque__1AFEE62D  tbRequests_201303   Archive RequestDT   EXECUTE sys.sp_rename  @objname = N'Archive.CK__tbRequest__Reque__1AFEE62D',  @newname = N'CK_tbRequests_201303_Partition',  @objtype = 'OBJECT';

查询的结果似乎是正确的，服务器执行得很快。

但是执行计划的根节点有一个警告：

表达式 (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) 中的类型转换可能会影响查询计划选择中的“CardinalityEstimate”

在这种情况下这意味着什么？如此复杂的过滤器会让优化器感到困惑吗？这是我应该担心的事情吗？

我试图找到一个可靠、高效的表达式来计算写一个正整数需要多少个小数位。

在数学上，整数的小数位数n是1 + floor(log(n))，其中 log 是常用对数（以 10 为底）。

有多种方法可以使用内置函数构造等价表达式，但其中一些会给出不正确的结果。有人可以解释为什么吗？

这是一个例子。

如何计算日志？

计算常用对数的最简单方法是使用LOG10函数。

如果您更喜欢对所有对数使用一个函数，则可以使用该LOG函数并在第二个参数中指定以 10 为底。

在 2012 年之前，SQL Server 的LOG函数只会计算自然对数（base e=2.71828...）。您可以通过将数字的自然对数除以底数的自然对数来计算数字的任意底数的对数。

以下查询计算某些示例值的所有三个表达式：

SELECT
  Number,
  LOG(Number, 10) AS LogAB,
  LOG10(Number) AS LogTen,
  LOG(Number) / LOG(10) AS LogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

输出：

Number      LogAB                  LogTen                 LogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2.99956548822598       2.99956548822598       2.99956548822598
1000        3                      3                      3
1001        3.00043407747932       3.00043407747932       3.00043407747932

我选择了值 999、1000 和 1001，因为 1000 是位数增加的点。999 有 3 个数字，1000 有 4 个数字。

所有三个表达式的值在视觉上是相同的，并且看起来是正确的。

让我们继续前进。

如何计算地板？

您可以使用如下查询获取前面示例中每个日志的发言权：

SELECT
  Number,
  FLOOR(LOG(Number, 10)) AS FloorLogAB,
  FLOOR(LOG10(Number)) AS FloorLogTen,
  FLOOR(LOG(Number) / LOG(10)) AS FloorLogOverLog
FROM (
  VALUES (999), (1000), (1001)
) AS Tally (Number);

输出：

Number      FloorLogAB             FloorLogTen            FloorLogOverLog
----------- ---------------------- ---------------------- ----------------------
999         2                      2                      2
1000        2                      3                      2
1001        3                      3                      3

999 和 1001 的每个表达式的值都相等且正确。如果我们将每个值加 1，则 999 中的计数为 3 位，1001 中的计数为 4 位。

1000 的值不一样！如果我们将每个值加 1，那么如果我们使用该函数，我们将得到 1000 中的 4 位数字，如果我们以任何一种形式LOG10使用该函数，我们将得到 3 位数字的计数。LOG

这里有一个不一致！

FLOOR(3) 如何等于 2？

含义很明确：使用该LOG函数会给我一些值的错误计数，因此我应该使用该LOG10函数。

但是每个日志表达式本身的值是相同且正确的。为什么 floor 函数从他们的输入中产生不同的值？

我的服务器的默认排序规则是 Latin1_General_CI_AS，由以下查询确定：

SELECT SERVERPROPERTY('Collation') AS Collation;

我惊讶地发现，通过这种排序规则，我可以使用 predicate 匹配字符串中的非数字字符LIKE '[0-9]'。

为什么在默认排序规则中会发生这种情况？我想不出这会有用的情况。我知道我可以使用二进制排序规则来解决该行为，但实现默认排序规则似乎是一种奇怪的方式。

过滤数字产生非数字字符

我可以通过创建一个包含所有可能的单字节字符值的列并使用数字匹配谓词过滤这些值来演示该行为。

下面的语句创建一个有 256 行的临时表，一个用于当前代码页中的每个代码点：

WITH P0(_) AS (SELECT 0 UNION ALL SELECT 0),
P1(_) AS (SELECT 0 FROM P0 AS L CROSS JOIN P0 AS R),
P2(_) AS (SELECT 0 FROM P1 AS L CROSS JOIN P1 AS R),
P3(_) AS (SELECT 0 FROM P2 AS L CROSS JOIN P2 AS R),
Tally(Number) AS (
  SELECT -1 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))
  FROM P3
)
SELECT Number AS CodePoint, CHAR(Number) AS Symbol
INTO #CodePage
FROM Tally
WHERE Number >= 0 AND Number <= 255;

每行包含代码点的整数值和代码点的字符值。并非所有字符值都是可显示的 - 一些代码点是严格控制字符。这是输出的选择性样本SELECT CodePoint, Symbol FROM #CodePage：

0   
1   
2   
...
32   
33  !
34  "
35  #
...
48  0
49  1
50  2
...
65  A
66  B
67  C
...
253 ý
254 þ
255 ÿ

我希望能够过滤符号列以使用 LIKE 谓词并指定字符范围“0”到“9”来查找数字字符：

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]';

它产生了令人惊讶的输出：

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9
178 ²
179 ³
185 ¹
188 ¼
189 ½
190 ¾

代码点 48 到 57 的集合是我所期望的。令我惊讶的是，上标和分数的符号也包含在结果集中！

将指数和分数视为数字可能有数学上的原因，但称它们为数字似乎是错误的。

使用二进制排序规则作为解决方法

我知道要获得我期望的结果，我可以强制执行相应的二进制排序规则 Latin1_General_BIN：

SELECT CodePoint, Symbol
FROM #CodePage
WHERE Symbol LIKE '[0-9]' COLLATE Latin1_General_BIN;

结果集仅包括代码点 48 到 57：

CodePoint   Symbol
48  0
49  1
50  2
51  3
52  4
53  5
54  6
55  7
56  8
57  9

存档以避免 ID 耗尽

我们的 OLTP 日志记录系统的一个表中的 ID 即将用完。我的工作是找到一种方法来“归档”整个表和所有引用表中的现有行数据，以便我们可以继续将新数据记录到表中。

我们将数据归档到一个表中的最快方法就是简单地重命名目标表和所有依赖对象——连接表、索引、约束。它速度快，并且保持数据完好无损。为了完成这项工作，我们必须使用旧名称为所有对象创建新的空副本。如果我们在一个事务中完成所有这些操作，插入新数据的存储过程就不会因为缺少表而失败。

尝试使用 PowerShell 解决

我已经将一个 PowerShell 脚本放在一起来生成一个 T-SQL 脚本来执行存档操作。

该脚本没有生成应有的重命名语句，我不明白为什么。

该脚本加载 SMO，设置目标对象的名称，并设置到服务器的仅捕获连接 - 这样我就可以捕获重命名命令以供稍后检查：

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

$ServerName = 'CLOUDCORP\LOGGING'
$DatabaseName = 'Logging'
$TableName = 'tbDataRequests'
$TableSchemaName = 'Logging'

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.ConnectionContext.SqlExecutionModes = [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql

接下来它创建一个对象数组，这些对象将通过重命名进行归档。目标表本身、所有引用表以及这些表的所有键和所有索引都将被归档：

$Database = $Server.Databases[$DatabaseName]

$Table = @($Database.Tables[$TableName, $TableSchemaName])

$ReferencingTables = $Database.Tables.ForeignKeys |
    ? { $_.ReferencedTable -eq $TableName -and $_.ReferencedTableSchema -eq $TableSchemaName } |
    % { $_.Parent } | Sort ID | Get-Unique

$TablesToArchive = $Table + $ReferencingTables

$ObjectsToArchive = $TablesToArchive + $TablesToArchive.Indexes + $TablesToArchive.ForeignKeys

接下来是问题部分。对于每个要归档的对象，我尝试捕获一个重命名语句。

$ObjectsToArchive | % { $_.Rename($_.Name + '_archive') }
$RenameCommands = $Server.ConnectionContext.CapturedSql.Text

意外的输出

对我的数据库运行后，该数组$ObjectsToArchive包含 48 个项目。该表达式$objectsToArchive.Name生成如下列表：

tbDataRequests
tbDataRequestPenguinServers
tbDataRequestLegs
tbDataRequestPaidDataRequests
tbDataRequestResponses
tbDataRequestUUIDMappings
tbDataRequestRouteNodes
tbRequestDataRequestQ
tbRequestDataRequests
IX_DataRequests_DataRequestDT
PK_DataRequests
PK_DataRequestPenguinServers
PK_DataRequestLegs
IX_DataRequestPaidDataRequests_PaidDataRequestID
PK_DataRequestPaidDataRequests
IX_DataRequestResponses_ScrapeResponseID
PK_DataRequestResponses
IX_DataRequestUUIDMappings_DataRequestID
PK_DataRequestUUIDMappings
PK_Geo_DataRequestRouteNodes
IX_RequestDataRequestQ_FK_RequestUUIDID
PK_RequestDataRequestQ
IX_RequestDataRequests_DataRequestID
PK_RequestDataRequests
FK_DataRequests_OrderType_OrderTypeID
FK_DataRequests_DatePairs_DatePairID
FK_DataRequests_CustomerCounts_CustomerCountID
FK_DataRequests_Routes_RouteID
FK_DataRequests_DataClients_DataClientID
FK_DataRequests_UserCountries_UserCountryID
FK_DataRequests_Websites_WebsiteID
FK_DataRequestPenguinServers_PenguinServers_JacqiardServerID
FK_DataRequestPenguinServers_DataRequests_DataRequestID
FK_DataRequestLegs_DatePairs_DatePairID
FK_DataRequestLegs_LegTypes_LegTypeID
FK_DataRequestLegs_Routes_RouteID
FK_DataRequestLegs_DataRequests_DataRequestID
FK_DataRequestPaidDataRequests_PaidDataRequests_PaidDataRequestID
FK_DataRequestPaidDataRequests_DataRequests_DataRequestID
FK_DataRequestResponses_DataRequests_DataRequestID
FK_DataRequestResponses_ScrapeResponses_ScrapeResponseID
FK_DataRequestUUIDMappings_DataRequests_DataRequestID
FK_DataRequestUUIDMappings_DataRequestUUIDs_Scrape RequestUUIDID
FK_Geo_DataRequestRouteNodes_DataRequests_DataRequestID
FK_RequestDataRequestQ_RequestUUIDs_RequestUUIDID
FK_RequestDataRequestQ_DataRequests_DataRequestID
FK_RequestDataRequests_Requests_RequestID
FK_RequestDataRequests_DataRequests_RequestID

该数组中的每个对象都应该有一个重命名语句。但是，CapturedSql 属性仅包含 10 个重命名语句。该表达式$RenameCommands | ? { $_ -like '*sp_rename*' }生成如下列表：

EXEC dbo.sp_rename @objname = N'[Logging].[tbDataRequests]', @newname = N'tbDataRequests_archive', @objtype = N'OBJECT'
EXEC sp_rename N'[Logging].[tbDataRequests].[IX_DataRequests_DataRequestDT]', N'IX_DataRequests_DataRequestDT_archive', N'INDEX'
EXEC sp_rename N'[Logging].[tbDataRequests].[PK_DataRequests]', N'PK_DataRequests_archive', N'INDEX'
EXEC sp_rename N'[FK_DataRequests_OrderType_OrderTypeID]', N'FK_DataRequests_OrderType_OrderTypeID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DatePairs_DatePairID]', N'FK_DataRequests_DatePairs_DatePairID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_CustomerCounts_CustomerCountID]', N'FK_DataRequests_CustomerCounts_CustomerCountID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Routes_RouteID]', N'FK_DataRequests_Routes_RouteID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_DataClients_DataClientID]', N'FK_DataRequests_DataClients_DataClientID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_UserCountries_UserCountryID]', N'FK_DataRequests_UserCountries_UserCountryID_archive', N'OBJECT'
EXEC sp_rename N'[FK_DataRequests_Websites_WebsiteID]', N'FK_DataRequests_Websites_WebsiteID_archive', N'OBJECT'

它为 Logging.tbDataRequests 及其键和索引编写重命名语句，但不为任何其他对象编写重命名语句。

我在这里做错了什么？

自从我安装 SQL Server 2012 Service Pack 1 以来，.NET 运行时优化服务 ( mscorsvw) 一直占用我所有工作站的内存。

在耗尽所有可用内存之前，它会消耗大约 1.6 GB 的内存：

我可以终止该进程，但是当我重新启动计算机或等待足够长的时间时，它会再次发生。

Techdows建议使用 .NET Native Image Generator ( ngen) 来解决这个问题。

我打开了一个命令提示符并运行了如下命令：

cd C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319.1
ngen executeQueuedItems

内存消耗mscorsvw下降到大约 20-30 MB，并ngen消耗了所有剩余的内存。

ngen在我终止进程之前只生成错误消息。输出的前几行如下所示：

Microsoft (R) CLR Native Image Generator - Version 4.0.30319.1
Copyright (c) Microsoft Corporation.  All rights reserved.
Failed to load dependency Microsoft.DataWarehouse of assembly
Microsoft.SqlServer.MaintenancePlanTasks, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.VisualStudio.DataTools.Interop of assembly 
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The system
cannot find the file specified. (Exception from HRESULT: 0x80070002)
Failed to load dependency Microsoft.Data.ConnectionUI of assembly
Microsoft.DataWarehouse, Version=11.0.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91 because of the following error : The located
assembly's manifest definition does not match the assembly reference.
(Exception from HRESULT: 0x80131040)

所有消息都说系统找不到程序集，或者清单定义与引用不匹配。

作为一种解决方法，我禁用了 Windows 服务clr_optimization_v4.0.30319_32和clr_optimization_v4.0.30319_64.

这隐藏了问题而不是解决问题，并阻止了所有 .NET 程序集的优化。

我还能尝试什么来解决这个问题？

假设您要查询数据库以发现其中包含的触发器类型。一种方法是对数据库中的所有触发器对象使用 OBJECTPROPERTY 函数。

有时 OBJECTPROPERTY 函数会产生令人困惑的结果。它的输出似乎取决于数据库上下文。

以下示例查询为 msdb 中的每个 sysmail 触发器返回一行：

SELECT
  object_id,
  name,
  OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS IsInsertTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS IsUpdateTrigger,
  OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS IsDeleteTrigger
FROM msdb.sys.objects
WHERE
  [type] = 'TR' AND
  name LIKE 'trig_sysmail_%';
GO

目的是找出哪个 DML 操作将触发每个触发器。例如，IsInsertTrigger如果触发器定义为 ，则该列包含 1 AFTER INSERT，否则为 0。

当我在 msdb 的上下文中执行查询时，结果集在每个计算列中都包含 0 或 1。它看起来像这样：

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         0               1               0
745105745   trig_sysmail_account         0               1               0
761105802   trig_sysmail_profileaccount  0               1               0
777105859   trig_sysmail_profile_delete  0               0               1
793105916   trig_sysmail_servertype      0               1               0
809105973   trig_sysmail_server          0               1               0
825106030   trig_sysmail_configuration   0               1               0
841106087   trig_sysmail_mailitems       0               1               0
857106144   trig_sysmail_attachments     0               1               0
873106201   trig_sysmail_log             0               1               0

当我在 master 上下文中执行查询时，结果集在每个计算列中都包含 NULL。它看起来像这样：

object_id   name                         IsInsertTrigger IsUpdateTrigger IsDeleteTrigger
----------- ---------------------------- --------------- --------------- ---------------
713105631   trig_sysmail_profile         NULL            NULL            NULL
745105745   trig_sysmail_account         NULL            NULL            NULL
761105802   trig_sysmail_profileaccount  NULL            NULL            NULL
777105859   trig_sysmail_profile_delete  NULL            NULL            NULL
793105916   trig_sysmail_servertype      NULL            NULL            NULL
809105973   trig_sysmail_server          NULL            NULL            NULL
825106030   trig_sysmail_configuration   NULL            NULL            NULL
841106087   trig_sysmail_mailitems       NULL            NULL            NULL
857106144   trig_sysmail_attachments     NULL            NULL            NULL
873106201   trig_sysmail_log             NULL            NULL            NULL

MSDN指出 OBJECTPROPERTY 函数在以下情况下返回 NULL：

1. 属性名称无效。
2. 对象 ID 无效。
3. id 是指定属性的不受支持的对象类型。
4. 调用者无权查看对象的元数据。

我可以排除原因 1 和 3，因为查询在 msdb 的上下文中返回了正确的结果。

起初我认为这可能是跨数据库权限问题（原因 4），但我是服务器上的系统管理员。

这留下了原因 2，这给我留下了以下问题：

跨数据库查询中的对象 ID 是否无效？

正在调用哪个数据库的 OBJECTPROPERTY 函数？

以下脚本的第一批sp_trace_create按文档顺序调用带参数的存储过程；第二批交换参数的位置@tracefile和@options：

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @options = 0,
  @tracefile = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @tracefile = N'C:\temp\TestTrace',
  @options = 0;

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

第一批创建一个新的跟踪，选择它的 id，然后关闭跟踪。返回一个结果集：

@new_trace_id
2

第二批失败并出现错误：

消息 214，级别 16，状态 3，过程 sp_trace_create，第 1 行过程需要类型为“nvarchar(256)”的参数“@tracefile”。

为什么参数顺序会影响存储过程的输出sp_trace_create？为什么它会因为这样一个误导性的错误信息而失败？

我的组织最近将其所有生产 SQL Server 2008 实例升级到Service Pack 3，Cumulative Update 1。我有责任确保我们的开发数据库环境运行与生产环境相同的版本，同时最大限度地减少开发人员的停机时间和中断。

每个开发环境包含六个 SQL Server 实例。我们有八个开发环境在两台机器之间平均共享。对于每个实例，我都必须安装 Service Pack 3，然后安装 Cumulative Update 1。每个安装程序都会在升级过程中重新启动实例的 Windows 服务，因此我必须提前警告使用该实例的开发人员。安装程序需要很长时间才能自行设置，所以我将每个环境作为批次升级以加快过程。

因为一台机器托管多个实例，重启可能会扰乱所有使用该机器的开发人员，即使他们的实例没有升级。因此，我想避免不必要的重启。但只有在第一次运行时，安装才告诉我“您必须重新启动计算机才能完成 SQL Server 安装程序”。

安装程序没有告诉我必须重新启动机器，我不想不必要地重新启动。我的高级 DBA 同事告诉我，无论如何我都应该重新启动主机，以保持环境“干净”。

如果在升级实例后不重启主机会发生什么坏事？

我使用 SQL Server Mangagement Studio 检查数据库中对象的定义。

例如，要检查 master 数据库中表 dbo.MSreplication_options 的定义，我会在对象资源管理器中右键单击该表并选择“将表脚本编写为... > 创建到 > 新查询编辑器窗口... '.

从我的 Management Studio 当前状态的屏幕截图中可以看出，为名为“SQLQuery9.sql”的新脚本创建了一个新选项卡，其中包含表定义。

到这时我已经用这种方式检查了其他几个对象，忘记了大多数其他名为“SQLQuery1.sql”、“SQLQuery2.sql”等的脚本包含什么。

我怎样才能配置管理工作室给脚本一个更具描述性的名称，可能包含对象的名称，例如“dbo.MSreplication_options.sql”。

我的 Management Studio 版本是 10.0.5500.0。