Database Reference
In-Depth Information
Generally, indexes are considered to be the responsibility of the database administrator (DBA). However, the
DBA can't proactively define how to use the indexes, since the use of indexes is determined by the database queries
and stored procedures written by the developers. Therefore, defining the indexes must be a shared responsibility since
the developers usually have more knowledge of the data to be retrieved and the DBAs have a better understanding of
how indexes work. Indexes created without the knowledge of the queries serve little purpose.
Because indexes created without the knowledge of the queries serve little purpose, database developers need
to understand indexes at least as well as they know t-SQL.
Note
Inaccurate Statistics
SQL Server relies heavily on cost-based optimization, so accurate data distribution statistics are extremely important
for the effective use of indexes. Without accurate statistics, SQL Server's built-in query optimizer can't accurately
estimate the number of rows affected by a query. Because the amount of data to be retrieved from a table is highly
important in deciding how to optimize the query execution, the query optimizer is much less effective if the data
distribution statistics are not maintained accurately. Statistics can age without being updated. You can also see issues
around data being distributed in a skewed fashion hurting statistics. Statistics on columns that auto-increment such
as identity or date and time can be out of date as new data gets added. You will look at how to analyze statistics
in Chapter 12.
Improper Query Design
The effectiveness of indexes depends in large part on the way you write SQL queries. Retrieving excessively large
numbers of rows from a table or specifying a filter criterion that returns a larger result set from a table than is required
renders the indexes ineffective. To improve performance, you must ensure that the SQL queries are written to make
the best use of new or existing indexes. Failing to write cost-effective SQL queries may prevent SQL Server from
choosing proper indexes, which increases query execution time and database blocking. Chapter 20 covers how to
write effective queries.
Query design covers not only single queries but also sets of queries often used to implement database
functionalities such as a queue management among queue readers and writers. Even when the performance of
individual queries used in the design is fine, the overall performance of the database can be very poor. Resolving
this kind of bottleneck requires a broad understanding of different characteristics of SQL Server, which can affect
the performance of database functionalities. You will see how to design effective database functionality using SQL
queries throughout the topic.
Poorly Generated Execution Plans
The same mechanisms that allow SQL Server to establish an efficient stored procedure and reuse that procedure
again and again instead of recompiling can, in some cases, work against you. A bad execution plan can be a real
performance killer. Inaccurate and poorly performing plans are frequently caused when a process called parameter
sniffing goes bad . Parameter sniffing is a process that comes from the mechanisms that the query optimizer uses to
determine the best plan based on sampled or specific values from the statistics. It's important to understand how
statistics and parameters combine to create execution plans and what you can do to control them. Statistics are
covered in Chapter 12, and execution plan analysis is covered in Chapters 14 and 15. I've added Chapter 16 just to
talk about bad parameter sniffing and how best to deal with it.
 
 
Search WWH ::




Custom Search