1 o o o CPU o o o o o SQL Server 2005 o CPU o o o o o SQL Server o Microsoft SQL Server 2005
1 1...3 2...20 3...28 4...41 5 Windows SQL Server...47 Microsoft SQL Server 2005 DBSRV1 Microsoft SQL Server Management Studio SSMS SQL Server 2005 AdventureWorks
1 1 capacity planning 40 Web service level agreement SLA Microsoft SQL Server 2005
1 tempdb 5 OLEDB.NET Framework SQL Server connection pooling SQL
1 Page/sec % Disk Time 1-1 Processor:% Processor Time System: Processor Queue Length Memory Pages/sec Memory Available Bytes Physical Disk % Disk Time Physical Disk Avg. Disk Queue Length Physical Disk Avg. Disk Reads/sec Physical Disk Avg. Disk Writes/ sec Network Interface Bytes Total/sec SQL Server: Buffer Manager Buffer Cache Hit Ratio SQL Server: Buffer Manager Page Life Expectancy SQL Server: General Statistics User Connections SQL Server: Databases Transactions/sec SQL Server: Databases Data File(s) Size KB 75% 50% 2 2 4 20 15 50 MB 50% 25 10 CPU 85% CPU 85% 90% 99% 300 CPU 1-1 SQL Server Microsoft SQL Server 2005
1 SQL Server: Databases Percent Log Used 1-1 SQL Server Quick Check 1. CPU 2. Avg. Disk Queue Length 3. Pages/sec 10 20 benchmark NOTE Windows Microsoft Excel1-1 Quick Check 1. 75% CPU % Processor Time 2. 2 3. 20
1 1-1Excel Excel 1-2 Average CPU Utilization 70.00% 60.00% 50.00% 40.00% 30.00% 20.00% 10.00% 0.00% 1/1/2006 2/1/2006 3/1/2006 4/1/2006 Percent Utilization 5/1/2006 6/1/2006 7/1/2006 8/1/2006 9/1/2006 10/1/2006 11/1/2006 1-2 CPU Date Microsoft SQL Server 2005
1 NOTE Excel 40 GB 5% Linear growth 200 GB 10 = + ( x ) 4010 =40+(10x3) = 40 + (10 x 3) = 70 1-3 10 MB
1 150 Disk Usage (MB) 100 50 Growth = 10 MB / month J F M A M J J A S O N D 1-3 Time geometric growth compound growth 2% = (1 + ) 600 GB2% = 600 (1 +.02) 36 = 600 (1.02) 36 = 600 (2.04) = 1224 GB 1-4 10% Microsoft SQL Server 2005
1 150 Disk Usage (MB) 100 50 Growth = 10% / month J F M A M J J A S O N D 1-4 Time Exam Tip 70-443 load testing 10
1 CAUTION knee in performance curve NOTE CPU75% 85% SQL ServerBatch Requests/ sec SQL Server:SQL Statistics 1-5 1-5 100 Microsoft SQL Server 2005 11
1 Stress climb Performance knee Application Response Time (sec.) 15 10 5 Maximum efficency 1 10 25 50 75 100 125 150 175 200 1-5 Number of Users stress climb 1-5 175 175 SLA SLA CPU 75% CPU CPU 75% NOTE (www. microsoft.com/services/microsoftservices/srv_tech.mspx) 12
1 workload analysis CPU RAID I/O IOPS 85% I/O Physical Disk: Disk Reads/sec Physical Disk: Disk Writes/sec SET DTATISTICS IO 828,800 page fault 20 Microsoft SQL Server 2005 13
1 NOTE OLTPI/O 66%33% DSS 90%10% I/O MORE INFO Disk Subsystem Performance Analysis for Windowshttp://www.microsoft.com/whdc/device/storage / subsys_perf.mspx CPU CPUCPU CPU CPU CPU 75% CPU SET STATISTICS TIME CPU CPU CPU CPU CPU CPU Transaction Processing Performance Council www.tpc. org CPU I/O CPU CPU 14
1 300 4 SQL Server 2005 SQL Server: General Statistics User Connections tempdb 85% SQL Server: Database Data File(s) Size KB NOTE 3 5 Microsoft SQL Server 2005 15
1 KB NOTE switch hub Network Interface Bytes Total/sec Bytes Total/sec NOTE Bytes Total/sec Network Interface Server Bytes Total/sec Network Interface Server 16
1 PRACTICE 1. Windows 2. 3. 4. SQL Server Performance Counters SQL Server Performance Counters 5. 6. 7. 1-2 SQL Server: Buffer Manager Buffer cache hit ratio N/A SQL Server: Databases Transactions/sec _Total SQL Server: Databases Data File(s) Size (KB) _Total SQL Server: General Statistics User Connections N/A 1-2 SQL Server 8. 9. SQL Server Performance Counters 10. Microsoft SQL Server 2005 17
1 11. C:\PerfLogs SQL Server Performance Counters 12. SQL Server Performance Counters 13. SQL Server Performance Counters 14. 15. SQL Server Performance Counters 16. SQL Server Performance Counters.HTM 17. HTM 18. SQL Server Performance Counters.HTM 19. 18
1 1 1. CPU A. Physical Disk % Disk Time averages 40 B. Processor % Processor Time 65% C. SQL Server: Buffer Manager Buffer Cache Hit Ratio 85% D. System Processor Queue Length 4 2. A. Network Interface: Bytes Total/sec B. Network Interface: Current Bandwidth C. Network Interface: Packets/sec D. Server: Bytes Total/sec 3. 100 GB 2% A. 259 GB B. 178 GB C. 235 GB D. 288 GB NOTE Microsoft SQL Server 2005 19
3 I/O I/O PRACTICE Test Exercise 1 AdventureWorks 1. DBSRV1 SSMS 2. SSMS 3. AdventureWorks 4. 5. AdventureWorks AdventureWorks 6. AdventureWorks_Data.mdf AdventureWorks_Data.mdf 164 MB. 7. AdventureWorks_Data.mdf 16 MB Microsoft SQL Server 2005 37
1 8. AdventureWorks_Data.mdf... 9. AdventureWorks_Data AdventureWorks_Data.mdf 10. AdventureWorks_Data.mdf 11. AdventureWorks_Data 12. 13. AdventureWorks AdventureWorks 14. Exercise 2 1. SSMS 2. Test 3. Test.mdf Test_log.ldf 4. Test.mdf... 5. 6. 7. SSMS 38
3 85% SQL Server RAID 5 RAID 10 2 1. SQL Server 2005 A. B. C. D. 2. RAID 1 RAID 5 A. B. byte C. D. 3. A. B. RAID 5 C. D. I/O I/O Microsoft SQL Server 2005 39
1 4. RAID A. RAID 0 B. RAID 1 C. RAID 5 D. RAID 10 5. OLTPSQL Server A. B. C. D. 6. A. B. C. D. NOTE 40