6 SQL Server Windows NT Windows 2000 6.1 Enterprise Manager SQL Server Enterprise Manager( ) (Microsoft Management C o n s o l e M M C ) Enterprise Manager SQL Server Enterprise Manager 6.1.1 Enterprise Manager Enterprise Manager Server Group( ) M a n a g e m e n t ( ) Current Activity( ) Process Info( ) ( 6-1 ) Enterprise Manager C u r r e n t A c t i v i t y Current Activity R e f r e s h ( ) 6-1 Enterprise Manager
6 69 Process Info( ) ID(server process ID S P I D ) S P I D SQL Server ( ) S P I D SQL Server I D SPID SPID SPID SPID SQL Server SPID SPID SPID S P I D S P I D Current Activity( ) Blocked By( ) ( ) 6.1.2 Enterprise Manager M a n a g e r m e n t ( ) Current Activity( ) L o c k s / P r o c e s s I D ( / I D ) L o c k s / O b j e c t ( / ) S P I D S P I 6.2 (Performance Monitor P e r f M o n ) SQL Server Windows NT Windows 2000 C P U SQL Server SQL Server C P U ( 6-2 ) ( 6-3
70 I / O 6-2 6-3 6.2.1 30 60 G 1 5
6 71 Windows NT4 Windows 2000 Windows 2000 1) 2) 3) ( + ) 4) 5) 6) 7) P r o c e s s o r M o n 8) 1) 2) 1) 2) 3) ( ) P r o c e s s o r M o n 4) 5) 6) ( + ) 1) E d i t ( ) Time Wi n d o w ( ) 2) Input Log File Ti m e F r a m e ( ) ( 6-4 ) 6-4
72 6.2.2 Windows NT (Disk Administor) R A I D R A I D (DiskPerf Service) D i s k P e r f D i s k P e r f D i s k P e r f Windows NT diskperf -y D i s k P e r f Services applet( ) I / O Wi n d o w s N T 4 I / O (I/O Request Packets I P R ) I R P Windows NT4 I R P D i s k P e r f I R P I R I / O ( S p i n L o c k ) ( S p i n L o c k S p i n L o c k ) S p i n L o c k C P U C P U D i s k P e r f I R P 1 C P U I / O S p i n L o c k Windows NT I R P 1) 2) R E G E D T 32 Windows NT ( Wi n d o w s NT Registry Editor) 3) H K E Y _ L O C A L _ M A C H I N E 4) S y s t e m ( ) C u r r e n t C o n t r o l S e t ( ) C o n t r o l ( ) S e s s i o n M a n a g e r ( ) 5) 6) I/O System 7) I/O System L a rg e I r p S t a c k L o c a t i o n s R E G _ D W O R D 8) 5 9) 6.2.3 SQL Server
6 73 1. (Processor Object) C P U C P U 90 9 0 9 ( Processor Time) 1 00 C P U ( Privileged Time) ( I / O ) C P U ( C P U ( D P C ) ( User Time) ( Interrupt Time) CPU Windows NT / (Interrupts/sec) D P C D P C 2. (System Object) (Processor Queue Length) / (Context Switches/sec) ( ) / 3. SQL Server ( B u ffer Manager Object) SQL Server
74 ( B u ffer Cache Hit Ratio) SQL Server I / O I / O 80 SQL Server SQL Server ( )(Cache Size) SQL Server 8 K B (Free Buffer) SQL Server / (Page Reads/sec) I / O (Stolen Page Count) SQL Server Windows NT / (Page Writes/sec) SQL Server 4. SQL Server (Database Object) SQL Server / (Log Flush Wait/sec) (Percent Log Used) SQL Server 5. SQL Server (General Statistics Object) (User Connections) 6. SQL Server (Latches Object) SQL Server ( )( Average Latch Wait Time) / (Latch Waits/sec) 7. SQL Server (Locks Object) SQL Server ( )( Average Wait Time) / (Lock Timeouts/sec) / (Lock Wa i t s / s e c ) / (Number of Deadlocks/sec) 8. SQL Server (Memory Manager Object)
6 75 SQL Server SQL Server (Memory Grants Pending) S Q L (KB)(SQL Cache Memory) SQL Server SQL ( K B )( Ta rget Server Memory) SQL Server ( K B )( Total Server Memory) SQL Server 9. SQL Server S Q L (SQL Statistics Object) S Q L / (Batch Requests/sec) S Q L SQL / (SQL Compilations/sec) SQL Server S Q L S Q L / (SQL Re-Compilations/sec) SQL Server S Q L 10. (Logical Disk Object) I / O Windows NT ( Disk Read Time) ( Disk Write Time) ( Disk Time) ( Idle Time) ( Avg. Disk Queue Length) ( Avg. Disk Read Queue Length) ( Avg. Disk Write Queue Length) / ( Avg. Disk sec/read) / ( Avg. Disk sec/write) / (( Avg. Disk sec/transfer) / (Disk Reads/sec) / (Disk Writes/sec) / (Disk Transfers/sec) 11. (PhysicalDisk Object) I / O
76 D i s k P e r f ( Disk Read Time) ( Disk Write Time) ( Disk Time) ( Idle Time) ( Avg. Disk Queue Length) ( Avg. Disk Read Queue Length) ( Avg. Disk Write Queue Length) / ( Avg. Disk sec/read) / ( Avg. Disk sec/write) / ( Avg. Disk sec/transfer) / (Disk Reads/sec) / (Disk Writes/sec) / (Disk Transfers/sec) 12. Windows NT Windows NT Windows NT ( ) / (Page Faults/sec) ( ) ( ) ( ) / (Page Reads/sec) ( ) ( / (Page Writes/sec) / (Pages/sec) /
6 77 / 6.2.4 I / O 1. (Processor Object) I / O I / O / I / O 3 I / O 2. (System Object) / 10 000 SQL Server (lightweight pooling) 1 ( s p _ c o n f i g u r e ) SQL Server Windows NT I / O 3 I / O 2 C P U 17 18 3. SQL Server ( B u ffer Manager Object) SQL Server B u ffer Cache Hit Ratio( ) SQL Server 80 SQL Server SQL Server SQL Server SQL Server (max server memory) 2147483674 SQL Server Windows NT
78 SQL Server SQL Server Cache Size( ) SQL Server 8 - K B SQL Server Cache Size Stolen Page( ) 8 - K B SQL Server SQL Server s p _ c o n f i g u r e min server memory( ) 0 SQL Server Windows NT Page Reads/sec( / ) S Q L S e r v e r 4. SQL Server (Database Object) Log Flush Wa i t s / s e c ( / ) I / O 5. SQL Server (Latches Object) I / O I / O I / O 6. SQL Server (Locks Object) SQL Server D e a d l o c k s / s e c ( / )
6 79 SQL Server (Deadlock Vi c t i m ) SQL Server 1205 SQL Server SQL Server Profiler(SQL Server ) SQL Server S u p p l i e r P a r t S u p p l i e r 6-5 ( ) 1 S u p p l i e r P a r t 2 P a r S u p p l i e r 1 S u p p l i e r P a r t 2 S u p p l i e r P a r t 6-5
80 (Read Committed) ( S e r i a l i z a b l e ) 7. SQL Server (Memory Manager Object) SQL Server Memory Grants Pending( ) SQL Server S Q L S e r v e r 8. SQL Server S Q L (SQL Statistics Object) S Q L Batch Requests/sec ( / ) S Q L S Q L S Q L S Q L SQL Server SQL 9. (Logical and PhysicalDisk Object) I / O RAID I/O Avg. Disk Queue Length( ) R A I D 7 9 1. 28 ( 9 / 7 ) 2 I / O Avg. Disk sec/read( / ) Avg. Disk sec/wr i t e ( / ) Avg. Disk sec/tr a n s f e r ( / ) 3 / 12 4
6 81 11 ~ 15 S Q L Disk Tr a n s f e r / s e c ( / ) 60 ~ 80 I / O R A I D I / O R A I D ( 3 3. 4. 3 ) 10. Windows NT Windows NT ( ) Windows NT C P U P a g e s / s e c ( / ) 0 ( Windows NT ) SQL Server SQL Server max m e m o r y ( ) / / / SQL Server SQL Server / SQL Server 6.3 SQL Server Windows NT SQL Server Profiler(SQL Server )