Stuart Blackler's questions

在我的应用程序中，我必须执行分布式锁定模式。因为我们已经有一个 SQL Server 实例可供使用，所以我们决定在我们的 Web 应用程序的 SQL 层实现锁定是最简单的。

可以根据多种条件获得锁，包括：

• 请求的锁类型
• 任意应用程序标识符

出于所有意图和目的，将上述两个条件视为int数据类型。

在这种模式中，我们希望将所有的锁视为 FIFO，我相信SERIALIZABLE隔离级别会给我们。

以下是我们建议执行“锁定”的方式：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

IF EXISTS (SELECT 1 FROM locks WHERE LockType = @LockType AND ApplicationIdentifier = @ApplicationIdentifier)
    BEGIN
        -- Awesome, the lock will be acquired
        INSERT INTO locks OUTPUT INSERTED.LockId VALUES (2,3)
    END
ELSE
    BEGIN
        -- Someone already has the lock
        SELECT -1
    END

SET TRANSACTION ISOLATION LEVEL READ COMITTED

和“解锁”：

DELETE FROM locks WHERE LockId = @LockId

所以我的问题有两个：

1. 我是否也需要进行“解锁” SERIALIZABLE？
2. 还有其他我可以使用的方法/我忘记的任何东西吗？

SQL Server 可以是 2008/2012

我试图更好地理解DELETEandTRUNCATE命令之间的区别。我对内部结构的理解大致如下：

DELETE-> 数据库引擎从相关数据页和输入该行的所有索引页中查找并删除该行。因此，索引越多，删除所需的时间就越长。

TRUNCATE-> 简单地删除所有表的数据页，使其成为删除表内容的更有效选项。

假设以上是正确的（如果不是，请纠正我）：

1. 不同的恢复模式如何影响每个语句？如果有任何影响
2. 删除时，是扫描所有索引还是只扫描该行所在的索引？我会假设所有索引都被扫描（而不是寻找？）
3. 命令是如何复制的？是否在每个订阅者上发送和处理 SQL 命令？还是 MSSQL 比这更智能一些？

我正在阅读 SQL Server 2012 考试的文档，我看到了以下几点：

case 与 isnull 与 coalesce

现在，我知道如何使用每一个，但我不知道何时使用每一个。请一些机构澄清一下。谢谢。

附言。请问我们可以给考试题一个标签吗？

继我最近在 Tempdb 上提出的一个问题之后。我想知道在添加和移动 tempdb 文件时如何正确设置 autogrowth 属性？

我问是因为我想让 SQL Server 使用循环算法将工作负载分散到每个文件中。我知道如果文件大小不相等，那么 SQL Server 将使用空间最多的文件，从而增加了导致闩锁争用的机会。我在这里的理解正确吗？

干杯，

我想知道两件事：

• 如何以最少的停机时间安全地移动 tempdb？
• 您需要多少个 tempdb 文件？

每个核心是 1 个文件吗？那么 quad-core = 4 tempdb 文件，创建三个新文件？

我有一个这样的查询：

SELECT col1
FROM   MyTable
WHERE  
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) 
       BETWEEN col2 
       AND     col3
;

这给出了一个关于执行计划的工具提示，类似于：

是否dateadd为查询中的每一行执行搜索谓词部分？或者 SQL Server 是否为整个查询计算一次值？

和标题描述的差不多。我刚刚检查了一个 SQL Server 并注意到有很多查询计划膨胀可以通过适当的参数化来修复。

对代码进行更改后，是否建议我们清除查询计划缓存并让它自行重建？还是会及时自行清理？

注意：在前 50 个最大的查询中，有 47 个相同但参数不同。

给定以下定义：

CREATE TABLE MyTable
(
    col1 int,
    col2 int,
    col3 int,
    col4 int
)

CREATE FUNCTION fn_MyFunction
(
    @Param1 int,
    @Param2 int
)
RETURNS TABLE AS RETURN
(
    SELECT @Param1 * 2 AS 'res1', @Param2 * 4 AS 'res2', @Param1 AS 'col3'
)

我正在尝试将函数加入表中，以便获得每行的计算值。示例：在预订表中，我需要根据开始日期和结束日期获取价格。

这是一个示例查询：

SELECT      tbl.col1
,           tbl.col2
,           tbl.col3
,           fn.res1
,           fn.res1
FROM        MyTable tbl
CROSS APPLY fn_MyFunction(tbl.col3, tbl.col4) fn
WHERE       fn.col3 = tbl.col3

虽然我认为我在实际查询中得到了正确CROSS APPLY的结果，但在这种情况下如何获得结果？它会逐行（有效地）获取它们吗？或者它是否以类似于CROSS JOIN（即：制作笛卡尔积）的方式工作？

我正在尝试创建一个过程，该过程将生成给定表的 xml 文档，同时更新所述表中的信息。CREATE TABLE除非我使用语句（我还没有尝试过 table var），否则我的过程无法识别临时表的使用。CREATE TABLE理想情况下，如果我/其他开发人员想在将来添加另一列，我不会使用该语句。

这是我目前的代码：

UPDATE Audit.ErrorLog SET ErrorReported = 0 OUTPUT inserted.* INTO ##temp

SELECT *
FROM ##temp 
FOR XML PATH('Error'), ROOT ('Errors')

DROP TABLE ##temp

它失败了：

消息 208，第 16 级，状态 0，第 24 行

无效的对象名称“##temp”。

如果我必须使用声明，这不是世界末日CREATE TABLE，但我不想明确声明它。

笔记：

1. 我知道目前没有错误处理，我想先让这部分工作。
2. 我也尝试过命名列而不是使用Inserted.*
3. 我故意设置ErrorReported = 0为我在测试环境中

我目前正在观看有关 Entity Framework的视频，大约 15 分钟后，演讲者说表值函数偶尔会执行 UDF。

我的问题是在什么情况下 TVF 的性能优于 UDF？

在Stackoverflow上问过这个问题后，我想知道我所做的在哪里是正确/最佳实践。

基本上，我创建的每个对象都将进入一个模式，其模式名称反映了一种用法。例如，我有模式Audit和Admin（以及其他）。

这反过来又不会在 中留下任何对象dbo。这个可以吗？还有什么我需要做的吗？

在为我之前提出的另一个问题创建测试数据库时，我记得可以声明主键NONCLUSTERED

什么时候使用NONCLUSTERED主键而不是CLUSTERED主键？

提前致谢

在我创建的大多数数据库中，我需要授予执行以下操作的权限：

1. 对数据库中的所有表执行 SELECT/INSERT/UPDATE/DELETE
2. 执行数据库中的所有（用户）存储过程

为用户datareader提供datawriter足够的存储空间或我需要提供哪些其他角色/权限？

如果我需要做的不仅仅是datareaderand datawriter，你能否指出我正确的方向来创建一个脚本来影响数据库中的所有用户。

提前致谢

我正在编写一个脚本来快速检查给定数据库中的索引。我想我的脚本写得不错，但是为了删除重复的行，我已经完成了很多SUM功能，但我不确定这是否正确：

SELECT      OBJECT_NAME(i.object_id)                                                                                                                AS 'Table Name'
,           COALESCE(i.name, '')                                                                                                                    AS 'Index Name'
,           i.type_desc                                                                                                                             AS 'Index Type'
,           i.is_primary_key                                                                                                                        AS 'Primary Key'
,           i.is_disabled                                                                                                                           AS 'Index Disabled'
,           ROUND(CONVERT(FLOAT,SUM(user_seeks)) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_scans),0),1))),2)                                          AS 'Seeks Per Scan'
,           ROUND((CONVERT(FLOAT,SUM(user_seeks) +SUM(user_scans) + SUM(user_lookups))) / (CONVERT(FLOAT,ISNULL(NULLIF(SUM(user_updates),0),1))),2) AS 'Reads Per Write'
,           (SUM(user_seeks) + SUM(user_scans) + SUM(user_lookups))                                                                                 AS 'Reads'
,           SUM(user_updates)                                                                                                                       AS 'Writes'
,           SUM(user_seeks)                                                                                                                         AS 'Seeks'
,           SUM(user_scans)                                                                                                                         AS 'Scan'
,           ROUND(AVG(ps.avg_fragmentation_in_percent),2)                                                                                           AS 'Avg Fragmentation %'
,           NULLIF((SUM(leaf_insert_count) + SUM(leaf_update_count) + SUM(leaf_delete_count)),0) / NULLIF(SUM(leaf_allocation_count),0)             AS 'Leaf Page Splits Per Write'
,           NULLIF((SUM(nonleaf_insert_count) + SUM(nonleaf_update_count) + SUM(nonleaf_delete_count)),0) / NULLIF(SUM(nonleaf_allocation_count),0) AS 'Non-Leaf Page Splits Per Write'
,           CAST (100.0 * SUM(row_lock_wait_count) / (1 + SUM(row_lock_count)) AS NUMERIC(15,2))                                                    AS 'Block %'
,           SUM(row_lock_wait_in_ms)                                                                                                                AS 'Row Lock Wait (ms)'
,           CAST(1.0 * SUM(row_lock_wait_in_ms) / (1 + SUM(row_lock_wait_count)) AS NUMERIC(15,2))                                                  AS 'Average Row Lock Wait'
FROM sys.indexes i
LEFT JOIN sys.objects o ON o.object_id = i.object_id 
LEFT JOIN sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) s ON o.object_id = s.object_id AND i.index_id = s.index_id
LEFT JOIN sys.dm_db_index_usage_stats u ON s.object_id = u.object_id AND u.index_id = s.index_id
LEFT JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps ON u.index_id = ps.index_id AND u.object_id = ps.object_id
WHERE OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 
AND NOT OBJECT_NAME(i.object_id) = 'sysdiagrams' -- exclude sysdiagrams
--AND i.is_primary_key = 0
GROUP BY OBJECT_NAME(i.object_id), COALESCE(i.name, ''),i.type_desc,i.is_primary_key,i.is_disabled
ORDER BY OBJECT_NAME(i.object_id), COALESCE(i.name, '')

这个可以吗？还是SUM行不好？

此外，是否有您认为有助于查找未使用/需要重新编制索引等的索引的任何信息

在阅读我的 70-433 试卷时，我想到了一些我认为行不通的东西，但我相信它行得通。这段话是这样写的：

该列还必须标记为PERSISTED，这意味着 SQL Server 将计算列表达式的结果物理存储在数据行中，而不是每次在查询中引用它时都计算它。

由此我明白了两点：

1. 每次在查询中引用非持久化计算列时都会对其进行计算
2. 因为没有为计算列存储任何内容，所以我假设不能为该列创建索引。

看完后，我觉得这有点奇怪，因为我在之前的项目中设法在非持久化列上创建了索引。

如何为不持久的东西创建索引，从长远来看这是有害的？

为了证明这一点，我运行了以下 SQL 语句：

CREATE TABLE testTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    telephone VARCHAR(14),
    c_areaCode AS (SUBSTRING(telephone,0,5)),
    cp_areaCode AS (SUBSTRING(telephone,0,5)) PERSISTED
)

INSERT INTO testTable VALUES('09823 000000');
INSERT INTO testTable VALUES('09824 000000');
INSERT INTO testTable VALUES('09825 000000');

CREATE NONCLUSTERED INDEX IX_NotPersisted ON testTable(c_areaCode);
CREATE NONCLUSTERED INDEX IX_Persisted ON testTable(cp_areaCode);

然后运行以下查询：

DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL');
DBCC DROPCLEANBUFFERS
GO
SELECT cp_areaCode FROM testTable;
GO
SELECT c_areaCode FROM testTable;

查看上面代码的查询计划后，我可以看到两个选择查询都使用了非持久索引。再说一次，如何？

在阅读了 Slow SQL query, not sure how to optimize之后，我开始思考查询的一般性能。当然，我们需要第一个表的结果（当其他表被连接时）在连接之前尽可能小（这个问题的内部连接），以使我们的查询更快一点。

例如，应该这样：

SELECT *
FROM   ( SELECT * FROM table1 WHERE col = @val ) t
INNER JOIN table2 ON col = col2

比以下更好/更快：

SELECT *
FROM table1
INNER JOIN table2 ON col = col2
WHERE table1.col = @val

我的理论如下（这可能不是正确的实现，我试图从我读过的 SQL Server 2008 内部书籍（MSFT Press）中记住）：

1. 查询处理器首先获取左表（table1）
2. 加入第二个表 (table2) 并在过滤出必要的行之前形成笛卡尔积（如果适用）
3. 然后使用 SEELCT 语句最后执行 WHERE、ORDER BY、GROUP BY、HAVING 子句。

因此，如果在上面的语句#1 中，表更小，则 SQL 引擎在形成笛卡尔积时要做的工作更少。然后，当您到达 where 语句时，您将在内存中筛选出一个缩减的结果集。

我可能离题太远了，这是不真实的。就像我说的，这是一个理论。

你的意见？

注意：我只是想到了这个问题，还没有机会自己进行任何测试。

注2：标记为SQL Server，因为我对MySql等的实现一无所知。请随时回答/评论

我正在为第一支团队在线阅读 SQL Server 书籍，同时学习如何使用服务代理，我遇到了这样的声明：

实现该服务的应用程序读取消息、运行查询并返回结果。所有这三个操作都发生在同一个事务中。如果在事务提交之前发生故障，则整个事务回滚，消息返回队列。当计算机恢复时，应用程序会重新启动并再次处理该消息。

我猜这会以与通常交易相同的方式阻止？IE。行/页锁。我对么？这是我应该担心的事情，还是在我遇到问题之前将其搁置？

继我的另一个问题之后，我想开始考虑在警报方面我应该每天/每周/每月查看哪些内容。我希望能够在问题发生之前看到问题（这是计划）......

到目前为止，我已经开始收集以下脚本（无顺序）：

日常的

• 检查系统正常运行时间（以防我作为 DBA 需要检查任何内容）
• 检查上次备份
• 检查事务日志备份
• 检查 SQL 作业的状态
• 检查过去 24 小时（或 1140 分钟）的平均 CPU 使用率

每周

• 检查 MSDB 备份历史记录
• 查看上次运行 CheckDB 的时间
• 检查索引碎片
• 检查索引统计信息（读取与写入等）
• 检查 IO 瓶颈

月刊

• 检查缺失的索引
• 检查不再使用的索引

还有其他建议吗？（我是 DBA 的新手，所以总是欢迎任何帮助/建议）

我有几个 SQL 脚本，我想每天在我的 SQL Server 上运行。如何多次查询并获得类似于以下内容的输出：

Health check for <server>

Query Title 1:

<query results 1>


Query Title 2:

<query results 2>

我正在检查上次良好备份、正常运行时间等内容，因此它们彼此无关。我计划在 SQL Server 作业中使用 sp_send_db_mail。这是完成这项任务的最佳方式吗？

理想情况下，我只想要一封电子邮件。

更新

经过多年的战斗！到目前为止我找到的解决方案是将其放入存储过程，然后像这样执行存储过程：

EXEC msdb.dbo.sp_send_dbmail
        @recipients = N'<email>',
        @body = @body, 
        @subject = @subjectt,
        @profile_name = @ProfileName,
        @query ='exec test..sp_checks'

这对多个 select 语句可以吗？

我在 SQL Server 2005 中有两个日期时间列，我需要在没有日期时间的时间部分的情况下进行查询。目前，我的查询看起来类似于此（只是一个例子）：

WITH Dates AS ( 
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] < @EndDate
) 
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))

现在这会导致完整的聚簇索引扫描（惊喜，惊喜）。我正在想办法让它更快（实际查询需要 3 分钟 :O）。我曾考虑过执行以下操作，但由于我之前没有时间，所以还没有尝试过：

1. 使用仅包含日期部分的计算列
2. 索引表示计算列（不确定这是否可能？）
3. 使用索引视图（同样，不确定这是否可行，是否可行？）

最简单的方法是更新列并删除所有时间信息，但我不能这样做:(

有任何想法吗？

更新

感谢您到目前为止的所有回答。我认为问题的重点被遗漏了，因为我不太清楚自己想要什么。我的错。我只是想优化查询的日期转换部分，因为我正在处理的数据量实际上很小（在交叉应用一年长的日期范围后 < 500,000）。很抱歉对此造成混淆。

对于那些为我优化其余查询的人，我可以通过使用看到人们在说什么，< >但请考虑以下几点：

1. 传入的参数是一个日期范围（例如1号到本月底）
2. 表中的开始日期可以出现在参数日期范围之前或期间（例如，只有结束日期在日期范围内）
3. 表中的结束日期可以出现在参数日期范围内或之后（例如，只有开始日期在日期范围内）
4. 最后，表中的开始和结束日期在参数日期范围内。

就个人而言，鉴于上述情况，我永远无法找到< >工作的解决方案。我能让它正常工作并且不会遗漏任何东西的唯一方法是使用 CTE 并说明 where d.[Date] BETWEEN t.StartDate AND t.EndDate.

我希望这能解决问题。再次感谢。