Packt+ | Advance your knowledge in tech

You're reading from Microsoft SQL Server 2012 Performance Tuning Cookbook With this book you'll learn all you need to know about performance monitoring, tuning, and management for SQL Server 2012. Includes a host of recipes and screenshots to help you say goodbye to slow running applications.

Product type Paperback

Published in Jul 2012

Publisher Packt

ISBN-13 9781849685740

Length 478 pages

Edition 1st Edition

Languages

Tools

SQL Server

Concepts

Servers

Table of Contents (28) Chapters

Microsoft SQL Server 2012 Performance Tuning Cookbook

Credits

About the Authors

Acknowledgement

About the Reviewers

www.PacktPub.com

1. Preface

1. Mastering SQL Trace Using Profiler FREE CHAPTER

2. Tuning with Database Engine Tuning Advisor

3. System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command

4. Resource Monitor and Performance Monitor

5. Monitoring with Execution Plans

6. Tuning with Execution Plans

7. Dynamic Management Views and Dynamic Management Functions

8. SQL Server Cache and Stored Procedure Recompilations

9. Implementing Indexes

10. Maintaining Indexes

11. Points to Consider While Writing Queries

12. Statistics in SQL Server

13. Table and Index Partitioning

14. Implementing Physical Database Structure

15. Advanced Query Tuning Hints and Plan Guides

16. Dealing with Locking, Blocking, and Deadlocking

17. Configuring SQL Server for Optimization

18. Policy-based Management

19. Resource Management with Resource Governor

Index

Detecting deadlocks with Trace Flag 1204

In the previous recipe, Detecting deadlocks with SQL Server Profiler, we learned to detect deadlocks using SQL Server Profiler. This can be useful when deadlocks occur regularly in a specific pattern and you are able to reproduce them by executing certain part of application code that you know produces the deadlock. For this type of investigation, you simply start an SQL Trace session, reproduce the deadlock condition, and analyze the queries.

However, when deadlocks occur irregularly, without any specific pattern, it becomes hard for you to investigate them because you do not know in which case they occur. Thus, it also becomes difficult for you to reproduce them. To troubleshoot such irregular deadlocks, you might prefer not to keep a trace session running and wait for deadlocks to occur for hours.

This recipe will show you how you can configure SQL Server so that whenever deadlocks occur, SQL Server logs the deadlock-related information into the...

The rest of the chapter is locked

You're reading from Microsoft SQL Server 2012 Performance Tuning Cookbook With this book you'll learn all you need to know about performance monitoring, tuning, and management for SQL Server 2012. Includes a host of recipes and screenshots to help you say goodbye to slow running applications.

Table of Contents (28) Chapters

Detecting deadlocks with Trace Flag 1204

Unlock this book and the full library FREE for 7 days

Personalised recommendations for you