开发者社区> 杰克.陈> 正文

分享一个SQLSERVER脚本

简介: 原文:分享一个SQLSERVER脚本 分享一个SQLSERVER脚本 很多时候我们都需要计算数据库中各个表的数据量很每行记录所占用空间 这里共享一个脚本 CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR(50) , ...
+关注继续查看
原文:分享一个SQLSERVER脚本

分享一个SQLSERVER脚本

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

这里共享一个脚本

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]

 

说一下两种方法的区别

第一种方法是效率高,当表有上亿条记录的时候,如果你使用第二种方法执行AVG(DATALENGTH(C0))是很慢的,因为SQLSERVER要统计字段大小信息

可能十几分钟都出不来结果

 

当然,第一种方法也有一些缺陷,就是当表的记录数少的时候,统计出来的每行记录占用空间是不准确的

因为datainfo这个值是以数据页大小为单位的,因为就算表只有一条记录,那么也会占用一个数据页(8KB)

那么当8KB/1 =8KB,一条记录肯定不会是8KB大小的,所以记录少的时候会不准确

但是当记录数很多的时候,就准确了

 

看一下TB106这个表统计出来的结果值

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

可以看到是比较准确的

 

注意:

无论方法一还是方法二都不包括索引所占用的空间 !!


总结

大家平时一定会想:究竟DBA有什么作用?

在这里就给大家一个例子了,在工作中,程序员是不会关心他要查询的数据的大小的,他不管三七二十一只要把数据select出来就行了,然后收工

DBA这里就要解决数据查询不出来的问题,一般的程序员觉得查询500条数据是很少的,根本不会关心表设计,表的字段的数据类型

当工作越来越多,开发任务越来越重的时候更是这样



 
所以本人觉得DBA这个角色还是比较重要的o(∩_∩)o 
 
如有不对的地方,欢迎大家拍砖o(∩_∩)o 
 
 
2014-7-7 脚本bug修复
由于算出来每行记录的精度有问题,我又对脚本的精度进行了改进
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 CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2))
                       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]

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
心中有“树”!图文并茂介绍数据结构中常见的树(三)
在前面两篇文章中,我们简要介绍了数据结构中的各种【树】在搜索、数据库等领域的使用场景,希望对大家有所帮助。
11 0
冬季实战营第一期:从零到一上手玩转云服务器 | 学习报告
训练营日期:2022年1月17日 - 2022年1月23日 需要先报名后学习,实际是17-21共5天5个场景的学习,链接地址:https://developer.aliyun.com/adc/series/wintercamplist1?spm=a2c6h.26268694.J_3660455150.2.503610f8RnszVx 我是5月份学习的,并没有实时参加训练营,因为觉着本次训练营课程设置很实用,所以对本次学习做个总结。
6 0
心中有“树”!图文并茂介绍数据结构中常见的树(二)
计算机科学家尼古拉斯·沃斯(Niklaus Wirth)曾说过:编程=数据结构+算法 ,可见数据结构在编程中的重要性。
5 0
手撸一款简单高效的线程池(四)
在前几章的内容中,我们给大家介绍了一些 C++线程池中的优化思路和实现方案。这一章中,我们来聊一聊在编程实现过程中,一些工程层面的优化。让我们的代码执行的速度,跟得上自己的思路。
4 0
心中有“树”!图文并茂介绍数据结构中常见的树(一)
提到数据结构中的树(Tree) ,大家应该都不陌生,相关书籍中都有大段篇幅的介绍,刷 Leetcode 的时候会遇到很多相关问题。很多人往往会用 “手写红黑树” 来形容面试难度很高。
10 0
JSP - 起源、执行过程、运行原理、生命周期
JSP - 起源、执行过程、运行原理、生命周期
5 0
如何在小游戏制作工具中使用云函数
本节试图以最简单的方式带你了解如何在小程序后台申请和开通云开发服务,创建并编写第一个云函数并在小游戏制作工具中对其进行调用。
10 0
Win系统 - 重装系统(微PE、MSDN镜像)【绝对纯净】
Win系统 - 重装系统(微PE、MSDN镜像)【绝对纯净】
14 0
Vue入门 基本使用 与 事务管理【1】
Vue入门 基本使用 与 事务管理【1】
9 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载