當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

SQL Server 性能调优（cpu）

發布時間：2024/4/15 数据库 29 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL Server 性能调优（cpu）小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

SQL Server 性能調優（cpu）

研究cpu壓力工具

perfom

SQL跟蹤

性能視圖

cpu相關的waitevent

Signal wait time

SOS_SCHEDULER_YIELD等待

CXPACKET等待

CMEMTHREAD等待

調度隊列

cpu密集型查詢

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

miss index

統計數據丟失

非SARG謂詞

隱式類型轉化

參數探測器

ad hoc 非參數化查詢

修改源代碼

強制性參數化

不合適的并發查詢

cost threshold forparallelism

max degree ofparallelism

超線程和并發查詢

診斷不合適的并發查詢

解決并發問題

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/ %UserTime ? ? ? ? ? ? ? ? ? ?? ? ? ? ? ? ? --用戶幾倍的cpu使用率

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

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

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

??SQLServer:SQLStatistics/Failed Auto-params/sec

??SQLServer:SQLStatistics/Batch Requests/sec

??SQLServer:SQL Statistics/SQLCompilations/sec

??SQLServer:SQL Statistics/SQLRe-Compilations/sec

??SQLServer:Plan Cache/Cache hitRatio

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

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相關的waitevent

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

Signalwait 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 ServerExecution Times:

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

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

CREATE NONCLUSTEREDINDEX?idx_SalesOrderDetail_LineTotal

ON?Sales.SalesOrderDetail?(LineTotal)

那么我們繼續運行上面的sql

SQL Server parse and compile time:

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

SQL ServerExecution 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.CustomersWHERE CustomerID = @CustID

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

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

-- WindowsCollation will get a Seek
CREATE TABLE #T (col1 varchar(10) COLLATELatin1_General_CI_AS PRIMARY KEY);
SELECT *?
FROM #T?
WHERE col1 = N'q'

-- SQL Collation will get a Scan
CREATE TABLE #T2 (col1 varchar(10) COLLATESQL_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 NONCLUSTEREDINDEX?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]))
??????????? |--ComputeScalar(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'

情況就完全不一樣了

? ?? ?|--ComputeScalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]))
??????????? |--NestedLoops(Inner Join, OUTER REFERENCES:([Bmk1000], [Expr1004]) WITH UNORDEREDPREFETCH)
????????????????|--IndexSeek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[IDX_ShipDate_ASC]),SEEK:([AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate] >= [@ShipDateStart]AND [AdventureWorks].[Sales].[SalesOrderHeader].[ShipDate] <= [@ShipDateEnd])ORDERED FORWARD)
????????????????|--Compute Scalar(DEFINE:([AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID],0),N'***ERROR ***')))
?????????????????????|--RID Lookup(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUPORDERED 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,sqlserver 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 中引入了一個新的提示?OPTIMIZEFOR?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?)

adhoc 非參數化查詢

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: SQLStatistics: SQL Compilations/Sec

??SQLServer: SQLStatistics: Auto-Param Attempts/Sec

??SQLServer: SQLStatistics: Failed Auto-Param/Sec

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

修改源代碼

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

cmd.CommandType = CommandType.Text;

cmd.CommandText = @"SELECTsoh.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 = @"SELECTsoh.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 PARAMETERIZATIONFORCED

如果使用強制參數化那么上面我們提過的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 hocworkloads

這是一個數據庫服務配置項，配置了之后當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 forparallelism 要大，那么優化器會考慮使用并發。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 forparallelism?是一個啟用并發的閥值，查過了就啟用并發，沒超過就不啟用。cost threshold forparallelism?的默認值是5秒，但是對于大數據庫5秒是一個比較小的值，因此設置cost threshold for parallelism?閥值很重要

SET TRANSACTIONISOLATION 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 flage4618,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最浪費時間，會有滯后性，所以已經滯后了還不如放到最后運行。

?轉載：

http://www.cnblogs.com/Amaranthus/archive/2012/03/07/2383551.html

總結

以上是生活随笔為你收集整理的SQL Server 性能调优（cpu）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： SQL Server 性能调优（方法论）
下一篇： SQL SERVER 2008 数据恢复