Free Sample
+ Collection

Microsoft SQL Server 2012 Performance Tuning Cookbook

Ritesh Shah, Bihag Thaker

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.
RRP $35.99
RRP $59.99
Print + eBook

Want this title & more?

$12.99 p/month

Subscribe to PacktLib

Enjoy full and instant access to over 2000 books and videos – you’ll find everything you need to stay ahead of the curve and make sure you can always get the job done.

Book Details

ISBN 139781849685740
Paperback478 pages

About This Book

  • Learn about the performance tuning needs for SQL Server 2012 with this book and ebook
  • Diagnose problems when they arise and employ tricks to prevent them
  • Explore various aspects that affect performance by following the clear recipes

Who This Book Is For

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. However, basic knowledge of SQL Server is expected, but 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.

Table of Contents

Chapter 1: Mastering SQL Trace Using Profiler
Creating a trace or workload
Filtering events
Detecting slow running and expensive queries
Creating trace with system stored procedures
Chapter 2: Tuning with Database Engine Tuning Advisor
Analyzing queries using Database Engine Tuning Advisor
Running Database Engine Tuning Advisor for workload
Executing Database Tuning Advisor from command prompt
Chapter 3: System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command
Monitoring system health using system statistical functions
Monitoring with system stored procedure
Monitoring log space usage statistics with DBCC command
Chapter 4: Resource Monitor and Performance Monitor
Monitoring of server performance
Monitoring CPU usage
Monitoring memory (RAM) usage
Chapter 5: Monitoring with Execution Plans
Working with estimated execution plan
Working with actual execution plan
Monitoring performance of a query by SET SHOWPLAN_XML
Monitoring performance of a query by SET STATISTICS XML
Monitoring performance of a query by SET STATISTICS IO
Monitoring performance of a query by SET STATISTICS TIME
Including and understanding client statistics
Chapter 6: Tuning with Execution Plans
Understanding Hash, Merge, and Nested Loop Join strategies
Finding table/index scans in execution plan and fixing them
Introducing Key Lookups, finding them in execution plans, and resolving them
Chapter 7: Dynamic Management Views and Dynamic Management Functions
Monitoring current query execution statistics
Monitoring index performance
Monitoring performance of TempDB database
Monitoring disk I/O statistics
Chapter 8: SQL Server Cache and Stored Procedure Recompilations
Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor
Monitoring recompilations using SQL Server Profiler
Chapter 9: Implementing Indexes
Increasing performance by creating a clustered index
Increasing performance by creating a non-clustered index
Increasing performance by covering index
Increasing performance by including columns in an index
Improving performance by a filtered index
Improving performance by a columnstore index
Chapter 10: Maintaining Indexes
Finding fragmentation
Playing with Fill Factor
Enhance index efficiency by using the REBUILD index
Enhance index efficiency by using the REORGANIZE index
How to find missing indexes
How to find unused indexes
Enhancing performance by creating an indexed view
Enhancing performance with index on Computed Columns
Determining disk space consumed by indexes
Chapter 11: Points to Consider While Writing Queries
Improving performance by limiting the number of columns and rows
Improving performance by using sargable conditions
Using arithmetic operator wisely in predicate to improve performance
Improving query performance by not using functions on predicate columns
Improving performance by Declarative Referential Integrity (DRI)
"Trust" your foreign key to gain performance
Chapter 12: Statistics in SQL Server
Creating and updating statistics
Effects of statistics on non-key column
Find out-of-date statistics and get it correct
Effect of statistics on a filtered index
Chapter 13: Table and Index Partitioning
Partitioning a table with RANGE LEFT
Partitioning a table with RANGE RIGHT
Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)
Chapter 14: Implementing Physical Database Structure
Configuring data file and log file on multiple physical disks
Using files and filegroups
Moving the existing large table to separate physical disk
Moving non-clustered indexes on separate physical disk
Configuring the tempdb database on separate physical disk
Chapter 15: Advanced Query Tuning Hints and Plan Guides
Using NOLOCK table query hint
Using FORCESEEK and INDEX table hint
Optimizing a query using an object plan guide
Implementing a fixed execution plan using SQL plan guide
Chapter 16: Dealing with Locking, Blocking, and Deadlocking
Determining long-running transactions
Detecting blocked and blocking queries
Detecting deadlocks with SQL Server Profiler
Detecting deadlocks with Trace Flag 1204
Chapter 17: Configuring SQL Server for Optimization
Configuring SQL Server to use more processing power
Configuring memory in 32 bit versus. 64 bit
Configuring "Optimize for Ad hoc Workloads"
Optimizing SQL Server instance configuration
Chapter 18: Policy-based Management
Evaluating database properties
Restricting database objects
Chapter 19: Resource Management with Resource Governor
Configuring Resource Governor with SQL Server Management Studio
Configuring Resource Governor with T-SQL script
Monitoring Resource Governor

What You Will Learn

  • Monitoring SQL Server Performance with Profiler, Execution Plan, and system statistical function
  • Finding performance bottleneck with the help of Resource Monitor and DTA
  • Creating and Managing Indexes efficiently to boost performance
  • Creating and Managing Partition efficiently
  • Managing database files efficiently
  • Planning Guide and Query Hints
  • Dealing with locking, blocking, and deadlocking
  • Configuring SQL Server instance option for achieving performance benefit
  • Managing Performance with Policy based management and Resource Governor

In Detail

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.


Read More