Tag Archives: Performance

效能監控

感謝承修提醒了一個效能監控軟體 Windows Performance Analyzer 的連結討論,

http://www.qa-knowhow.com/?cat=67

它的安裝畫面與 blog 中稍有不同

image

解釋 Windows Performance Counter 不錯的工具 Performance Analysis of Logs (PAL) Tool

下載該工具的位址:http://pal.codeplex.com/

解釋 PAL 工具用途的 Video:http://video.sina.com.cn/v/b/96373268-1136772730.html

其產生的 HTML 報告外觀約略如下,有許多透過 Counter 加以演算的結果與提醒,告知可能的效能問題。為了避免外洩資料,故意將圖縮小後呈現 :),僅呈現報告示意圖

image

Windows Logon 程序造成 SQL 的 xp_CmdShell 或 RS 運作緩慢

連續碰到因為 SQL Services 或 RS 因為要換身分,造成雖然機器不忙但反應時間緩慢,其原因是 Logon 會 Call 所有

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\NetworkProvider\Order Registry 內所列的 Provider,而這些 Provider 有些第三方廠商加入的,會造成回應緩慢甚至錯誤,參看 Ellie 的 Blog

http://www.dotblogs.com.tw/ellie.hong/archive/2012/07/05/73235.aspx

http://blogs.technet.com/b/instan/archive/2008/04/03/the-case-of-the-mysterious-2-minute-logon-delay.aspx

留個記錄

承修給了個營運記錄,在 User 可接受的系統回應下,伺服器的運作

Windows 2003/SQL 2008(10.0.4000) 32 bit,ProLiant DL380 G6 CPU 2666 Mhz ,16 (開Hyper thread) Mem 12G(SQL Server 開 AWE Max RAM 10 G):每秒批次要求數量  19879,平均批次數量13247 /sec,當時 CPU 在 95

在 blog 留個記錄,看日後各系統的演進,也有個 baseline 微笑
期待各方的回應,那就有個台灣 SQL Server 的 baseline 了

 

 

微軟 SQL CAT 2010 年在全世界的紀錄:http://channel9.msdn.com/Events/TechEd/NorthAmerica/2010/DAT305

Largest single database:70 TB

Largest table:20 TB

Biggest total data 1 application:88 PB

Highest database transactions per second 1 db (from Perfmon):130,000

Fastest I/O subsystem in production (SQLIO 64k buffer):18 GB/sec

Fastest “real time” cube:5 sec latency

data load for 1TB:20 minutes

Largest cube:12 TB

一位朋友告訴我,他們的線上遊戲的 SQL 主機全部採用 RAM Disk,同時上線存取人數破萬

手動啟動 VS2010 效能分析

在 Visual Stuido 2010 整合環境因故無法啟動 "分析"->”啟動效能精靈”,可以透過如下的方式以 Console 的方式手動錄製

http://blogs.msdn.com/b/graycode/archive/2005/05/10/aspnetoffroadprofilingarticle.aspx

碰到兩個問題:

  • 找不到 64 bit 的錄製工具,因此將 IIS AppPool 設成  32 bit
  • 文中的指令 vsperfmon /SAMPLE /OUTPUT:[your ouputfile].vsp /USER:"[ASP.NET worker process user]" /CS 要加 /CrossSession 參數,可縮寫為 CS

安裝監控 .NET 程式執行的 AviCode

僅是安裝單機的 AviCode,測試監控 .NET 應用程式執行

image

嘗試如下的安裝:

前置非必要安裝,只是因為在同一台環境測試

  • 安裝 SQL Server 2008 R2,包含 RS (需要先有這兩者,RS 是 AVIcode Advisor 需要)
  • VS 2010

安裝

image

  • 安裝 AVIcode Intercept SE-Viewer 5.7

image

  • 安裝 AVIcode Intercept Agent

過程中會要求 SE-Viewer Server 的位置,若要監控多台 Web,例如 Web AP –> Web Services,只要在 Web Services 裝 Agent,並設定 SE-Viewer 機器,Agent 會 Call SE-Viewer 的 Web Service 上傳資料

image

安裝完畢後,會叫出設定畫面,透過 Add Web Application 增加要監控的 Web Ap

image

這已經可以工作了

image

可分析多層次的呼叫關係,跨機器都可以繼續呈現

image

當時系統的效能狀況

image

  • 安裝 AVIcode Advisor

若  RS 是中文版,又漏改 Role 設定,會因為沒有 Browser 這個 Role 而出現錯誤

image

會發生以下的錯誤

image

可以在 RS 加角色,但似乎應該改前面的 Configuration

image

SQL Agent Services 要啟動,AVIcode 會透過 Reporting Services 設定大量 Job

image

安裝完畢後,會需要手動啟動 Intercept Reporting Windows Services

image

而後就可以有綜合報表,彼此之間的 Link 做得很好

image

SQL Server Denali 新增或修改的動態管理物件

 

以下的動態管理物件參照:http://technet.microsoft.com/en-us/library/cc645579(SQL.110).aspx,但似乎都在 SQL Server 2008 R2 SP1 已經新增,應不算 Denali

 

  • sys.dm_exec_query_stats :增加了 total_rows, min_rows, max_rows 和 last_rows 等欄位
  • sys.dm_os_volume_stats:傳回儲存指定資料庫及檔案所在之作業系統磁碟區 (目錄) 的相關資訊。

SELECT db_name(f.database_id) db, f.name,f.physical_name, dovs.*
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) dovs;

image

 

  • sys.dm_server_memory_dumps 等傳回服務相關資訊。

select * from sys.dm_server_memory_dumps
select * from sys.dm_server_services
select * from sys.dm_server_registry

image

Recovery Mode 是 Simple 或 Bulk_Logged 在 Rebuild Index 可以省硬碟空間,但仍會耗空間

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

create database db
go
use db
alter database db set recovery simple
go
create table t(c1 int identity,c2 char(900) default convert(char(900),rand()))
go
set nocount on
go
insert t default values
go 100000

select * from t
–db 399616 KB log 576 KB
exec sp_helpdb ‘db’

–log 3904 kb
create index idx on t(c2)
–log 576 KB
dbcc shrinkfile(db_log)

alter index idx on t rebuild

–log 3520 kb
exec sp_helpdb ‘db’

–log 576 KB
dbcc shrinkfile(db_log)

alter database db set recovery bulk_logged

alter index idx on t rebuild

–log 3520 kb
exec sp_helpdb ‘db’

 

alter database db set recovery full

alter index idx on t rebuild

–log 125888 KB
exec sp_helpdb ‘db’

use master
drop database db

Heap 的 Row Forward 與 Clustered Index 的 Page Split

觀察動態長度資料修改時,若該 page 無法提供足夠空間放變動後資料,會造成的影響。

若資料表為 Heap,則 SQL Server 會在該紀錄原址放一個 Forward 指標,並將新資料放在新增的 Page 內,這在修改上效率較佳。但查詢大量資料時效率較差,因為需來回逐紀錄讀取 Page

若資料表為 Clustered,則 SQL Server 會做 Page Split,這在大量修改超過長度的紀錄時效率較差。但查詢大量資料時效率較佳

Heap

建立 Heap:

use tempdb
drop table Heap
create table Heap(c1 int identity,c2 varchar(1000))
go
set nocount on
insert Heap values(‘a’)
go 1000

begin tran
    update Heap set c2=REPLICATE(‘a’,500) where c1<10

觀察 Performance Counter

\SQL Server:Access Methods\Forwarded Records/sec  : 9.007

\SQL Server:Access Methods\Page Splits/sec : 2.002


    exec sp_lock @@spid

image

    dbcc traceon(3604)
    dbcc page(tempdb,1,455,3)
commit

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息,請連絡您的系統管理員。

PAGE: (1:455)

BUFFER:

BUF @0x0000000097FF6A00

bpage = 0x0000000097E70000           bhash = 0x0000000000000000           bpageno = (1:455)
bdbid = 2                            breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 26877                        bstat = 0xc0000b
blog = 0xcbcbbbbb                    bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000097E70000

m_pageId = (1:455)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 628154 m_indexId (AllocUnitId.idInd) = 28160
Metadata: AllocUnitId = 7926335385338773504                              
Metadata: PartitionId = 7782220191937069056                               Metadata: IndexId = 0
Metadata: ObjectId = 1930489956      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 8                          m_slotCnt = 428                      m_freeCnt = 455
m_freeData = 7025                    m_reservedCnt = 63                   m_lsn = (151:433:215)
m_xactReserved = 63                  m_xdesId = (0:21857)                 m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x63 MIXED_EXT ALLOCATED  95_PCT_FULL                         DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x1b20 Length 9

Record Type = FORWARDING_STUB        Record Attributes =                  Record Size = 9

Memory Dump @0x000000001694BB20

0000000000000000:   04e30100 00010090 00†††††††††††††††††………       
Forwarding to  =  file 1 page 483 slot 144                               

Slot 1 Offset 0x1b29 Length 9

 

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘Heap’)

結果

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
50                           2                    1.5                        3                    93.451939708426                9

查詢資料時會浪費 I/O,往返讀取 Forward 紀錄

set statistics io on
select * from Heap where c1 < 10

資料表 ‘Heap’。掃描計數 1,邏輯讀取 12,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 2008 後才提供重整 Heap 的能力

alter table Heap rebuild

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘Heap’)

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
50                           2                    2                          4                    69.4495922905856               0

重整後 I/O 減少了

select * from Heap where c1 < 10

資料表 ‘Heap’。掃描計數 1,邏輯讀取 4,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

 

Clustered Index

若是採用 Clustered Index,則會造成 Page Split

建立 Clustered Index:

drop table tblClustered
create table tblClustered(c1 int identity primary key,c2 varchar(1000))
go


set nocount on
insert tblClustered values(‘a’)
go 1000

begin tran
    update tblClustered set c2=REPLICATE(‘a’,500) where c1<10

觀察 Performance Counter

\SQL Server:Access Methods\Forwarded Records/sec : 0

\SQL Server:Access Methods\Page Splits/sec : 7.999

exec sp_lock @@spid

image

dbcc traceon(3604)
dbcc page(tempdb,1,448,3)
commit

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息,請連絡您的系統管理員。

PAGE: (1:448)

BUFFER:

BUF @0x0000000099FF2A40

bpage = 0x0000000099DC6000           bhash = 0x0000000000000000           bpageno = (1:448)
bdbid = 2                            breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 27917                        bstat = 0xc0000b
blog = 0xbcbcbbbb                    bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000099DC6000

m_pageId = (1:448)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 647529 m_indexId (AllocUnitId.idInd) = 3328
Metadata: AllocUnitId = 936748764929523712                               
Metadata: PartitionId = 216172818896715776                                Metadata: IndexId = 1
Metadata: ObjectId = 39007220        m_prevPage = (1:240)                 m_nextPage = (1:247)
pminlen = 8                          m_slotCnt = 217                      m_freeCnt = 3192
m_freeData = 4582                    m_reservedCnt = 0                    m_lsn = (159:376:27)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 515

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 515                   
Memory Dump @0x000000001011A060

0000000000000000:   30000800 08000000 02000001 00030261 †0…………..a
0000000000000010:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000020:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000030:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000040:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000050:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000060:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000070:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000080:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000090:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000A0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000B0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000C0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000D0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000E0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000F0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000100:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000110:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000120:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000130:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000140:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000150:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000160:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000170:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000180:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000190:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001A0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001B0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001C0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001D0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001E0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001F0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000200:   616161†††††††††††††††††††††††††††††††aaa             

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘tblClustered’)

結果

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
60                           4                    1.25                       5                    55.5497899678774              NULL
0                            1                    1                          1                    0.778354336545589             NULL

查詢資料時直接 Scan

set statistics io on
select * from Heap where c1 < 10

資料表 ‘tblClustered’。掃描計數 1,邏輯讀取 3,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

索引使用狀況

感謝承修提供了一些關於索引使用的查詢

SELECT

    ObjectName      = object_schema_name(idx.object_id) + ‘.’ + object_name(idx.object_id) 

    ,IndexName      = idx.name

    ,IndexType      = CASE

                        WHEN is_unique = 1 THEN ‘UNIQUE ‘

                        ELSE " END + idx.type_desc 

    ,User_Seeks     = us.user_seeks 

    ,User_Scans     = us.user_scans 

    ,User_Lookups   = us.user_lookups 

    ,User_Updates   = us.user_updates 

FROM sys.indexes idx 

LEFT JOIN sys.dm_db_index_usage_stats us 

    ON idx.object_id = us.object_id 

    AND idx.index_id = us.index_id 

    AND us.database_id = db_id() 

WHERE object_schema_name(idx.object_id) != ‘sys’

ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC

 

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  ‘CREATE INDEX [missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
  + ‘_’ + LEFT (PARSENAME(mid.statement, 1), 32) + ‘]’
  + ‘ ON ‘ + mid.statement
  + ‘ (‘ + ISNULL (mid.equality_columns,")
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE " END
    + ISNULL (mid.inequality_columns, “)
  + ‘)’
  + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, “) AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
–WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

 

 

SELECT
  ‘CREATE INDEX [missing_index_’ + CONVERT (VARCHAR, mig.index_group_handle) + ‘_’ + CONVERT (VARCHAR, mid.index_handle)
  + ‘_’ + LEFT (PARSENAME(mid.STATEMENT, 1), 32) + ‘]’
  + ‘ ON ‘ + mid.STATEMENT
  + ‘ (‘ + ISNULL (mid.equality_columns,")
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE " END
    + ISNULL (mid.inequality_columns, “)
  + ‘)’
  + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, “) AS create_index_statement,
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  migs.avg_user_impact AS [%Increase],
  db_name(mid.database_id) AS DatabaseName,
  object_name(mid.[object_id]) AS TableName
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
    ON mig.index_handle = mid.index_handle
WHERE
    mid.database_id = DB_ID() –<——-Run from the db you want to query
    AND CONVERT (decimal (28,1),
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY improvement_measure DESC