SQL Server 2005效能調整二(zt)

zhouwf0726發表於2019-05-27
SQLServer
SQL Server 2005效能調整二(zt)

在查詢中涉及的表上執行UPDATE STATISTICS，檢查是否還有這種問題。

是否查詢使用的構造導致優化器不能精確的評估？

考慮是否可以將查詢修改為其他的方法，避免這種問題。

◆如果不能修改架構或查詢，SQL Server 2005有一個新的查詢計劃特性，允許你將指定查詢提示新增到滿足某種文字的查詢中。這可以用在獨立查詢中，也可以用在儲存過程內。例如OPTION (OPTIMIZE FOR)這樣的提示允許你影響評估而忘記所有列出的潛在計劃。其他的提示，類似OPTION(FORCE ORDER)或 OPITON(USE PLAN)允許你改變控制查詢計劃的程度。

內部查詢的並行

當為一個查詢生成執行計劃時，SQL Server優化器嘗試為該查詢選擇最快的相應計劃。如果查詢的開銷超過了在cost threshold for parallelism選項中指定的值，並行不會被禁用，優化器嘗試生成一個可以用於並行的計劃。並行查詢計劃使用多執行緒處理查詢，每個執行緒分佈在可用的 CPU上並同時利用每個CPU的時間資源。最大的並行度可以通過伺服器上的max degree of parallelism選項或每個查詢使用OPTION(MAXDOP)提示限制。

用於執行實際並行度（DOP）的結果——度量有多少執行緒將在給定的操作上並行——是知道執行時才能確定。在執行查詢前，SQL Server 2005決定有多少個排程器未充分利用併為查詢選擇DOP來充分利用剩餘的排程器。一旦一個DOP被選擇了，直到完成，查詢將使用這個選擇的並行度來執行。並行查詢的使用時CPU有一些偏高，但是它在elapsed time上的時間很短。如果沒有其他瓶頸，類似於物理I/O等待，並行計劃將會使用所有處理器的100%資源。

查詢開始執行後，一個關鍵的因素（系統有多空閒）可以導致執行並行計劃的改變。例如，如果查詢執行在空閒時間，伺服器可以選擇使用並行計劃並使用 DOP為4，在4個不同的處理器上產生執行緒。一旦這種執行緒開始執行，現存的連線可以提交其他需要大量CPU的查詢。在這種情況，所有不同的執行緒將共享可用的CPU的時間切片，導致更高的查詢持續時間。

通過並行計劃執行不是一定是不好的，並行可以為查詢提供最快的響應時間。然而，給定查詢的響應時間必須與整體的吞吐量和系統其他查詢的響應進行衡量。並行查詢一般最適合批處理和決策支援系統，而不適合一個事務處理環境。

檢測

內部查詢的並行問題可以通過下列方法檢測。

系統監視器(Perfmon)

考慮SQL Server:SQL Statistics – Batch Requests/sec 計數器，並檢視SQL Server聯機叢書中的“SQL Statistics Object”獲取更多資訊。

因為在考慮使用並行計劃前，查詢必須評估開銷超過為並行配置設定的開銷閥值（預設被設定為5），伺服器每秒處理的批小於執行在並行計劃中的批。執行很多並行查詢的伺服器一般配置為較小的每秒批請求數（例如，小於100的值）。

DMVs

在執行的伺服器上，你可以使用下列查詢確認在給定會話中是否可以並行執行任何活動的請求。

select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0

通過這些資訊，查詢的文字可以通過使用sys.dm_exec_sql_text輕鬆獲取，而查詢計劃可以使用sys.dm_exec_cached_plan獲取。

你也可以搜尋符合執行在並行的計劃。這可以通過搜尋快取的計劃來檢視如果關係操作符有Parrallel屬性為非零的值。這些計劃也許可以不執行在平行中，但是他們如果系統不忙，他們也適合這樣做。

--
-- Find query plans that may run in parallel
--
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace

p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0

一般來說，查詢的持續時間長於CPU時間總量，因為一些時間花費在等待資源上例如鎖或物理I/O。查詢使用CPU時間長於持續時間的唯一場景是當查詢執行在並行計劃例如多執行緒併發使用CPU。注意並不是所有並行查詢將證明這種行為（CPU時間大於持續時間）。

select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time
SQL Trace
Look for the following signs of parallel queries,

which could be either statements or batches that

have CPU time greater than the duration.

select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc', default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in milliseconds, Duration in microseconds
Or can be Showplans (un-encoded) that have Parallelism operators in them
select
EventClass,
TextData
from
::fn_trace_gettable('c:temphigh_cpu_trace.trc', default)
where
TextData LIKE '%Parallelism%'

解決

任何執行在並行計劃的查詢被查詢優化器認為是成本昂貴的，並會超過並行閥值，預設為5（粗略的是在涉及的機器上5秒執行一次）。任何通過上述方法確認的查詢都是以後要調節的候選者。

◆使用Database Engine Tuning Advisor檢視是否任何索引改變，改變索引檢視或分割槽改變能減少查詢的開銷◆檢查實際值和評估集的重要不同因為評估集在評估查詢開銷中是重要因素。如果找到重要的不同：

如果auto create statistics資料庫設定被禁用，確認在Showplan輸出的Warnings列中沒有MISSING STATS項。

嘗試在關閉評估的表上執行UPDATE STATISTICS。

驗證查詢沒有使用優化器無法精確評估的查詢構造，例如多語句表值函式或CLR函式，表值或Transact-SQL變數比較（引數比較是可以的）。

◆評估是否可以使用不同的Transact-SQL語句或表示式將查詢寫的更有效率

拙劣遊標使用

SQL Server 2005之前的SQL Server 版本僅支援在每個連線上有單個活動的操作。一個查詢正在執行或有了結果等待傳送到客戶端時將被認為是活動的。在一些情形中，客戶端應用程式也許需要從結果中讀取並向SQL Server提交其他基於剛剛從結果集中讀取的行的查詢。這在預設的結果集中是不能實現的，因為還有其他等待的結果。一般的解決方法是改變連線屬性是用伺服器端遊標。

當使用伺服器端遊標，資料庫客戶端軟體(OLE DB提供者或ODBC驅動)顯然會封裝客戶端請求在特殊的擴充套件儲存過程中，例如sp_cursoropen，sp_cursorfetch等等。這提到了 API遊標（而不是TSQL遊標）。當使用者執行查詢，查詢文字通過sp_cursoropen被髮送到伺服器，請求讀取從sp_cursorfetch指示伺服器進傳送某些數量的行。通過控制獲取行的數量，可以為ODBC驅動或OLE DB提供者快取行。這阻止發生伺服器等待客戶端都區所有傳送的行的情形。因此，伺服器可以在這個連線上接受新的請求。

一次性開啟遊標並獲取1行（或少量行）的應用程式能被網路延時的網路瓶頸影響，特別是在廣域網（WAN）。在有快速網路並有不同使用者連線時，處理很多遊標請求的開銷變得更重要。因為開銷來自於遊標位置的變化來適應在結果集上的位置改變，預請求的處理開銷，類似的處理，伺服器處理1個請求返回100行必處理100不同請求相同的100行但是每次1行更有效率。

檢測

你可以使用下列方法為拙劣遊標使用排錯。

系統監視器(Perfmon)

通過考慮SQL Server:Cursor Manager By Type – Cursor Requests/Sec計數器，你可以通過這個效能計數器知道有多少遊標在系統中使用。系統還有很高的CPU利用率，因為小量的讀取通常會有每秒數百個遊標請求。這裡沒有特殊的計數器告訴你關於獲取的快取大小。

DMVs

接下來的查詢可以用於測定使用API遊標（不是TSQL遊標）連線獲取一行使用的快取大小。它對於大的獲取快取更有效，例如100行。

select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%'

-- API cursor (TSQL cursors always have fetch buffer of 1)

SQL 跟蹤

使用包括RPC：Completed事件類的跟蹤用於搜尋sp_cursorfetch語句。第4個引數的值是通過獲取返回的行數。請求返回的最大行數是被指定為與RPC:Starting事件類關聯的引數。

解決

◆確定遊標是完成操作的最佳方法或是否基於集合這種更有效的操作是可行的。◆當連線到SQL Server 2005，考慮使用多活動結果集(MARS)◆參考你使用的API文件決定如何指定遊標的獲取快取大小：

ODBC - SQL_ATTR_ROW_ARRAY_SIZE
OLE DB – IRowset::GetNextRows or IRowsetLocate::GetRowsAt

記憶體瓶頸

這部分給出了低快取的條件和對不同記憶體錯誤診斷方法，可能的原因和排錯方法。

背景

引用不同的記憶體資源通過使用簡單的術語記憶體。但是卻有一些記憶體資源型別，對於理解和區分特殊的記憶體資源這是很重要的。

虛擬地址空間和實體記憶體

在Microsoft Windows®，每個程式都有自己的虛擬地址空間(VAS)。程式可用所有虛擬地址有VAS的大小決定。VAS的大小依賴於架構（32位或64位）和作業系統。在排錯的上下文中，理解虛擬地址空間使用記憶體資源，瞭解應用程式可以超出VAS即使在64位平臺只要實體地址可能一直可用，這些很重要。

更多有關於虛擬地址空間，請檢視SQL Server聯機叢書中“Process Address Space”和MSDN中的Virtual Address Space。

Windows地址擴充套件和SQL Server

Windows地址擴充套件(AWE)是允許32位應用程式跨越32位地址限制操作記憶體的API。AWE機制技術上不需要64位平臺。然而它出現了。記憶體頁通過AWE機制涉及在64位平臺上的鎖定頁。

在32位和64位平臺上，記憶體通過AWE機制分配不能分頁出界。這可以有益於應用程式（這是在64位平臺上使用AWE機制的原因）。這也影響了系統和其他應用程式可用RAM總數，這可能是有害的影響。因為這個原因為了使用AWE，Lock Pages in Memory權利必須分配該執行SQL Server的賬號。

從排錯的角度來看，要點是SQL Server快取池使用AWE對映記憶體；然而，只有資料庫（hash過的）頁面可以利用AWE分配記憶體。通過AWE機制記憶體分配將不會在任務管理器或在 Process: Private Bytes效能計數器中看到。你需要使用SQL Server特殊的計數器或動態管理檢視來獲取這些資訊。

更多關於AWE對映記憶體的資訊，請在SQL Server聯機叢書中檢視“Managing memory for large databases” 和 “Memory Architecture”以及MSDN中的Large Memory Support

下列表彙總了不同SQL Server 2005支援的最大記憶體（注意特殊的SQL Server版本或Windows可以有不同支援記憶體的限制）

表1

配置

VAS

最大實體記憶體

AWE/locked pages支援

Native 32-bit on 32-bit OS

with /3GB boot parameter[1]

2 GB

3 GB

64 GB

16 GB

支援

支援

32-bit on x64 OS (WOW)

4 GB

64 GB

支援

32-bit on IA64 OS (WOW)

2 GB

2 GB

不支援

Native 64-bit on x64 OS

8 terabyte

1 terabyte

支援

Native 64-bit on IA64 OS

7 terabyte

1 terabyte

不支援

[1]更多有關於啟動引數，請參考SQL Server聯機叢書中“Using AWE”文章。

;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242517/，如需轉載，請註明出處，否則將追究法律責任。

相關文章