分享一个SQLSERVER脚本(1)

分享一个SQLSERVER脚本(1)

很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间。

这里共享一个脚本：

CREATE TABLE #tablespaceinfo  
    (  
      nameinfo VARCHAR(50) ,  
      rowsinfo BIGINT ,  
      reserved VARCHAR(20) ,  
      datainfo VARCHAR(20) ,  
      index_size VARCHAR(20) ,  
      unused VARCHAR(20)  
    )    
   
DECLARE @tablename VARCHAR(255);    
   
DECLARE Info_cursor CURSOR 
FOR 
    SELECT  '[' + [name] + ']' 
    FROM    sys.tables  
    WHERE   type = 'U';    
   
OPEN Info_cursor    
FETCH NEXT FROM Info_cursor INTO @tablename    
   
WHILE @@FETCH_STATUS = 0  
    BEGIN   
        INSERT  INTO #tablespaceinfo  
                EXEC sp_spaceused @tablename    
        FETCH NEXT FROM Info_cursor    
    INTO @tablename    
    END   
   
CLOSE Info_cursor    
DEALLOCATE Info_cursor    
   
--创建临时表  
CREATE TABLE [#tmptb]  
    (  
      TableName VARCHAR(50) ,  
      DataInfo BIGINT ,  
      RowsInfo BIGINT ,  
      Spaceperrow AS ( CASE RowsInfo  
                         WHEN 0 THEN 0  
                         ELSE DataInfo / RowsInfo  
                       END ) PERSISTED  
    )  
 
--插入数据到临时表  
INSERT  INTO [#tmptb]  
        ( [TableName] ,  
          [DataInfo] ,  
          [RowsInfo]  
        )  
        SELECT  [nameinfo] ,  
                CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,  
                [rowsinfo]  
        FROM    #tablespaceinfo  
        ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC    
 
 
--汇总记录  
SELECT  [tbspinfo].* ,  
        [tmptb].[Spaceperrow] AS '每行记录大概占用空间KB）' 
FROM    [#tablespaceinfo] AS tbspinfo ,  
        [#tmptb] AS tmptb  
WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]  
ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC    
 
DROP TABLE [#tablespaceinfo]  
DROP TABLE [#tmptb] 

注意：使用之前要计算哪个数据库的记录，请先USE一下要统计表记录数的那个数据库！！

---

工作中遇到的问题

可以说我在实际的工作中 ，在100个问题中有90个都会先用到这个脚本。

这里举一个我本人工作中遇到的一些问题。

问题一：

程序员反映数据库查询慢，5分钟还没有出结果。

我先用这个脚本看一下这个表有多少记录，大概有1000w+条数据。

然后在本地的SSMS里查询，确实也是大概4分钟的样子才出来数据，看一下执行计划，发现查询能使用到索引。

看一下数据库的压力，并不是很大，我跟会不会跟数据量有关系呢？

程序员要查询的结果条数是500条数据，业务表是做了分区的，按道理应该不会慢成这样。。。

后来我再看一下共享出来的那个脚本的结果，发现查询的结果大小=每行记录的大小*记录数。

要查询大概500MB的数据，再传到客户端，不慢才怪。

为什麽查询出的结果这么大？

主要是有几个大字段：例如：二进制字段和NVARCHAR(MAX)

并且时间范围跨度比较大

马上叫程序员改一下查询的语句，由于是entity framework程序，怎麽改我就不太清楚了，主要是不必要的字段就不查询处理并且缩小时间范围。

问题二：

还有一些问题也需要知道每行记录的大小，例如删除表的历史数据，QA说要保留2013年之前的数据，你需要查出保留的数据或者2013年之前的数据占用多少G空间。

再结合当前服务器的磁盘可用空间，来评估删除的数据是否太多或者太少。

那么流程是：先查出2013年之前的记录数有多少-》计算表的总记录数-》计算表的大小-》手工计算每行记录的大小-》乘以2013年之前的记录数。

如果没有每行记录数这个字段，那么你手工计算，是不是效率就变慢了？？？

问题三：

导数据的时候，你想知道当前已经导了多少数据了，那么执行一下这个脚本就可以了，这个脚本基本不会被阻塞。

很快就能查出结果。

---

脚本的计算方法

方法一

实际上利用的就是数据行大小的信息除以记录数

CASE RowsInfo  
WHEN 0 THEN 0  
ELSE DataInfo / RowsInfo 

方法二

SELECT AVG(DATALENGTH(C0))+AVG(DATALENGTH(C1))+AVG(DATALENGTH(C2))+AVG(DATALENGTH(C3)) FROM [dbo].[TB106]