Preface

Microsoft SQL Server 2012 Performance Tuning Cookbook is divided into three major parts—Performance Monitoring, Performance Tuning, and Performance Management—that are mandatory for dealing with performance in any capacity.

Microsoft 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 precautions 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 functions, SPs with DBCC commands, Resource Monitor, Reliability and Performance Monitor, and execution plans.

The second part of the book offers execution plan, dynamic management views and dynamic management functions, SQL Server Cache, stored procedure recompilations, indexes, important ways to write effective T-SQL, 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 about performance management with the help of policy based management and management with Resource Governor.

What this book covers

Chapter 1, SQL Server Profiler, teaches you how to create and start your first SQL Trace, limit the trace data and capture only the events which are of interest, detect slow running and expensive queries, and create a trace with system stored procedures.

Chapter 2, Tuning with Database Engine Tuning Advisor, covers how to analyze queries using Database Engine Tuning Advisor, how to run Database Engine Tuning Advisor for Workload, and how to execute Database Tuning Advisor from the command prompt.

Chapter 3, System Statistical Functions, System Stored Procedures, and DBCC SQLPERF Command, starts with the monitoring of system health using system statistical functions and later on covers the monitoring of SQL Server processes and sessions with system stored procedures, and log space usage statistics with the DBCC SQLPERF command.

Chapter 4, Resource Monitor and Performance Monitor, teaches you how to do quick monitoring of server performance, followed by monitoring of CPU and memory (RAM) usage.

Chapter 5, Monitoring with Execution Plans, includes recipes for working with Estimated Execution Plan and Actual Execution Plan, monitoring the performance of queries by SET SHOWPLAN_XML, SET STATISTICS XML, and SET STATISTICS IO, finding the execution time of a query by SET STATISTICS TIME, and including and understanding Client Statistics.

Chapter 6, Tuning with Execution Plans, explains the Hash, Merge, and Nested Loop Join strategies, teaches how to find table/index scans in execution plans and how to fix them, introduces Key Lookups, and explains how to find them in execution plans and resolve them.

Chapter 7, Dynamic Management Views and Dynamic Management Functions, includes recipes to monitor current query execution statistics, manage and monitor index performance, monitor the TempDB database's performance with database-related dynamic management views, and monitor disk I/O statistics.

Chapter 8, SQL Server Cache and Stored Procedure Recompilations, covers monitoring of compilations and recompilations at instance level, using Reliability and Performance Monitor, and monitoring of recompilations using SQL Server Profiler.

Chapter 9, Implementing Indexes, explains how to improve performance by creating a clustered index, by creating a non-clustered index, by covering index, by including columns in an index, by a filtered index, and by a columnstore index.

Chapter 10, Maintaining Indexes, includes recipes to find fragmentation, to enhance index efficiency by using the REBUILD and REORGANIZE index, to find missing and unused indexes, to enhance performance by creating indexed views and creating an index on Computed Columns, and to determine disk space consumed by indexes.

Chapter 11, Points to Consider While Writing Query, covers how to improve performance by limiting the number of columns and rows and by using sargable conditions, how to use arithmetic operators wisely in predicate to improve performance, how to improve query performance by not using functions on predicate columns, how to improve performance by Declarative Referential Integrity (DRI), and how to gain performance by trusting your foreign key.

Chapter 12, Statistics in SQL Server, explains how to create and update statistics, effects of statistics on non-key columns, how to find out-of-date statistics and correct them, and effects of statistics on a filtered index.

Chapter 13, Table and Index Partitioning, covers partitioning of table with RANGE LEFT and RANGE RIGHT, and deleting and loading of bulk data by splitting, merging, and switching partitions (sliding window).

Chapter 14, Implementing Physical Database Structure, includes recipes for configuring a data file and log file on multiple physical disks, using files and filegroups, moving an existing large table to a separate physical disk, moving non-clustered indexes to a separate physical disk, and configuring the TempDB database on a separate physical disk.

Chapter 15, Advanced Query Tuning: Hints and Plan Guides, includes recipes for using the NOLOCK table query hint, using the FORCESEEK and INDEX table hints, optimizing a query using an object plan guide, and implementing a fixed execution plan using a SQL plan guide.

Chapter 16, Dealing with Locking, Blocking, and Deadlocking, covers determining long-running transactions, detecting blocked and blocking queries, detecting deadlocks with SQL Server Profiler, and detecting deadlocks with Trace Flag 1204.

Chapter 17, Configuring SQL Server for Optimization, includes recipes for configuring SQL Server to use more processing power, configuring memory in 32-bit versus 64-bit, configuring "Optimize for Ad hoc Workloads", and optimizing SQL Server instance configuration.

Chapter 18, Policy Based Management, explains how to evaluate database properties and restrict database objects.

Chapter 19, Management with Resource Governor, includes recipes for configuring Resource Governor with SQL Server Management Studio and T-SQL script, and monitoring Resource Governor.

What you need for this book

To work with the examples given in the book, you must have the following infrastructure:

• SQL Server Denail CTP version 3 or higher, or SQL Server 2012 RTM

• The AdventureWorks2012 database, which can be freely downloaded from the following link: http://msftdbprodsamples.codeplex.com/releases/view/55330

• A Windows administrator login and/or a SQL server login with the sysAdmin privilege

Who this book is for

Microsoft SQL Server 2012 Performance Tuning Cookbook is aimed at SQL Server Database Developers, DBAs, and Database Architects who are working in any capacity to achieve optimal performance. Basic knowledge of SQL Server is expected, and professionals who want to get hands-on with performance tuning and have not worked on tuning the SQL Server for performance will find this book helpful.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "You may notice some TextData appearing multiple times in a trace for a single execution of a T-SQL statement."

A block of code is set as follows:

--creating table for demonstration
CREATE TABLE ordDemo (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO

Any command-line input or output is written as follows:

dta -D AdventureWorks2012 -s adventureworks2012FromDTA5 -S WIN-SLYJ9UY3PKD\DENALICTP3 -E -if D:\test.sql -F -of D:\DTA.sql

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Connect object explorer with server and move to Management | Policy Management | Policies".

Note

Warnings or important notes appear in a box like this.

Note

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[e-mail]>, and mention the book title through the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website, or added to any list of existing errata, under the Errata section of that title.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.