关闭 x
IT技术网
    技 采 号
    ITJS.cn - 技术改变世界
    • 实用工具
    • 菜鸟教程
    IT采购网 中国存储网 科技号 CIO智库

    IT技术网

    IT采购网
    • 首页
    • 行业资讯
    • 系统运维
      • 操作系统
        • Windows
        • Linux
        • Mac OS
      • 数据库
        • MySQL
        • Oracle
        • SQL Server
      • 网站建设
    • 人工智能
    • 半导体芯片
    • 笔记本电脑
    • 智能手机
    • 智能汽车
    • 编程语言
    IT技术网 - ITJS.CN
    首页 » SQL Server »分享一个SQLSERVER脚本(1)

    分享一个SQLSERVER脚本(1)

    2015-09-14 00:00:00 出处:ITJS
    分享

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

    这里共享一个脚本:

    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] 

    本文出自:http://www.cnblogs.com/lyhabc/p/3828496.html

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

    这里共享一个脚本:

    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] 

    本文出自:http://www.cnblogs.com/lyhabc/p/3828496.html

    上一篇返回首页 下一篇

    声明: 此文观点不代表本站立场;转载务必保留本文链接;版权疑问请联系我们。

    别人在看

    正版 Windows 11产品密钥怎么查找/查看?

    还有3个月,微软将停止 Windows 10 的更新

    Windows 10 终止支持后,企业为何要立即升级?

    Windows 10 将于 2025年10 月终止技术支持,建议迁移到 Windows 11

    Windows 12 发布推迟,微软正全力筹备Windows 11 25H2更新

    Linux 退出 mail的命令是什么

    Linux 提醒 No space left on device,但我的空间看起来还有不少空余呢

    hiberfil.sys文件可以删除吗?了解该文件并手把手教你删除C盘的hiberfil.sys文件

    Window 10和 Windows 11哪个好?答案是:看你自己的需求

    盗版软件成公司里的“隐形炸弹”?老板们的“法务噩梦” 有救了!

    IT头条

    公安部:我国在售汽车搭载的“智驾”系统都不具备“自动驾驶”功能

    02:03

    液冷服务器概念股走强,博汇、润泽等液冷概念股票大涨

    01:17

    亚太地区的 AI 驱动型医疗保健:2025 年及以后的下一步是什么?

    16:30

    智能手机市场风云:iPhone领跑销量榜,华为缺席引争议

    15:43

    大数据算法和“老师傅”经验叠加 智慧化收储粮食尽显“科技范”

    15:17

    技术热点

    商业智能成CIO优先关注点 技术落地方显成效(1)

    用linux安装MySQL时产生问题破解

    JAVA中关于Map的九大问题

    windows 7旗舰版无法使用远程登录如何开启telnet服务

    Android View 事件分发机制详解

    MySQL用户变量的用法

      友情链接:
    • IT采购网
    • 科技号
    • 中国存储网
    • 存储网
    • 半导体联盟
    • 医疗软件网
    • 软件中国
    • ITbrand
    • 采购中国
    • CIO智库
    • 考研题库
    • 法务网
    • AI工具网
    • 电子芯片网
    • 安全库
    • 隐私保护
    • 版权申明
    • 联系我们
    IT技术网 版权所有 © 2020-2025,京ICP备14047533号-20,Power by OK设计网

    在上方输入关键词后,回车键 开始搜索。Esc键 取消该搜索窗口。