Professional Microsoft SQL Server 2014 Administration

Chapter 13Performance Tuning T-SQL

WHAT'S IN THIS CHAPTER ?

• Understanding query processing
• Reading query plans
• Using query operators
• Performance tuning in a production environment

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at www.wrox.com/go/prosql2014admin on the Download Code tab. The code is in the Chapter 13 download and individually named according to the names throughout the chapter.

Performance tuning on a SQL Server is the process of looking at T-SQL statements and identifying queries that don't return results in a timely manner. Performance tuning T-SQL involves some knowledge of the internal workings of SQL Server, as well as the syntax of the query being written. You must understand how individual statements are analyzed, and how SQL Server's compilation engine uses various resources (for example, indexes, statistics, query plans, data tables, and compilation steps) to help a user create T-SQL statements that are more efficient and process faster. The process of tuning T-SQL statements includes measuring actual performance, reviewing the query plans, and then modifying architecture and T-SQL statements to optimize the query performance. Sometimes a very simple change to a query plan can result in a substantially better-performing query. But understanding how to identify those changes can be tedious and frustrating unless you first understand how the SQL compilation engine handles individual parts of query statements. ...