Download
microsoft sql server 2000 performance n.
Skip this Video
Loading SlideShow in 5 Seconds..
Microsoft ® SQL Server ™ 2000 Performance PowerPoint Presentation
Download Presentation
Microsoft ® SQL Server ™ 2000 Performance

Microsoft ® SQL Server ™ 2000 Performance

109 Views Download Presentation
Download Presentation

Microsoft ® SQL Server ™ 2000 Performance

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Microsoft® SQL Server™2000 Performance Matthew Stephen SQL Server Evangelist http://blogs.msdn.com/mat_stephen Mattstep@microsoft.com Microsoft Corporation

  2. What We Will Cover • Locking • Query Processor • Query Tuning • System Configuration • Performance Monitoring

  3. Session Prerequisites • This session assumes that you understand the fundamentals of • Windows® 2000 Server • SQL Server 2000 • System Monitor Level 200-300

  4. Agenda • Locking • Query Processor • Query Tuning • System Configuration • Performance Monitoring

  5. Lock ManagerWhat it does for you • Acquires and Releases Locks • Maintains compatibility Between Lock Modes • Resolves Deadlocks • Escalates Locks • Uses 2 Locking Systems • Shared Data Locks • Internal latches for Internal data and index concurrency

  6. Lock Isolation Levels • Supports all 4 ANSI and ISO isolation levels • Serializable • Repeatable Read • Read Committed • Read Uncommitted

  7. LockingUser data lock types – Shared • Acquired automatically when data is read • Applies to Table, Page, Index Key or row. • Many processes can hold a shared lock on the same data. • Cannot be locked exclusively while in shared lock mode* *Unless it is the same process that holds the shared lock

  8. LockingLock Granularity for user data

  9. LockingUser data lock types – Exclusive • Automatically acquired when data is modified • Only one process can hold at a time on any data. • Held until the end of a Transaction • All other lock requests by other processes will be refused. • Can use Query hints to decide to read locked data.

  10. LockingUser data lock types – Update • Hybrid of shared and exclusive • Acquired when a search is required before and modification • Allow others to still read while lock applied • Needs an exclusive lock to modify data • Data can have many shared but only one update

  11. LockingUser data lock types – Intent • Not a real lock mode just a qualifier e.g. Intent Update lock • Qualifier to modes already discussed

  12. LockingUser data lock types – Special • 3 special modes • Schema Stability – used when queries are modified, prevents scheme modification locks. • Scheme Modification – used when table structures are being modified • Bulk update – used when the BULK INSERT or BCP command are used.

  13. LockingViewing lock information • Use the sp_lock stored procedure • Shows current and waiting locks

  14. LockingViewing lock information - Type

  15. LockingViewing lock information - Mode

  16. Locking Lock Overhead • Lock Overhead • Each lock – 32 bytes • Each Process holding lock – 32 bytes • Each Process waiting for lock – 32 bytes

  17. Agenda • Locking • Query Processor • Query Tuning • System Configuration • Performance Monitoring

  18. Query Processor Query Compilation

  19. Query Processor Query Optimization

  20. Query Processor How the Optimizer Works • Query Analysis • Index Selection • Join Selection • Nested iteration • Hashing • Merging

  21. Query Processor Cost and cache plan

  22. Query ProcessorCompilation and execution flow

  23. Agenda • Locking • Query Processor • Query Tuning • System Configuration • Performance Monitoring

  24. Query TuningTuning – When to Start • Start at the Beginning • Consider performance before you even write your first line of code • Be sure that you've set up a good database structure • Create what appear to be useful indexes • Make sure all analysis is done with a representative workload

  25. Query Tuning Application and Database Design • Provides Biggest Performance Gains • Normalize • Evaluate Your Critical Transactions • Keep Table Row Lengths and Key Lengths Compact • Create Useful Indexes • Benchmark, Prototype and Test

  26. Query tuningIndex Creation and Tuning SQL Server Tuning Recommendations Real-Time Queries (Filtered) Workload Index Tuning Wizard SQL Profiler

  27. Query PerformanceGraphical Execution Plan

  28. Query Tuning Monitoring Query Performance • STATISTICS – Input/Output • Logical Reads • Physical Reads • Read Ahead Reads • Scan Count • STATISTICS - Timings • SHOWPLAN • Showplan_Text, Showplan_All, Graphical Showplan

  29. Query Tuning Query Hints • Query hints should be used for special cases—not as standard operating procedure • Hint Types: • Join Hints • Index Hints • Lock Hints • Processing Hints

  30. Query Tuning Blocking and Deadlocks – How to Resolve Them • Keep transactions as short as possible • Never add a pause within a transaction for user input • When you process a result set, process all rows as quickly as possible • For browsing applications, consider using cursors with optimistic concurrency control

  31. Query Tuning Deadlocks – How to Resolve Them • To prevent cycle deadlocks, make all processes access resources in a consistent order. • Reduce the transaction isolation level if it's suitable for the application. • To prevent conversion deadlocks, explicitly serialize access to a resource.

  32. Query tuningPerformance Tuning • A step-by-step approach • Gather information about the application’s behavior • Use SQL Profiler • Analyze the information • Query Analyzer • Index Tuning Wizard • Apply Changes • Index Tuning Wizard • Enterprise Manager

  33. Agenda • Locking • Query Processor • Query Tuning • System Configuration • Performance Monitoring

  34. System Configuration Resource Allocation and System File Location • Ensure that Maximize Data Throughput for Network Applications is selected • Do not locate SQL Server files on same drive as PAGEFILE.sys

  35. System Configuration Configuring SQL Resources • Sp_configure • EXEC sp_configure • RECONFIGURE • With override • Enterprise Manager

  36. System Configuration Configuring SQL Resources • Min Server Memory and Max Server Memory • Set Working Set Size • Minimum Query Memory

  37. System Configuration Configuring SQL Resources • Scheduling • Lightweight Pooling • Affinity mask • Priority boost • Max Worker Threads • Disk I/O Options

  38. System Configuration Configuring SQL Resources • Query Processing Options • Min Memory Per Query • Query Wait • Index Create Memory • Query Governor Cost Limit • Max Degree of Parallelism

  39. System Configuration Configuring SQL Resources • Database Options • Read Only • Single User • Autoclose • Autoshrink • Auto Create Statistics • Auto Update Statistics

  40. System Configuration Configuring SQL Resources • Buffer Manager • “Pintable” Option • Monitoring Performance • SQLPERF(WAITSTATS) • SQLPERF(LRUSTATS)

  41. Agenda • Locking • Query Processor • Query Tuning • System Configuration • Performance Monitoring

  42. Performance MonitoringHow to Use SQL Profiler • Graphical tool to monitor and collect server events • Step through problem queries to find the cause of the problem • Identify poorly-performing queries • Capture the series of SQL statements that lead to a problem • Use the saved traces to replicate problems on a test server where they can be diagnosed

  43. Performance MonitoringHow to UseSQL Profiler • Debug T-SQL or stored procedures • Monitor the performance of SQL Server to tune workloads • Capture deadlocking scenarios • Playback events captured

  44. Performance MonitoringSQL Profiler • Event Categories • Data Columns • Filters

  45. Performance MonitoringSystem Stored Procedures • SQL Trace • sp_trace_create • sp_trace_setevent • sp_trace_setfilter • sp_trace_setstatus • sp_trace_generateevent • SQLDIAG

  46. Performance MonitoringUsing System Monitor • Monitors Entire System Performance • System Counters • SQL Counters

  47. Performance MonitoringSystem Monitor • View data simultaneously from any number of computers • View and change charts to reflect current activity, and show counter values that are updated at a user-defined frequency • Export data from charts, logs, alert logs, and reports to spreadsheet or database applications for further manipulation and printing • Add system alerts that list an event in the alert log and can notify you by reverting to the Alert view or issuing a network alert

  48. Performance MonitoringSystem Monitor • Run a predefined application the first time or every time a counter value goes over or under a user-defined value • Create log files that contain data about various objects from different computers • Append to one file selected sections from other existing log files to form a long-term archive • View current-activity reports, or create reports from existing log files • Save individual chart, alert, log, or report settings, or the entire workspace setup for reuse when needed

  49. Performance MonitoringSystem Monitor - System Counters • System: • Context Switches/sec • Processor: • %Processor Time • %Privileged Time • %User Time • Processor Queue Length

  50. Performance MonitoringSystem Monitor - System Counters • SQL Server: Memory Manager: • Total Server Memory(KB) • Process: • Working Set Counter For SQL Server Instance • SQL Server Buffer Manager: • Buffer Cache Hit Ratio