Summary

This one-day seminar is intended for database developers and administrators already familiar with basic Transact-SQL language and who want to upgrade this knowledge to the next step. The attendees learn to write more advanced queries and use some less known or overlooked, but useful, syntax elements and database objects. The seminar does not discuss performance improvements in general terms, such as “this is faster and this is slower”, but also quantifies the differences through measurements.

Abstract

How often did you read in an article or hear in a presentation that using a specific T-SQL syntax or SQL Server feature is better for the performance, but not how much better it is? You might have a hard time when you want to distinguish between excellent pieces of advice, which can improve the performance for magnitudes, and average or even useless pieces of advice when you can measure the improvement in fractions of a percent? This seminar is focused on quantification, on measurements of the impact of a specific feature in terms of locks, CPU and elapsed times, and disk reads and writes.

After this seminar, you will have a clear understanding of the impact of using specific language elements and engine features. In addition, you will get the code that is an infrastructure for your future measurements in your development, testing, and production environments. You will be able to evaluate the actual impact of your and developers’ decisions in advance, or even after implementation of those decisions in production.

You probably already know that you can improve the performance of your database server with the help of clustered and nonclustered indices. Again, you can find tons of resources online, in courses, in books, and in conference presentations explaining how to create the best indices, what is the statistics associated with an index, how row operations with cursors are slow in SQL Server, and similar. However, we have some useful, yet less-known elements in the T-SQL language, that can improve the performance of the queries as well.

Besides learning about overlooked language elements, you will also learn how can you optimize a query by rewriting it. When rewriting a query, you can take maximum advantage of your knowledge of the database engine and the T-SQL language. You will also see that many times, knowledge of mathematics can be extremely useful for writing efficient queries.

You might have been tempted to use data compression in SQL Server, but you were afraid of it because of the potential impact on updates of the data. Nevertheless, you or your developers might like to use GUIDs for keys in your database. Why don’t you join this seminar and see the actual impact of those features mentioned, and learn how to measure it in your database?

Prerequisites

To get the maximum from the seminar, it is recommended that the attendees already have working knowledge of basic T-SQL data manipulation language elements, specifically:

• SELECT
• FROM, WHERE, GROUP BY, HAVING, ORDER BY
• INSERT, UPDATE, DELETE

n addition, the delegates should understand the fundamentals of the data structures in SQL Server, namely heaps, clustered, and nonclustered indices. They should be capable of reading SQL Server execution plans as well.

• Introduction


• Less known T-SQL language elements
• Approximate aggregate functions
• Grouping sets
• String aggregation and splitting
• APPLY operator


• Working with procedures and optimizer hints
• Parameter sniffing
• Stricter contract with client applications
• Optimizer hints and plan guides


• Seldomly used optimization options
• Row and page compression
• Measuring the impact of the compression
• Measuring the impact of GUIDs
• Filtered indexes and indexed views


• Optimizing queries by rewriting the code
• Troubles with subqueries
• Using window functions for row-by-row operations
• Rewriting a query by using T-SQL knowledge
• Rewriting a query by using math knowledge


• Finding changed data
• DML triggers
• The OUTPUT clause
• Change Data Capture
• Change Tracking
• System-versioned tables
• The ROWVERSION data type