最常用的数据库脚本前十名

最常用的数据库脚本前十名

 

英文原文：

https://www.simple-talk.com/sql/t-sql-programming/top-10-most-common-database-scripts/

 

什么脚本DBA最常用而称为肌肉记忆的一部分？Grant Fritchey在SQL Server Central论坛问这个问题。从大量的反馈中，Grant找出了最受欢迎的T-SQL命令、脚本或代码片段的前十名。看上去好像，除了这些外，还有些常用工具。

你或许没有DBA的称谓，但是你和我都知道你是负责数据库的人，你也可能有这个称谓。你会认为自己是一个偶然的DBA、一个不情愿的DBA、一个经常性的DBA或甚至是一个被迫的DBA，但是无论如何你是本地SQL Server实例的负责人。你可能得到了很少的帮助。

有大量的文档描述关于如何配置备份或维护数据库、索引、统计信息、日志等等，并且大量的工作会被自动化。然而，你正要反复去手动运行的日常T-SQL命令是什么呢？

我通过大量论坛在线问了如下问题：

你常输入的、现在称为你第二本能的前五个T-SQL命令、脚本、代码片段是什么？

我收到许多回复，以及大量好的脚本。我也请求每个人只提交使用本地命令的脚本，而不是他们喜爱的第三方脚本或工具。几个脚本浮出水面，这里是前十名：

1. sp_who2 / sys.dm_exec_request / sp_whoisactive

2. STATISTICS IO/TIME

3. BACKUP DATABASE

4. sp_help

5. DBCC SQLPERF

6. sys.dm_exec_query_stats

7. RESTORE DATABASE

8. RESTORE FILELISTONLY

9. sp_spaceused

10. DBCC SHOW_STATISTICS

如果你是一个新手DBA，或者偶尔是，这些你从现在开始需要记住，因为你会频繁使用到。

#1 sp_who2 / sys.dm_exec_requests / sp_whoisactive

因为一些原因，我将这三个脚本放在了一起。当我非常明显的问人们不要提交第三方脚本时，Adam Machanic的sp_whoisactive反复被提及，因此我决定将它放在该列表中。

sp_whoisactive脚本使用大量的DMV调查当前系统上的活动。突出显示慢查询和阻塞。他利用了sys.dm_exec_requests动态管理视图（DMV），因此我将他们放在一起。sp_who2是查询系统当前活动的老方法，包含在这里是因为我想人们停止使用它。而使用sys.dm_exec_requests和相关的DMV来代替找到信息（或者sp_whoisactive）。

有很多方法将DMV中的信息关联到一起输出。下面的示例显示了当前系统正在运行什么，查询文本和执行计划。

SELECT *
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO

像这样准备一个简单的脚本，你可以快速和容易地看到你通过sp_who2看到的一切。使用相同的数据（相同的FROM从句），你可以开始以有趣的方式拆分它。这里有个示例，我不希望你去记住：

SELECT SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
( CASE der.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE der.statement_end_offset
- der.statement_start_offset
END ) / 2 + 1) AS querystatement ,
deqp.query_plan ,
der.session_id ,
der.start_time ,
der.status ,
DB_NAME(der.database_id) AS DBName ,
USER_NAME(der.user_id) AS UserName ,
der.blocking_session_id ,
der.wait_type ,
der.wait_time ,
der.wait_resource ,
der.last_wait_type ,
der.cpu_time ,
der.total_elapsed_time ,
der.reads ,
der.writes
FROM sys.dm_exec_requests AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;
GO

#2 SET STATISTICS IO/TIME

坦率的讲，我有些惊讶看到SET STATISTICS IO / TIME命令如此频繁的出现。个人来说，我在意识到它们会曲解分析后的查询性能时，我停止使用了。我使用扩展事件来捕获查询指标代理，因为我发现它们对性能影响低，并提供了更多精确的指标。

不过，对于简单易行的测试，这些命令提供了有价值的信息，并收到欢迎。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
...
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

将省略号替换为你的查询，你看在SSMS查询窗口的Messsages输出看到写入了一些信息，查询的时间花费和查询导致的读写。

(2636 row(s) affected)
Table 'SalesOrderDetail'. Scan count 449, logical reads 1834, physical reads 3, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 321 ms.

对每个表相关的重点关注“logical reads”，对查询重点关注“elapsed time”。

#3 BACKUP DATABASE

我并不惊讶BACKUP DATABASE会出现在表单中，事实上，我期待它出现在第1位，而不是第3位。当你正在自动化生产数据库备份，而不用你直接参与，需要创建一些额外数据库备份是极其常见的。

例如，当部署新的对象到数据库之前，你可能想要一个“fallback”选项，或者对于某些额外测试，创建QA数据库的副本。

BACKUP DATABASE AdventureWorks2014
TO DISK = 'D:\bu\adw.bak'
WITH COPY_ONLY;

这是一个直接的数据库备份命令。我唯一添加的参数是使用WITH COPY_ONLY从句。因为这是一个即席备份，我不想它影响随后运行的任何DIFFERENTIAL备份。

只有少数人将BACUP LOG列为一个普遍运行的命令。可能是因为它不是很多人手动去做，因此，谈到常用脚本时并没有被提及。

#4 sp_help

基于sp_help频繁的出现在调查中，人们花费大量的时间可能不是设计或创建数据库。sp_help以及相关系列命令，例如sp_helpdb，sp_helpindex和sp_helpfile，可以让我们收集目标对象的信息，针对不同的对象运行sp_help将会获得不同的结果，显示这些对象的大量详细数据。例如，在一个表上运行sp_help。

sp_help 'Sales.SalesOrderHeader';

下面的命令返回表的大量信息，你可以在图中看到。

你可以在顶部看到基本信息，描述了表，表的所有者和表创建的数据。在那下面，会看到详细信息。第二部分显示了列，例如IDENTITY列和它的定义。

存储，索引和约束都详细列出，最后列出表的外键参照信息。

如果你不确定一个数据库或它的设计，sp_help是简单易用的，最重要的使用它以编程的方式识别对象的详细信息。

#5 DBCC SQLPERF

惊讶的是大多数DBA选择它，曾经在日志文件大小快速增长的时候。可能会导致日志备份的延时，或不频繁的日志备份，或某些其他问题，例如长时间运行或孤立事务阻止了存在的日志空间的重用。

DBCC SQLPERF对每个数据库显示了日志文件的小小和当前使用的日志空间的百分比。这会允许你快速访问需要更多日志空间的数据库，多少日志空间正在被使用，如果有只是太大了。

执行它很容易：

DBCC SQLPERF (LOGSPACE);

输出结果非常容易理解：

此外，你也可以使用DBCC SQLPERF重置收集到的等待和闩锁，通过命令清空它们。

DBCC SQLPERF("sys.dm_os_latch_stats" , CLEAR);
DBCC SQLPERF("sys.dm_os_wait_stats" , CLEAR);

只需记住，这些统计信息完全重置为0，因此你运行以上命令后将没有历史跟踪信息。

#6 sys.dm_exec_query_stats

如果说从sys.dm_exec_requests可以找出当前服务器正在运行什么请求，那么从sys.dm_exec_query_stats你可以找到聚合的，收集之前活动的实例级别的数据。

它显示了查询、存储过程和批处理的语句的性能指标的聚合。然而，这些信息只保留当前存储在缓存中的查询。只要查询离开了缓存，信息就会完全消失。如果查询返回缓存，会重新收集指标数据。你使用与sys.dm_exec_requests类似的方式使用这个DMV。

SELECT SUBSTRING(dest.text, ( deqs.statement_start_offset / 2 ) + 1,
( CASE deqs.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
- deqs.statement_start_offset
END ) / 2 + 1) AS querystatement ,
deqp.query_plan ,
deqs.execution_count ,
deqs.total_worker_time ,
deqs.total_logical_reads ,
deqs.total_elapsed_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp;

与sys.dm_exec_query_stats相比，尤其当与其他DMV关联时，可以提供有意义、有用的信息，但是只需记住这些是聚合的，因此不会显示独立的参数值或者行计数，或其他查询单独执行相关结果。

你也可以通过sys.dm_exec_procedure_stats查看一些存储过程相关信息。

#7 RESTORE DATABASE

一方面，我相当惊奇的看到RESTORE DATABASE在这个名单中如此靠后。另一方面，当我考虑我已经做的备份的数量和使用这些备份来恢复一个数据库的次数，是有意义的。再则，我并不惊讶于这个表单，因为我们不得不恢复一个或多个备份。

有大量的文章写到RESTORE过程的细节。下面显示了RESTORE的使用，从备份文件创建一个新的数据库。

RESTORE DATABASE ADW
FROM DISK = 'D:\bu\adw.bak'
WITH MOVE 'AdventureWorks2014_Data' TO 'E:\data\adwnew.mdb',
MOVE 'AdventureWorks2014_Log' TO 'F:\log\adwnewlog.ldb',
NORECOVERY;

对大多数DBA来说，这些命令基本是本能反应。你得定义要恢复哪个数据库，然后你得处理这些文件。在这里，我移动数据到一个新的位置，并使用新的名称，为了能恢复一个数据库到相同实例，作为已经存在数据库的一个拷贝。最后，我使用NORECOVERY选项，在我开放数据库给人们之前，我可以验证RESTORE过程。最后一步，我只需运行RESTORE...WITH RECOVERY。

RESTORE DATABASE ADW
WITH RECOVERY;
GO

#8 RESTORE FILELISTONLY

与RESTORE DATABASE近似的是RESTORE FILELISTONLY。如果你要运行RESTORE，你要知道逻辑文件名和文件的物理位置。进一步，你要知道你有多少文件。因为数据库可以有多个数据文件，所有这些必须用于一次RESTORE（不用累积多次的RESTORE）。语法非常简单。

RESTORE FILELISTONLY
FROM DISK = 'c:\bu\adw.bak';

结果非常详细。我只显示了详细结果的子集：

LogicalName	PhysicalName	Type	FileGroupName	Size
AdventureWorks2014_Data	E:\data\adw_data.mdf	D	PRIMARY	282329088
AdventureWorks2014_Log	F:\log\adw_log.ldf	L	NULL	102760448

你会看到所有你需要反馈到RESTORE DATABASE操作的。

#9 sp_spaceused

作为一个DBA你要做的主要工作之一是管理磁盘上的文件并担心磁盘空间。你需要考虑的方式之一是调查独立对象，表和索引，那些占用磁盘空间的。于是sp_spaceused来到手头。

EXEC sys.sp_spaceused @objname = N'Sales.SalesOrderHeader';

结果容易理解。

如果我们不提供一个对象名称，例如，只运行EXEC sys.sp_spaceused，然后我们会看到完整数据库的结果，返回非常不一样的结果集合。

一个非常值得的额外参数是@updateusage，当设置为true，将会运行DBCC UPDATUSAGE。它会重新扫描存储数据的系统视图，并更新页和行计数，以便报告更加准确的大小和空间信息。

例如，下面返回SalesOrderheader表的sys.sp_spaceused。

EXEC sys.sp_spaceused @objname = N'Sales.SalesOrderHeader',
@updateusage = 'true';
GO

我会得到一个稍微不同的结果：

我们看到了一个稍微不同的值的表单；注意到reserved，index_size和unused值变化了。运行DBCC UPDATUSAGE会导致额外的系统I/O负载，因此谨慎行事。

很多人停止使用sp_spaceused，更喜欢直接查询系统表和DMV中的对象使用信息。然而，我的调查结果表名sp_spaceused的受欢迎程度。

#10 DBCC SHOW_STATISTICS

排名第10的是DBCC SHOW_STATISTICS。我再次惊讶它会排名如此靠后。也许我不得不处理太多次参数嗅探。也许我只是做了大量的索引和查询优化。我发现我的表和索引的统计信息比我的同事更多。但是显然，他们常查询它因此这个命令上了表单。

如果你想看到SQL Server如何深入数据到优化器，统计信息给出了答案。获取它们的语法相当清晰。

DBCC SHOW_STATISTICS('Sales.SalesOrderHeader',
'PK_SalesOrderHeader_SalesOrderID');

你只需要提供表名和你感兴趣的统计信息的集合的名字。在这个示例中，我正关注主键。结果分为三个部分。

顶部提供了一些普通信息。接下来是密度图显示了列或生成统计信息集合的列组的唯一性或可选择性。最后，你有柱状图，直到200步显示第一列的数据分布，尽管有许多列在统计信息集合中。我选择显示表的主键，除了表中有31465行外，柱状图只显示了两步。这是因为这些行的每个单一值对于主键值是唯一的，因此分布不需要200步去显示数据如何展示。

总结

如果任何脚本出现在最普遍的数据库脚本前十名都不惊讶。我所关心的是人们如何使用这些脚本的变化，成为他们自己的版本。每个人有共同的任务。每个人的任务是唯一的。每个人找到新的方法完成任务，但大多数人使用普遍的工具集。

我希望这些脚本对于DBA有帮助。