计算数据库中各个表的数据量和每行记录所占用空间

王世权
王世权
王世权
121
文章
5
评论
技术分享评论4,856 views2阅读模式

在企业管理软件开发、维护中我们经常需要计算数据库中各个表的数据量和每行记录所占用空间，然后根据行占用空间乘以查询需要获取的行数计算总返回量，以便优化查询语句。下面是具体的查询脚本，要计算哪个数据库的记录，请先USE一下要统计表记录数的那个数据库。

Code   ViewPrint
  1. use TCPay  
  2. go  
  3. CREATE TABLE #tablespaceinfo  
  4.     (  
  5.       nameinfo VARCHAR(500) ,  
  6.       rowsinfo BIGINT ,  
  7.       reserved VARCHAR(20) ,  
  8.       datainfo VARCHAR(20) ,  
  9.       index_size VARCHAR(20) ,  
  10.       unused VARCHAR(20)  
  11.     )    
  12.    
  13. DECLARE @tablename VARCHAR(255);    
  14.    
  15. DECLARE Info_cursor CURSOR  
  16. FOR  
  17.     SELECT  '[' + [name] + ']'  
  18.     FROM    sys.tables  
  19.     WHERE   type = 'U';    
  20.    
  21. OPEN Info_cursor    
  22. FETCH NEXT FROM Info_cursor INTO @tablename    
  23.    
  24. WHILE @@FETCH_STATUS = 0  
  25.     BEGIN   
  26.         INSERT  INTO #tablespaceinfo  
  27.                 EXEC sp_spaceused @tablename    
  28.         FETCH NEXT FROM Info_cursor    
  29.     INTO @tablename    
  30.     END   
  31.    
  32. CLOSE Info_cursor    
  33. DEALLOCATE Info_cursor    
  34.    
  35. --创建临时表  
  36. CREATE TABLE [#tmptb]  
  37.     (  
  38.       TableName VARCHAR(50) ,  
  39.       DataInfo BIGINT ,  
  40.       RowsInfo BIGINT ,  
  41.       Spaceperrow  AS ( CASE RowsInfo  
  42.                          WHEN 0 THEN 0  
  43.                          ELSE CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2))  
  44.                        END ) PERSISTED  
  45.     )  
  46.   
  47. --插入数据到临时表  
  48. INSERT  INTO [#tmptb]  
  49.         ( [TableName] ,  
  50.           [DataInfo] ,  
  51.           [RowsInfo]  
  52.         )  
  53.         SELECT  [nameinfo] ,  
  54.                 CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,  
  55.                 [rowsinfo]  
  56.         FROM    #tablespaceinfo  
  57.         ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC    
  58.   
  59.   
  60. --汇总记录  
  61. SELECT  [tbspinfo].* ,  
  62.         [tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'  
  63. FROM    [#tablespaceinfo] AS tbspinfo ,  
  64.         [#tmptb] AS tmptb  
  65. WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]  
  66. ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC    
  67.   
  68. DROP TABLE [#tablespaceinfo]  
  69. DROP TABLE [#tmptb]  

计算数据行占用空间

rowsinfo：记录行数

reserved：总占用空间

datainfo：数据占用空间

index_size/unused：索引占用空间

datainfo=rowsinfo*每行记录大概占用空间

reserved=datainfo+index_size+unused

在select查询中，我们只考虑结果数据行数*每行记录大概占用空间就可以了。

本文脚本来源于cnblogs桦仔，桦仔大师是三届SQL Server MVP，是我们学习的典范。

end

  • 工资小程序
  • 员工工资查询小程序
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
王世权
  • 本文由 发表于 2016年3月1日 09:23:16
  • 转载请务必保留本文链接：http://www.liaodestudio.com/?p=1543