很多时候我们需要计算数据库中每个表的数据量和每行记录占用的空间。这里分享一个脚本：CREATETABLE#tablespaceinfo(nameinfoVARCHAR(50),rowsinfoBIGINT,reservedVARCHAR(20),datainfoVARCHAR(20),index_sizeVARCHAR(20),unusedVARCHAR(20))DECLARE@tablenameVARCHAR(255);DECLAREInfoOR_curs[name']+']'FROMsys.tablesWHEREtype='U';OPENInfo_cursorFETCHNEXTFROMInfo_cursorINTO@tablenameWHILE@@FETCH_STATUS=0BEGININSERTINTO#tablespaceinfoEXECsp_spaceused@tablenameFETCHNEXTFROMInfo_cursorINTO@tablenameENDCLOSEInfo_cursorDEALLOCATEInfo_cursor--创建临时表CREATETABLE[#tmptb](TableNameVARCHAR(50),DataInfoBIGINT,RowsInfoBIGINT,SpaceperrowAS(CASERowsInfoWHEN0THEN0ELSEDataInfo/RowsInfoEND)PERSISTED)--插入数据到临时表INSERTINTO[#tmptb]([TableName],[DataInfo],[RowsInfo])SELECT[nameinfo],CAST(REPLACE([datainfo],'KB','')ASBIGINT)AS'datainfo',[rowsinfo]FROM#tablespaceinfoORDERBYCAST(REPLACE(reserved,'KB','')ASINT)DESC--汇总记录SELECT[tbspinfo].*,[tmptb].[Spaceperrow]AS'Approximate每行占用的空间(KB)'FROM[#tablespaceinfo]AStbspinfo,[#tmptb]ASTmptbWHERE[tbspinfo].[nameinfo]=[tmptb].[TableName]ORDERBYCAST(REPLACE([tbspinfo].[reserved],'KB','')ASINT)DESCDROPTABLE[#tablespaceinfo]DROPTABLE[#tmptb]注意：请使用需要计算表记录数的数据库再使用！！工作中遇到的问题可以说在我的实际工作中，100个问题中有90个都会首先使用这个脚本。下面列出我在工作中遇到的一些问题。问题一：程序员反映查询数据库慢，5分钟没有结果。我先用这个脚本看这张表有多少条记录，大概有1000w+条数据。然后在本地SSMS中查询，确实需要4分钟左右的时间才能拿到数据。查看执行计划，发现查询可以使用索引。看数据库的压力，不是很大，是不是跟数据量有关系？程序员要查询的结果条数是500条数据，业务表是分区的，应该不会这么慢。..后来查看共享脚本的结果，发现查询结果的大小=每行记录的大小*记录条数。查询500MB左右的数据再传给客户端，难怪不慢。为什么查询结果这么大？主要有几个大字段：比如：二进制字段和NVARCHAR(MAX)而且时间范围跨度比较大，马上要求程序员改查询语句。由于是实体框架程序，不知道怎么改，主要是查询不处理不需要的字段，缩小时间范围。问题2：还有一些问题也需要知道每行记录的大小，比如删除表的历史数据，QA说要保留2013年之前的数据，你需要找出多少G预留数据或2013年以前的数据占用的空间。结合当前服务器的可用磁盘空间来评估删除的数据是过多还是过少。那么过程就是：先找出2013年之前的记录数-》计算表的总记录数-》计算表的大小-》手动计算每行记录的大小-》乘以记录数2013年之前，如果没有每行记录数的字段，那么如果手动计算，效率会不会变慢？？？问题3：在导入数据的时候，如果想知道导入了多少数据，那么执行这个脚本就可以了，基本不会被阻塞。快速找出结果。脚本的计算方法方法一实际使用的是数据行大小除以记录数的信息CASERowsInfoWHEN0THEN0ELSEDataInfo/RowsInfo方法二SELECTAVG(DATALENGTH(C0))+AVG(DATALENGTH(C1))+AVG(DATALENGTH(C2))+AVG(DATALENGTH(C3))FROM[dbo].[TB106]#p#说说两种方法的区别：第一种方法效率高，当表有几亿条记录时，如果你使用第二种方法执行AVG(DATALENGTH(C0))时速度很慢，因为SQLSERVER需要统计字段大小信息。十分钟之内可能还出不了结果。当然，第一种方法也有一些缺陷，就是当表中的记录数较少时，每行记录所占用的空间并不准确。因为datainfo的值是根据数据页的大小而定的，因为即使表只有一条记录，也会占用一个数据页（8KB）。那么当8KB/1=8KB时，一条记录的大小肯定不会是8KB，所以记录小了就会不准确。但是当记录数很大时，它是准确的。看一下TB106表统计结果值SELECTAVG(DATALENGTH(C0))+AVG(DATALENGTH(C1))+AVG(DATALENGTH(C2))+AVG(DATALENGTH(C3))FROM[dbo].[TB106]能看的更准注意：方法一和方法二都不包括索引占用的空间！！综上所述，大家总会想到：DBA的作用是什么？这里给大家举个例子。在工作中，程序员并不关心他要查询的数据的大小。这里的DBA需要解决数据无法查询的问题。一般程序员认为查询500条数据是非常难得的，并不关心表设计和表字段的数据类型。当工作岗位越来越多，开发任务越来越多时，尤其如此。所以我觉得DBA的作用还是比较重要的o(∩_∩)o如果有不对的地方，欢迎拍砖o(∩_∩)o2014-7-7脚本bug修复由于精度问题计算每行记录的问题，我再次提高了脚本的精度CREATETABLE#tablespaceinfo(nameinfoVARCHAR(50),rowsinfoBIGINT,reservedVARCHAR(20),datainfoVARCHAR(20),index_sizeVARCHAR(20),unusedVARCHAR(20))DECLARE@tablenameInVARCHARSORLEORLE(255);DECLARE'['+[name]+']'FROMsys.tablesWHEREtype='U';OPENInfo_cursorFETCHNEXTFROMInfo_cursorINTO@tablenameWHILE@@FETCH_STATUS=0BEGININSERTINTO#tablespaceinfoEXECsp_spaceused@tablenameFETCHNEXTFROMInfo_cursorINTO@tablenameENDCLOSEInfo_cursorDEALLOCATEInfo_cursor--创建临时表CREATETABLE[#tmptb](TableNameVARCHAR(50),DataInfoBIGINT,RowsInfoBIGINT,SpaceperrowAS(CASERowsInfoWHEN0THEN0ELSECAST(DataInfoASdecimal(18,2))/CAST(RowsInfoASdecimal(18,2))END)PERSISTED)--插入数据到临时表INSERTINTO[#tmptb]([TableName],[DataInfo],[RowsInfo])SELECT[nameinfo],CAST(REPLACE([datainfo],'KB','')ASBIGINT)AS'datainfo',[rowsinfo]FROM#tablespaceinfoORDERBYCAST(REPLACE(reserved,'KB','')ASINT)DESC--汇总记录SELECT[tbspinfo].*,[tmptb].[Spaceperrow]AS'每行记录大概占用空间（KB）'FROM[#tablespaceinfo]ASTbspinfo,[#tmptb]ASTmptbWHERE[tbspinfo].[nameinfo]=[tmptb].[TableName]ORDERBYCAST(REPLACE([tbspinfo].[reserved],'KB','')ASINT)DESCDROPTABLE[#tablespaceinfo]DROPTABLE[#tmptb]本文来自：http://www.cnblogs.com/lyhabc/p/3828496.html
