Sp_spaceused是随SQL Server发布的一个存储过程,它用来显示SQL Server对象所占用的硬盘空间。但是我往往发现它并不能满足要求。比如,当我想要查看一个特定的SQL Server数据库的用户表大小概况时,或者希望看一下前10个最大的索引对象,或者需要计算一组表所占用的空间总大小时,sp_spaceused并不能做到。 所以,我创建了存储过程sp_SOS,它是sp_spaceused的扩展版本,它可以用来计算SQL Server对象空间和执行其它的功能。
在sp_SOS中我依然保持了sp_spaceused的核心功能——如,计算数据、索引总和的算法,为一个对象保留和……
我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。
我原创,你原创,我们的内容世界才会更加精彩!
【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
![TechTarget微信公众号二维码](/wp-content/uploads/res/images/spriteimg/ttweixin.jpg)
TechTarget
官方微博
![TechTarget中国官方微博二维码](/wp-content/uploads/res/images/spriteimg/ttweibo.jpg)
TechTarget中国
Sp_spaceused是随SQL Server发布的一个存储过程,它用来显示SQL Server对象所占用的硬盘空间。但是我往往发现它并不能满足要求。比如,当我想要查看一个特定的SQL Server数据库的用户表大小概况时,或者希望看一下前10个最大的索引对象,或者需要计算一组表所占用的空间总大小时,sp_spaceused并不能做到。
所以,我创建了存储过程sp_SOS,它是sp_spaceused的扩展版本,它可以用来计算SQL Server对象空间和执行其它的功能。
在sp_SOS中我依然保持了sp_spaceused的核心功能——如,计算数据、索引总和的算法,为一个对象保留和释放空间。同时我还增加了数据库大小计算部分,并将得到一个单独用于数据大小报告的存储程。点击下载代码列表1:sp_SOS的完整T-SQL定义。
Sp_SOS有8个输入参数,见表1。
Variable | Data type | Nullable | Default | Default implication |
@DbName | sysname | Yes | NULL | Current database 当前数据库 |
@SchemaName | sysname | Yes | NULL | All schemas 所有的Schemas |
@ObjectName | sysname | Yes | % | Including all objects in "LIKE" clause 在“LIKE”子句中包含所有对象 |
@TopClause | nvarchar(20) | Yes | NULL | All objects. Can be "TOP N" or "TOP N PERCENT" 所有对象。可以是“TOP N”或“TOP N PERCENT”。 |
@ObjectType | nvarchar(50) | Yes | NULL | All objects that can be sized. Valid values are S(system), U(user), V(indexed view), SQ(service broker queue), IT(internal table) or any combination of them 所有可以计算大小的对象。其中有效值包括S(system,系统),U(user,用户),V(indexed view,索引视图),SQ(service broker queue,服务代理队列),IT(internal table,内部表)或它们的任意组合 |
@ShowInternalTable | nvarchar(3) | Yes | NULL | Includes internal table. The Parent excludes it in size 包括所有内部表。Parent除外。 |
@OrderBy | nvarchar(100) | Yes | NULL | By object name, can be any size related column. Valid short terms are N(name), R(row), T(total), U(used), I(index), D(data), F(free or unused) and Y(type) 对象名排序,可以是任意与大小相关的字段。有效的缩写有N(name),R(row),T(total),U(used),I(index),D(data),F(free or unused)和Y(type)。 |
@UpdateUsage | bit | Yes | 0 | Do not run "DBCC UPDATEUSAGE" 不运行“DBCC UPDATEUSAGE”。 |
表1:sp_SOS的参数变量和它们的特性。
我更喜欢使用类似公式化的样式,以使它更好地解释数字关系。例如,公式“Total(MB) - Unused(MB) == Used(MB) = Index(MB) + Data(MB)”,使用的空间是总大小与未使用大小的差,也是索引与数据的大小之和。sp_spaceused中的“reserved”字段实际上等于sp_SOS的总大小。内部表是SQL Server 2005和SQL Server 2008的一个新概念。它们是父对象处理XML主索引、Service Broker队列、全文索引和查询通知订阅的中间表。
在计算父对象总和时,类型202(xml_index_nodes)和204(fulltext_catalog_map)的内部表应该被加到总大小中。因此,我在sp_SOS中设计了一个包含两个部分的临时基本对象表(##BO)。左半部分包含6个字段表示子对象。而右半部分是父对象。当一个父对象拥有一个子对象,它会显示不同的模型、模型ID、对象名和对象ID。否则,名称和ID是相同的。当一个内部表显示时,它们的父名称显示在括号中以表示清晰的关系。
对象类型的开头与Microsoft SQL Server Books Online一致。它们可以是系统表(S)、用户表(U)、视图(V)、服务队列(SQ)或内部表(IT)。每一种类型是由一个或多个空格、逗号或分号分隔。分隔符的数量和顺序并不重要。如果你使用除了这些允许的分隔符之外的字符时,sp_SOS会报错并退出。另外这个存储过程是兼容Unicode和大小写敏感的。
Sp_SOS可以运行在SQL Server 2000、2005和2008上。在SQL Server 2000中,sp_SOS报告的值是不可以更新的。@UpdateUsage参数可以为每一次运行指定一个“DBCC UPDATEUSAGE”以确保这些值是下面报告的当前值。但是要注意它可能影响大型数据库的性能。从SQL Server 2005开始,sp_spaceused总是报告正确的数值,它使DBCC命令不再有用了。
以下是一些如何使用sp_SOS的场景的典型说明:
@DbName是你想要在SQL Server中查找对象空间的数据库名称。如果没有数据库名,它将会使用当前数据库。比如,如果你想快速地查看AdventureWorks数据库中的所有数据库对象空间,你可以运行列表2中的T-SQL语句。
USE AdventureWorks; EXEC dbo.sp_SOS; |
列表2:AdventureWorks数据库中的所有对象占用空间概况。注意所有的参数都是默认值。执行结果显示所有按字母排序的模型对象。
@SchemaName和@ObjectName这两个参数都将通配符%作为默认值。这让你对有类似模式名称或拥有者名称的对象组进行求和。我们仍然使用AdventureWorks作为示例数据库,而我们想要按硬盘空间使用状况列出所有类似于“Sales”的Sales模型对象。我们还不想显示内部表。我喜欢先运行“DBCC UPDATEUSAGE”更新任何不正确的值。其中使用的T-SQL语句类似于代码列表3。
sp_SOS 'AdventureWorks', 'Sales%', 'Sales%', NULL, ' SQ,;u v ;iT;', 'no',
'U', 1
列表3:列出Sales模型所拥有名称类似于“Sales”的对象,按硬盘使用空间降序排列。
类似于列表3,在列表4中的命令可以获取特定对象的大小信息。注意其中大小表示对象属性GUI显示值和sp_SOS或sp_spaceused返回值的差。此外,注意补充的父对象名在XML索引后面以表示它们的关系。
sp_SOS 'AdventureWorks', NULL, 'xml_index_nodes_309576141_32000', NULL, 'IT',
'yes', 'N', 0
列表4:检查一个特定对象的使用空间。因为它是一个内部表,因此模型名被忽略了。显然一个对象不需要排序,所以@OrderBy参数也被忽略了。
下面是在我管理的一个SQL Server 2000数据库中sp_SOS运行的2个屏幕截图:
1、你可以在图1中看到2组表,ARCHIVED_HISTORY和HISTORY。我经常需要计算HISTORY表的总和。通过sp_SOS,我可以在结果最后计算值的总和。图2显示每一个相关表上执行DBCC UPDATEUSAGE的详细信息。箭头(= =>)表示实际的命令,后面是更新的明细。
图1:sp_SOS显示了一个SQL Server 2000用户数据库的分组用户表,它们有相似的名称和统计空间大小。
图2:SQL Server 2000 Query Analyzer显示sp_SOS代码段以及“DBCC UPDATEUSAGE”命令的详细结果。这时,大多数的对象需要更新它们的大小信息。
最后,这是sp_SOS在SQL Server 2008 CTP的运行情况。使用列表5的脚本,我们可以得到图3显示的结果。
列表5:在SQL Server 2008 CTP上的AdventureWorks2008数据库根据对象类型顺序对所有用户表、视图、内部表和服务队列排序。最后运行DBCC UPDATEUSAGE,同时显示内部对象。
图3:兼容SQL Server 2008 CTP的sp_SOS在AdventureWorks2008数据库运行后显示根据对象类型排序的所有对象列表。
我不能一一解释sp_SOS执行的所有不同的参数设置。如果你发现一些有趣的或与预期行为有冲突的结果时,请给我发一个评论,我将会检查一下看是否能再改进。
你可以阅读我接下来的关于存储过程sp_SDS文章。sp_SDS不仅仅能确定“SQL数据空间”,它也可以用于监控数据的增长,并在数据或日志文件增长时提醒DBA,执行一个日志备份事务,甚至提供文件级的详细分析,这样DBA就可以压缩文件以获取更多空余空间。
![Richard Ding](http://img.techtarget.com.cn/articlepic/2009-01-17-15-16-06.jpg)
Richard Ding,波士顿东北大学数据库管理员,他从1990年代后期就开始进行SQL Server相关工作。他的兴趣包括数据库管理、T-SQL开发、灾难恢复、问题复现和性能优化等。他为几个杂志撰写关于SQL Server产品的文章,包括《SQL Server Magazine》和《SQL Server Standard》。他是一个SearchSQLServer.com的贡献者,也是各种SQL Server在线论坛的会员。尽管之前获取了医学学位和生物科学博士学位,Richard仍然很热衷于成为一名有创造力的SQL Server专家。你可以通过电子邮件与他联系:rding@rcn.com。
作者
翻译
TechTarget中国特约技术编辑,某高校计算机科学专业教师和网络实验室负责人,曾任职某网络国际厂商,关注数据中心、开发运维、数据库及软件开发技术。有多本关于思科数据中心和虚拟化技术的译著,如《思科绿色数据中心建设与管理》和《基于IP的能源管理》等。
相关推荐
-
云端SQL Server高可用性最佳做法
与内部部署相比,在云端运行SQL Server可为数据库软件用户提供更多的灵活性和可扩展性,也可能更省钱。但云 […]
-
绘制数据关系图的利器:SQL Server 图像数据库工具
SQL Server 2017新增了图形数据库功能,你可以使用图结构来表示不同数据元素之间的关系。
-
如何在Azure部署时选择合适的SQL Server?
想要在Azure上运行SQL Server,企业一般会面临两种选择:在Azure虚拟机上安装SQL Server或使用Azure SQL Database。
-
Linux支持的引入 推动了SQL Server 2016集成服务的发展
随着SQL Server的不断发展,集成服务也在发生相应的变化。在最新的SSIS更新中,增加Linux支持和SQL Server 2016升级向导。