Chapter 14 dealt with query optimization which is a really cool topic! I've listened to one particular podcast probably 5 times about query optimization (podcast is SQL Down Under, and the guest is Grant Fritchey).
Lesson 1: Query optimization is exactly as it sounds, it's trying to find the most optimal way of executing code. The SQL optimizer estimates a cost on the hardware when it begins a transaction. The cost part is only one part of about five that the SQL engine will execute before returning the result set of the query. Here are the 5 pieces in order: T-SQL, Parsing, Binding, Optimization, Execution... google these or look at page 520 in the kit to understand these further. SQL has a few tools to help analyze queries, Extended Events, SQL Trace, and SQL Profiler. Note that SQL Trace, and Profiler will be deprecated in future versions of SQL, so it's better to use Extended Events.
Lesson 2: Talks about execution plans, and how to read graphical plans. Make sure to read them from right to left, and the top down. As I learned in the podcast it's important to remember that actual execution plans, and estimated execution plans, are still all only ESTIMATED.
Lesson 3: Was about dynamic management objects (DMOs). DMO's are another tool for finding queries that don't run optimally. There are more then 130 DMOs available in SS2012. Important DMO's to remember at ones that deal with SQL Server operating system, execution-related DMOs, and Index-related DMOs.
Credit: Querying Microsoft SQL Server 2012; Itzik Ben-Gan, Dejan Sarka, Ron Talmage
No comments:
Post a Comment