Microsoft SQL Server 2012 Performance Tuning Cookbook
eBook - ePub

Microsoft SQL Server 2012 Performance Tuning Cookbook

  1. 478 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

Microsoft SQL Server 2012 Performance Tuning Cookbook

Book details
Book preview
Table of contents
Citations

About This Book

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.

Approach

This book has individual recipes and you can read it from cover to cover or dip into any recipe and get solution to a specific issue. Every recipe is based on a script/procedure explained with step-by-step instructions and screenshots. Concepts are illustrated for better understanding of why one solution performs better than another.

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.

Frequently asked questions

Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Yes, you can access Microsoft SQL Server 2012 Performance Tuning Cookbook by Ritesh Shah, Bihag Thaker in PDF and/or ePUB format, as well as other popular books in Computer Science & Databases. We have over one million books available in our catalogue for you to explore.

Information

Publisher
Packt Publishing
Year
2012
ISBN
9781849685740
Edition
1
Topic
Computer Science
Subtopic
Databases
Index
Computer Science

Microsoft SQL Server 2012 Performance Tuning Cookbook

Table of Contents

Microsoft SQL Server 2012 Performance Tuning Cookbook
Credits
About the Authors
Acknowledgement
Acknowledgement
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers and more
Why Subscribe?
Free Access for Packt account holders
Instant Updates on New Packt Books
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
1. Mastering SQL Trace Using Profiler
Introduction
Creating a trace or workload
Getting ready
How to do it...
How it works...
There's more...
Some background of SQL Trace
SQL Trace terms and concepts
SQL Trace
SQL Server Profiler
Event
Event class
Event category
Data column
Trace
Trace properties and Trace definition
Filter
Trace file
Trace table
Trace template
Architecture of SQL Trace
Trace and workload
Commonly-used event classes
Commonly-used data columns
Filtering events
Getting ready
How to do it...
How it works...
There's more...
Detecting slow running and expensive queries
Getting ready
How to do it...
How it works...
There's more...
Trace templates
Creating trace with system stored procedures
Getting ready
How to do it...
How it works...
2. Tuning with Database Engine Tuning Advisor
Introduction
Analyzing queries using Database Engine Tuning Advisor
Getting ready
How to do it...
How it works...
Running Database Engine Tuning Advisor for workload
Getting ready
How to do it...
How it works...
There's more...
Executing Database Tuning Advisor from command prompt
Getting ready
How to do it...
How it works...
There's more...
3. System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command
Introduction
Monitoring system health using system statistical functions
Getting ready
How to do it...
How it works...
There's more...
Monitoring with system stored procedure
Getting ready
How to do it...
How it works...
There's more...
Monitoring log space usage statistics with DBCC command
Getting ready
How to do it...
How it works...
There's more...
4. Resource Monitor and Performance Monitor
Introduction
Monitoring of server performance
Getting ready
How to do it...
How it works...
There's more...
Monitoring CPU usage
Getting ready
How to do it...
How it works...
Monitoring memory (RAM) usage
Getting ready
How to do it...
How it works...
5. Monitoring with Execution Plans
Introduction
Working with estimated execution plan
Getting ready
How to do it...
How it works...
Working with actual execution plan
Getting ready
How to do it...
How it works...
There's more...
Monitoring performance of a query by SET SHOWPLAN_XML
Getting ready
How to do it...
How it works...
Monitoring performance of a query by SET STATISTICS XML
Getting ready
How to do it...
How it works...
Monitoring performance of a query by SET STATISTICS IO
Getting ready
How to do it...
How it works...
There's more...
Monitoring performance of a query by SET STATISTICS TIME
Getting ready
How to do it...
How it works...
There's more...
Including and understanding client statistics
Getting ready
How to do it...
How it works...
There's more...
6. Tuning with Execution Plans
Introduction
Understanding Hash, Merge, and Nested Loop Join strategies
Getting ready
How to do it...
How it works...
There's more...
Finding table/index scans in execution plan and fixing them
Getting ready
How to do it...
How it works...
There's more...
Introducing Key Lookups, finding them in execution plans, and resolving them
Getting ready
How to do it...
How it works...
There's more...
7. Dynamic Management Views and Dynamic Management Functions
Introduction
Monitoring current query execution statistics
Getting ready
How to do it......
How it works...
There's more...
sys.dm_exec_connections (DMV)
sys.dm_exec_sessions (DMV)
sys.dm_exec_requests (DMV)
sys.dm_exec_sql_text (DMF)
sys.dm_exec_query_plan (DMF)
sys.dm_exec_cursors (DMF)
Monitoring index performance
Getting ready
How to do it...
How it works...
There's more...
sys.dm_db_missing_index_details (DMV)
sys.dm_db_missing_index_groups (DMV)
sys.dm_db_missing_index_group_stats (DMV)
sys.dm_db_index_usage_stats (DMV)
sys.dm_db_index_physical_stats (DMF)
Monitoring performance of TempDB database
Getting ready
How to do it...
How it works...
There's more...
sys.dm_db_session_space_usage (DMV)
sys.dm_db_file_space_usage (DMV)
Monitoring disk I/O statistics
Getting ready
How to do it...
How it works...
There's more...
dm_io_virtual_file_stats (DMF)
dm_io_pending_io_requests (DMV)
8. SQL Server Cache and Stored Procedure Recompilations
Introduction
Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor
Getting ready
How to do it...
How it works...
Monitoring recompilations using SQL Server Profiler
Getting ready
How to do it...
How it works...
There's more...
9. Implementing Indexes
Introduction
Increasing performance by creating a clustered index
Getting ready
How to do it...
How it works...
There's more...
Heap
Table and Index Scan/Seek
Increasing performance by creating a non-clustered index
Getting ready
How to do it...
How it works...
There's more...
Increasing performance by covering index
Getting ready
How to do it...
How it works...
There's more...
Increasing performance by including columns in an index
Getting ready
How to do it...
How it works...
Improving performance by a filtered index
Getting ready
How to do it...
How it works...
There's more...
Improving performance by a columnstore index
Getting ready
How to do it...
How it works...
There's more...
10. Maintaining Indexes
Introduction
Finding fragmentation
Getting ready
How to do it...
How it works...
There's more...
Playing with Fill Factor
Getting ...

Table of contents

  1. Microsoft SQL Server 2012 Performance Tuning Cookbook