資料庫調校思路分享

此案例是一個已運作的系統,因此Schema的異動成本很高,所以針對耗能語法,索引與檔案系統進行分析.

大致流程如下, 但也可各自擊破

檢查耗能語法 -> 檢查 遺漏索引 或 無用索引 -> 檢查檔案系統(filegroup/file/partition/disk)

目前接手的案例,已經檢查到以下可調整部分

(1)耗效能語法

-- 歷史耗效IO語法  
select --top 5  
total\_logical\_reads as 總讀取數,  
(total\_logical\_reads/execution\_count) as [平均每次執行邏輯讀取次數],  
(total\_logical\_writes/execution\_count) as [平均每次執行邏輯寫入次數],  
(total\_physical\_reads/execution\_count) as [平均每次執行實體讀取次數],  
 Execution\_count 執行次數,  
substring(qt.text,r.statement\_start\_offset/2+1,  
(case when r.statement\_end\_offset = -1  
then datalength(qt.text)  
else r.statement\_end\_offset end - r.statement\_start\_offset)/2+1) [執行語法],  
qt.text 執行文字  
from sys.dm\_exec\_query\_stats  as r  
 cross apply sys.dm\_exec\_sql\_text(r.sql\_handle) as qt  
order by  
 total\_logical\_reads Desc




--歷史耗CPU語法  
SELECT total\_worker\_time/execution\_count AS AvgCPU    
, total\_worker\_time AS TotalCPU  
, total\_elapsed\_time/execution\_count AS AvgDuration    
, total\_elapsed\_time AS TotalDuration    
, (total\_logical\_reads+total\_physical\_reads)/execution\_count AS AvgReads   
, (total\_logical\_reads+total\_physical\_reads) AS TotalReads  
, execution\_count     
, SUBSTRING(st.TEXT, (qs.statement\_start\_offset/2)+1    
, ((CASE qs.statement\_end\_offset  WHEN -1 THEN datalength(st.TEXT)    
ELSE qs.statement\_end\_offset    
END - qs.statement\_start\_offset)/2) + 1) AS txt    
, query\_plan  
FROM sys.dm\_exec\_query\_stats AS qs    
cross apply sys.dm\_exec\_sql\_text(qs.sql\_handle) AS st    
cross apply sys.dm\_exec\_query\_plan (qs.plan\_handle) AS qp   
ORDER BY 1 DESC




--執行中耗效能語法  
SELECT      r.scheduler\_id as 排程器識別碼,  
            status         as 要求的狀態,  
            r.session\_id   as SPID,  
            r.blocking\_session\_id as BlkBy,  
            substring(  
    ltrim(q.text),  
    r.statement\_start\_offset/2+1,  
    (CASE  
                 WHEN r.statement\_end\_offset = -1  
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2  
                 ELSE r.statement\_end\_offset  
                 END - r.statement\_start\_offset)/2)  
                 AS [正在執行的 T-SQL 命令],  
            r.cpu\_time      as [CPU Time(ms)],  
            r.start\_time    as [開始時間],  
            r.total\_elapsed\_time as [執行總時間],  
            r.reads              as [讀取數],  
            r.writes             as [寫入數],  
            r.logical\_reads      as [邏輯讀取數],  
            -- q.text, /* 完整的 T-SQL 指令碼 */  
            d.name               as [資料庫名稱]  
FROM        sys.dm\_exec\_requests r   
   CROSS APPLY sys.dm\_exec\_sql\_text(sql\_handle) AS q  
   LEFT JOIN sys.databases d ON (r.database\_id=d.database\_id)  
WHERE       r.session\_id > 50 AND r.session\_id <> @@SPID  
ORDER BY    r.total\_elapsed\_time desc





--查詢目前各session與thread執行狀況  
select r.session\_id,  
       r.cpu\_time,  
       p.physical\_io,  
       t.text,  
       substring(t.text, r.statement\_start\_offset/2 + 1, case when r.statement\_end\_offset = -1 then len(t.text) else (r.statement\_end\_offset - r.statement\_start\_offset)/2 end) as text\_running,  
       p.blocked,  
       db\_name(p.dbid) as dbname,  
       r.status,  
       r.command,  
       r.start\_time,  
       r.wait\_type,  
       p.waitresource,  
       p.status,  
       p.open\_tran,  
       p.loginame,           
       p.hostname,  
       p.program\_name,  
       r.percent\_complete,  
       r.wait\_type,  
       r.last\_wait\_type,  
       p.waittime  
from sys.dm\_exec\_requests r  
       cross apply sys.dm\_exec\_sql\_text(r.sql\_handle) t  
       inner join sys.sysprocesses p on p.spid = r.session\_id

(2)遺漏索引(cluster index也沒有)與無用索引

--遺漏索引  
SELECT TOP 20  
 ROUND(s.avg\_total\_user\_cost *  
 s.avg\_user\_impact  
 * (s.user\_seeks + s.user\_scans),0)  
 AS [Total Cost]  
 , d.[statement] AS [Table Name]  
 , equality\_columns  
 , inequality\_columns  
 , included\_columns  
FROM sys.dm\_db\_missing\_index\_groups g  
INNER JOIN sys.dm\_db\_missing\_index\_group\_stats s  
 ON s.group\_handle = g.index\_group\_handle  
INNER JOIN sys.dm\_db\_missing\_index\_details d  
 ON d.index\_handle = g.index\_handle  
ORDER BY [Total Cost] DESC





--無用索引  
SELECT b.name,user\_seeks,user\_scans,user\_lookups,user\_updates ,last\_user\_seek,last\_user\_scan,last\_system\_lookup,last\_system\_update  
FROM sys.dm\_db\_index\_usage\_stats a  
JOIN  
sys.objects b   
ON a.object\_id = b.object\_id  
ORDER BY user\_scans DESC





--无用的索引  
SELECT ind.index\_id ,  
obj.name AS [表名] ,  
ind.name AS [索引名称] ,  
ind.type\_desc ,  
ind.is\_primary\_key,  
ind.is\_unique,  
indUsage.user\_seeks ,  
indUsage.user\_scans ,  
indUsage.user\_lookups ,  
indUsage.user\_updates ,  
indUsage.last\_user\_seek ,  
indUsage.last\_user\_scan,indUsage.last\_system\_lookup,indUsage.last\_system\_update  
--‘drop index [‘ + ind.name + ‘] ON [‘ + obj.name + ‘]’ AS DropIndexCommand  
FROM sys.indexes AS ind  
INNER JOIN sys.objects AS obj ON ind.object\_id = obj.object\_id  
LEFT JOIN sys.dm\_db\_index\_usage\_stats indUsage ON ind.object\_id = indUsage.object\_id  
AND ind.index\_id = indUsage.index\_id  
WHERE   
obj.type <> 'S'  
AND OBJECTPROPERTY(obj.object\_id, 'isusertable') = 1  
ORDER BY user\_scans DESC

(3)檔案系統分流

目前打算先於測試區測試, 希望能在盡量不影響系統的情況下, 成功調校資料庫.

以上大致思路供各位參考,如有任何問題,歡迎留言,一起討論.

參考來源:

http://www.maomao365.com/?p=10612

隨意窩維護頁面
Edit descriptionblog.xuite.net

https://stackoverflow.com/questions/28952/cpu-utilization-by-database

• ← SQL Server--資料庫調校思路分享2 資料表筆數、容量(大小)
• 英文學習--英文email 請他人協助怎麼說? →