Distributed query vs OPENQUERY

Article
09/19/2016

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 table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server. https://msdn.microsoft.com/en-us/library/ms175537.aspx 但SQL 2012開始則不需要此權限

Best Performer: Distributed query (Four-part) or OPENQUERY when executing linked server queries in SQL Server
https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/
Security for Linked Servers
https://msdn.microsoft.com/en-us/library/ms175537.aspx
Guidelines for Using Distributed Queries
https://msdn.microsoft.com/en-us/library/ms175129.aspx
OPENQUERY (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188427.aspx
Optimizing Distributed Queries
https://technet.microsoft.com/en-us/library/ms180972(v=sql.105).aspx
Improving the Performance of Distributed Queries
https://sqlmag.com/database-performance-tuning/improving-performance-distributed-queries