SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2008 R2 と SQL Server 2012 RC0 のカウンタの比較

leave a comment

SQL Server 2008 R2 と SQL Server 2012 RC0 のパフォーマンスモニタと町事象のカウンタを比較してみました。
# 2012 をベースに比較しています。

■パフォーマンスモニタ

まずは、パフォーマンスモニタのカウンタをリンクサーバー経由で 2008 R2 と 2012 を比較してみました。

SELECT DISTINCT
RTRIM(RIGHT(BASE.object_name, CHARINDEX(‘:’, REVERSE(BASE.object_name)) – 1))
, RTRIM(counter_name)
FROM
[sys].[dm_os_performance_counters] AS BASE
WHERE
NOT EXISTS(
SELECT
  *
FROM
[localhostSQL2008R2].[master].[sys].[dm_os_performance_counters] AS TARGET
WHERE
RTRIM(RIGHT(BASE.object_name, CHARINDEX(‘:’, REVERSE(BASE.object_name)) – 1)) COLLATE Japanese_BIN2 =
RTRIM(RIGHT(TARGET.object_name, CHARINDEX(‘:’, REVERSE(TARGET.object_name)) – 1)) COLLATE Japanese_BIN2
AND
BASE.counter_name COLLATE Japanese_BIN2 = TARGET.counter_name
)

 

Access Methods InSysXact waits/sec
Availability Replica Bytes Received from Replica/sec
Availability Replica Bytes Sent to Replica/sec
Availability Replica Bytes Sent to Transport/sec
Availability Replica Flow Control Time (ms/sec)
Availability Replica Flow Control/sec
Availability Replica Receives from Replica/sec
Availability Replica Resent Messages/sec
Availability Replica Sends to Replica/sec
Availability Replica Sends to Transport/sec
Batch Resp Statistics Batches >=000000ms & <000001ms
Batch Resp Statistics Batches >=000001ms & <000002ms
Batch Resp Statistics Batches >=000002ms & <000005ms
Batch Resp Statistics Batches >=000005ms & <000010ms
Batch Resp Statistics Batches >=000010ms & <000020ms
Batch Resp Statistics Batches >=000020ms & <000050ms
Batch Resp Statistics Batches >=000050ms & <000100ms
Batch Resp Statistics Batches >=000100ms & <000200ms
Batch Resp Statistics Batches >=000200ms & <000500ms
Batch Resp Statistics Batches >=000500ms & <001000ms
Batch Resp Statistics

Batches >=001000ms & <002000ms
Batch Resp Statistics Batches >=002000ms & <005000ms
Batch Resp Statistics Batches >=005000ms & <010000ms
Batch Resp Statistics Batches >=010000ms & <020000ms
Batch Resp Statistics Batches >=020000ms & <050000ms
Batch Resp Statistics Batches >=050000ms & <100000ms
Batch Resp Statistics Batches >=100000ms
Buffer Manager Background writer pages/sec
Buffer Manager Integral Controller Slope
Database Replica File Bytes Received/sec
Database Replica Log Bytes Received/sec
Database Replica Log remaining for undo
Database Replica Log Send Queue
Database Replica Mirrored Write Transactions/sec
Database Replica Recovery Queue
Database Replica Redo blocked/sec
Database Replica Redo Bytes Remaining
Database Replica Redone Bytes/sec
Database Replica Total Log requiring undo
Database Replica Transaction Delay
Databases Log Flush Write Time (ms)
Databases Log Pool Cache Misses/sec
Databases Log Pool Disk Reads/sec
Databases Log Pool Requests/sec
FileTable Avg time delete FileTable item
FileTable Avg time FileTable enumeration
FileTable Avg time FileTable handle kill
FileTable Avg time move FileTable item
FileTable Avg time per file I/O request
FileTable Avg time per file I/O response
FileTable Avg time rename FileTable item
FileTable Avg time to get FileTable item
FileTable Avg time update FileTable item
FileTable FileTable db operations/sec
FileTable FileTable enumeration reqs/sec
FileTable FileTable file I/O requests/sec
FileTable FileTable file I/O response/sec
FileTable FileTable item delete reqs/sec
FileTable FileTable item get requests/sec
FileTable FileTable item move reqs/sec
FileTable FileTable item rename reqs/sec
FileTable FileTable item update reqs/sec
FileTable FileTable kill handle ops/sec
FileTable FileTable table operations/sec
FileTable Time delete FileTable item BASE
FileTable Time FileTable enumeration BASE
FileTable Time FileTable handle kill BASE
FileTable Time move FileTable item BASE
FileTable Time per file I/O request BASE
FileTable Time per file I/O response BASE
FileTable Time rename FileTable item BASE
FileTable Time to get FileTable item BASE
FileTable Time update FileTable item BASE
Memory Broker Clerks Internal benefit
Memory Broker Clerks Memory broker clerk size
Memory Broker Clerks Periodic evictions (pages)
Memory Broker Clerks Pressure evictions (pages/sec)
Memory Broker Clerks Simulation benefit
Memory Broker Clerks Simulation size
Memory Manager Database Cache Memory (KB)
Memory Manager External benefit of memory
Memory Manager Free Memory (KB)
Memory Manager Log Pool Memory (KB)
Memory Manager Reserved Server Memory (KB)
Memory Manager Stolen Server Memory (KB)
Memory Node Database Node Memory (KB)
Memory Node Foreign Node Memory (KB)
Memory Node Free Node Memory (KB)
Memory Node Stolen Node Memory (KB)
Memory Node Target Node Memory (KB)
Memory Node Total Node Memory (KB)
Query Execution Local data access/sec
Query Execution Rem Req Cache Hit Ratio
Query Execution Rem Req Cache Hit Ratio Base
Query Execution Remote activations/sec
Query Execution Remote requests/sec
Query Execution Remote resend requests/sec

 

97 項目が追加されていました。新機能関係が多いみたいですね。
Batch Resp Statistics は少し調べてみたいです。

■Wait Stats

続いて Wait Stats (待ち事象) の比較を。

SELECT
wait_type
FROM
[sys].[dm_os_wait_stats] AS BASE
WHERE
NOT EXISTS(
SELECT
*
FROM
[localhostSQL2008R2].[master].[sys].[dm_os_wait_stats] AS TARGET
WHERE
BASE.wait_type COLLATE Japanese_BIN2 =
TARGET.wait_type COLLATE Japanese_BIN2
)
ORDER BY wait_type

 

BROKER_TRANSMISSION_TABLE HADR_WORK_QUEUE
BROKER_TRANSMISSION_WORK HADR_XRF_STACK_ACCESS
COUNTRECOVERYMGR HTBUILD
CREATE_DATINISERVICE HTREPARTITION
DBCC_SCALE_OUT_EXPR_CACHE LOGCAPTURE_LOGPOOLTRUNCPOINT
DBSTATE LOGPOOL_CACHESIZE
DIRTY_PAGE_POLL LOGPOOL_CONSUMER
DIRTY_PAGE_SYNC LOGPOOL_CONSUMERSET
DISPATCHER_PRIORITY_QUEUE_SEMAPHORE LOGPOOL_FREEPOOLS
DTCPNTSYNC LOGPOOL_MGRSET
ENABLE_EMPTY_VERSIONING LOGPOOL_REPLACEMENTSET
FFT_NSO_DB_KILL_FLAG LOGPOOLREFCOUNTEDOBJECT_REFDONE
FFT_NSO_DB_LIST MD_AGENT_YIELD
FFT_NSO_FCB MD_LAZYCACHE_RWLOCK
FFT_NSO_FCB_FIND PREEMPTIVE_HADR_LEASE_MECHANISM
FFT_NSO_FCB_PARENT PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES
FFT_NSO_FCB_RELEASE_CACHED_ENTRIES PREEMPTIVE_SP_SERVER_DIAGNOSTICS
FFT_NSO_FILEOBJECT PWAIT_ALL_COMPONENTS_INITIALIZED
FFT_NSO_TABLE_LIST PWAIT_COOP_SCAN
FFT_NTFS_STORE PWAIT_EVENT_SESSION_INIT_MUTEX
FFT_RECOVERY PWAIT_HADR_ACTION_COMPLETED
FFT_RSFX_COMM PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNC
FFT_RSFX_WAIT_FOR_MEMORY PWAIT_HADR_CLUSTER_INTEGRATION
FFT_STARTUP_SHUTDOWN PWAIT_HADR_FAILOVER_COMPLETED
FFT_STORE_DB PWAIT_HADR_OFFLINE_COMPLETED
FFT_STORE_ROWSET_LIST PWAIT_HADR_ONLINE_COMPLETED
FFT_STORE_TABLE PWAIT_HADR_POST_ONLINE_COMPLETED
FILESTREAM_CACHE PWAIT_HADR_SERVER_READY_CONNECTIONS
FILESTREAM_CHUNKER PWAIT_HADR_WORKITEM_COMPLETED
FILESTREAM_CHUNKER_INIT PWAIT_MD_LOGIN_STATS
FILESTREAM_FCB PWAIT_MD_RELATION_CACHE
FILESTREAM_FILE_OBJECT PWAIT_MD_SERVER_CACHE
FILESTREAM_WORKITEM_QUEUE PWAIT_MD_UPGRADE_CONFIG
FT_MASTER_MERGE_COORDINATOR PWAIT_PREEMPTIVE_AUDIT_ACCESS_WINDOWSLOG
FT_PROPERTYLIST_CACHE PWAIT_QRY_BPMEMORY
GDMA_GET_RESOURCE_OWNER PWAIT_REPLICA_ONLINE_INIT_MUTEX
GHOSTCLEANUPSYNCMGR PWAIT_RESOURCE_SEMAPHORE_FT_PARALLEL_QUERY_SYNC
HADR_AG_MUTEX PWAIT_SECURITY_CACHE_INVALIDATION
HADR_AR_CRITICAL_SECTION_ENTRY QRY_PARALLEL_THREAD_MUTEX
HADR_AR_MANAGER_MUTEX QUERY_TASK_ENQUEUE_MUTEX
HADR_AR_UNLOAD_COMPLETED REDO_THREAD_PENDING_WORK
HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST REDO_THREAD_SYNC
HADR_BACKUP_BULK_LOCK RESOURCE_GOVERNOR_IDLE
HADR_BACKUP_QUEUE SCAN_CHAR_HASH_ARRAY_INITIALIZATION
HADR_CLUSAPI_CALL SECURITY_CRYPTO_CONTEXT_MUTEX
HADR_COMPRESSED_CACHE_SYNC SECURITY_KEYRING_RWLOCK
HADR_CONNECTIVITY_INFO SECURITY_RULETABLE_MUTEX
HADR_DATABASE_FLOW_CONTROL SEMPLAT_DSI_BUILD
HADR_DATABASE_VERSIONING_STATE SLEEP_MASTERDBREADY
HADR_DATABASE_WAIT_FOR_RESTART SLEEP_MASTERMDREADY
HADR_DATABASE_WAIT_FOR_TRANSITION
_TO_VERSIONING		 SLEEP_MASTERUPGRADED
HADR_DB_COMMAND SOS_MEMORY_TOPLEVELBLOCKALLOCATOR
HADR_DB_OP_COMPLETION_SYNC SOS_PHYS_PAGE_CACHE
HADR_DB_OP_START_SYNC SP_PREEMPTIVE_SERVER_DIAGNOSTICS_SLEEP
HADR_DBR_SUBSCRIBER SP_SERVER_DIAGNOSTICS_BUFFER_ACCESS
HADR_DBR_SUBSCRIBER_FILTER_LIST SP_SERVER_DIAGNOSTICS_INIT_MUTEX
HADR_DBSTATECHANGE_SYNC SP_SERVER_DIAGNOSTICS_SLEEP
HADR_FILESTREAM_BLOCK_FLUSH STARTUP_DEPENDENCY_MANAGER
HADR_FILESTREAM_FILE_CLOSE UCS_ENDPOINT_CHANGE
HADR_FILESTREAM_FILE_REQUEST UCS_MANAGER
HADR_FILESTREAM_IOMGR UCS_MEMORY_NOTIFICATION
HADR_FILESTREAM_IOMGR_IOCOMPLETION UCS_SESSION_REGISTRATION
HADR_FILESTREAM_MANAGER UCS_TRANSPORT
HADR_GROUP_COMMIT UCS_TRANSPORT_STREAM_CHANGE
HADR_LOGCAPTURE_SYNC WAIT_XTP_GUEST
HADR_LOGCAPTURE_WAIT WAIT_XTP_TASK_SHUTDOWN
HADR_LOGPROGRESS_SYNC WAIT_XTP_TRAN_COMMIT
HADR_NOTIFICATION_DEQUEUE WAITFOR_PER_QUEUE
HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS XDES_HISTORY
HADR_NOTIFICATION_WORKER_STARTUP_SYNC XDES_OUT_OF_ORDER_LIST
HADR_NOTIFICATION_WORKER_TERMINATION_SYNC XDES_SNAPSHOT
HADR_PARTNER_SYNC XDESTSVERMGR
HADR_READ_ALL_NETWORKS XE_CALLBACK_LIST
HADR_RECOVERY_WAIT_FOR_CONNECTION XE_LIVE_TARGET_TVF
HADR_RECOVERY_WAIT_FOR_UNDO XTPPROC_PARTITIONED_STACK_CREATE
HADR_REPLICAINFO_SYNC
HADR_SYNC_COMMIT
HADR_SYNCHRONIZING_THROTTLE
HADR_TDS_LISTENER_SYNC
HADR_TDS_LISTENER_SYNC_PROCESSING

 

こちらも新機能系が多いですね。
165 項目が追加されているようです。

■Latch Stats

最後はラッチの比較を。

SELECT
latch_class
FROM
[sys].[dm_os_latch_stats] AS BASE
WHERE
NOT EXISTS(
SELECT
*
FROM
[localhostSQL2008R2].[master].[sys].[dm_os_latch_stats] AS TARGET
WHERE
BASE.latch_class COLLATE Japanese_BIN2 =
TARGET.latch_class COLLATE Japanese_BIN2
)
ORDER BY latch_class

 

ACCESS_METHODS_COLUMN_CACHE_SHARING
ACCESS_METHODS_INDEX_BUILD_ALLOCATION
ACCESS_METHODS_IOAFF_READAHEAD_QUEUE
COLUMNSTORE_ATTRIB_CACHE
COLUMNSTORE_INDEX_BUILD
COLUMNSTORE_OBJECT
CSIBUILD_HASHDICT_BUCKET
CSIBUILD_HASHDICT_GLOBAL
CSIBUILD_STRINGSTORE_GLOBAL
FILESTREAM_ALLOCATION_MANAGER
FILESTREAM_CONTAINER_HEADER
GHOST_CLEANUP_SYNC_MANAGER
HADR_BACKUP_LOCK_RESQUEUE_TABLE
HADR_BACKUP_TASK_TABLE
HADR_XDES_TABLE
HOBT_LOBPAGEINFO
METADATA_SEQUENCE_GENERATOR
SPACE_CACHES_MANAGER
SPACEMGR_ALLOCEXTENT_CACHE
SPACEMGR_CREATE_FREESPACE_CACHE
SPACEMGR_CREATE_RINGBUF
SPACEMGR_FREESPACE_CACHE
SPACEMGR_IAM_PAGE_RANGE_CACHE

 

CSI (Columnstore Index) 用のラッチが追加されているみたいですね。
こちらは 23 項目追加されているようです。

新機能を使う場合はこれらのカウンタの意味を調べておくとよさそうですね。

Share

Written by Masayuki.Ozawa

1月 11th, 2012 at 7:52 am

Posted in SQL Server

Tagged with ,

«
»

Leave a Reply