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 ready -- How to do it... -- How it works... -- There's more... -- Enhance index efficiency by using the REBUILD index -- Getting ready -- How to do it... -- How it works... -- There's more... -- Enhance index efficiency by using the REORGANIZE index -- Getting ready -- How to do it... -- How it works... -- There's more... -- How to find missing indexes -- Getting ready -- How to do it... -- How it works... -- There's more... -- How to find unused indexes -- Getting ready -- How to do it... -- How it works... -- There's more... -- Enhancing performance by creating an indexed view -- Getting ready -- How to do it... -- How it works... -- There's more... -- Enhancing performance with index on Computed Columns -- Getting ready -- How to do it... -- How it works... -- Determining disk space consumed by indexes -- Getting ready -- How to do it... -- How it works... -- 11. Points to Consider While Writing Queries.

Introduction -- Improving performance by limiting the number of columns and rows -- Getting ready -- How to do it... -- How it works... -- See also -- Improving performance by using sargable conditions -- Getting ready -- How to do it... -- How it works... -- Using arithmetic operator wisely in predicate to improve performance -- Getting ready -- How to do it... -- How it works... -- Improving query performance by not using functions on predicate columns -- Getting ready -- How to do it... -- How it works... -- Improving performance by Declarative Referential Integrity (DRI) -- Getting ready -- How to do it... -- How it works... -- "Trust" your foreign key to gain performance -- Getting ready -- How to do it... -- How it works... -- There's more... -- 12. Statistics in SQL Server -- Introduction -- Creating and updating statistics -- Getting ready -- How to do it... -- How it works... -- There's more... -- Effects of statistics on non-key column -- Getting ready -- How to do it... -- How it works... -- Find out-of-date statistics and get it correct -- Getting ready -- How to do it... -- How it works... -- There's more... -- Effect of statistics on a filtered index -- Getting ready -- How to do it... -- How it works... -- 13. Table and Index Partitioning -- Introduction -- Partitioning a table with RANGE LEFT -- Getting ready -- How to do it... -- How it works... -- Partitioning a table with RANGE RIGHT -- Getting ready -- How to do it... -- How it works... -- Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window) -- Getting ready -- How to do it... -- How it works... -- There's more... -- 14. Implementing Physical Database Structure -- Introduction -- Configuring data file and log file on multiple physical disks -- Getting ready -- How to do it... -- How it works... -- Using files and filegroups.

Getting ready.