1 / 92

Enhancing SQL Server 2000 Performance: Locking, Query Tuning, and Monitoring

This session dives into optimizing SQL Server 2000 performance, focusing on locking mechanisms, query processing, and effective query tuning. Attendees will learn about various lock types, including shared and exclusive locks, and how they affect data concurrency. The agenda covers key topics like system configuration and performance monitoring, offering strategies for indexing, query hints, and resolving deadlocks. This training is designed for those with foundational knowledge in SQL Server and Windows Server, aiming to enhance database performance through best practices in query management and design.

sanne
Télécharger la présentation

Enhancing SQL Server 2000 Performance: Locking, Query Tuning, and Monitoring

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related