很多時候我們都需要計算數據庫中各個表的數據量和每行記錄所占用空間

這里共享一個腳本

CREATE TABLE #tablespaceinfo    (      nameinfo VARCHAR(500) ,      rowsinfo BIGINT ,      reserved VARCHAR(20) ,      datainfo VARCHAR(20) ,      index_size VARCHAR(20) ,      unused VARCHAR(20)    )   DECLARE @tablename VARCHAR(255);   DECLARE Info_cursor CURSORFOR    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 tmptbWHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC  DROP TABLE [#tablespaceinfo]DROP TABLE [#tmptb]

注意：使用之前要計算哪個數據庫的記錄，請先USE一下要統計表記錄數的那個數據庫?。?/strong>

工作中遇到的問題

可以說我在實際的工作中 ，在100個問題中有90個都會先用到這個腳本

這里舉一個我本人工作中遇到的一些問題

問題一：

程序員反映數據庫查詢慢，5分鐘還沒有出結果

我先用這個腳本看一下這個表有多少記錄，大概有1000w+條數據

然后在本地的SSMS里查詢，確實也是大概4分鐘的樣子才出來數據，看一下執行計劃，發現查詢能使用到索引

看一下數據庫的壓力，并不是很大，我跟會不會跟數據量有關系呢？

程序員要查詢的結果條數是500條數據，業務表是做了分區的，按道理應該不會慢成這樣。。。

后來我再看一下共享出來的那個腳本的結果，發現查詢的結果大小=每行記錄的大小*記錄數

要查詢大概500MB的數據，再傳到客戶端，不慢才怪

為什麼查詢出的結果這么大？

主要是有幾個大字段：例如：二進制字段和NVARCHAR(MAX)

并且時間范圍跨度比較大

馬上叫程序員改一下查詢的語句，由于是entity framework程序，怎麼改我就不太清楚了，主要是不必要的字段就不查詢處理并且縮小時間范圍

問題二：

還有一些問題也需要知道每行記錄的大小，例如刪除表的歷史數據，QA說要保留2013年之前的數據，你需要查出保留的數據或者2013年之前的數據占用多少G空間

再結合當前服務器的磁盤可用空間，來評估刪除的數據是否太多或者太少

那么流程是：先查出2013年之前的記錄數有多少-》計算表的總記錄數-》計算表的大小-》手工計算每行記錄的大小-》乘以2013年之前的記錄數

如果沒有每行記錄數這個字段，那么你手工計算，是不是效率就變慢了？？？

問題三：

導數據的時候，你想知道當前已經導了多少數據了，那么執行一下這個腳本就可以了，這個腳本基本不會被阻塞

很快就能查出結果

腳本的計算方法

方法一

實際上利用的就是數據行大小的信息除以記錄數

CASE RowsInfoWHEN 0 THEN 0ELSE 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條數據是很少的，根本不會關心表設計，表的字段的數據類型

當工作越來越多，開發任務越來越重的時候更是這樣

CREATE TABLE #tablespaceinfo    (      nameinfo VARCHAR(500) ,      rowsinfo BIGINT ,      reserved VARCHAR(20) ,      datainfo VARCHAR(20) ,      index_size VARCHAR(20) ,      unused VARCHAR(20)    )   DECLARE @tablename VARCHAR(255);   DECLARE Info_cursor CURSORFOR    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 tmptbWHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC  DROP TABLE [#tablespaceinfo]DROP TABLE [#tmptb]