SQL Server Management Studio Keyboard Shortcuts

SQL Server Management Studio Keyboard Shortcuts 常用快速鍵 SQL Server Management Studio Keyboard Shortcuts https://msdn.microsoft.com/en-us/library/ms174205.aspx Action SQL Server 2016 SQL Server 2008 R2 Select text from the cursor to the beginning of the document CTRL+SHIFT+ HOME CTRL+SHIFT+ HOME Select text from the cursor to the end of the document CTRL+SHIFT+END CTRL+SHIFT+END Select text from the cursor…

0

SQL Server Physical Joins

SQL Server Physical Join (Nested Loops joins, Merge joins, Hash joins) Advanced Query Tuning Concepts https://technet.microsoft.com/en-us/library/ms191426(v=sql.105).aspx SQL Server employs three types of join operations: Nested loops joins Merge joins Hash joins   Nest Loops Joins If one join input is small (fewer than 10 rows) and the other join input is fairly large and indexed…

0

Log Shipping Monitoring and Troubleshooting

Log Shipping Monitoring and Troubleshooting 交易紀錄傳送監控與疑難排解 1.Log Shipping status View the Log Shipping Report (SQL Server Management Studio) https://msdn.microsoft.com/en-us/library/ms181149.aspx To display the Transaction Log Shipping Status report on a server instance Connect to a monitor server, primary server, or secondary server. Right-click the server instance in Object Explorer, point to Reports, and point to Standard…

0

SQL Server Learning Resource

SQL Server 學習資源 中英文官方線上課程 Microsoft Virtual Academy Database Development Course https://mva.microsoft.com/training-topics/database-development#!jobf=Developer&lang=1033 必學的 SQL Server 2014 資料庫管理技巧 https://mva.microsoft.com/zh-tw/training-courses/-sql-server-2014–11125 SQL Server 管理入門 (一):安裝、設定與連線 https://mva.microsoft.com/zh-tw/training-courses/sql-server–11718?l=Pym6n7iEB_9804984382 SQLPASS http://www.sqlpass.org/ 線上研討會 研討會錄影檔會放到youtube High Availabilty Disaster Recovery Virtual Chapter https://www.youtube.com/channel/UCVXM_bmBuTEln3K4GHeuBAQ SQLPASS Taiwan https://www.facebook.com/groups/sqlpasstaiwan/ 每個月舉辦研討會 Super SQL Server https://www.facebook.com/groups/222546864546011/ 不定期舉辦線上研討會課程 Tutorials SQL Server 2008 R2 https://msdn.microsoft.com/en-us/library/ms167593(v=sql.105).aspx SQL Server技術文件 SQL Server 2008 R2 –…

0

File xxx.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see get-help about_signing

PS C:\Temp>.\myPS.ps1 C:\Temp\myPS.ps1 檔案無法載入,因為這個系統上已停用指令碼執行。如需詳細資訊,請參閱 “get-help about_signing”。 位於 行:x 字元:xx + .\myPS.ps1 <<<< + CategoryInfo          : NotSpecified: (:) [], PSSecurityException + FullyQualifiedErrorId : RuntimeException File C:\Temp\myPS.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details. Resolution: PS C:\Users\Administrator>Set-ExecutionPolicy RemoteSigned Using the Set-ExecutionPolicy Cmdlet https://technet.microsoft.com/en-us/library/ee176961.aspx Restricted…

0

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