Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Microsoft SQL Server 2012 Performance Tuning Cookbook

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.

Arrow left icon
Product type Paperback
Published in Jul 2012
Publisher Packt
ISBN-13 9781849685740
Length 478 pages
Edition 1st Edition
Languages
Concepts
Arrow right icon
Toc

Table of Contents (28) Chapters Close

Microsoft SQL Server 2012 Performance Tuning Cookbook
Credits
About the Authors
Acknowledgement
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

Implementing a fixed execution plan using SQL plan guide


As plan guides can be used to specify query hints for SQL statements, they can also specify an execution plan instead of query hints to force an execution plan.

In this recipe, we first observe that even though there is a non-clustered index on ProductID column, some of the values of ProductID cause an index scan operation when data is retrieved. We will create an SQL plan guide so that all the queries as specified by the plan guide performs the index seek operation for every ProductID by forcing a previously saved execution plan.

Getting ready

We will see that ProductID 806 causes an index seek operation while ProductID 800 causes an index scan operation. We will generate an execution plan with an index seek operation and force that plan for the same type of queries so that for every ProductID an index seek is performed.

The following are the prerequisites for this recipe:

  • An instance of SQL Server 2012 Developer or Enterprise Evaluation...

lock icon The rest of the chapter is locked
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image