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 SQL Server效能-學習資源 查詢處理架構: 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…

0

SQL Server 2008 R2 Performance Dashboard Reports

SQL Server 2008 R2 Performance Dashboard Reports SQL Server 2008 R2效能儀表板報表 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…

1

設定收集效能計數器記錄檔,建立SQL Server 效能基準線Configure Performance Log collection for SQL Server Performance Baseline

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啟動 或 每天 上午12:00啟動 9.在Stop Condition頁籤,設定停止條件,例如勾選Overall Duration 16 hours,在Limit限制的區塊,勾選 Restart the data collector set at…

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