SQL Server 2017: World’s First Diskless Database
(引用自: https://www.youtube.com/watch?v=QElCYYoVA3Q)
使用 dbcc dropcleanbuffers 用來清除快取內資料, 確保資料都由 disk 輸出.
使用 set statistics time on 來記錄使用時間.
從影片可以看出來在 diskless 資料庫比 ssd 資料庫還要快 5 倍的效率(指使用時間來比較)
在 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 的時間與空間了.
前一篇提到: 如何在沒有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 移除, 方式可以參考前面文章連結的內容.
若是資料庫備份有快速異常變大時, 可以參考看看!
有時候, 在測試環境下(當然生產環境下也有可能發生), 會因為某些原因, 在一組架構好的 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 移除, 方便又好用!
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
這個是朋友請我查的資料, 其實邏輯上還蠻容易的, 因為 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).
上面紅字部分就是說明這個限制.
這篇文章寫得十分有用, 建議有在做資料庫效能調校的朋友們可以讀一下.
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%’
這樣也是較函數來得佳, 都是可以改善查詢效能的.
試看看, 應該都有空間可以再精進!
從 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
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
一般資料庫我們備份後, 由於全文檢索技術, 和資料庫引擎不同, 是使用檔案及全文檢索引擎架構的一種方式, 所以在回復到異地後, 資料庫上線了, 但 fulltext catalog 內的資全文檢索資料不能用的問題, 這時候, 只需要再做一次 rebuild 即可, 因為所有的資料庫及相關欄位等設定其實都在, 但路徑上及檔案結構上的問題, 導致會有無法讀取的狀況. 這時候, 於 restore 資料庫後, 做一次 fulltext catalog 的 rebuild, 問題就能順利解決了.
若是原地復原, 則沒有這個問題.