SQL Server 2008 R2 と SQL Server 2012 RC0 のパフォーマンスモニタと町事象のカウンタを比較してみました。
# 2012 をベースに比較しています。
■パフォーマンスモニタ
まずは、パフォーマンスモニタのカウンタをリンクサーバー経由で 2008 R2 と 2012 を比較してみました。
SELECT DISTINCT |
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 td> | 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 |
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 |
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 項目追加されているようです。
新機能を使う場合はこれらのカウンタの意味を調べておくとよさそうですね。