《SQL Server企业级平台管理实践》读书笔记——SQL Server数据库文件分配方式

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
日志服务 SLS,月写入数据量 50GB 1个月
简介: 原文:《SQL Server企业级平台管理实践》读书笔记——SQL Server数据库文件分配方式1、文件分配方式以及文件空间检查方法 最常用的检查数据文件和表大小的命令就是:sp_spaceused 此命令有三个缺陷:1、无法直观的看出每个数据文件和日志文件的使用情况。
原文: 《SQL Server企业级平台管理实践》读书笔记——SQL Server数据库文件分配方式

1、文件分配方式以及文件空间检查方法

最常用的检查数据文件和表大小的命令就是:sp_spaceused

此命令有三个缺陷:1、无法直观的看出每个数据文件和日志文件的使用情况。2、这个存储过程依赖SQL Server存储在一些系统视图里的空间使用统计信息计算出的结果,如果没有更新空间统计信息,比如刚刚发生大数据插入,sp_spaceused的结果就不准确。3、这个命令主要是针对普通用户的数据库,对于tempdb数据库里存储的一些系统临时数据对象,是无法用这个存储过程来统计的。

可以通过“sp_helptext sp_spaceused”命令查看该存储过程明细。

SQL Server的文件分为数据文件(.mdf,.ndf)和日志文件(.ldf)两种。不同的文件查看方式不同。

数据内容以页形式存储,可以通过DBCC PAGE命令查看页内容

DBCC PAGE(<db_id>,<file_id>,<page_id>,<forma_id>)

比如我们要看AdventureWorks这个数据库里面的AdventureWorks_Data数据文件里的第3230页。我们先从db_id可以从sp_helpdb的结果中得到;file_id可以从sp_helpfile的结果中得到。

Format_id是指定的输出格式,有1、2、3三个值。一般来讲,3这个输出格式比较直观。在运行DBCC PAGE之前,还需要打开跟踪标识(trace flag)3604.

我们来查看明细:

 这里有几个信息比较重要,比如ObjectID=133575514、IndexId=1

它告诉我们,这个页面属于133575514这个对象,ID为1的索引(index)。通过下列语句可以得到它属于什么对象。

select s.name,o.name
from sys.sysobjects o 
inner join sys.schemas s
on o.uid=s.schema_id
where o.id=133575514
go
select id,indid,name
from sys.sysindexes
where id=133575514 and  indid=1
go exec sp_helpindex N'DimProduct' go

我们可以看到此页面属于DimProduct表、然后这个表下面的索引:PK_DimProduct_ProductKey,这个索引建立在ProductKey列上

可以通过下面的命令来查看数据库中某张表有那些区,该区中第一个页面的ID是多少:

上面的这张表用到了这么多区的内容,并且每个区中大部分存储为8个的连续页,只有第一个为1个页,页面的ID为217。

大型行的支持

在SQL Server中,行不能跨页,属于同一行的所有字段的数据都要放在同一个页面里,页的最大数据量是8064B(8KB)。所以一般数据类型字段所组成的一行,最长加起来不能超过8KB,但这个限制不包括varchar(max)、nvarchar(max)、varbinary(max)这样的数据类型,在2005以后版本中这种大类型数据字段突破了8KB的限制,方便了用户的使用,如果不超过8KB,数据还是会一起存放在普通数据页里面。如果总长度超过了8KB,SQL Server就会把这些字段分开,单独存放在一种叫做Row-Overflow(行溢出)的页面里。

数据文件空间使用的计算方法

 查看数据库使用的最简单的方法,就是在Management Studio中,右键点击数据库名称,选择报表项中的标准报表进行查看:

这里的统计结果和sp_spaceused统计的结果稍有不同,原因是这里是按照区统计的,由于每个区里面就有8个页,而这8个页不一定全部被使用到了,但是如果按照区统计默认的就是全部被使用了,而按照页统计的结果就不一样了。

两种统计方法不同,使用场景也不一样,按照区统计结果速度快

按照区统计:

如果按照区统计我们使用一个命令就可以实现

DBCC SHOWFILESTATS

由于SQL Server在绝大多数时间都是按照区为单位进行分配新空间的,而系统分配页上的信息永远是实时更新的,所以按照这种统计方法比较准确可靠。在服务器负载很高的情况下也能安全执行,不会增加额外的系统负担。所以要考察数据库数据文件级的使用情况,它是个比较好的选择。

按照页面统计

如果想要知道某个具体的表格或者索引使用了多少空间,就要从页面级别进行分析。这里我们就可以选择sp_spaceused或者DBCC SHOWCONTIG

当然在SQL Server2005以后新加了一个sys.dm_db_index_physical_stats来查看索引的存储明细,包括页多少,锁片百分比等

sp_spaceused是根据sys.allocation_units和sys.partitions这两张管理视图来计算存储空间的。当删除索引后、表的空间信息不实当前信息时,这两张表可能不能及时反映出数据库的准确信息。

可以加入Undateusage这个参数,要求SQL Server为这句指令更新管理视图里的统计信息。这样做会件消耗资源的工作。在生产坏境下建议不要轻易使用。

sp_spaceused的另一个缺点一次只能查询一个对象,不能对所有表一次性查找,我们可以通过sys.dm_db_partition_stats视图来实现这件事情:

select o.name,
       SUM(p.reserved_page_count) as reserved_page_count,
       SUM(p.used_page_count) as used_page_count,
       SUM(
           case when (p.index_id<2) then (p.in_row_data_page_count+
           p.lob_used_page_count+p.row_overflow_used_page_count)
           else p.lob_used_page_count+p.row_overflow_used_page_count
           end 
           )as DataPages,
           SUM(
               case when (p.index_id<2) then row_count
               else 0
               end 
               ) as rowCounts
from sys.dm_db_partition_stats p inner join sys.objects o
on p.object_id=o.object_id
group by o.name

sp_spaceused方法简单,但功能比较脆弱,也不是最准确的方法。不特别推荐使用。

sys.dm_db_partition_stats会来的更直接,对系统性能也没有多少影响。

DBCC SHOWCONTIG(或者sys.dm_db_index_physical_stats)是检查数据库空间分配最准确的方法,它可以展现用了多少页面、多少区、甚至页面上的平均数据量。但是也会付出性能上的代价,SQL Server从整体性能角度出发,不可能一直维护这样的底层统计信息。为了完成这个命令,就必须对库进行扫描。总之得到的结果越精确,扫描的范围就越大。

总之:如果管理者只要看数据文件的整体使用情况,DBCC SHOWFILESTATS是比较好的选择。如果要看每个对象的空间使用情况,可以使用动态管理视图sys.dm_db_partition_stats。如果想要了解每个页、每个区的使用情况、碎片程度,那DBCC SHOWCONTIG是比较好的选择。

日志文件

数据库的日志文件是包含用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件,当然也可以有多个。日志文件的推荐文件扩展名是.ldf。与数据库文件按8KB组织不同,日志文件不是按照页、区来组织的。

SQL Server数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元。虚拟日志单元没有固定大小,且一个物理日志文件所包含的虚拟日志单元数不固定。管理员不能配置或设置虚拟日志单元的大小或数量。但SQL Server会尝试控制虚拟日志单元的数目,把它限制在一个合理的范围内,但是有种情况特殊,日志文件每自动增长一次,会至少增加一个虚拟日志单元。所以,如果一个日志文件经历了多次小的自动增长,里面的虚拟日志单元数目会比正常的日志文件多很多。这种情况会影响到日志文件管理的效率,甚至造成数据库启动要花很长时间。

事务日志是一种回绕的文件。例如,假设有一个数据库,它包含一个分成5个虚拟日志单元的物理日志文件。当创建数据库时,逻辑日志文件从物理日志文件的始端开始。新日志记录被添加到逻辑日志的末端,然后向物理日志的末端扩展。

当逻辑日志的末端到达物理日志文件的末端时,新的日志记录将回绕到物理日志文件的始端,继续向后写

查看日志文件的使用情况非常简单。我们通过下面这个语句查看:

DBCC SQLPERF(LOGSPACE)

上图显示了SQL Server上所有数据库的日志大小,以及使用比率。语句的执行不会对SQL Server带来负担,这个语句返回的结果总是正确的。可以随时在SQL Server上运行这个命令。 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
10天前
|
SQL 数据库 数据安全/隐私保护
数据库数据恢复——sql server数据库被加密的数据恢复案例
SQL server数据库数据故障: SQL server数据库被加密,无法使用。 数据库MDF、LDF、log日志文件名字被篡改。 数据库备份被加密,文件名字被篡改。
|
28天前
|
SQL Java 数据库连接
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
|
2天前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
2天前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
24天前
|
SQL 数据库连接 Linux
数据库编程:在PHP环境下使用SQL Server的方法。
看看你吧,就像一个调皮的小丑鱼在一片广阔的数据库海洋中游弋,一路上吞下大小数据如同海中的珍珠。不管有多少难关,只要记住这个流程,剩下的就只是探索未知的乐趣,沉浸在这个充满挑战的数据库海洋中。
41 16
|
27天前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
29天前
|
SQL Java 数据库连接
【YashanDB 知识库】解决 mybatis 的 mapper 文件 sql 语句结尾加分号";"报错
【YashanDB 知识库】解决 mybatis 的 mapper 文件 sql 语句结尾加分号";"报错
|
1月前
|
SQL 关系型数据库 网络安全
Navicat Premium 17 最新版下载与配置:5分钟完成企业级数据库工具部署
Navicat Premium 17 是一款支持多种主流数据库(如 MySQL、Oracle、PostgreSQL 等)的多数据库管理工具,提供可视化数据建模、SQL 编辑和数据同步等功能。试用版提供 14 天全功能体验,商业版支持跨平台使用。安装环境要求 Windows 10/11 或 macOS 12.0+,最低配置为 4GB 内存。下载并解压安装包后,按步骤启动安装程序、接受许可协议、自定义安装路径并完成安装。首次运行时需激活许可证并配置数据库连接。常见问题包括无法写入注册表、试用期续费及连接数据库权限问题。高级功能涵盖 SSH 通道加速、自动化任务调度和性能调优建议。
247 19
|
2月前
|
SQL 数据库
数据库数据恢复—SQL Server报错“错误 823”的数据恢复案例
SQL Server数据库附加数据库过程中比较常见的报错是“错误 823”,附加数据库失败。 如果数据库有备份则只需还原备份即可。但是如果没有备份,备份时间太久,或者其他原因导致备份不可用,那么就需要通过专业手段对数据库进行数据恢复。
|
2月前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
110 9

热门文章

最新文章