Database Corruption Challenge – Steve Stedman

2016年9月13日 PASS Database Corruption Challenge – Steve Stedman Presented by Steve Stedman https://www.youtube.com/watch?v=5oBA5nL0uRc&feature=youtu.be&a 1.備份資料庫,還原至測試機來進行修復工作(確保還原程序遭遇問題時,可以重頭再來修復一次) 2.如果決定用allow data loss,嘗試先找出可能遺失的資料並事先嘗試SELECT INTO保留下來 3.執行之前尋求是否有其他人的意見   定期執行 osql -E -Q”DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS” -oC:\outputfile.txt sqlcmd -E -Q”DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS” -o C:\outputfile.txt   PS.WITH NO_INFOMSGS只顯示錯誤訊息,一般的information不顯示   基本流程 1.將有損毀的資料庫備份檔還原到測試機,切換成single_user mode。 ATLER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK_IMMEDIATELY; 2.取得錯誤訊…

0

add text to beginning or end of each line using SSMS

1.Add text to the beginning of each line Ctrl+H type “^” in the “Find” type text you want to add in “Replace with” Choose the “Regular expressions” checkbox   2.Add text to the end of each line Ctrl+H type “$” in the “Find” type text you want to add in “Replace with” Choose the “Regular…

0

DBA Fundamentals-SQL Server Performance Tuning

2016/9/21 PASS online event DBA Fundamentals: SQL Server Performance Tuning Presented by Pinal Dave 1.資料庫相容性層級影響基數估計(Cardinality Estimation) 提升資料庫相容性層級就可以改善效能 The New and Improved Cardinality Estimator in SQL Server 2014 https://blogs.technet.microsoft.com/dataplatforminsider/2014/03/17/the-new-and-improved-cardinality-estimator-in-sql-server-2014/ Cardinality Estimation (SQL Server) https://msdn.microsoft.com/en-us/library/dn600374.aspx CE(Cardinality Estimation)會預測您的查詢可能傳回的資料列數目。 查詢最佳化工具使用基數預測,來產生最佳查詢計劃。 CE 愈精確,查詢計劃通常愈理想。 In 1998, a major update of the CE was part of Microsoft SQL Server 7.0, for which…

0

Distributed query vs OPENQUERY

Distributed query (Four-part name) and OPENQUERY OpenQuery Distributed Query 速度 一般來說 OPENQUERY較快一點 也很快 Query optimizer 遠端產生執行計畫 本地產生執行計畫 產生local query與remote query 連線數量 只會產生1條連線到遠端取回資料 會產生2條連線 第1條連線先取回統計資訊 第2條連線再取回資料 優點 可以在一個Query裡面JOIN多個不同SQL Server的Table 可能出現issue 如果太多連線使用Distributed Query,則會造成大量等待SOSHOST_MUTEX wait 缺點 雖然有WHERE條件,但SQL Server可能會送出SELECT * FROM the remote table,然後等資料回到本地端才進行filter 權限 只需要設定SELECT資料表的權限 為了產生最佳執行計畫,remote login account必須有以下權限,才能取得完整的統計資訊,若沒有以下權限,則查詢效能則會比較差 To create the best query plans the user must own the…

0

SQL Server Performance study resources

SQL Server Performance study resources 查詢處理架構: Query Processing Architecture https://technet.microsoft.com/en-us/library/cc280362(v=sql.105).aspx 效能監控與分析: Monitor and Tune for Performance https://msdn.microsoft.com/en-us/library/ms189081.aspx Topic Task Monitor SQL Server Components Required steps to monitor any SQL Server component. Performance Monitoring and Tuning Tools Lists the monitoring and tuning tools available with SQL Server. Establish a Performance Baseline How to establish a performance…

0

SQL Server 2008 R2 Performance Dashboard Reports

Microsoft® SQL Server® 2012 Performance Dashboard Reports https://www.microsoft.com/en-us/download/details.aspx?id=29063 Supported Operating System Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2 Works with the following SQL Server versions: SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 1.安裝 2.設定 Getting Started With the Performance…

1

Configure Performance Log collection for SQL Server Performance Baseline

設定收集效能計數器記錄檔,建立SQL Server效能基準線 1.啟動效能監視器 Perfmon 2.新增收集器 Data Collector Sets>User Defined>New>Data Collector Set 3.輸入名稱 (例如 SQLPerfLogBaseline)並選擇 Create from a template (Recommended) 4.範本Template,選擇System Performance 5.指定blg檔存放的根目錄 6.選擇Open Properties for this data collector set 7.按下Finish之後,則出現此視窗 8.在schedule頁籤,新增一個排程,例如每天08:00啟動 9.在Stop Condition頁籤,設定停止條件,例如勾選Overall Duration 16 hours,在Limit限制的區塊,勾選 Restart the data collector set at limits.,勾選 Maximum Size:設定300MB 10.按下OK之後,回到主視窗,若不需要可將NT Kernel trace刪除 11.在Performance Counter按右鍵選擇 Properties 12.修改預設選取的Performance counters,只留下需要的Performance Counter 12.修改與確認blg黨與報表檔保留期間…

0

SQL Server Rowset Trace

Rowset trace(Rowset Provider) Server-Side Trace with file provider(File Provider) 如果是Server-Side Trace則是使用file provider select * from sys.traces where is_rowset = 0 and path is not NULL PS. The file provider is designed with a guarantee that no event data will be lost. Rowset Trace(Rowset Provider) SQL Server Profiler連線到SQL Server啟動的就是Rowset Trace (使用rowset provider) select * from sys.traces…

0

Visual Studio(SSDT BIDS) for SQL Server

Visual Studio(SSDT BIDS) for SQL Server SSDT: SQL Server Data Tool BIDS: Business Intelligence Development Studio SQL Server Visual Studio Version SQL Server 2005 透過SQL Server安裝程式勾選安裝 Visual Studio 2005 8.0.50727.42 (RTM.050727-4200) SQL Server 2008 透過SQL Server安裝程式勾選安裝 Visual Studio 2008 9.0.30729.1 SP Visual Studio 2008含有SP1 Actions that are required before you install SQL Server 2008 on…

0

Delete large amount of data from a table

刪除大量資料作法 Method 1 若刪除完成之後留下的資料較多的話(例如要刪除1/3的資料),就用WHILE DELETE top語法來刪除  declare @n int  while 1=1  begin  DELETE top(2000)  FROM dbo.BigTable  WHERE time <= ‘2013-09-03 22:00:00.000’  OPTION(MAXDOP 1) — 可考慮是否只使用一個CPU來執行刪除動作  set @n=@@ROWCOUNT  if @n<2000  break  end Method 2 若留下的資料比較少(例如要刪除2/3的資料或更多的資料),就可以考慮INSERT INTO再TRUNCATE或INSERT INTO再RENAME INSERT INTO and TRUNCATE 1.將要保留的資料INSERT INTO到dbo.Temp_BigTable SELECT * INTO dbo.Temp_BigTable FROM dbo.Temp_BigTable WHERE Date < ‘2015/1/1’; 2.清空dbo.Temp_BigTable TRUNCATE TABLE…

0