Andy Roberts

Query Performance Tuning

SQL Server query performance tuning and optimization requires a multilayered approach. The following are a few key factors that impact SQL Server query performance:

Managing Large Tables and Databases

Very large tables (where you have row counts in the tens of millions) have special needs. All data manipulations and maintenance operations need special considerations. This chapter will deal with features in SQL Server that can help; specifically, I’ll cover how partitioning a table can ease data movements and how the use of filegroups and data compression can help you improve performance by distributing data I/O across multiple drives and having a smaller amount of data to store on disk.

Getting Started with SELECT

The SELECT command is the cornerstone of the Transact-SQL language, allowing you to retrieve data from a SQL Server database (and more specifically from database objects within a SQL Server database). Although the full syntax of the SELECT statement is enormous, the basic syntax can be presented in a more boiled-down form:

Working with Numbers

SQL Server supports integer, decimal, and floating-point numbers. Working with numbers requires an understanding of the types available and what they are capable of. Implicit conversion rules sometimes lead to surprising results from seemingly simple-to-understand expressions. The recipes in this chapter show some of the more common operations as well as techniques for guarding against unexpected and unwanted results.

Working with Strings

Para>This next set of recipes demonstrates SQL Server’s string functions. String functions provide a multitude of uses for your Transact-SQL programming, allowing for string cleanup, conversion between ASCII and regular characters, pattern searches, removal of trailing blanks, and much more.

Principals and Users

Microsoft uses a set of terminology to describe SQL Server security functionality, which separates the security architecture into the following:

Advanced Select Techniques

It’s easy to return data from a table. What’s not so easy is to get the data you need how you need it, utilizing fast, set-based methods. This chapter will show you some of the advanced techniques that can be used when selecting data.

Querying from Multiple Tables

It is the rare database that has all its data in a single table. Data tends to be spread over multiple tables in ways that optimize storage and ensure consistency and integrity. Part of your job when writing a query is to deploy and link together T-SQL operations that can operate across tables in order to generate needed business results.

Aggregations and Windowing

In this chapter, we will look at several of the built-in functions that are frequently used when querying data for reporting purposes. We’ll start off with the aggregate functions in their nonwindowed form. We’ll then explore the windowing functions: aggregate functions, ranking functions, analytic functions, and the NEXT VALUE FOR sequence generation function.

Transactions, Locking, Blocking, and Deadlocking

In this chapter, I’ll review recipes for handling transactions, lock monitoring, blocking, and deadlocking. I’ll review the SQL Server table option that allows you to disable lock escalation or enable it for a partitioned table. I’ll demonstrate the snapshot isolation level, as well as Dynamic Management Views (DMVs) that are used to monitor and troubleshoot blocking and locking.