Book Image

Microsoft SQL Server 2012 Performance Tuning Cookbook

Book Image

Microsoft SQL Server 2012 Performance Tuning Cookbook

Overview of this book

As a DBA you must have encountered a slow running application on SQL Server, but there are various factors that could be affecting the performance. If you find yourself in this situation, don't wait, pick up this book and start working towards improving performance of your SQL Server 2012. SQL Server 2012 Performance Tuning Cookbook is divided into three major parts -- Performance Monitoring, Performance Tuning, and Performance Management--that are mandatory to deal with performance in any capacity. SQL Server 2012 Performance Tuning Cookbook offers a great way to manage performance with effective, concise, and practical recipes. You will learn how to diagnose performance issues, fix them, and take precaution to avoid common mistakes. Each recipe given in this book is an individual task that will address different performance aspects to take your SQL Server's Performance to a higher level.The first part of this book covers Monitoring with SQL Server Profiler, DTA, System statistical function, SPs with DBCC commands, Resource Monitor & Reliability, and Performance Monitor and Execution Plan. The second part of the book offers Execution Plan, Dynamic Management Views, and Dynamic Management Functions, SQL Server Cache and Stored Procedure Recompilations, Indexes, Important ways to write effective TSQL, Statistics, Table and Index Partitioning, Advanced Query tuning with Query Hints and Plan Guide, Dealing with Locking, Blocking and Deadlocking and Configuring SQL Server for optimization to boost performance.The third and final part gives you knowledge of performance management with help of Policy Based Management and Management with Resource Governor.
Table of Contents (28 chapters)
Microsoft SQL Server 2012 Performance Tuning Cookbook
Credits
About the Authors
Acknowledgement
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
Index

Working with actual execution plan


The actual execution plan is one of the most important features provided by SQL Server, as it tells us how the query has actually performed. We can find the bottleneck in query, if any, and find out the way to resolve it.

Getting ready

In the previous recipe, Working with estimated execution plan, we have already discussed about the importance of estimated execution plan, which shows how your query should be processed, with the way it should get executed.

Most of the time, your query runs exactly in the same way as it has been mentioned in estimated execution plan. But, what if you find that your execution plan is perfect and still your query execution seems slow? Well, in that case the actual execution plan comes into picture.

Execute your query with actual execution plan and see whether it is following the ways we saw in estimated execution plan. In most cases, both the plans should be the same (except that the estimated execution plan is based on sampling...