sp_spaceused_如何使用sp_spaceused和PowerShell捕获数据库使用情况统计信息

sp_spaceused

Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique challenges though and the database administrator needs to balance the key business and technical metrics of the environment. One of the key technical metrics is disk space estimation, which is vital to capacity planning and forecasting. The simplest way to get this information is by using the system stored procedure, called, sp_spaceused. The growth metrics can be captured periodically and stored in a central repository, a repository dedicated to helping with capacity planning and forecasting the disk requirements. This is a critical part of the day-to-day activities of a database administrator.

当今的企业需要处理大量数据。 有时，了解数据的粒度有助于数据管理。 但是，了解粒度带来了独特的挑战，数据库管理员需要平衡环境的关键业务和技术指标。 关键技术指标之一是磁盘空间估计，这对容量规划和预测至关重要。 获取此信息的最简单方法是使用称为sp_spaceused的系统存储过程。 可以定期捕获增长指标并将其存储在中央存储库中，该存储库专用于帮助进行容量规划和预测磁盘需求。 这是数据库管理员日常活动的关键部分。

介绍 (Introduction)

The objective of this article is to look at the possible ways of capturing database usage trends using the most commonly available tools and techniques. The integration of SQL, the use of sp_spaceused, and the flexibility offered by PowerShell have been used to get the required database growth patterns; this data is combined into a single result. After the transformation is done, the data is stored in a dedicated repository.

本文的目的是研究使用最常用的工具和技术来捕获数据库使用趋势的可能方法。 SQL的集成，sp_spaceused的使用以及PowerShell提供的灵活性已用于获取所需的数据库增长模式。 此数据合并为一个结果。 转换完成后，数据将存储在专用存储库中。

背景 (Background)

The output of the stored procedure, sp_spaceused, is a pair of result sets when the @objname parameter is omitted. The stored procedure accepts the @oneresultset parameter starting from SQL 2016, therefore, the result can be viewed in a single result pane. The simulation can be derived using DMVs but the result may vary in terms of numbers, sometimes, based on the use of complex design and data structures. This introduces a level of complexity in the process. To overcomes, this, we use PowerShell with SQL, in order to integrate the two result-sets into one and seamlessly executing the scripts across several servers. The generated stream of data is converted into JSON, the lightweight data interchange format. Using JSON helps simplify the integration of the output into a dedicated repository for data transformation.

当省略@objname参数时，存储过程的输出sp_spaceused是一对结果集。 存储过程从SQL 2016开始接受@oneresultset参数，因此，可以在单个结果窗格中查看结果。 可以使用DMV进行仿真，但有时结果可能会因使用复杂的设计和数据结构而在数量方面有所不同。 这在过程中引入了一定程度的复杂性。 为了克服这个问题，我们将PowerShell与SQL结合使用，以便将两个结果集集成到一个中，并在多个服务器上无缝执行脚本。 生成的数据流将转换为JSON（轻量级数据交换格式）。 使用JSON有助于简化将输出集成到专用存储库中以进行数据转换的过程。

Through this article, let’s learn how to integrate SQL and PowerShell to execute stored procedures, as well as the techniques to transform the output into the desired form which focuses on the database usage trends.

通过本文，我们将学习如何集成SQL和PowerShell以执行存储过程，以及将输出转换为所需形式（着重于数据库使用趋势）的技术。

We shall discover:

我们将发现：

• the workings of sp_spaceused,
• sp_spaceused的工作原理，
• how the stored procedure is executed to transform and merge the result,
  如何执行存储过程以转换和合并结果，
• loading of the JSON data,
  加载JSON数据，
• the available JSON constructs,
  可用的JSON构造，
• and more…
  和更多…

入门 (Getting Started)

sp_spaceused存储过程 (The sp_spaceused stored procedure)

sp_spaceused is the most common stored procedure used by any database administrator to check the database space usage details. It displays the number of rows, the disk space reserved, and the disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

sp_spaceused是任何数据库管理员用来检查数据库空间使用情况详细信息的最常见的存储过程。 它显示行数，保留的磁盘空间以及当前数据库中的表，索引视图或Service Broker队列使用的磁盘空间，或者显示整个数据库保留和使用的磁盘空间。

If objname is omitted, the following result sets are returned with the current database size information.

如果省略objname ，则返回以下结果集以及当前数据库大小信息。

权限 (Permissions