Archive for the '資料庫' Category

三月 13 2013

[MS SQL Server]如何在Replication中新增Article時只建立新增的Article的Snapshot

Published by under 資料庫

在 MS SQL Server 中, 已建立好的 Replication , 若是要新增 article 時, 可以參考這篇: http://tim.diary.tw/2009/02/27/adding-articles-to-existing-publications/ , 比較討厭的地方就是若是原來的 publication 內容多或是資料量大時, 在重做一個新的可用的 snapshot 會很久, 而且佔用很大的空間, (雖然只會將新增的 article 同步到 subscriber).

其實有個參數可以讓新增 article 到 publication 時, 只建立新增 article 的 snapshot, 這樣就會省時又省空間多了, 參數就是在 publication 中的 @allow_anonymous 及 @immediate_sync, 預設在建立 publication 時, 這兩個參數是 true, 我們可以利用 sp_changepublication 這個 stored procedure 來調整已建立的 publication 將參數改為 false 即可, 如下:


EXEC sp_changepublication
@publication = 'your publication name',
@property = 'allow_anonymous' ,
@value = 'false'
GO

EXEC sp_changepublication
@publication = 'your publication name',
@property = 'immediate_sync' ,
@value = 'false'
GO

如此一來, 在新增 article 到publication時, 就可以大幅減少建立整個 snapshot 的時間與空間了.

參考資料: http://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/

No responses yet

七月 26 2012

[MS SQL]Replication架構下的資料庫變大問題

Published by under 資料庫

前一篇提到: 如何在沒有distributor及subscriber的狀況下, 移除publication (MS SQL Server)  http://tim.diary.tw/2012/07/16/remove-publication-without-distributor-and-subscriber/

在 replication 的架構下, 若是 publication database 在 distributor 及 subscriber 不存在的狀況下, 該 publication database 在 backup 出來時的檔案, 將會愈來愈來大, 主要是因為讓 distributor 及 subscriber 回復時, 能再將未同步的資料寫回.

不過若是在 distributor 及 subscriber 下架, 又無法以正常的方式將 replication 架構移除時, 將會導致原來的 publication database 的備份愈來愈大, 解決之道當然是將該 replication 移除, 方式可以參考前面文章連結的內容.

若是資料庫備份有快速異常變大時, 可以參考看看!

No responses yet

七月 16 2012

如何在沒有distributor及subscriber的狀況下, 移除publication (MS SQL Server)

Published by under 資料庫

有時候, 在測試環境下(當然生產環境下也有可能發生), 會因為某些原因, 在一組架構好的 replication 下, 沒有了 distributor 及 subscriber 的狀況下, 但 publication 還在時, 如何能移除該 publication.

使用 UI 操作的狀況下, 會回應沒有 distributor, 所以移除失敗等狀況, 所以得使用指令的方式來進行, 使用方式如下:


sp_replicationdboption @dbname= 'dbname',
@optname=  'Publish',
@value= 'false',
@ignore_distributor=  1

(參考 sp_replicationdboption 指令用法: http://msdn.microsoft.com/zh-tw/library/ms188769.aspx )

該資料庫(db)在沒有其他發行集(publication)的狀況下, 直接使用這個指令即可, 可以快速地將 publication 移除, 方便又好用!

One response so far

十二月 09 2011

使用備份來做起始化Replication-MS SQL

Published by under 資料庫

交易式複寫從備份初始化,而非快照集 – http://byronhu.wordpress.com/2009/09/01/%E4%BA%A4%E6%98%93%E5%BC%8F%E8%A4%87%E5%AF%AB%E5%BE%9E%E5%82%99%E4%BB%BD%E5%88%9D%E5%A7%8B%E5%8C%96%EF%BC%8C%E8%80%8C%E9%9D%9E%E5%BF%AB%E7%85%A7%E9%9B%86/

How to: Initialize a Transactional Subscriber from a Backup (Replication Transact-SQL Programming) – http://msdn.microsoft.com/en-us/library/ms147834%28v=SQL.90%29.aspx

No responses yet

五月 06 2011

SQL Server的Replication中的Article必須要有Primary Key

Published by under 資料庫

這個是朋友請我查的資料, 其實邏輯上還蠻容易的, 因為 SQL Server的Transactional Replication是參考table的primary key來進行新增刪除修改的, 若是該table沒有primary key的話, 是沒有辦法加入到publication的article裡的.

資料可以參考這裡:

http://msdn.microsoft.com/en-US/library/ms152559%28v=SQL.90%29.aspx

其中的:

Limitations on Publishing Objects

  • The maximum number of articles and columns that can be published differs by publication type. For more information, see the “Replication Objects" section of Maximum Capacity Specifications for SQL Server 2005.
  • Stored procedures, views, triggers, and user-defined functions that are defined as WITH ENCRYPTION cannot be published as part of SQL Server replication.
  • XML schema collections can be replicated but changes are not replicated after the initial snapshot.
  • Tables published for transactional replication must have a primary key. If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.
  • Bound defaults created with sp_bindefault (Transact-SQL) are not replicated (bound defaults are deprecated in favor of defaults created with the DEFAULT keyword of ALTER TABLE or CREATE TABLE).

 

上面紅字部分就是說明這個限制.

 

No responses yet

十一月 09 2010

SQL Server Optimization

Published by under 資料庫

這篇文章寫得十分有用, 建議有在做資料庫效能調校的朋友們可以讀一下.

http://msdn.microsoft.com/en-us/library/aa964133%28SQL.90%29.aspx

其中最後有還蠻精要的整理, 也就是這段落: Optimization Rules of Thumb

對於 index 等的設計都有蠻好的解說及經驗, 利用執行計劃 (execution plan) 的數據資料來檢視索引建立的狀況是否良好, 而且也應該適時更新統計資料(statistics), 利用這個指令: update statistics <TableName>, 更新好統計資料的 table , 再來檢視執行計劃, 會更準確.

另外查詢時, 儘量不要對資料欄位進行操作, 也就是以原本的資料來做比對及判斷, 查詢引擎才會使用對應的索引, 若利用了函數或一些轉型, 讓原始欄位改變的查詢條件, 查詢引擎就沒有辦法使用索引帶來的好處了, 如文章中舉的例子, 像是:

cast(DateField as varchar(20)) = @dateString

應該調整為 @dateString 轉型成為 DateField 的型態, 再來進行查詢, 肯定效果會更好, 另外還有這個例子(不是文章中提到的)也是很經典, 像是

Left(VarCharField, 1) = ‘k’

應該要改為

VarCharField like ‘k%’

這樣也是較函數來得佳, 都是可以改善查詢效能的.

試看看, 應該都有空間可以再精進!

No responses yet

七月 07 2010

SQL Server 中的 Pivot 語法

Published by under 程式技術,資料庫

從 SQL Server 2005 起, 支援了 Pivot 語法及 UnPivot 語法.

利用 Pivot 語法, 可以做出一些轉置及特殊報表使用的查詢結果(對於要分析資料時特別有用).

先來建立範例資料, 這個資料表是一個流水號(logid, 一個會員編號 userno, 一個投票標的號 voteid, 一個時間欄位 createtime), 為能將資料表模擬真實的狀況, 建立一些資料來供應用, script 如下:


CREATE TABLE VOTELOG (logid int identity(1,1), userno int, voteid int, createtime datetime default getdate())
insert into VOTELOG (userno, voteid, createtime) values (1, 1, '2010/7/1 1:15')
insert into VOTELOG (userno, voteid, createtime) values (2, 1, '2010/7/1 3:27')
insert into VOTELOG (userno, voteid, createtime) values (4, 2, '2010/7/1 3:29')
insert into VOTELOG (userno, voteid, createtime) values (15, 2, '2010/7/1 16:31')
insert into VOTELOG (userno, voteid, createtime) values (35, 1, '2010/7/1 17:31')
insert into VOTELOG (userno, voteid, createtime) values (24, 1, '2010/7/1 17:38')
insert into VOTELOG (userno, voteid, createtime) values (8, 2, '2010/7/1 20:18')
insert into VOTELOG (userno, voteid, createtime) values (7, 2, '2010/7/2 2:22')
insert into VOTELOG (userno, voteid, createtime) values (32, 1, '2010/7/2 2:36')
insert into VOTELOG (userno, voteid, createtime) values (9, 1, '2010/7/2 10:09')
insert into VOTELOG (userno, voteid, createtime) values (25, 2, '2010/7/2 13:35')
insert into VOTELOG (userno, voteid, createtime) values (19, 2, '2010/7/2 18:19')
insert into VOTELOG (userno, voteid, createtime) values (16, 2, '2010/7/2 19:28')
insert into VOTELOG (userno, voteid, createtime) values (6, 1, '2010/7/2 23:06')
insert into VOTELOG (userno, voteid, createtime) values (36, 2, '2010/7/3 0:42')
insert into VOTELOG (userno, voteid, createtime) values (18, 2, '2010/7/3 2:19')

假如現在我們需要分時的每日投票狀況的報表, 可以使用如下的 pivot查詢:


select dates, [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23]
from (select convert(varchar, createtime, 111) as dates, datepart(hh, createtime) as hours from votelog ) as cum
pivot (count(hours) for hours in ( [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23] )) as pv

查詢結果如下:

這樣可以很清楚地呈現出, 每日分時的統計狀況, 當然, 若是要查的是分開兩個候選人的結果, 僅需要在中間的子查詢(cum)的裡面多加入 where 條件即可.

另外多提一個部分, 這樣的查詢, 可以在子查詢先做集總, 再進行 pivot, 效果會更好(尤其在資料量愈大時), 語法如下:


select dates, [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23]
from (select convert(varchar, createtime, 111) as dates, datepart(hh, createtime) as hours, count(*) as counts from votelog group by convert(varchar, createtime, 111), datepart(hh, createtime) ) as cum
pivot (count(counts) for hours in ( [0],[1],[2],[3],[4],[5],[6],[7], [8],[9],[10],[11],[12],[13],[14],[15], [16],[17],[18],[19],[20],[21],[22],[23] )) as pv

也就是在子查詢中(cum), 先 group 好, 再進行 pivot 的查詢(但記得要用sum前面count結果), 這樣的效果會更有效率.

pivot 及 unpivot 語法可以參考以下資料:

使用 PIVOT 和 UNPIVOT: http://technet.microsoft.com/zh-tw/library/ms177410%28SQL.90%29.aspx

另外有人整理好的 cross-table stored procedure也很方便, 可以參考這篇: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

No responses yet

二月 25 2010

SQL Server中的索引離散狀況

Published by under 資料庫

SQL Server 資料庫中的索引是用來加速查詢效率的一種方式, 利用了空間換取時間的方法來將查詢的速度增加.

既然是利用空間換取時間的方法, 代表著該存放的空間, 資料的排列狀況就會隨著時間的過去, 有資料的新增, 刪除, 修改時, 造成的索引離散狀況, 若是索引離散時, 代表著存取這些索引的 IO 量會增加, 也代表著效率會下降的狀況會發生.

如何查詢這個資料, 可以參考這篇文章: http://sharedderrick.blogspot.com/2010/02/index-fragmentation.html 有詳細的說明, 利用該查詢指令, 若是要查找特定 table 的話, 可以這樣下(SQL Server 2005以上版本):

SELECT sch.name N’結構描述’, obj.name N’資料表’,
inx.name AS N’索引名稱’,
index_type_desc N’索引類型’,
avg_fragmentation_in_percent ‘片段(%)’,
avg_page_space_used_in_percent N’頁面飽和度(%)’,
fragment_count,
avg_fragment_size_in_pages,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(‘TABLE_NAME’),NULL, NULL, ‘SAMPLED’) AS phy
INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> ‘HEAP’  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 5 DESC

利用了 OBJECT_ID() 函數, 把 table 的 object id 找出來, 便能指定查找特定 table 中的索引離散狀況.

根據微軟官方的資料: http://technet.microsoft.com/zh-tw/library/ms189858.aspx , 離散狀況在 5% 以下不需要調整, 在 5% ~ 30%之間, 可以利用 ALTER INDEX REORGNIZE 來進行重組, 而大於 30% 時, 可以使用 ALTER INDEX REBUILD (ONLINE = ON) 來進行索引重建.

不過若系統效能允許的狀況下, 其實也是可以直接使用 DROP INDEX, CREATE INDEX 的方式來重建的.

其他相關文章一併整理如下:

Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://technet.microsoft.com/en-us/library/cc966523.aspx

sys.dm_db_index_physical_stats (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188917.aspx

ALTER INDEX (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188388.aspx

Table and Index size in SQL Server
http://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server

SQL Server Indexes
http://odetocode.com/articles/70.aspx

One response so far

十月 22 2009

SQL Server全文檢索資料庫的復原

Published by under 資料庫

一般資料庫我們備份後, 由於全文檢索技術, 和資料庫引擎不同, 是使用檔案及全文檢索引擎架構的一種方式, 所以在回復到異地後, 資料庫上線了, 但 fulltext catalog 內的資全文檢索資料不能用的問題, 這時候, 只需要再做一次 rebuild 即可, 因為所有的資料庫及相關欄位等設定其實都在, 但路徑上及檔案結構上的問題, 導致會有無法讀取的狀況. 這時候, 於 restore 資料庫後, 做一次 fulltext catalog 的 rebuild, 問題就能順利解決了.

若是原地復原, 則沒有這個問題.

No responses yet

五月 26 2009

查詢分割資料表的資料存放位置

Published by under 程式技術,資料庫

由於資料量龐大, 為能改善效能, SQL Server 2005 增加了 Partition Table 功能將 table 依 partition function 來做分塊"橫切"的方式存放, 以將常用資料及不常用資料(如歷史訂單資料), 做實體的存放位置劃分, 讓它實際上是同一個表, 但在存放上是在不同的 filegroup 上, 如此一來, 便能有效將資料表做分塊(分區橫切)管理, 若是 partition function 切割有效的話, 對於查詢應用及備份應用應該會有非常大的幫助.

接下來, 我們需要了解的是實作完成後的 partition table 內, 各分區的資料存放筆數狀況, 可以利用兩個系統表來操作取得, 分別為:

  1. sys.partitions : (http://msdn.microsoft.com/zh-tw/library/ms175012.aspx)
  2. sys.dm_db_partition_stats: (http://msdn.microsoft.com/zh-tw/library/ms187737.aspx)

若是要取得某 table 名為 tblTest 這個 table 在各 partition table 中的存放筆數, 可以利用這個查詢:


SELECT ps.partition_number
,ps.row_count, ps.index_id
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('tblUsers')

這樣取得的結果, 一般若非 partition table, 會得到一筆資料, 例如:

d1

其中三個欄位說明如下:

  • partition_number: 指對應的資料分割編號(以1為基礎), 把他想成存放的分區代碼
  • row_count: 這個就是資料筆數
  • index_id: 這個很重要, 0為堆積, 1為clustered, 2以上為non-clustered

若是有無切分 partition table , 但又有多個 index 的 table 會得到如下結果:

d2

其中, 就是 1為clustered, 而9, 11, 12為 non-clustered 的資料.

再來就是看有切 partition table 的結果:

d3

這個案例來自於這篇文章: [SQL Server]如何將現有table調整為partition table (http://bbs.diary.tw/viewtopic.php?f=13&t=464)

其中, 可以清楚地看到 index_id 為0的, 是 partition_number 為1, 2, 3的資料存放筆數狀況, 而 index_id為2的是後來利用增加 primary key non-clustered index 的方式加入的索引, 得到總筆數的一個 index 存放狀況.

簡而言之, 資料存放的方式為 parititon1 放了 2筆, partitiona2放了 3筆, partition3放了4筆資料, 這樣的查詢結果對於要查目前的 partition table內存放資料的對應 parition 狀況應該就有很清楚的結果呈現了.

No responses yet

Next »