網摘 SQL Server

標籤：des   http   使用   os   資料   for   2014   問題   

http://soft.chinabyte.com/database/215/12374715.shtml

 

.

 

軟體與服務

我們也在這裡：

. 企業計算 大資料 儲存 軟體與服務 資料庫/開發 伺服器 作業系統 網 絡 安 全

. 您的位置： 位元網 > 軟體與服務 > 本文 .  

 

 

分享一個 SQLSERVER指令碼

 發布時間：2014-07-17 16:21:00來源：論壇 譯名 .

 

. 關鍵字：資料庫  

 

 

 

　　很多時候我們都需要計算資料庫中各個表的資料量和每行記錄所佔用空間。   　　

這裡共用一個指令碼：   　　

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://soft.chinabyte.com/89/13018589.shtml