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

Configuring "Optimize for Ad hoc Workloads"

Execution of any query or stored procedure for the first time creates an execution plan, which is stored in SQL Server 's procedure cache memory. It happens many times that we execute a simple query once, which is not even going to be used again anytime soon and it may never run again in future too even execution plan generated for that query will consume space in procedure cache. You may run out of cache sometimes, due to lack of memory, which affects performance. This was really a big issue till SQL Server 2005. In order to remedy this, Microsoft introduced "Optimize for Ad hoc Workloads" in SQL Server 2008, and it is still available in SQL Server 2012. This setting is instance-wide in SQL Server.

Note

In one of my performance tuning consultation projects, I had observed the company's SQL developer making and testing a query directly on the production server. If they didn't get the required results, they'd change the query and re-test it on 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

Configuring "Optimize for Ad hoc Workloads"

Note

Unlock this book and the full library FREE for 7 days

Personalised recommendations for you