工作中遇到的問題

可以說我在實(shí)際的工作中 ，在100個(gè)問題中有90個(gè)都會(huì)先用到這個(gè)腳本。

這里舉一個(gè)我本人工作中遇到的一些問題。

問題一：

程序員反映數(shù)據(jù)庫查詢慢，5分鐘還沒有出結(jié)果。

我先用這個(gè)腳本看一下這個(gè)表有多少記錄，大概有1000w+條數(shù)據(jù)。

然后在本地的SSMS里查詢，確實(shí)也是大概4分鐘的樣子才出來數(shù)據(jù)，看一下執(zhí)行計(jì)劃，發(fā)現(xiàn)查詢能使用到索引。

看一下數(shù)據(jù)庫的壓力，并不是很大，我跟會(huì)不會(huì)跟數(shù)據(jù)量有關(guān)系呢？

程序員要查詢的結(jié)果條數(shù)是500條數(shù)據(jù)，業(yè)務(wù)表是做了分區(qū)的，按道理應(yīng)該不會(huì)慢成這樣。。。

后來我再看一下共享出來的那個(gè)腳本的結(jié)果，發(fā)現(xiàn)查詢的結(jié)果大小=每行記錄的大小*記錄數(shù)。

要查詢大概500MB的數(shù)據(jù)，再傳到客戶端，不慢才怪。

為什麼查詢出的結(jié)果這么大？

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

并且時(shí)間范圍跨度比較大

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

問題二：

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

再結(jié)合當(dāng)前服務(wù)器的磁盤可用空間，來評(píng)估刪除的數(shù)據(jù)是否太多或者太少。

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

如果沒有每行記錄數(shù)這個(gè)字段，那么你手工計(jì)算，是不是效率就變慢了？？？

問題三：

導(dǎo)數(shù)據(jù)的時(shí)候，你想知道當(dāng)前已經(jīng)導(dǎo)了多少數(shù)據(jù)了，那么執(zhí)行一下這個(gè)腳本就可以了，這個(gè)腳本基本不會(huì)被阻塞。

很快就能查出結(jié)果。

腳本的計(jì)算方法

方法一

實(shí)際上利用的就是數(shù)據(jù)行大小的信息除以記錄數(shù)

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] 

#p#

說一下兩種方法的區(qū)別：

第一種方法是效率高，當(dāng)表有上億條記錄的時(shí)候，如果你使用第二種方法執(zhí)行AVG(DATALENGTH(C0))是很慢的，因?yàn)镾QLSERVER要統(tǒng)計(jì)字段大小信息。

可能十幾分鐘都出不來結(jié)果。

當(dāng)然，第一種方法也有一些缺陷，就是當(dāng)表的記錄數(shù)少的時(shí)候，統(tǒng)計(jì)出來的每行記錄占用空間是不準(zhǔn)確的。

因?yàn)閐atainfo這個(gè)值是以數(shù)據(jù)頁大小為單位的，因?yàn)榫退惚碇挥幸粭l記錄，那么也會(huì)占用一個(gè)數(shù)據(jù)頁（8KB）

那么當(dāng)8KB/1 =8KB，一條記錄肯定不會(huì)是8KB大小的，所以記錄少的時(shí)候會(huì)不準(zhǔn)確。

但是當(dāng)記錄數(shù)很多的時(shí)候，就準(zhǔn)確了。

看一下TB106這個(gè)表統(tǒng)計(jì)出來的結(jié)果值

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

可以看到是比較準(zhǔn)確的

注意：

無論方法一還是方法二都不包括索引所占用的空間 ??！

總結(jié)

大家平時(shí)一定會(huì)想：究竟DBA有什么作用？

在這里就給大家一個(gè)例子了，在工作中，程序員是不會(huì)關(guān)心他要查詢的數(shù)據(jù)的大小的，他不管三七二十一只要把數(shù)據(jù)select出來就行了，然后收工。

DBA這里就要解決數(shù)據(jù)查詢不出來的問題，一般的程序員覺得查詢500條數(shù)據(jù)是很少的，根本不會(huì)關(guān)心表設(shè)計(jì)，表的字段的數(shù)據(jù)類型。

當(dāng)工作越來越多，開發(fā)任務(wù)越來越重的時(shí)候更是這樣。

所以本人覺得DBA這個(gè)角色還是比較重要的o(∩_∩)o 

如有不對(duì)的地方，歡迎大家拍磚o(∩_∩)o 

2014-7-7 腳本bug修復(fù)

由于算出來每行記錄的精度有問題，我又對(duì)腳本的精度進(jìn)行了改進(jìn)

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    
   
--創(chuàng)建臨時(shí)表  
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  
    )  
 
--插入數(shù)據(jù)到臨時(shí)表  
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