Course Topics

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