【免費註冊】 【會員登入】 【個人資料】 【會員列表】 【論壇幫助】 【論壇搜尋】 【登出論壇】

∮Ω奧米加空間∮
∮Ω奧米加空間∮»技術文件區»【轉貼】[SQL]索引重整與效率

訂覽該主題更新消息 | 將該主題推薦給朋友 發表新主題 發起投票  回覆
作者 主題     分頁:[ 1 2 ]
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 1 篇) 【轉貼】[SQL]索引重整與效率

資料來源: 湯瑪的吳@安達利.機車行.台南   SQL索引重整與效率

剛接觸程式設計工作時,只知道設定索引是一件很重要的事
依著前輩的指點,囫圇吞棗般只會把常query或是關鍵欄位設定為索引...

直到一次,不知情的同事將一個近百萬筆資料table的欄位中,
連包含非key值的長字串資料欄位全都設為索引之後....
這動作一下..效能盡失,最後嚴重拖垮整個資料庫服務
此時,我才知道「會不會」設索引,才是一件重要的事~~~

最近利用DBCC SHOWCONTIG這指令去觀查幾個資料量較多的table
在觀察數據的同時,對於DBCC INDEXDEFRAG和DBCC DBREINDEX兩指令又有一新認知。

step1.執行 DBCC SHOWCONTIG(my_table)
單純針對table進行層級掃描先取得分頁相關資訊
- 掃描的分頁................................: 64
- 掃描的範圍..............................: 11
- 範圍切換..............................: 12
- 每個範圍的平均分頁........................: 5.8
- 掃描密度 [最佳次數:實際次數].......: 33.54% [4:13]
- 邏輯掃描片段 ..................: 53.13%
- 範圍掃描片段 ...................: 36.36%
- 每個分頁的平均可用位元組.....................: 376.5
- 平均分頁密度 (全滿).....................: 95.35%

step2.執行 DBCC INDEXDEFRAG (MyDB, 'my_table',PK_my_table)
執行破碎頁面的整理後,掃描密度明顯地變大,分頁程度變得更緊密更平均。
邏輯掃描片段(cluster index叢集索引的葉層分頁狀況)愈大表示指標的指向愈紊亂,
也就是說「失序」頁面的比例,重整破碎頁面後確實有得到明顯的效果。
- 掃描的分頁................................: 63
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.0
- 掃描密度 [最佳次數:實際次數].......: 88.89% [8:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 44.44%
- 每個分頁的平均可用位元組.....................: 254.0
- 平均分頁密度 (全滿).....................: 96.86%

step3.執行 DBCC DBREINDEX (my_table,'' ,90)
執行索引的重整,並依需求重新設定填滿因子
(這個TABLE 查詢的比例多,資料異動機率並不是很頻繁,因此設為90%)
發現掃描密度、範圍掃描片段、分頁平均可用位元組得到更多的改善空間。
- 掃描的分頁................................: 68
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.6
- 掃描密度 [最佳次數:實際次數].......: 100.00% [9:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 0.00%
- 每個分頁的平均可用位元組.....................: 830.6
- 平均分頁密度 (全滿).....................: 89.74%

後記:
索引重整就像梳頭髮一樣,原本雜亂的頭髮愈梳他就會愈順
定期整理這些資料量大的table,讓index分頁的b-tree目錄在搜尋資料時發揮應有的效能,
索引一旦建立之後,應該更需要去維護它,放著不管最終反而變成是影響效能的因素之一。

但是索引並不是完全只有好處,異動頻繁的table除了不要建立太多的索引之外
對於索引的填滿率(填滿因子)更需注意,保留適當的空間,讓新刪修頻繁的table展現最好的效益。

微軟的官方文件中也有提到....
下一版的 Microsoft SQL Server 將不再提供此功能。
請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。 請改用 ALTER INDEX

延伸閱讀:
SQL Server 2008 線上叢書 DBCC SHOWCONTIG
SQL Server 2008 線上叢書 DBCC INDEXDEFRAG




本帖由dc最後編輯於2009-03-16 11:06

Your mind to my mind,

your thought to my thought
發表時間:2009-03-16 11:05
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 2 篇)

資料來源:讓 SQL Server 告訴你有哪些索引應該被重建或重組

我去年有一段時間睡覺前都在看 SQL Server 2005 證照的書(MCTS 70-431),從中學到許多資料庫實際運作的技術細節,例如: 索引的結構。當資料庫中的索引碎裂(index fragmentation)程度過高時,索引的效率就會大大降低,為了避免這個問題發生,就必須定時替資料庫健檢(維護資料庫),也就是進行索引重建 (rebuild)或索引重組(reorganize)。

在 Microsoft SQL Server 2005 實作與維護 Ⅱ 此書的第12章有提到你可以透過 SELECT 指令搭配 sys.dm_db_index_physical_stats 這個動態管理函示(DMF, Dynamic Management Function) 可以查出資料庫中所有索引的碎裂狀態,如下 T-SQL 語法:

SELECT OBJECT_NAME(dt.object_id)      ,
       si.name                        ,
       dt.avg_fragmentation_in_percent,
       dt.avg_page_space_used_in_percent
FROM
       (SELECT object_id                   ,
               index_id                    ,
               avg_fragmentation_in_percent,
               avg_page_space_used_in_percent
       FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')
       WHERE   index_id <> 0
       ) AS dt --does not return information about heaps
       INNER JOIN sys.indexes si
       ON     si.object_id = dt.object_id
          AND si.index_id  = dt.index_id

執行後的結果長這樣:

查出資料庫中所有索引的碎裂狀態

但何時該做索引重建(ALTER INDEX…REBUILD)?又何時該做索引重組(ALTER INDEX…REORGANIZE)呢?此書也有提供一些建議值供資料庫管理員在進行索引維護時的參考。

索引重組的時機

    * 檢查 External fragmentation 部分
          o 當 avg_fragmentation_in_percent 的值介於 10 到 15 之間
    * 檢查 Internal fragmentation 部分
          o 當 avg_page_space_used_in_percent 的值介於 60 到 75 之間

索引重建的時機

    * 檢查 External fragmentation 部分
          o 當 avg_fragmentation_in_percent 的值大於 15
    * 檢查 Internal fragmentation 部分
          o 當 avg_page_space_used_in_percent 的值小於 60

由於索引的維護都是透過 ALTER INDEX 進行的,所以即便索引的數據分析出來後還是要人工下 ALTER INDEX 指令來重建或重組索引。最近看到一篇文章分享一個很實用的 T-SQL 指令,他可以自動幫你算出哪些索引需要被重建或重組,而且直接幫你把 ALTER INDEX 的 T-SQL 都寫好,程式碼如下:

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE
              WHEN ps.avg_fragmentation_in_percent > 15
              THEN 'REBUILD'
              ELSE 'REORGANIZE'
       END +
       CASE
              WHEN pc.partition_count > 1
              THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
              ELSE ''
       END,
       avg_fragmentation_in_percent
FROM   sys.indexes AS ix
       INNER JOIN sys.tables t
       ON     t.object_id = ix.object_id
       INNER JOIN sys.schemas s
       ON     t.schema_id = s.schema_id
       INNER JOIN
              (SELECT object_id                   ,
                      index_id                    ,
                      avg_fragmentation_in_percent,
                      partition_number
              FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
              ) ps
       ON     t.object_id = ps.object_id
          AND ix.index_id = ps.index_id
       INNER JOIN
              (SELECT  object_id,
                       index_id ,
                       COUNT(DISTINCT partition_number) AS partition_count
              FROM     sys.partitions
              GROUP BY object_id,
                       index_id
              ) pc
       ON     t.object_id              = pc.object_id
          AND ix.index_id              = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10
   AND ix.name IS NOT NULL

這段 T-SQL 只有檢查 External fragmentation 部分而已,而且上面分享的這段 T-SQL 語法與原文的數值不太一樣,我有調整過,讓判斷的臨界值符合 Microsoft SQL Server 2005 實作與維護 Ⅱ 書中建議的數值。以下是執行結果的圖示:

自動幫你算出哪些索引需要被重建或重組的結果

你只要複製這些語法,並且執行一遍,就可以完成索引維護了。






Your mind to my mind,

your thought to my thought
發表時間:2009-03-16 11:41
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 3 篇) 【轉貼】SQL中取得時間的一些技巧

資料來源:
卡修


*********在Sql Server中時間是精確到3毫秒*****************
--------------第一天---------------------------------------
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

select DATEDIFF(mm,0,getdate())

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)


--------------當天半夜--------------------------------------------
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

--------------最後一天--------------------------------------------
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))

select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)


----------去掉時分秒----------------------------
declare @a datetime
set @a = getdate()
SELECT @a,DATEADD(day,DATEDIFF(day,0,@a),0)
    
----------顯示星期幾----------------------------
select datename(weekday,getdate())
    
----------如何取得某個月天數-------------------
declare @m int
set @m=3 --月份
select datediff(day,'2006-'+cast(@m as varchar)+'-15' ,'2006-'+cast(@m+1 as varchar)+'-15')
    
----------或者使用計算本月的最後一天的腳本,然後用DAY函數區最後一天
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))

----------判斷是否閏年:------------------------
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '閏年' end

select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01'))
when 28 then '平年' else '閏年' end
    
----------一個季度多少天------------------------
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))






Your mind to my mind,

your thought to my thought
發表時間:2009-03-16 17:54
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 4 篇) 【原創】

SELECT CONVERT(VARCHAR(8), DATEADD(m, DATEDIFF(m, '', getdate()), ''), 112) AS [月初]
select CONVERT(VARCHAR(8), DATEADD(m, -3, getdate()), 112) AS [三個月前]






Your mind to my mind,

your thought to my thought
發表時間:2009-03-16 17:56
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 5 篇) 【轉貼】

資料來源:Joseph

重整SQL交易紀錄檔的Command可有效壓縮交易紀錄檔的容量

1.截斷交易記錄檔 BACKUP LOG '資料庫名稱' WITH TRUNCATE_ONLY
2.顯示資料庫檔案,找出交易記錄檔的邏輯檔名 EXEC sp_helpdb '資料庫名稱'
3.壓縮交易記錄檔 DBCC SHRINKFILE('ldf的檔案名稱',2)-2的意思為2MB

範例:

以下就是可以將SmartIT資料庫的交易記錄檔縮小為2M

BACKUP LOG SmartIT WITH TRUNCATE_ONLY
exec sp_helpdb SmartIT
DBCC SHRINKFILE('SmartIT_Log',2) --SmartIT_Log 為交易記錄檔名稱,此Command執行後,只會剩下2MB

另一個強制清除交易紀錄檔的設定

use DB_Name
Backup Log DB_Name with TRUNCATE_ONLY
dbcc shrinkfile (DB_Name_log , truncateonly)

說明:
執行Backup Log with Truncate_Only 其用法是備份資料庫的Log檔,由於我們在上述語法中沒有指定備份的裝置為何,Sql Server即會認為此動作為單純要把已交易完成的Log資料清空(已交易完成的資料所指即為已commit的資料),而Truncate_Only的選項則是告訴Sql Server目的在清空Log之空間,至於清出的空間則由Sql Server管理,不還給OS。

Backup Log完成後,要再執行DBCC SHRINKFILE ,,{NOTRUNCATE TRUNCATEONLY }

其作為為將資料的空間作重整及壓縮,類似硬碟重組的功能,FileName為指定要壓縮的資料檔名(在Enterprise選擇資料庫按右鍵選內容,選交易紀錄檔的Tag即可看到檔案名稱),TargetSize指的是要將資料壓縮至多少MB(假設檔案有10MB,TargetSize指定8MB,則原檔案最後2MB的資料會被搬移至前面的8MB存放,而至少空出2MB的空間)。
NOTRUNCATE指的是空出來的空間供Sql Server使用不還給OS,TRUNCATEONLY就是空出的空間還給OS。

延伸閱讀
http://msdn.microsoft.com/zh-tw/library/ms189493(SQL.90).aspx
http://support.microsoft.com/kb/256650/zh-tw




本帖由dc最後編輯於2009-03-18 11:01

Your mind to my mind,

your thought to my thought
發表時間:2009-03-18 01:26
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:122.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 6 篇)

如何將 SQL Server 交易記錄檔壓縮

這裡有幾件事情請注意:

    * 在做了會影響系統的變更前後,一定要執行系統資料庫及使用者資料庫的備份。 DBCC SHRINKFILE 及 DBCC SHRINKDATABASE 是非記錄式的作業,且執行它們會使進一步的交易記錄檔備份無效。在您執行 DBCC SHRINKFILE 或 DBCC SHRINKDATABASE 兩者之一的命令後,一定要製作一個完整的資料庫備份。

    * 請確認在應該發生壓縮的期間,沒有排定進行備份。

請先確認沒有舊有、長期執行,或未複製的交易。若要執行這項作業,請使用下列的程式碼查詢

DBCC OPENTRAN (database_name)


截斷交易記錄檔 BACKUP LOG '資料庫名稱' WITH TRUNCATE_ONLY

BACKUP LOG database_name WITH TRUNCATE_ONLY

顯示資料庫檔案找出交易記錄檔的邏輯檔名。usage 為 log only。 name 為 database_name_log。

EXEC SP_HELPDB database_name

壓縮交易記錄檔 DBCC SHRINKFILE('ldf的檔案名稱',num)  num 為 壓縮到多少的 MB 數

DBCC SHRINKFILE('database_name_log',10)

1024KB=1MB
10240KB=10MB
51200KB=50MB

因為在交易記錄檔壓縮後會將交易紀錄清除微軟的技術文件建議執行壓縮程序前後
1. 請執行主要資料庫的完整資料庫備份。
2. 再執行使用者資料庫的完整資料庫備份。這是有必要的,因為除非完成完整的資料庫備份, 否則 SHRINK 命令將不會有記錄,而未來的交易記錄檔備份將變成無效。

database 刪除大量資料後容量並不會縮小需壓縮
這個指令會對資料庫與交易記錄檔壓縮
DBCC SHRINKDATABASE(database_name)




本帖由dc最後編輯於2009-03-19 11:07

Your mind to my mind,

your thought to my thought
發表時間:2009-03-18 11:22
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 7 篇)

使用資料表資料分割簡化資料庫維護
http://technet.microsoft.com/zh-tw/magazine/cc162478.aspx

使用Partition Table改善效能
http://jonesyeh.spaces.live.com/blog/cns!DF30C7A0D4E0C557!554.entry

SQL Server 2005 分區表實踐——分區切換
http://www.diggcms.cn/html/2008-12/119_5276_00.html

sqlserver 2005 如何創建分區表
http://it.7747.net/html/database/MSSQL/20070413/11471.html

運用SQL Server數據表分區優化數據庫
http://idcnews.net/html/edu/20071102/144970.html

sqlserver2005 分割資料表問題
http://www.blueshop.com.tw/board/show.asp?subcde=BRD20081006151625EL7&fumcde=FUM20041006152735ZFS&rplcnt=2#

MS Sql Server 2005 分區表有點麻煩
http://blog.csdn.net/ZengMuAnSha/archive/2008/12/29/3638881.aspx




本帖由dc最後編輯於2009-03-19 10:59

Your mind to my mind,

your thought to my thought
發表時間:2009-03-18 15:33
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 8 篇)

資料來源:MSSQL2005資料庫,在已建立鏡像同步後,如何清除主體資料庫的交易記錄檔呢?

backup log DBName with truncate_only
dbcc shrinkfile (LogName ,10)

因為在執行第一行T-SQL敘述時,會出現下列的錯誤訊息,
訊息 3048,層級 16,狀態 1,行 1
BACKUP LOG WITH TRUNCATE_ONLY 已設定為資料庫鏡像,因此無法在資料庫 'DBName' 中操作。
訊息 3013,層級 16,狀態 1,行 1
BACKUP LOG 正在異常結束。

是否一定要將鏡像同步移除,才能進行資料庫交易記錄檔的清除動作,
因為交易記錄檔過個一個月左右就會變的粉大,這樣清完log檔,
還需重新執行資料庫鏡像,就顯得麻煩許多,不知有沒有好一點的處理方式。


解答
資料來源:Truncate Mirrored Database Log File

If you are running asynchronous database mirroring, then there could be a backlog of transaction log records that have not been sent from the principal to the mirror (called the database mirroring SEND queue). The transaction log records cannot be freed until they have been successfully sent. With a high rate of transaction log record generation and limited bandwidth on the network (or other hardware issues), the backlog can grow quite large and cause the transaction log to grow.



On the mirrored database, you cannot backup the log file with TRUNCATE_ONLY. Here the steps to shrink the log file for a database participating in mirroring



   1. Backup the log file to a location

BACKUP Log YourDatabaseName TO DISK = 'D:\BACKUP\DBNAME_20090201.TRN'


   2. Check if there is enough free space on perform the shrink operation

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB

FROM sys.database_files;


DBCC SQLPERF(LOGSPACE);


If there is no sufficient free space then the shrink operation cannot reduce file size.


   3. Check if all the transactions are written into the disk


DBCC LOGINFO('YourDatabaseName')


The status of the last transaction should be 0. If not, then backup the transaction log once again.


   4. Shrink the log file

DBCC SHRINKFILE(logfilename , target_size)


If the transaction lof file does not shrink after performing the above steps then backup the log file again to make more of the virtual log files inactive.


Also check the column LOG_REUSE_WAIT_DESC in the sys.databases catalog view to check if the reuse of the transaction log space is waiting on anything.


Check this link to find the factors that can delay log truncation

http://msdn.microsoft.com/en-us/library/ms345414(SQL.90).aspx






Your mind to my mind,

your thought to my thought
發表時間:2009-03-19 12:25
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 9 篇)

資料表資料分割

CREATE PARTITION FUNCTION


CREATE PARTITION SCHEME






Your mind to my mind,

your thought to my thought
發表時間:2009-03-19 14:06
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:210.*.*.* 編輯  引言回覆 
dc
管理員



性別:男
來自:瓦肯星
發表總數:6957
註冊時間:2002-05-07 16:32
(第 10 篇)

SQL 轉置語法 使用 PIVOT 和 UNPIVOT
http://technet.microsoft.com/zh-tw/library/ms177410.aspx

將結果 行變成列 PIVOT 列變成行UNPIVOT
如果無須計算數值使用 MAX()
如果需要結合表單在 AS SourceTable 裡就要先下






Your mind to my mind,

your thought to my thought
發表時間:2009-11-08 18:38
dc的個人資料 傳送郵件給dc dc的個人首頁 dc發表的所有文章 送出悄悄話給dc IP:122.*.*.* 編輯  引言回覆 
所有時間均為GMT+8, 現在是2019-01-22 01:09     分頁:[ 1 2 ]
訂覽該主題更新消息 | 將該主題推薦給朋友 發表新主題 發起投票  回覆

快速回覆
主題 ( 回覆文章可以不輸入標題 )
URLs自動分析
有回覆時郵件通知
禁用表情符號
使用簽名

<聯絡我們 - OMEGA - 控制面板>

Powered by Centaur & Joksky & DC, ver 2003.08.14
Copyright ©2002-2008 PHPY.COM


頁面生成時間:0.021750926971436