none
資料庫透過維護計畫作索引造成資料增刪修改變得很慢 RRS feed

  • 一般討論

  • 上星期透過維護計畫作對資料庫索引
    結果一些Table資料增刪修改都變得很慢

    查了Table屬性發現
    原本200MB的Table變成2GB(都大在MDF檔)
    也自動增加了幾個索引
    有約1.7GB被用在索引上

    刪掉幾個索引後 再壓縮DB後發現
    原本用在索引的1.7GB變成50MB
    但Table所載大小還是2GB
    同時增刪修改還是慢

    我是過用介面作DB壓縮

    DBCC SHRINKDATABASE
    DBCC SHRINKFILE
    都沒作用


    這個狀況有指令可以還原嗎

    我現在只有試出來
    1.先將資料匯出
    2.刪TABLE資料
    3.匯回資料
    這樣可將Table縮回原來大小


    謝謝~


    永遠的初學者
    2009年7月22日 上午 11:18

所有回覆

  • 請問你的指令是怎麼下的?

    參考資料:
    ☞ 這裡是「免費的討論區」付費支援服務請造訪 此處享受尊榮服務
    如果回覆對您有幫助,請記得按下標示為解答」
    2009年7月22日 下午 03:13
    版主
  • Maintenance plan will not add index for you, you should review your query and check execution plan to ensure sql uses correct index.
    2009年7月22日 下午 05:32
  • BACKUP LOG myDBName WITH TRUNCATE_ONLY

    DBCC SHRINKDATABASE (myDBName)

    不過據我自己以往的經驗
    這個是對壓縮交易紀錄檔LDF比較有效

    在我目前的狀況 下沒辦法把MDF壓回原來大小

    同時 增刪修改的速度不會因索引刪掉而回恢復正常

    1.先將資料匯出
    2.刪TABLE資料
    3.匯回資料
    將Table縮回原來大小後速度才會正常


    永遠的初學者
    2009年7月23日 上午 07:06
  • 這個是我同事依據SQL Server Profiler抓的紀錄檔
    再讓sql自己跑出來的
    永遠的初學者
    2009年7月23日 上午 07:09
  • Database file size will not affect performance as long as tables are not fragmented.
    2009年7月23日 下午 01:09
  • BACKUP LOG myDBName WITH TRUNCATE_ONLY

    DBCC SHRINKDATABASE (myDBName)

    不過據我自己以往的經驗
    這個是對壓縮交易紀錄檔LDF比較有效

    所以我參考資料才沒提到 DBCC SHRINKDATABASE,可以參考國外 MVP 的文章:Why you want to be restrictive with shrink of database files

    一般來說,有大量資料被刪除,且只有少量的新增資料,才有可能經過適當的指令操作來讓資料庫檔案要變小:

    1. 用 DBCC DBREINDEX 或 ALTER INDEX 重建資料表的索引
    2. 用 DBCC SHRINKFILE 指定壓縮資料庫檔或記錄檔的大小

    有時候,手動執行 UPDATE STATISTICS 可以改善查詢效能


    ☞ 這裡是「免費的討論區」付費支援服務請造訪 此處享受尊榮服務
    如果回覆對您有幫助,請記得按下標示為解答」
    2009年7月23日 下午 01:42
    版主
  • 我在意的問題不是檔案變大
    而是insert.update.delete的速度變慢
    造成這個結果原因我相信是索引沒設好的關係

    設完索引後
    某一TABLE所佔空間變很大,193MB=>2.03GB
    同時insert.update.delete速度變慢
    相同情形發生在很多TABLE

    我會想要去縮MDF檔的原因是
    在刪了TABLE索引後 增刪修改速度還是慢
    同時檢查TABLE所佔空間還是很大
    所以我認為索引造成的影響還在
    因此我想透過縮TABLE所佔空間來恢復原有效能,當然這只是假設


    我做了一個實驗透過這段指令
    BEGIN TRANSACTION
    SELECT * INTO  tblTemp from (select * from tblBig) T
    DELETE tblBig 
    INSERT INTO tblBig select * from tblTemp
    DROP TABLE tblTemp
    COMMIT TRANSACTION

    TABLE tblBig所佔空間縮回原有大小
    而insert.update.delete的速度也恢復了
    我已經先對幾個主要table作這個動作了,
    畢竟線上的東西沒辦法讓慢慢想解法

    但我的解法有點土法煉鋼 而且要user off line 時進行
    所以我在想是不是應該有指令是可以直接做到這一點


    永遠的初學者
    2009年7月24日 上午 02:37
  • 檔案變大是加完索引後的現象
    而insert.update.delete的速度變慢也是
    刪掉索引後上述現象還是存在

    研判設索引造成的影響還在
    所以我現在想透過壓縮table來去除這個影響
    後來我有做實驗  驗證了這個想法


    永遠的初學者
    2009年7月24日 上午 02:52
  • That's why you should check execution plan, and ensure you use 'sort in tempdb' option when rebuild index. That will prevent database grow during index rebuild.
    2009年7月24日 下午 12:15
  • 謝謝,這個選項我會試一下,不過這次是先在測試機上...哈

    我想試一下 如果增長的size跑到tempdb上  是不是在增刪修改時效能就不受影響

    另外,假設一個table的size是100mb 那索引佔的size在多少之間算正常呢?

    以前雖然知道索引過大會影響資料增刪修改  不過會影響這麼大倒也是出乎我意料之外

    發生類似的狀況沒有指令能直接作有效的處理嗎?


    先前會發生這個問題就時同事將使用sql2000的維護方式帶到sql2005上

    他說同樣作法在sql2000上是ok的 而用在sql2005卻發生這個問題.  不過還好是有解決








    永遠的初學者
    2009年7月27日 上午 10:47
  • Index size in the table depends on how many indices the table has and number of columns, data type and length of those column in each index.

    2009年7月27日 下午 05:35