SQL Server Performance Killers CONINTUED
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 SECTION 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 LESSON.
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 SECTION 12, and execution plan analysis is covered in SECTION 14 and 15. We’ve added SECTION 16 just to talk about bad parameter sniffing and how best to deal with it.
Excessive blocking and deadlocks
Because SQL Server is fully atomicity, consistency, isolation, and durability (ACID) compliant, the database engine ensures that modifications made by concurrent transactions are properly isolated from one another. By default, a transaction sees the data either in the state before another concurrent transaction modified the data or after the other transaction completed—it does not see an intermediate state.
Because of this isolation, when multiple transactions try to access a common resource concurrently in a non compatible way, blocking occurs in the database. Two processes can’t update the same piece of data the same time. Further, since all the updates within SQL Server are founded on a page of data, 8KB worth of rows, you can see blocking occurring even when two processes aren’t updating the same row. Blocking is a good thing in terms of ensuring proper data storage and retrieval, but too much of it in the wrong place can slow you down.
Related to blocking, but actually a separate issue, a deadlock occurs when two resources attempt to escalate or expand locked resources and conflict with one another. The query engine determines which process is the least costly to roll back and chooses it as the deadlock victim. This requires that the database request be resubmitted for successful execution. Deadlocks are a fundamental performance problem even though many people think of them as a structural issue. The execution time of a query is adversely affected by the amount of blocking and deadlocks, if any, it faces.
For scalable performance of a multiuser database application, properly controlling the isolation levels and
transaction scopes of the queries to minimize blocking and deadlocks is critical; otherwise, the execution time of the queries will increase significantly, even though the hardware resources may be highly underutilized. We cover the problem in depth in SECTION 20 and 21.
Non-set-based operations, usually T-SQL cursors
Transact-SQL is a set-based scripting language, which means it operates on sets of data. This forces you to think in terms of columns rather than in terms of rows. Non-set-based thinking leads to excessive use of cursors and loops rather than exploring more efficient joins and subqueries. The T-SQL language offers rich mechanisms for manipulating sets of data. For performance to shine, you need to take advantage of these mechanisms rather than force a row-by-row approach to your code, which will kill performance. Examples of how to do this are available throughout the book; also, I address T-SQL best practices in Chapter 18 and cursors in SECTION 22.
Inappropriate database design
A database should be adequately normalized to increase the performance of data retrieval and reduce blocking. For example, if you have an undernormalized database with customer and order information in the same table, then the customer information will be repeated in all the order rows of the customer. This repetition of information in every row will increase the number of page reads required to fetch all the orders placed by a customer. At the same time, a data writer working on a customer’s order will reserve all the rows that include the customer information and thus could block all other data writers/data readers trying to access the customer profile.
Overnormalization of a database can be as bad as undernormalization. Overnormalization increases the number and complexity of joins required to retrieve data. An overnormalized database contains a large number of tables with a small number of columns. Overnormalization is not a problem I’ve run into a lot, but when I’ve seen it, it seriously impacts performance. It’s much more common to be dealing with undernormalization or improper normalization of your structures.
Having too many joins in a query may also be because database entities have not been partitioned distinctly or the
query is serving a complex set of requirements that could perhaps be better served by creating a new stored procedure. Database design is a large subject. I will provide a few pointers in Chapter 18 and throughout the rest of the boo
Excessive fragmentation
While analyzing data retrieval operations, you can usually assume that the data is organized in an orderly way, as indicated by the index used by the data retrieval operation. However, if the pages containing the data are fragmented in a nonorderly fashion or if they contain a small amount of data because of frequent page splits, then the number of read operations required by the data retrieval operation will be much higher than might otherwise be required. The increase in the number of read operations caused by fragmentation hurts query performance. In SECTION 13, you will learn how to analyze and remove fragmentation.
• Nonreusable execution plans
• Frequent recompilation of queries
• Improper use of cursors
• Improper configuration of the database transaction log
• Excessive use or improper configuration of tempdb