blocks|key|2145454|text|数据保存在磁盘上以数据库命名的目录中。这包括所有的东西，包括你没有测量的东西(指数等)。测量一下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2145455|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The data is saved on disk in directories named for the database. That includes everything, including what you're not measuring (indices, etc.). Measure that.

blocks|key|4256712|text|您可以用一个相当复杂的公式来计算一行的大小(行数)。有关确切的详细信息，请参阅Books+Online+(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/81fd5ec9-ce0f-4c2c-8ba0-6c483cea6c75.htm)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4256713|简而言之：|4256714|获取列数，并根据固定长度数据类型的数据类型确定每列的大小。|4256715|计算任何可为空的列使用的空间，然后计算行空间：|4256716|Row_Size+=+Fixed_Data_Size+%2B+Variable_Data_Size+%2B+Null_Bitmap+%2B4|4256717|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms189124.aspx^0|13|C|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|10|8|@]|9|@]|C|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|M|$N|$5|O|P|Q|C|$R|S]]]]

You can calculate the size a row (of number of rows) with a fairly complex formula. See <a href="http://msdn.microsoft.com/en-us/library/ms189124.aspx" rel="noreferrer">Books Online</a> (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/81fd5ec9-ce0f-4c2c-8ba0-6c483cea6c75.htm) for the exact details.

In short:

Take the number of columns and determine the size of each column by its datatype for fixed-length datatypes.

Calculate the space used for any nullable columns
Then calculate the rowspace:

Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4

blocks|key|4256719|text|使用您的表名运行DBCC+SHOWCONTIG|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4256720|dbcc+showcontig+('TableName')+with+tableresults|code-block|syntax|javascript|4256721|然后查看最大最小值和平均记录大小|4256722|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms175008.aspx^0|8|F|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Run <a href="http://msdn.microsoft.com/en-us/library/ms175008.aspx" rel="noreferrer">DBCC SHOWCONTIG</a> with your table name

<pre><code>dbcc showcontig ('TableName') with tableresults
</code></pre>

then look at max min and average record size

blocks|key|1690396|text|下面的查询将通过查看数据库的对象分配了多少页来告诉您数据正在使用的@SpaceAllotted的百分比。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1690397|不值得尝试在行级测量使用情况，因为SQL+Server在区级分配所有空间。每个区段由8个8KB数据页组成。因此，如果您的数据库只有一行，而该行有4个字节的数据，那么仍然需要分配整个区段来容纳这4个字节(或者使用具有未分配页的现有区段)。这称为混合范围)。|1690398|DECLARE+@SpaceAllotted++++++FLOAT
--+2MB+converted+to+kilobytes...
SET+++++@SpaceAllotted+=+2048

SELECT+
++++--+Allocation+is+done+on+the+extent-level.
++++--+Each+extent+contains+eight+8KB+data+pages.
++++((1+/+(@SpaceAllotted))+*+CEILING(CAST(SUM([ips].[page_count])+AS+FLOAT)+/+8)+*+64)+*+100+AS+PercentageUsed
FROM
++++[sys].[dm_db_index_physical_stats](DB_ID(),+NULL,+NULL,+NULL,+NULL)+ips
++++--+This+will+allow+us+to+retrieve+the+page+count+of+all+tables+in+the
++++--+current+database,+regardless+of+whether+or+not+they+have+clustered
++++--+indexes+and/or+non-clustered+indexes.
INNER+JOIN
++++[sys].[indexes]+i
++++ON
++++++++[ips].[object_id]+=+[i].[object_id]
++++AND+[ips].[index_id]+=+[i].[index_id]|code-block|syntax|javascript|1690399|由于我们确实有混合区段的可能性，并且没有好的方法(有一些方法，但它们不会很好)来确定将哪些页分配给哪些区段，所以这不是100%25准确的。此外，区段甚至可能有空闲的页面(这些页面是保留的，因此仍然占用磁盘空间)，因此通常情况下，这个估计值总是很低。但是，这可能是您在不编写任何东西来检查页面级数据库的情况下获得的最好结果。|1690400|啊，是的，看看其他答案，这是另一个选择。这将基本上查看页面中所有数据文件的当前大小，并确定它们占用的空间百分比。现在，这里也有一些注意事项...|1690401|1690402|如果没有为数据库指定最大大小(自动增长已启用且不受限制)，则不会执行此操作，因为max_size将作为-1.|unordered-list-item|offset|length|style|BOLD|1690403|Again，返回，因此我们无法准确确定实际数据使用了多少空间。这里我们查看的是文件系统上实际使用了多少空间。|1690404|我们不查看日志文件空间。是的，这仍然会消耗磁盘空间。|1690405|1690406|希望其中一个能解决你的问题。|1690407|SELECT+
++++((1+/+CAST(SUM([df].[max_size])+AS+FLOAT))+*+CAST(SUM([df].[size])+AS+FLOAT))+*+100+AS+PercentUsed
FROM+
++++[sys].dm_io_virtual_file_stats(DB_ID(),+NULL)+++vfs
INNER+JOIN+
++++[sys].[database_files]++df
++++ON+
++++++++[vfs].[file_id]+=+[df].[file_id]
WHERE+
++++[df].[type]+=+0|1690408|entityMap^0|0|0|0|0|0|0|1F|2|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|17|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|18|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|19|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1B|8|@$Q|1C|R|1D|S|T]]|9|@]|A|$]]|$1|U|3|V|5|P|7|1E|8|@]|9|@]|A|$]]|$1|W|3|X|5|P|7|1F|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1H|8|@]|9|@]|A|$]]|$1|11|3|12|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|13|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|14|$]]

The following query will tell you the percentage of @SpaceAllotted that is being used by the data by looking at how many pages are allocated by the database's objects.

It's not worth attempting to measure usage at the row-level, since SQL Server allocates all space at the extent-level. Each extent is composed of eight 8KB data pages. So if your database only had one row, and that row had 4 bytes of data, an entire extent would still need to be allocated to hold that 4 bytes (or an existing extent with unallocated pages would be used. This is called a mixed extent).

<pre><code>DECLARE @SpaceAllotted FLOAT
-- 2MB converted to kilobytes...
SET @SpaceAllotted = 2048

SELECT 
 -- Allocation is done on the extent-level.
 -- Each extent contains eight 8KB data pages.
 ((1 / (@SpaceAllotted)) * CEILING(CAST(SUM([ips].[page_count]) AS FLOAT) / 8) * 64) * 100 AS PercentageUsed
FROM
 [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, NULL) ips
 -- This will allow us to retrieve the page count of all tables in the
 -- current database, regardless of whether or not they have clustered
 -- indexes and/or non-clustered indexes.
INNER JOIN
 [sys].[indexes] i
 ON
 [ips].[object_id] = [i].[object_id]
 AND [ips].[index_id] = [i].[index_id]
</code></pre>

Since we do have the possibility of mixed extents, and no good way (there are ways, but they're not going to be pretty) of determining what pages are allocated to what extents, this isn't 100% accurate. Additionally, extents may even have free pages (that are reserved and therefore still taking up disk space), so generally, this estimate will always come in low. However, it's probably the best you're going to get without writing something to inspect the database at the page-level.

Ah yes, looking at the other answers, this is another option. This will basically look at all the current size of the data files in pages, and determine the percentage of the space they have consumed. Now, there are caveats here as well...

<ul>
<li>If there's no maximum size specified for the database (autogrowth is enabled and unrestricted), this won't fly, since max_size will be returned as -1.</li>
<li>Again, we can't accurately determine how much space is used by the actual data. Here we're looking at how much space is actually used on the file system.</li>
<li>We're not looking at log file space. Yes, that does still consume disk space.</li>
</ul>

Hope one of these solves your problem.

<pre><code>SELECT 
 ((1 / CAST(SUM([df].[max_size]) AS FLOAT)) * CAST(SUM([df].[size]) AS FLOAT)) * 100 AS PercentUsed
FROM 
 [sys].dm_io_virtual_file_stats(DB_ID(), NULL) vfs
INNER JOIN 
 [sys].[database_files] df
 ON 
 [vfs].[file_id] = [df].[file_id]
WHERE 
 [df].[type] = 0
</code></pre>

blocks|key|5674616|text|由于showcontig将被弃用，因此您还可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5674617|SELECT+*+FROM+sys.dm_db_index_physical_stats
(DB_ID(N'DatabaseName'),+OBJECT_ID(N'TableName'),+NULL,+NULL+,+'DETAILED')|code-block|syntax|javascript|5674618|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Since showcontig is going to be deprecated, you can also use:

<pre><code>SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'DatabaseName'), OBJECT_ID(N'TableName'), NULL, NULL , 'DETAILED')
</code></pre>

I have a scenario where every user has been allocated 2 MB of database space.
Now I have to show Percenatge usage of their allocated space, to do so I need to know the size of single record in the Table.

I have tried to use sp_spaceused and even wrote a custom procedure making use of datalength .. but they show vast difference in their result.

Is there any way around to get this thing right.

Size of a single Record ? SQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个场景，每个用户都被分配了2MB的数据库空间。现在，我必须显示他们分配的空间的感知使用情况，为此，我需要知道表中单个记录的大小。我曾尝试使用sp_spaceused，甚至编写了一个使用数据长度的自定义过程。但他们在结果上表现出巨大的差异。有没有什么办法能把这件事做好。

问单个记录的大小？SQL
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问单个记录的大小？SQLEN