互聯網 - 大數據

文章出處
記一次公司倉庫數據庫服務器死鎖過程
 
倉庫揀貨卡死，排查了數據庫的很多地方，都沒有頭緒，最后到SQL Server 錯誤日志里查看，終于發現了蛛絲馬跡

EXEC xp_readerrorlog 0,1,NULL,NULL,'2015-09-21','2015-10-10','DESC'

 waiter id=process5c30e08 mode=U requestType=wait
waiter-list
owner id=process5c26988 mode=X
owner-list
keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.Orderxxx indexname=IX_PricingExpressProductCode_State id=lock17fa96980 mode=X associatedObjectId=72057597785604096
waiter id=process5c26988 mode=U requestType=wait
waiter-list
owner id=process5c30e08 mode=X
owner-list
keylock hobtid=72057597785604096 dbid=33 objectname=stoxxx.dbo.Orderxxx indexname=IX_PricingExpressProductCode_State id=lock87d69e780 mode=X associatedObjectId=72057597785604096
resource-list
(@OperateState money,@HandledByNewWms bit,@State int,@OrderOut int)
UPDATE [Orderxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR'))) 
inputbuf
unknown 
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
UPDATE [Orderxxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR'))) 
frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
executionStack
process id=process5c30e08 taskpriority=0 logused=10320 waitresource=KEY: 33:72057597785604096 (112399c2054a) waittime=4813 ownerId=31578743038 transactionname=user_transaction lasttranstarted=2015-09-24T10:22:58.410 XDES=0x372e95950 lockMode=U schedulerid=17 kpid=8496 status=suspended spid=153 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24T10:22:58.540 lastbatchcompleted=2015-09-24T10:22:58.540 clientapp=.Net SqlClient Data Provider hostname=CK1-WIN-WEB02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31578743038 currentdb=33 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
(@OperateState money,@HandledByNewWms bit,@State int,@OrderOut int)UPDATE [Orderxxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR'))) 
inputbuf
unknown 
frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
UPDATE [Orderxxx] SET [OperateState] = @OperateState,[HandledByNewWms] = @HandledByNewWms WHERE (([Orderxxx].[State] = @State) And ([Orderxxx].[OrderOut] = @OrderOut) And ([Orderxxx].[PricingExpressProductCode] IN ('UKNIR'))) 
frame procname=adhoc line=1 stmtstart=134 sqlhandle=0x020000009d376d18a17e7ea51289d8caa2fb4de65c976389
executionStack
process id=process5c26988 taskpriority=0 logused=9892 waitresource=KEY: 33:72057597785604096 (70f5b089bb2b) waittime=4813 ownerId=31579268946 transactionname=user_transaction lasttranstarted=2015-09-24T10:27:01.357 XDES=0x98312f950 lockMode=U schedulerid=16 kpid=9184 status=suspended spid=454 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2015-09-24T10:27:01.490 lastbatchcompleted=2015-09-24T10:27:01.487 clientapp=.Net SqlClient Data Provider hostname=CK1-WIN-WEB02 hostpid=37992 loginname=ck1.biz isolationlevel=read committed (2) xactid=31579268946 currentdb=33 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
process-list
deadlock victim=process5c26988
deadlock-list

 
咋一看上面的錯誤信息，可以發現兩條相同的語句造成的死鎖，但是這么短的語句不可能持有排他鎖太久
 
 
再仔細分析一下錯誤日志，發現都死鎖在同一個非聚集索引上，再問了一下開發，開發那邊說，這條語句是在一個大事務里面，這個事務會做7、8件事
 
索引屬性
 
 
還有索引里面的數據，發現很多重復值
 
 
SQL語句是這樣的

(@OperateState money,@HandledByNewWms bit,@State int,@OrderOut int)
@HandledByNewWms=(1) @OperateState=($1.0000) @OrderOut=(4055484) @State=(3) 
UPDATE [Orderxxx] SET [OperateState] = $1.0000,[HandledByNewWms] = 1
WHERE (([Orderxxx].[State] = 3) And ([Orderxxx].[OrderOut] = 4055484) And ([Orderxxx].[PricingExpressProductCode] IN ('UKRRM','UKRLE')))

 
 
下圖為語句生成的執行計劃
 
 
當時的情況是大量SQL語句被阻塞，而阻塞的語句正是下面這條語句

UPDATE [Orderxxx] SET [OperateState] = $1.0000,[HandledByNewWms] = 1
WHERE (([Orderxxx].[State] = 3) And ([Orderxxx].[OrderOut] = 4055484) And ([Orderxxx].[PricingExpressProductCode] IN ('UKRRM','UKRLE')))

 
 
解決方法
上面得出幾個癥狀
1、update語句是在一個大事務里面，事務太大導致其他session等待排他鎖的時間變長
2、大家都在使用同一個非聚集索引，并掃描PricingExpressProductCode字段
3、索引里的重復值很多
 
從上面的癥狀基本可以判斷，這個非聚集索引無啥用，可以禁用之

ALTER INDEX [IX_PricingExpressProductCode_State] ON [dbo].[Orderxxx] DISABLE

禁用之后，死鎖消失，問題解決，倉庫的怨氣也隨之消失
 
這一次排查過程時間有點長，但是很好定位，SQL Server錯誤日志給出了足夠的信息定位死鎖問題，所以遇到問題的時候一定要分析清楚日志
 
 
如有不對的地方，歡迎大家拍磚o(∩_∩)o 

文章出處
SQL Server相關書籍
（排名不分先后）
Microsoft SQL Server 企業級平臺管理實踐
 
 
 
SQL Server 2008數據庫技術內幕
 
SQL Server性能調優實戰
 
SQL Server 性能優化與管理的藝術
 
SQL Server2012實施與管理實戰指南
 
 
 
 
SQL Server 2012王者歸來：基礎、安全、開發及性能優化
 
 
 
SQL Server 2008學習筆記：日常維護、深入管理、性能優化
 
 
SQL Server 2008 R2數據挖掘與商業智能基礎及高級案例實戰
 
 
SQL Server 2012 深入解析與性能優化（第3版）
 
SQL Server2012IntegrationServices高級教程(第2版)
 
 
SQL Server 2008管理員必備指南
 
 
SQL Server 2008報表服務從入門到精通
 
 
SQL Server2012管理高級教程(第2版)
 
 
SQL Server 2012編程入門經典（第4版）
 
 
SQL Server2005性能監測與優化
 
 
SQL Server 2008查詢性能優化
 
 
SQL Server 2012 Analysis Services高級教程（第2版）
 
 
 
SQL Server 2008入門與提高
 
 
 
SQL Server 2008實現與維護(MCTS教程)
 

文章出處
Windows和Linux都有的Copy-on-write技術
 
MySQL技術內幕Innodb存儲引擎第2版 P375
SQL Server2008 實現與維護（MCTS教程）P199
 
LVM快照技術
LVM使用寫時復制copy-on-write技術來創建快照，當創建一個快照時，僅復制原始卷中數據的元數據metadata，并不會有數據物理操作，因此快照創建過程非常快。當快照創建完成，原始卷上有寫操作時，快照會跟蹤原始卷塊的改變，將要改變的數據在改變之前復制到快照預留空間里，因此這個原理的實現叫寫時復制。對于快照讀取操作，如果讀取的數據塊是創建快照后沒有修改過的，那么會重定向讀取操作到原始卷，如果讀取的是已經修改過的塊，讀取保存在快照中該塊在原始卷改變之前的數據，簡單來將就是讀取快照中的數據，因此采用寫時復制機制保證了讀取快照得到的數據和快照和快照創建時一致
簡單來講：
寫：有變更寫快照，無變更不寫快照，寫快照是變更之前的版本
 
SQL Server的快照技術
SQL Server使用寫時復制copy-on-write，SQL Server對區extent進行讀寫，首次對一個區中的數據頁進行修改時，SQL Server將區的前影像復制到數據快照。
讀取select：無更改過的，讀取源庫，更改過的，讀取快照
由于在首次發生改變時寫入了區的前影像，所以SQL Server一方面允許對源庫進行更改
另一方面保證對數據庫快照的任何查詢不會反映創建快照之后發生的任何改變
最初的更改寫入區的一個頁，而且SQL Server將這個區的前影像寫入數據庫快照之后，寫時復制功能會忽略后續對這個區的任何更改
由于可以為一個源庫創建多個數據庫快照，所以凡是沒有獲得區的副本的數據庫快照都會被寫入區的一個前影像
 
原理跟Linux LVM的快照原理是一樣的！
 
如有不對的地方，歡迎大家拍磚o(∩_∩)o 

文章出處
數據庫運維保障
 
國慶假期本來是可以開開心心去玩的，但是由于某些突發情況，例如天災導致的數據庫故障的情況還是有可能出現
如果出現這種情況不但破壞了國慶假期玩樂的美好心情，節后上班也可能由于沒有做好預防措施要遭遇領導挨批。
 
為了避免發生這種情況，對于公司業務系統的相關運維人員來說不能掉以輕心，一定要做好預防措施。
以下是總結的一些突發情況預防措施
 
1、做好公司業務系統的監控報警，關鍵時刻啟動應急預案
 
2、服務器選擇雙電源服務器，避免單電源故障造成的服務器宕機
 
3、選擇優質的機房，機房一定要有發電機，遇到臺風這類天災不能到機房正常關機或者處理的情況下，發電機非常重要
 
4、服務器配置UPS，雙電源服務器，一個電源接入市電，另一個電源接入UPS，避免立刻斷電的情況下服務器非正常關機
 
5、數據庫做好容災方案，保證系統的可用性，SQL Server通常選擇的策略有：故障轉移集群、鏡像、日志傳送、AlwaysOn等，根據自身需求選擇合適的容災方案
 
6、異地災備，萬一機房出口網絡中斷可以切換到別的機房，重要業務系統考慮兩地三中心的異地災備方案
 
7、安排運維人員值班，在類似于國慶、春節這些長假期節日里面，可以考慮讓一些運維人員24小時在機房值班
 
TIPS說明
對于前四個措施，是基本要求，因為本人曾經看到過一些比較舊的機房，發電機也沒有，市電供電也不是特別穩定，如果不配備雙電源和UPS，那么服務器宕機可能性就會比較高
如果機房設施條件不是太好，經常停電、網絡出口不穩定，那么大家應該也不會選擇這樣的機房，機房不穩定，公司的業務也會受到牽連。
 
對于SQL Server的災備方案，如果資金允許的話可以購買存儲，并且用SQL Server故障轉移集群，數據庫放在共享存儲里面。
如果資金不允許又要數據不丟失，可以考慮使用SQL Server鏡像和AlwaysOn，把鏡像配置為高安全模式或者把AlwaysOn配置為同步模式，可以做到Failover的時候數據不丟失。
而資金不允許，但是可以容許分鐘級別的數據丟失，可以考慮日志傳送。
 
而一些非常非常重要的業務就需要考慮異地災備，例如全市斷電、運營商的網絡出口出現問題，這時候就需要考慮切換機房。
 
對于最后一點，安排相關運維人員值班本人認為是最有效的，因為誰都說不準哪一天可能遇到地震或者水災，這時候相關人員可能不能到機房或者公司進行應急處理
這時候只能依靠值班人員進行處理。
 

文章出處
SQL Server恢復軟件 Stellar Phoenix sql recovery
http://www.stellarinfo.com/
http://www.stellarinfo.com/sql-recovery.htm
SQL Database Repair
 
 
f
 
f
 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
SQL Server恢復軟件SysTools SQL Recovery/SysTools SQL Server Recovery Manager
http://www.systoolsgroup.com/sql-server-recovery-manager.html
http://www.systoolsgroup.com/sql-recovery.html
 
 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
ApexSQLLog可以只讀取ldf文件 
需要數據庫在線
 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
在一個SQL Server表中一行的多個列找出最大值
 
有時候我們需要從多個相同的列里（這些列的數據類型相同）找出最大的那個值，并顯示
這里給出一個例子

IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL)
DROP TABLE ##TestTable
CREATE TABLE ##TestTable
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(40),
UpdateByApp1Date DATETIME,
UpdateByApp2Date DATETIME,
UpdateByApp3Date DATETIME
)
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date )
VALUES('ABC', '2015-08-05','2015-08-04', '2015-08-06'),
('NewCopmany', '2014-07-05','2012-12-09', '2015-08-14'),
('MyCompany', '2015-03-05','2015-01-14', '2015-07-26')
SELECT * FROM ##TestTable

結果如下所示
 
 
有三種方法可以實現
方法一

SELECT ID ,
Name ,
( SELECT MAX(LastUpdateDate)
FROM ( VALUES ( UpdateByApp1Date), ( UpdateByApp2Date),
( UpdateByApp3Date) ) AS UpdateDate ( LastUpdateDate )
) AS LastUpdateDate
FROM ##TestTable

 
 
方法二

SELECT ID ,
[Name] ,
MAX(UpdateDate) AS LastUpdateDate
FROM ##TestTable UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date,
UpdateByApp2Date,
UpdateByApp3Date ) ) AS u
GROUP BY ID ,
Name 

 
 
方法三

SELECT ID ,
name ,
( SELECT MAX(UpdateDate) AS LastUpdateDate
FROM ( SELECT tt.UpdateByApp1Date AS UpdateDate
UNION
SELECT tt.UpdateByApp2Date
UNION
SELECT tt.UpdateByApp3Date
) ud
) LastUpdateDate
FROM ##TestTable tt

 
第一種方法使用values子句，將每行數據構造為只有一個字段的表，以后求最大值，非常巧妙
第二種方法使用行轉列經常用的UNPIVOT 關鍵字進行轉換再顯示
第三種方法跟第一種方法差不多，但是使用union將三個UpdateByAppDate字段合并為只有一個字段的結果集然后求最大值
 
第一種方法的執行計劃
 
第二種方法的執行計劃
 
第三種方法的執行計劃
 
 
總的來說，第一種方法的執行計劃是最好的
 
注意，這里不涉及分組

IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL)
DROP TABLE ##TestTable
CREATE TABLE ##TestTable
(
ID INT IDENTITY(1, 1)
PRIMARY KEY ,
Name NVARCHAR(40) ,
UpdateByApp1Date DATETIME ,
UpdateByApp2Date DATETIME ,
UpdateByApp3Date DATETIME
)
INSERT INTO ##TestTable
( Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date )
VALUES ( 'ABC', '2015-08-05', '2015-08-04', '2015-08-06' ),
( 'ABC', '2015-07-05', '2015-06-04', '2015-09-06' ),
( 'NewCopmany', '2014-07-05', '2012-12-09', '2015-08-14' ),
( 'MyCompany', '2015-03-05', '2015-01-14', '2015-07-26' )
SELECT *
FROM ##TestTable
SELECT ID ,
Name ,
( SELECT MAX(LastUpdateDate)
FROM ( VALUES ( UpdateByApp1Date), ( UpdateByApp2Date),
( UpdateByApp3Date) ) AS UpdateDate ( LastUpdateDate )
) AS LastUpdateDate
FROM ##TestTable

name列相同的話，是無法得出name分組之后的最大值，這里要注意一下
 
轉載自：https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/

文章出處
什么是XA事務
 
分布式事務處理是指一個事務可能涉及多個數據庫操作
分布式事務處理的關鍵是必須有一種方法可以知道事務在任何地方所做的所有動作，提交或回滾事務必須產生一致的結果（全部提交或全部回滾）。
XA是X/Open DTP組織（X/Open DTP group）定義的兩階段提交協議，XA被許多數據庫（如Oracle、DB2、SQL Server、MySQL）和中間件等工具(如CICS 和 Tuxedo).本地支持 。
X/Open DTP模型（1994）包括應用程序（AP）、事務管理器（TM）、資源管理器（RM）、通信資源管理器（CRM）四部分。
在這個模型中，通常事務管理器（TM）是交易中間件，資源管理器（RM）是數據庫，通信資源管理器（CRM）是消息中間件。
一般情況下，某一數據庫無法知道其它數據庫在做什么，因此，在一個DTP環境中，交易中間件是必需的，由它通知和協調相關數據庫的提交或回滾。而一個數據庫只將其自己所做的操作（可恢復）影射到全局事務中。
XA就是X/Open DTP定義的交易中間件與數據庫之間的接口規范（即接口函數），交易中間件用它來通知數據庫事務的開始、結束以及提交、回滾等
 
XA接口函數由數據庫廠商提供。通常情況下，交易中間件與數據庫通過XA 接口規范，使用兩階段提交來完成一個全局事務，XA規范的基礎是兩階段提交協議。
 
在第一階段：交易中間件請求所有相關數據庫準備提交（預提交）各自的事務分支，以確認是否所有相關數據庫都可以提交各自的事務分支。當某一數據庫收到預提交后，如果可以提交屬于自己的事務分支，則將自己在該事務分支中所做的操作固定記錄下來，并給交易中間件一個同意提交的應答，此時數據庫將不能再在該事務分支中加入任何操作，但此時數據庫并沒有真正提交該事務，數據庫對共享資源的操作還未釋放（處于鎖定狀態）。如果由于某種原因數據庫無法提交屬于自己的事務分支，它將回滾自己的所有操作，釋放對共享資源上的鎖，并返回給交易中間件失敗應答。
 
在第二階段：交易中間件審查所有數據庫返回的預提交結果，如所有數據庫都可以提交，交易中間件將要求所有數據庫做正式提交，這樣該全局事務被提交。而如果有任一數據庫預提交返回失敗，交易中間件將要求所有其它數據庫回滾其操作，這樣該全局事務被回滾。
 
在Windows中的DTC屬性中設置開啟 XA事務 ，SQL Server就可以聯同Windows使用分布式事務
 
相關文章：http://www.sqldbadiaries.com/2014/09/24/no-transaction-is-active-message-when-accessing-linked-server/
 
如有不對的地方，歡迎大家拍磚o(∩_∩)o 
本文版權歸作者所有，未經作者同意不得轉載。

文章出處
2015 西雅圖微軟總部MVP峰會記錄
今年決定參加微軟MVP全球峰會，在出發之前本人就已經寫這篇博客，希望將本次會議原汁原味奉獻給大家
因為這次是本人第一次寫會議記錄，寫得不好的地方希望各位園友見諒！
 
出發前
關于簽證問題，微軟會給決定參加峰會的朋友發送邀請函的，Visa Officer看到邀請函之后90%會給你通過的，所以不需要太擔心
微軟的邀請函
 
 
在出發前的最后一個星期是選擇課程，因為峰會這幾天時間是需要上課的
登錄峰會的選課系統
因為我是SQL Server方向，所以基本都是SQL Server相關的課程
 
 
今天是2015年10月31日，開始正式出發！
 
這一次去的地方是西雅圖，也是云計算兩大巨頭總部所在地--云計算兩大巨頭 亞馬遜和微軟，據說某些MVP在前年去的時候在街上撞見亞馬遜中國的一些員工
當時給他們提了一些亞馬遜中國購物網站的意見，如果這次讓我碰見亞馬遜員工，我也要跟他們提提意見，關于亞馬遜AWS的一些意見
（我們公司使用亞馬遜AWS在美國西部部署了一些節點）
 
 
進行中
廢話不說太多了，附上精選圖片，有些圖片是用手機拍的，可能拍得不是那么好，希望大家見諒
 
景色相關
太空針塔
Company store外面的logo
Building 92外面的景色
Building 92外面的景色
微軟園區內部有East  Campus和West Campus
園區里的林蔭小道
足球場
穿梭巴士站
 
峰會注冊和歡迎晚宴
注冊地點設置在Hyatt Hotel
給MVP的小禮物
我的Name Badge
歡迎晚宴 Welcome Reception
小合照
大合照
Ball room
 
活動
峰會開始前的熱身活動 農作
微軟園區里面的牌子  雙11是美國的退伍軍人節 Veterans Day  哈哈~
 
會議
 
多個會議室，不同會議室進行不同產品的課程
Building33 會議中心
北美的SQL MVP  Lead
.NET課室 妹紙在認真聽課
又見大神
服務臺
頗大的會議室
SQL Server安全團隊負責人在講解SQL 2016的最新安全特性 
 
 
結束后
活動
峰會最后一天舉行的.NET和ASP.NET/IIS的 Hackathon
 
 
這次峰會里面認識了很多朋友，見識了很多東西，至于自己的專業SQL Server數據庫，微軟也一直在進步，在SQL 2016里面包括R語言的集成、BI功能的改進 還有其他企業安全特性
這些功能都使SQL Server成為一個更加好的企業級數據庫
在SQL Server課室里面有Apex SQL 團隊成員，Sql Sentry團隊成員，SQL Server geeks的帶頭人，見到很多業內大牛
希望明年也可以繼續參加峰會，繼續認識更多朋友
 
這次微軟安排了我和一個印度朋友同一個房間，他是ASP.NET方向的高級工程師，他說在印度，JAVA和.NET的工資是一樣的
JAVA和.NET比較沒有哪個差哪個好之分，相比起國內天天在爭論確實有點。。。
 
本文版權歸作者所有，未經作者同意不得轉載。