[轉]SQL Server 性能調優（cpu）

研究cpu壓力工具

perfom

SQL跟蹤

性能視圖

cpu相關的wait event

Signal wait time

SOS_SCHEDULER_YIELD等待

CXPACKET等待

CMEMTHREAD等待

排程隊列

cpu密集型查詢

高CPU使用率的建立幾種狀況

miss index

統計資料丢失

非SARG謂詞

隐式類型轉化

參數探測器

ad hoc 非參數化查詢

修改源代碼

強制性參數化

不合适的并發查詢

cost threshold for parallelism

max degree of parallelism

超線程和并發查詢

診斷不合适的并發查詢

解決并發問題

TokenAndPermUserStore

總結

參考資料：

cpu在sql server 中扮演了很重要的角色，雖然cpu綁定的伺服器排除cpu問題相對比較簡單，但并不意味着總是簡單。如果你的1個或多個cpu滿負荷運作，那麼就要小心了。sql server 對cpu的使用無處不在，是以如果cpu滿負荷運作，那麼問題很嚴重。

cpu性能出現問題，一般很慢盤查為啥，因為會照成cpu性能問題的很多，如記憶體不足，資料換進換出，cpu一路飙高。寫操作性能很爛，索引建的不合适，sql server 配置等問題都會引起cpu過高的問題。是以cpu性能盤查需要很小心和仔細。

不管是什麼問題引發的，對cpu的性能分析就是把問題隔離到一個特定資源，我們可以使用perfmon，性能視圖，還有sql跟蹤來收集資源。

一旦發生問題，我們就要把問題鎖定在一個或多個查詢上，對其進行調整如調整cpu密集型的查詢，添加合适的索引，使用存儲過程替換ad hoc查詢等等。

研究cpu壓力工具

perfom

對于cpu壓力的研究我們一般使用一下工具：perfmon，SQL跟蹤，動态性能視圖

perfmon我們可以跟蹤如下性能名額：

Processor/ %Privileged Time --核心級别的cpu使用率

Processor/ %User Time --使用者幾倍的cpu使用率

Process (sqlservr.exe)/ %Processor Time --某個程序的cpu使用率

上面3個性能名額是全局範圍的，SQL Satatistics 計數器雖然不能直接說明cpu的使用率但是可以間接的說明cpu的使用情況。

• SQLServer:SQL Statistics/Auto-Param Attempts/sec

• SQLServer:SQL Statistics/Failed Auto-params/sec

• SQLServer:SQL Statistics/Batch Requests/sec

• SQLServer:SQL Statistics/SQL Compilations/sec

• SQLServer:SQL Statistics/SQL Re-Compilations/sec

• SQLServer:Plan Cache/Cache hit Ratio

這些計數器沒有額定的閥值，需要和性能基線做對比

SQL跟蹤

SQL跟蹤的具體用法就不多講，很多人都已經會用了，SQL跟蹤在某個時間點上的捕獲遠遠不如動态性能視圖，而且捕獲的時候要注意設定過濾不然會捕獲大量無用的sql。

性能視圖

性能視圖是分析的利器：

驗證cpu壓力的wait event 可以使用 sys.dm_os_wait_stats.

通過sys.dm_os_wait_stats和 sys.dm_os_schedulers，通過wait event 類型診斷。

可以用sys.dm_exec_query_stats和sys.dm_exec_sql_text說明使用大量cpu的執行計劃

可以使用sys.dm_os_waiting_task檢視cpu相關的等待類型

通過sys.dm_exec_requests檢視目前正在的查詢的資源使用情況

cpu相關的wait event

sql server 所有的等待資訊，都會被記錄。可以使用sys.dm_os_wait_stats中檢視。這個視圖可以用來确定cpu壓力，檢視cpu綁定系統中大多數的wait event。

Signal wait time

根據特定的等待類型(wait type)，有一些等待時間：

wait_time_ms該等待類型所有等待時間。

signal_wait_time_ms從發出信号到開始運作的時間差，時間花費在等待運作隊列中，是單純的cpu等待。

signal_wait_time_ms是所有等待時間的一個重要部分，說明了等待一個可用資源的等待時間。可以表示sql server 中是否正在運作cpu密集型查詢。

下面代碼量化的像是signal_wait_time_ms占的比重

SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,

( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))

/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )

AS PercentageSignalWaitsOfTotalTime

FROM sys.dm_os_wait_stats

這個dmv記錄了統計資訊，系統重新開機之後會被情況，是以如果檢視某一時間點情況不是很好用，隻能用臨近的統計相減，也可以用 dbcc sqlperf清空統計資訊。

關于session級和語句級的wait event 可以檢視文章：http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/30/an-xevent-a-day-30-of-31-tracking-session-and-statement-level-waits.aspx

我們可以使用sys.dm_os_wait_stats檢視那個資源等待時間最長。top 10 用等待時間排序，但是這樣就容易忽略一開始的等待也就是signal wait time，是以要減去signal_wait_time，作為等待排程器的時間。

下面讨論三個wait type 這三個和cpu壓力息息相關。

SOS_SCHEDULER_YIELD等待

sql server 排程器是非搶占式排程，也就是說是依靠查詢自動放棄cpu，但是windows是搶占式，也就是說一定時間之後，windows 會直接從cpu上删除任務。

當查詢自動放棄cpu，并且等待恢複執行，這個等待就叫做SOS_SCHEDULER_YIELD,如果這個值很小那麼就說明，花費在等待cpu上，而不是等待其他資源上。

如果sys.dm_exec_requests或者 sys.dm_os_waiting_tasks 的SOS_SCHEDULER_YIELD的等待值偏高，那麼說明有cpu密集型查詢，需要優化sql或者增加cpu。

CXPACKET等待

當同步查詢程序，worker之間交換疊代器的時候發生CXPACKET等待，特别是發生并發查詢的時候。如果是在dw,或者是報表資料庫，那麼發生sql比較少，并且有大量的并發查詢可以減少執行時間。對dw來說是正常的，但是在oltp中大多數是小的sql和事務，如果發生大量的并發，會導緻性能下降。

CMEMTHREAD等待

CMEMTHREAD等待就是等待被同步的記憶體對象。有一些對象支援查詢同時通路，有些不支援。當一個查詢通路一個對象時，其他查詢就必須等待，這就是CMEMTHREAD等待。

通常CMEMTHREAD等待不會很長時間。但是當記憶體出現問題後，cpu使用率和CMEMTHREAD等待都會變高，這是性能比較差的查詢引起的。

排程隊列

關于排程隊列最主要的視圖就是sys.dm_os_schedulers，視圖主要的二個名額之一是每個排程器有幾個task，和可運作隊列的長度。可運作隊列内都是等待cpu時鐘的task，其他的task在current_tasks_count内，都處于sleep或者在等待其他資源。

SELECT scheduler_id ,

current_tasks_count ,

runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

這些值沒有固定的閥值，隻能通過性能基線來對比。當然這些值都是越低越好。如果可運作隊列越長那麼，signal time 的時間也就越長，就意味着可能cpu不足。

上面的sql過濾掉了一些 scheduler 因為其他的是backup，dac等排程器。

cpu密集型查詢

關于cpu密集型查詢，有2個性能視圖，sys.dm_exec_query_stats和sys.dm_exec_sql_text。sys.dm_exec_query_stats統計了每個查詢計劃的各類資訊。如*_worker_time：cpu花費的時間。*_elapsed_time:總共運作的時間。

下面的sql統計了前10個最費時間的查詢：

SELECT TOP ( 10 )

SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,

( ( CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE QS.statement_end_offset

END - QS.statement_start_offset ) / 2 ) + 1)

AS statement_text ,

execution_count ,

total_worker_time / 1000 AS total_worker_time_ms ,

( total_worker_time / 1000 ) / execution_count

AS avg_worker_time_ms ,

total_logical_reads ,

total_logical_reads / execution_count AS avg_logical_reads ,

total_elapsed_time / 1000 AS total_elapsed_time_ms ,

( total_elapsed_time / 1000 ) / execution_count

AS avg_elapsed_time_ms ,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY total_worker_time DESC

這個查詢并不會顯示所有的query，執行計劃是被儲存在cache中的，當cache被淘汰，因為dbcc指令沒清理，資料庫狀态發生變化，資料庫配置發生變化等等，都會引起cache丢失的情況。有一些查詢使用了recompile标示或者提示那就永遠不會被保留在cache中。

如果你要全局的分析執行計劃，請使用sql跟蹤，而不要事情清空緩存，特别是在生産庫中，緩存一旦被清空在一點時間内，講嚴重影響性能。

高CPU使用率的建立幾種狀況

不管在伺服器硬體配置和技術上面花了多大的成本，總有怎麼一些查詢會導緻伺服器的資源滿負荷運作。每個sql被執行的時候，sql server優化器終會找一個盡量高效的方式來擷取資料。如果當一個查詢miss index或者忽略了合适的索引，那麼優化器就無法生存一個真正高效的執行計劃。如果優化器相關的資訊是不準确的，那麼優化器生存的執行計劃也是不準備的，因為關于成本的計算也是不準确的。另外一種狀況就是優化器生存的結果對一個查詢是優化的，但是對其他查詢并不優化。因為不合适的參數探測導緻了這個問題。

miss index

miss index 是照成大量cpu和io使用的狀況之一，也是最常發生的狀況。目前的索引并不能滿足查詢的時候，優化器會試圖是用表掃描來完成，這樣就照成了大量的非必須的資料參與到預算中，會照成cpu和io的極大浪費。那麼我們就以 adventureworks2008 資料庫作為例子

SELECT per.FirstName ,

per.LastName ,

p.Name ,

p.ProductNumber ,

OrderDate ,

LineTotal ,

soh.TotalDue

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail sod

ON soh.SalesOrderID = sod.SalesOrderID

INNER JOIN Production.Product AS p ON sod.ProductID = p.ProductID

INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID

INNER JOIN Person.Person AS per

ON c.PersonID = per.BusinessEntityID

WHERE LineTotal > 25000

這個查詢在salesorderdetail使用了表掃描，因為并沒有關于linetotal列的索引

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 452 ms, elapsed time = 458 ms.

雖然傳回24行隻用了半秒的時間但是還是不夠優化。那麼我們就在linetotal建一個索引

CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_LineTotal

ON Sales.SalesOrderDetail (LineTotal)

那麼我們繼續運作上面的sql

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 8 ms.

結果有很大的不通，通過這個簡單的例子說明cpu的壓力有可能且很大的可能都是miss index 照成的。

統計資料丢失

優化器會通過統計資訊估計每個查詢操作的基數。通過估計行數，操作的花費。操作的花費決定了整個計劃的花費。如果統計資訊不準确，那麼優化器的成本計算也就不準确，這樣就會導緻優化器誤判，估計的花費是低的但是并不一定實際的花費也是低的。通常統計值不準确是比實際值要小，一旦小，那麼優化器就會選擇比較适合較小數量的操作符如nest loop，key lookup，但是實際的資料量很大，這樣就會對查詢照成嚴重的影響。有一個方法檢視統計值是否丢失，就是在ssms中運作實際的查詢計劃，并且對比估計值和實際值的差距，如果差距很大那麼就是統計資料丢失了，需要及時更新統計值。當然可以通過 update statistics 更新統計值，詳細的用法可以參見聯機文檔。

如果是統計值過期的問題照成的那麼有一下3個方法：

1.把資料庫設定為自動更新統計值。

2.如果自動更新統計資訊無效，那麼有可能是索引建立的時候有不計算統計值的标記。

3.建立一個腳本定時更新統計值。

非SARG謂詞

就是不要再表的字段上使用函數或者計算，因為你一用，就沒辦法使用索引了。一不能使用索引，顯而易見cpu飙高了，io堵塞了。

隐式類型轉化

很多人都認為隐式轉化沒什麼關系，并不會給性能帶來多大的沖擊。一個過濾如果類型不同那麼sql server 是無法比較的，這時候就要隐式轉化了，隐式轉化的時候都是從低的優先級轉化到高的優先級，比如如果一個是varchar一個是nvarchar那麼就會把varchar隐式轉化成nvarchar。問題就來了如果一個表列是varchar但是過濾的條件是nvarchar，那麼就會隐式轉化把varchar轉化成為nvarchar那麼就會發生非SARG謂詞，無法使用索引查找了。下面有個例子：

SELECT p.FirstName ,

p.LastName ,

c.AccountNumber

FROM Sales.Customer AS c

INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID

WHERE AccountNumber = N'AW00029594'

當然 accountnumber 上是有索引的

就變成索引掃描了，我使用2008r2測試，結果不是索引掃描。但是當我把accountnumber 禁用掉之後，盡然和書上發的執行計劃是一樣的了，讓我深深的懷疑，是不是作者在寫書的時候，把accountnumber 禁用了而沒發現呢？我在網上查了寫資料，發現了在sql server 2000下的測試語句ok，在2000 下面是會照成索引掃描。是以大家如果用2008r2的就不需要太擔心這個問題。如果在其他版本真的遇到這個問題那麼如何解決呢？那麼就把類型轉化放在常量這一端。或者直接修改表的資料類型。

我把2000的測試語句發出來：

DECLARE @CustID NCHAR(5)

SET @CustID = N'FOLKO'

SELECT CompanyName FROM NorthWind.dbo.Customers WHERE CustomerID = @CustID

這裡要注意因為 customers 表的結構是 nchar的是以我們在測試的時候先要修改掉這個資料類型，改為char。northwind裡面有外鍵要統統删掉，主鍵需要重建。

說到這裡，我就和書的作者聯系了，根據他給的結論，和測試結果

-- Windows Collation will get a Seek

CREATE TABLE #T (col1 varchar(10) COLLATE Latin1_General_CI_AS PRIMARY KEY);

SELECT *

FROM #T

WHERE col1 = N'q'

-- SQL Collation will get a Scan

CREATE TABLE #T2 (col1 varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY);

SELECT *

FROM #T2

WHERE col1 = N'q'

-- Your Collation will get a Seek

CREATE TABLE #T3 (col1 varchar(10) COLLATE Chinese_PRC_CI_AS PRIMARY KEY);

SELECT *

FROM #T3

WHERE col1 = N'q'

DROP TABLE #T

DROP TABLE #T2

DROP TABLE #T3

當你用SQL Server 的排序規則那麼就是掃描如果用windows 的排序規則那麼就是查詢。

上面就是他發過來的sample

參數探測器

當sql server為存儲過程，函數或者參數化查詢建立執行計劃的時候，會探測參數，并結合統計資料計算花費選擇較好的執行計劃。參數探測器隻會在編譯或者重編譯的時候發生，那麼這裡就有個問題如果當建立執行計劃的時候該參數的值是非典型的，那麼就很可能并不适用于以後傳過來的參數。初始化編譯的時候，隻有輸入的參數會被探測，本地變量是不會被探測的。如果一個語句在一個batch 中被重編譯那麼參數和變量都會被探測。

下面是一個運作在Adventureworks資料庫的例子最大日期是2011-7-8 最小日期是2004-8-7.

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

會對shipdate進行過濾那麼就在shipdate設定一個索引

CREATE NONCLUSTERED INDEX IDX_ShipDate_ASC

ON Sales.SalesOrderHeader (ShipDate)

接下來會運作2次這個存儲過程第一次誇多年的，第二次就誇幾天。并檢視實際的執行計劃

DBCC FREEPROCCACHE

EXEC user_GetCustomerShipDates '2001/07/08', '2004/01/01'

EXEC user_GetCustomerShipDates '2001/07/10', '2001/07/20'

查詢結果2個都用了掃描

|--Filter(WHERE:([AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate]>=[@ShipDateStart] AND [AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate]<=[@ShipDateEnd]))

|--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]))

|--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID],0),N'*** ERROR ***')))

|--Table Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader]))

這個是我的結果和書上的不一樣。那麼為什麼為産生表掃描不是索引查找呢，因為第一個查詢在編譯的時候優化器任務用表掃描比較合适。但是到第二句的時候，雖然是不合适，但是已經有執行計劃存儲在了記憶體裡面，sql server 就直接拿來用了，就照成了這個問題。開 SET STATISTICS IO on

表'SalesOrderHeader'。掃描計數1，邏輯讀取700 次，實體讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 實體讀取0 次，lob 預讀0 次。

那麼我們把2個存儲過程倒過來：

DBCC FREEPROCCACHE

EXEC user_GetCustomerShipDates '2001/07/10', '2001/07/20'

EXEC user_GetCustomerShipDates '2001/07/08', '2004/01/01'

情況就完全不一樣了

|--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1004]) WITH UNORDERED PREFETCH)

|--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[IDX_ShipDate_ASC]), SEEK:([AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate] >= [@ShipDateStart] AND [AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate] <= [@ShipDateEnd]) ORDERED FORWARD)

|--RID Lookup(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

果斷使用了索引查找，但是對第二句來說索引查找不一定是好事情，因為他要掃描的行太多，如果假定現在樹是3層，那麼讀一個頁需要讀3次你想想。

表'SalesOrderHeader'。掃描計數1，邏輯讀取17155 次，實體讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 實體讀取0 次，lob 預讀0 次。

比較一下誇多年的那個存儲過程的邏輯讀。

通常keylookup隻适合較少的資料通常是整表的1%，當然不是絕對的。

跟蹤标記4136

SQL Server 2008 引入了一個新的跟蹤标記 4316，使用了這個跟蹤标記之後sql server 會關掉參數探測功能，這個功能在sql server 2008 sp2 cu7 ,sql server 2--8 r2 cu2,sql server 2005 sp3 cu9 中才加入。先前讨論過了如果開了參數探測，一個存儲過程如果第一次編譯的時候估計值偏小，或者偏大，都會對接下來使用這個存儲過程産生影響。當參數探測器被停用的時候 4316 跟蹤是如何處理的呢，舉個例子這裡又一個列 X 有如下的值1,2,3,3,3,3,3,4,5,5，那麼他的估計值就是2這個哪來的？就是所有資料的平均估計值。所有的計劃都會被這個值優化。如果開了這個選項那麼會給很多存儲過程優化帶來好處。

這邊有篇關于4316的文章比較簡單但是很到位：http://blogs.msdn.com/b/axperf/archive/2010/05/07/important-sql-server-change-parameter-sniffing-and-plan-caching.aspx

使用 OPTIMIZE FOR 提示

到了sql server 2005 以後你可以使用OPTIMIZE FOR 來優化查詢

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( OPTIMIZE FOR ( @ShipDateStart = '2001/07/08',

@ShipDateEnd = '2004/01/01' ) )

使用了OPTIMIZE FOR 提示那麼sql server 就會按提示的資訊來編譯，當然如果提示的值不理想那麼也會産生問題。

在SQL Server 2008 中引入了一個新的提示 OPTIMIZE FOR UNKNOWN，那麼sql server 就不會再用參數探測的功能，它的功效和4316相同，是以這個方法是比較可取的因為畢竟參數探測還是一個比較好的東西。

重編譯選項

重編譯也是解決參數探測的一個方法，但是問題就是執行計劃不會被儲存在記憶體中，但是就有一個問題存儲過程的執行的花費就會變高。

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

WITH RECOMPILE

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

如果存儲過程中隻需要一部分重新編譯，那麼就可以使用OPTION(RECOMPILE)選項放到查詢中即可，相比重編譯整個存儲過程，這樣會好些。

CREATE PROCEDURE user_GetCustomerShipDates

(

@ShipDateStart DATETIME ,

@ShipDateEnd DATETIME

)

SELECT CustomerID ,

SalesOrderNumber

FROM Sales.SalesOrderHeader

WHERE ShipDate BETWEEN @ShipDateStart AND @ShipDateEnd

OPTION ( RECOMPILE )

ad hoc 非參數化查詢

Ad hoc查詢語句發送到sql server 的時候優化器還是會從cache查找合适的執行計劃。ad hoc 查詢會讓所有的語句都生産一遍執行計劃，這樣會照成資源浪費特别是CPU。

SELECT soh.SalesOrderNumber ,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = 'SO43662'

SELECT soh.SalesOrderNumber ,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = 'SO58928'

SELECT soh.SalesOrderNumber ,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = 'SO70907'

很不幸，這三個語句本來是應該可以用同一個執行計劃的。現在因為ad hoc 用不了了。如果是簡單的查詢那麼sql server 會使用簡單參數化來重用執行計劃。但是上面的例子太複雜了是以沒辦法。那就會有2個問題

1.執行計劃緩存充滿了單使用者的計劃，不能被重用。浪費記憶體空間。

2.執行計劃因為不可用是以總是要編譯新的計劃，導緻cpu時鐘浪費。

可以用perfmon來監視編譯重編譯的量

• SQLServer: SQL Statistics: SQL Compilations/Sec

• SQLServer: SQL Statistics: Auto-Param Attempts/Sec

• SQLServer: SQL Statistics: Failed Auto-Param/Sec

如果真的是非參數化照成的問題，那麼又很多方法去調整，最好的方式是修改源代碼。如果不行那麼隻能設定sql server 來調整

修改源代碼

關于修改源代碼就不讨論了，直接給demo自己看。

cmd.CommandType = CommandType.Text;

cmd.CommandText = @"SELECT soh.SalesOrderNumber,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = '" + txtSalesOrderNo.Text + "'";

dtrSalesOrders = cmd.ExecuteReader();

dtrSalesOrders.Close();

cmd.CommandType = CommandType.Text;

cmd.CommandText = @"SELECT soh.SalesOrderNumber,

sod.ProductID

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.SalesOrderNumber = @SalesOrderNo";

cmd.Parameters.Add("@SalesOrderNo", SqlDbType.NVarChar, 50);

cmd.Parameters["@SalesOrderNo"].Value = txtSalesOrderNo.Text;

dtrSalesOrders = cmd.ExecuteReader();

強制性參數化

關于強制參數化，可以設定資料庫選項

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

如果使用強制參數化那麼上面我們提過的3個sql的執行計劃就變成一個了。可以使用如下sql查詢

SELECT b.text,c.* FROM sys.dm_exec_query_stats a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c

使用強制參數化很不好，就會使得所有的sql都使用同一個查詢計劃，不管好壞，有點和參數探測器的問題類似了。

Optimize for ad hoc workloads

這是一個資料庫服務配置項，配置了之後當ad hoc第一次運作的時候sql server 會産生一個子查詢計劃不能用，當第二次執行的時候産生一個執行計劃。可以有效的減少記憶體壓力。

EXEC sp_configure 'show advanced options',1

RECONFIGURE

EXEC sp_configure 'optimize for ad hoc workloads',1

RECONFIGURE

不合适的并發查詢

當查詢在不同的線程，每個線程在不同的排程器下運作，就可以了解為并發查詢。

當一個查詢被送出到sql server 優化器，優化器開始估算花費，如果花費比cost threshold for parallelism 要大，那麼優化器會考慮使用并發。max degree of parallelism 用來限制查詢的最大并發數如果查詢中使用了maxdop提示的話那麼最大并發數則為提示的值。并發查詢通過把資料水準分區到各個不同的邏輯cpu，通過多個處理器核心執行相同的操作來減少查詢的時間。這個對于dw或者報表資料庫是很有用的因為資料量很大，而且并發請求比較少。是以能夠充分的利用硬體資源，并且減少執行的時間。對于并發的負載還是又一些要素，并不是指餘下的裝置資源能否應付并發負載帶來的大記憶體配置設定和磁盤io的問題。并發查詢使用的好會給伺服器的整體性能帶來很大的提升，但是并發負載對oltp系統來說是非常不利的，oltp是又很多小的事務組成，并發量比較大，如果oltp上有并發負載，占據了較長時間的cpu，那麼其他事務就會等待并發的完成，導緻查詢假死在那邊。

對于并發的配置參數有2個cost threshold for parallelism ，max degree of parallelism 第一個是啟用并發查詢的閥值，第二個是最大并發數。當發生不合适的并發的時候，建議的解決方法是調整max degree of parallelism，減少1/2，或者減少1/4或者直接設定為1。當然這個是不理想的解決方案，最理想的解決方案是設定2個配置參數，到一個比較合理的值。

cost threshold for parallelism

cost threshold for parallelism 是一個啟用并發的閥值，查過了就啟用并發，沒超過就不啟用。cost threshold for parallelism 的預設值是5秒，但是對于大資料庫5秒是一個比較小的值，是以設定cost threshold for parallelism 閥值很重要

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

WITH XMLNAMESPACES

(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT query_plan AS CompleteQueryPlan ,

n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText ,

n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)')

AS StatementOptimizationLevel ,

n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)')

AS StatementSubTreeCost ,

n.query('.') AS ParallelSubTreeXML ,

ecp.usecounts ,

ecp.size_in_bytes

FROM sys.dm_exec_cached_plans AS ecp

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp

CROSS APPLY query_plan.nodes

('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')

AS qn ( n )

WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

是以通過以上查詢，分析相似的查詢。以最小化cpu，io競争為目标設定cost threshold for parallelism。

max degree of parallelism

sql server 并發查詢的并發度有以下3點：

1.可用的處理器數量

2.max degree of parallelism

3.MAXDOP查詢提示

如果你的伺服器現在出現了并發問題那麼修改閥值和最大并發度是解決這個問的最快速的方法。

網上有種說法就是直接把max degree of parallelism設為1，對于oltp系統的特性是可能性的，但是還是覺得你這樣設定之後就不能使用并發了，感覺會減少性能。

分析CXPACKETwait event，CXPACKET隻是一種症狀，并沒有真正的發生問題。檢視sys.dm_os_waiting_tasks中其他的wait event可以更好的得出合适的 max degree of parallelism。如果相關的等待事件是 PAGEIOLATCH_SH，并發正在等待io讀取，減少max degree of parallelism 并不能解決根本問題，它隻會減小被使用的工作任務，減少CXPACKET累計等待時間。但是也可能會減少額外的io，給你提示io性能的空間。

并發查詢也需要考慮到記憶體的結構體系，在NUMA結構下，最大并發度設定在一個NUMA節點的可用經常。這樣node之間就不會産生互動，因為node間的共享記憶體操作代價很高。在SMP結構中，多個處理器核心都在單個晶片上共享二級緩存，這樣很容易照成記憶體命中率下降，但是好處是在并發查詢下高并發的性能表現很好，當然max degree of parallelism 也要根據硬體裝置的能力做适當的調節。在sql server 2008 以上的版本還可以使用資料總管來限制。

超線程和并發查詢

超線程是Intel一個技術，為了提高并發操作，就設計了2個邏輯核心對于1個實體核心。就是說不想以前一個排程器一個實體核心，現在2個核心，并且可以“同時”使用。當然我們關心的是性能，那麼sql server 有沒有使用超線程，會給sql server 帶來什麼影響。

對于olap和dss系統并發查詢是又很大好處的，但是當開了超線程的時候性能就變差了。但是超線程對oltp沒什麼影響，對于oltp來說超線程在增加并發度是又好處的。對于早期的超線程因為會帶來很多問題是以dba都是在bios中關閉超線程的。近幾年sql server 2008 釋出了建議關閉超線程特别是olap/dw/dss系統。超線程最大的問題是超線程會共享内置的cache，照成命中率下降。現在很多問題都解決了，windows 2003 就能認識實體核心和邏輯核心，并且給予不同的工作量。現在的處理器緩存變大不容易發生。事實上對于目前的處理器結構，特别是intel nehalem，開超線程是有好處的，除非是有明确的理由。是以在決定是否使用超線程的時候最好先做一下測試。

診斷不合适的并發查詢

最好診斷的方法是檢視wait統計資訊和latch統計資訊，當執行并發的時候出現瓶頸，CXPACKET等待就會變的很高。當并發查詢等待交換疊代器到另外一個工作任務的時候就會發生等待。通常這裡也會有一些相關的其他等待，來協助工作，因為大量的并發查詢，CXPACKET的等待會比根本原因蓋過去。最好的方法是分隔在troubleshooting各個相關的等待時間。因為并發查詢會影響全局的性能問題。CXPACKET很有可能隻是一個症狀很多問題都會引起CXPACKET偏高。當io不能維持并發查詢的需求，關鍵的等待可能是IO_COMPLETION,ASYNC_IO_COMPLETION,PAGEIOLATCH_*,不能擴充io性能。但是減小并發度，任然會發生io性能瓶頸的狀況，那麼就要提從全局的系統性能。如果CXPACKE相關的等待是LATCH_*,SOS_SCHEDULER_YIELD,那麼很有可能是并發的問題，深入latch驗證是并發的問題。sys.dm_os_latch_stats包含一些特殊的latch等待，如ACCESS_METHODS_DATASET_PARENT,LATCH_*,SOS_SCHEDULER_YIELD等待都比較高，那麼減少并發度就可能解決問題。

解決并發問題

先前已經讨論過，對于大的，長運作時間的查詢使用并發很有好處。不合适的并發主要問題是負載類型是混合的。很多庫本質上是oltp的但是因為sql比較複雜超過了cost threshold for parallelism。是以試圖提升一下cpu性能。如果診斷到了并發存在問題，如果沒有被調整過，那麼很有可能因為索引丢失或者不合适的索引造成問題，如果調整完之後還是這樣那麼就用先前提到的2個系統配置參數，來全局的管理資料庫并發。

TokenAndPermUserStore

TokenAndPermUserStore在2005的時候被引進來優化關于權限驗證，怎麼TokenAndPermUserStore是怎麼工作的呢？這裡有一個簡單的例子說明TokenAndPermUserStore的工作情況。例子當你執行的時候select * from t1 join t2 join t3，那麼sql sever 就會對權限進行驗證，驗證後會緩存在TokenAndPermUserStore以免以後重複驗證。但是這個會引起性能問題，特别是較早版本的sql server 2005，因為這個cache的記憶體限制過高性能問題的表現為cpu使用率比較高，cmemthread等待比較嚴重。微軟已經給出了一個解決方案http://support.microsoft.com/kb/927396/通常問題發生在非awe記憶體配置設定的sql server 上（特别是64b的伺服器），很多動态的或者 adhoc查詢，資料庫使用者過多。你可以使用如下sql查詢TokenAndPermUserStore使用量：

SELECT SUM(single_pages_kb + multi_pages_kb) / 1024.0 AS CacheSizeMB

FROM sys.dm_os_memory_clerks

WHERE [name] = 'TokenAndPermUserStore'

如果cache一直增長，并且伴随着cmemthread等待，那麼很有可能導緻高cpu使用率，如果使用sql server2005低于sp2更新檔，那麼第一時間就是打上更新檔。嫌少動态sql和adhoc來減少發生問題的機率。

短期修複

使用sysadmin角色，因為sysadmin是sql server 最大的權限，不需要做權限檢查。那麼也就不會産生cache

定期清理cache：DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

在sql 2005 sp2 以上版本使用 trace flage 4618,4610來限制cache中的條目數量，當4618開啟，cache中隻能有1024個cache，當2個trace flag 都開啟那麼又8192個條目。這個限制會影響其他cache，是以隻能臨時使用。sql server 2005 sp3以後有個新的trace flag 4612，可以設定用戶端的配額詳細看：(http://support.microsoft.com/kb/959823)

sql2008的配置項

在sql server 2008 對于TokenAndPermUserStore有2個配置項，access check cache quota，access check cache bucket count,如果問題很明顯的發生，那麼就減少這2個值的大小，其實并不建議修改預設值，除非又微軟客服支援。

總結

troubleshooting是一個分析問題的過程，我上一篇文章也說了，是一個根據統計的資訊，分析問題的過程。是以需要了解資料庫核心，内部運作的結構才能更好的進行調優。調優第一步的資訊往往都是來至于perfmon，和動态性能視圖，最後才是sqltrace，為啥，因為sqltrace最浪費時間，會有滞後性，是以已經滞後了還不如放到最後運作。

參考資料：

Implicit data conversations

•http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/findingimplicit-column-conversions-in-the-plan-cache.aspx

Query tuning

• http://www.straightpathsql.com/presentations/ucandoit/

• http://www.simple-talk.com/sql/performance/simple-query-tuning-with-statistics-io-and-execution-plans/

•http://www.simple-talk.com/sql/t-sql-programming/13-things-youshould-know-about-statistics-and-the-query-optimizer/

• http://www.simple-talk.com/author/gail-shaw/

Estimated vs. actual row counts

• http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/

Cost threshold for parallelism

• http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/26/21172.aspx

• Max degree of parallelism

• http://msdn.microsoft.com/en-us/library/ms181007.aspx

Query hints

• http://msdn.microsoft.com/en-us/library/ms181714.aspx

Guidelines for modifying MAXDOP

• http://support.microsoft.com/kb/329204

Limiting MAXDOP with the Resource Governor

•http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/controlling-maxdop-executing-queries-140163

Parallelism/MAXDOP configuration

• http://msdn.microsoft.com/en-us/library/ms178065.aspx

• http://msdn.microsoft.com/en-us/library/ms188611.aspx

• http://blogs.msdn.com/b/joesack/archive/2009/03/18/should-you-worryabout-sos-scheduler-yield.aspx

SQLOS architecture

• http://blogs.msdn.com/b/sqlosteam/archive/2010/06/23/sqlos-resources.aspx

•http://sqlblogcasts.com/blogs/sqlworkshops/archive/2007/11/25/findingoptimal-number-of-cpus-for-a-given-long-running-cpu-intensive-dss-olaplike-queries-workload.aspx

System Monitor CPU counters

• http://msdn.microsoft.com/en-us/library/ms178072.aspx

DMV usage for CPU usage from ring buffers

•http://troubleshootingsql.com/2009/12/30/how-to-find-out-the-cpuusage-information-for-the-sql-server-process-using-ring-buffers/

• http://msdn.microsoft.com/en-us/library/ms175048(SQL.90).aspx

• http://technet.microsoft.com/en-us/library/cc966540.aspx

Forced parameterization

• http://technet.microsoft.com/en-us/library/ms175037(SQL.90).aspx

Fixing TokenAndPermUserStore problems Identification and overview

• http://support.microsoft.com/kb/927396

Access check result cache

• http://support.microsoft.com/kb/955644

• http://msdn.microsoft.com/en-us/library/cc645588.aspx

• Purging the cache whenever it reaches a certain size

• http://blogs.msdn.com/chrissk/archive/2008/06/19/script-to-purgetokenandpermuserstore.aspx

SQL Server 2008 sp_configure options

• http://support.microsoft.com/kb/955644/en-us

• Hot-fixes associated with this problem

• http://support.microsoft.com/kb/959823

轉載于:https://www.cnblogs.com/crystal-guoguo/p/3655818.html

[轉]SQL Server 性能調優（cpu）

研究cpu壓力工具

perfom

SQL跟蹤

性能視圖

cpu相關的wait event

Signal wait time

SOS_SCHEDULER_YIELD等待

CXPACKET等待

CMEMTHREAD等待

排程隊列

cpu密集型查詢

高CPU使用率的建立幾種狀況

miss index

統計資料丢失

非SARG謂詞

隐式類型轉化

參數探測器

ad hoc 非參數化查詢

修改源代碼

強制性參數化

不合适的并發查詢

cost threshold for parallelism

max degree of parallelism

超線程和并發查詢

診斷不合适的并發查詢

解決并發問題

TokenAndPermUserStore

總結

參考資料：

繼續閱讀