1 / 32

Course Topics

Course Topics. 03 | Performance Optimization and Troubleshooting. George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United. Module 3 Overview. Locking, Blocking, and Deadlocks Profiler Auditing

Download Presentation

Course Topics

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. Course Topics

  2. 03 | Performance Optimization and Troubleshooting George Squillace | Senior Technical Trainer – New Horizons Great Lakes Richard Currey | Senior Technical Trainer – New Horizons United

  3. Module 3 Overview • Locking, Blocking, and Deadlocks • Profiler • Auditing • Catalog Views and DMOs • Data Collector and the Management Data Warehouse

  4. Topic: Locking, Blocking and Deadlocks

  5. Topic:Locking, Blocking, and Deadlocks • What Are Locking, Blocking, and Deadlocks? • How Are Locking Problems Discovered and Solved?

  6. What Are Locking, Blocking, and Deadlocks? • Resource locking is a data integrity mechanism that restricts access to various objects while under modification and not yet committed • Locking is natural and expected • Examples of lockable resources are: • Key values • Rows • Pages • Tables • Files • Databases • Blocking occurs when one connection is waiting for other connections to release locked resources • Some blocking is expected • Excessive blocking is undesirable • A deadlock occurs when two connections have locked resources that are seeking each other’s locked resources before their respective transactions complete; this results in the termination of one of the connections

  7. How Are Locking Problems Discovered and Solved? • Deadlock problems discovered by: • Client error messages • Activity monitor • DMOs • Profiler • Trace flags • Extended Events • Deadlock problems are solved by (OK, minimized by): • Order of objects accessed within queries / sprocs • Keeping transactions short • Isolation level • Avoiding user interaction (data browsing) during transactions

  8. Using Activity Monitor to Detect a Blocked Connection

  9. Topic: Profiler

  10. Topic: Profiler • What Is Tracing? • What Is Profiler?

  11. What Is Tracing? • Tracing permits the recording of a wide range of SQL Server activity (events within event classes) to files • Sampling of event classes and events • Class 1 • Event 1 • Event 2 • Class 2 • Event 3 • Event 4 • Based on the use of many system stored procedures

  12. What Is Profiler? • Graphical user interface into the tracing stored procedures • The “three legs” of the profiler configuration • Event classes and events • Columns • Filters • Integrate Windows Perfmon data with profiler data • Simple-Talk eBook on Profiler

  13. Using Profiler to Capture a Deadlock Graph

  14. Topic: Auditing

  15. Topic:Auditing • C2 and Common Criteria • Auditing with Triggers • SQL Auditing

  16. C2 and Common Criteria • Older methods • C2, sp_Configure with ‘C2 audit mode’ • Common criteria, sp_Configure with ‘common criteria compliance enabled’ • Much overhead associated with these methods

  17. Auditing with Triggers • DML triggers • Write to a designated audit table on the basis of modifications • DDL triggers • EventData() function • Login triggers • Trigger limitations

  18. SQL Auditing • What does this feature provide compared to other types of general auditing methods? • Based on extended events feature • Terminology • Server vs. database • What are the configuration steps?

  19. Configuring SQL Auditing

  20. Topic: Catalog Views and DMOs

  21. Topic: Catalog Views and DMOs • Purpose of catalog views and dynamic management objects (DMOs) • Classification of DMOs • Resources for further exploration

  22. Purpose of Catalog Views and DMOs • What is metadata retrieval? • Metadata retrieval methods that have been available in the past • GUI—obtain information on one object at a time • System Stored Procs—can’t really customize • System Functions—generally retrieve information one property at a time • In more recent versions • Catalog views and DMOs • Standard query language clauses • Can customize columns retrieved • Can customize rows retrieved • Can aggregate and order • Can join with multiple catalogs

  23. Classification of DMOs • DMO (general classification) • sys.dm_ prefix • Dynamic management views • No input accepted • sys.dm_ prefix • Dynamic management functions • Input accepted and often required • sys.dm_ prefix

  24. Resources for Further Exploration • Simple Talk eBook by Louis Davidson and Tim Ford • Tim Ford’s “Periodic Table of Dynamic Management Objects” presentation at PASS SQL Rally – May 2012 • The Redgate DMV Starter Pack • Quest Poster

  25. Querying Dynamic Management Objects

  26. Topic: Data Collector and the Management Data Warehouse

  27. Topic:Data Collector and the Management Data Warehouse • Overview of Data Collector • Configuring Data Collector • Consuming Data Collector Data

  28. Overview of Data Collector • Collects capacity planning and performance data over time • Problem: DMO data isn’t persistent • Fact: Troubleshooting is difficult when “normal” hasn’t been established • Solution: Management Data Warehouse (MDW) • The MDW is created and configured using two wizards • The wizard creates SSIS packages, jobs and schedules to collect performance planning and capacity data • The MDW accommodates multiple servers • Very useful reports available from the MDW database in SSMS

  29. Configuring Data Collector • Execute two wizards • Wizard 1: Create the Management Data Warehouse • Wizard 2: Set Up Data Collection • Data to be collected • Frequency of collection • Retention period • Specific database roles exist for management • mdw_Admin • mdw_writer • mdw_reader • Specific database roles exist for configuration • Dc_admin • Dc_operator • Dc_proxy

  30. Consuming Data Collector Data • Reports available in SSMS • Disk usage report • Server activity report • Query statistics report

  31. Configuring the Management Data Warehouse

More Related